## What are VBA Random Numbers?

VBA random numbers are numeric values generated using the VBA programming language. These VBA random numbers are pseudo-random, generated using mathematical algorithms based on a seed value. While not truly random, they exhibit properties that resemble randomness and can be used for various purposes within programming.

Let us look at an example where we showcase how to generate a VBA random number between 1 and 10. The process involves initializing the VBA random number generator with the **Randomize** statement to ensure varied sequences of VBA random numbers. By multiplying the **Rnd** function’s output (ranging from 0 to 1) by ten and adding 1, we achieve a VBA random number within the desired range.

Once the macro is executed, a message box displays the generated VBA random numbers.

##### Table of contents

###### Key Takeaways

- We utilize the Rnd function with arithmetic operations and Randomize to generate VBA random numbers within specific ranges.
- When utilizing the VBA Fill Array to add random numbers to a range, the Randomize statement ensures the random number generator is initialized effectively.
- Within the scope of the VBA random number from list, the calculated integer index derived from Rnd is a reliable means to access and retrieve random elements from a given list.
- Modify the output of the Rnd function with arithmetic operations to adjust the range, scaling, and shifting of the generated VBA random numbers to match your requirements.

### How to Generate Random Numbers using VBA Code?

Generating VBA random numbers involves utilizing built-in functions like Rnd and Randomize. Here’s a step-by-step guide:

**Step 1:** Open the VBA editor in your Microsoft Office application (e.g., Excel) using ALT + F11.

**Step 2:** Inside the VBA editor, add a module by selecting “**Insert**” from the menu and choosing “**Module.”**

**Step 3:** Within the module, create VBA code for generating random numbers using functions like “Rnd.” Define the range and calculations as required.

**Step 4:** Begin your code with “Randomize” to initialize the VBA random number generator, ensuring varied results.

**Step 5:** Modify the “Rnd” function’s output using arithmetic operations to match your desired range and constraints.

**Step 6:** Determine how to use the VBA random numbers. Options include:

- Displaying through message boxes.
- Printing to a worksheet.
- Storing in an array.
- Applying within your application.

**Step 7:** Close the VBA editor and execute your macro by pressing ALT + F8. Choose the macro’s name and click “Run.”

### Examples

Let us look at some examples of generating a random number in VBA.

#### Example #1 – Paste in Same Excel Worksheet:

In this example, we will fill a specified range with random whole numbers between 1 and 100, providing a practical approach to populate cells with random data.

**Step 1:** In the new module, start with a sub-procedure named “PasteRandomNumbers.”

**Step 2:** Here, a Range object named “rng” is declared to represent the range where random numbers will be posted.

**Step 3:** Next, the Randomize statement initializes the random number generator, ensuring that the generated numbers are not the same in each execution.

**Step 4:** Now, loop through the cells in the range. This loop iterates through each cell in the specified range.

**Step 5:** This line generates a random number using the Rnd function within the loop. It multiplies the value returned by Rnd (between 0 and 1) by 100 and then adds 1 to ensure that the result is between 1 and 100.

The “Int” function rounds down the result to an integer. The generated random number is then assigned to the current cell’s Value property.

**Step 6:** The loop iterates through each cell in the range, generating and pasting random numbers.

**Step 7:** Save the macro and exit the VBE. Now press Alt + F8 to open the macro window and select “PasteRandomNumbers” and click on run.

**Step 8:** Once you execute the code you will see that the VBA random numbers are generated in the excel worksheet in the cell range from A1 to A10.

**Here is the full code:**

**Sub PasteRandomNumbers()Dim rng As RangeSet rng = Range(“A1:A10”) ‘ Change to desired rangeRandomizeFor Each cell In rngcell.Value = Int((100 * Rnd) + 1)Next cellEnd Sub**

#### Example #2 – Get the Same Random Number Every Time:

In this example, a fixed seed value 42 generates a consistent random number between 0 and 1 using the Rnd function, ensuring the same result with each execution.

**Step 1:** In the new module, create a sub-procedure named “SameRandomNumber.”

**Step 2:** Here, we declare a variable named randomNumber. This variable will store the generated random number.

**Step 3:** Next, the Randomize 42 line initializes the random number generator with a specific seed value of 42. This seed value is used to ensure that the sequence of random numbers remains the same on each execution of the code.

**Step 4:** The line randomNumber = Rnd(0) uses the Rnd function with an argument of 0. This argument indicates that the random number should be generated using a fixed seed, which was set with the Randomize statement.

This means that the same random number will be generated every time the code is executed.

**Step 5:** The last line, MsgBox “Random Number: ” & randomNumber, displays a message box containing the text “Random Number: ” followed by the value of the randomNumber variable.

It is the random number generated based on the fixed seed value 42.

**Step 6**: Now, save the macro and click on run. Once you run the code, you will see a message box with a random number. But when you execute the same code again you will see the same random number every time by using a fixed seed value with the “Rnd” function.

**Here is the full code:**

**Sub SameRandomNumber()Dim randomNumber As DoubleRandomize 42 ‘ Seed the random number generator with a specific valuerandomNumber = Rnd(0) ‘ Generate a random number between 0 and 1 using a fixed seedMsgBox “Random Number: ” & randomNumberEnd Sub**

#### Example #3 – Generate Whole Random Number:

In this example, the random number generator is initialized, and a random whole number between 1000000000 and 9999999999 is generated using the Rnd function. The result is displayed through a message box.

**Step 1:** In the new macro, create a sub-procedure named GenerateWholeRandom.

**Step 2:** Next, we initialize the Random Number Generator for generating random numbers.

**Step 3:** Here, a variable “randomWholeNumber” is declared to store the generated random number. The CDbl function converts the random number generated by the “Rnd” function into a double-precision floating-point number.

The calculation ensures that the generated number is between 1000000000 and 9999999999.

**Step 4:** Finally, we use “MsgBox “Random Whole Number: ” & randomWholeNumber.” to display the VBA random number in a message box.

**Step 5:** Save the macro and click on run. Once you execute the macro, the output of the code will be a message box displaying a random whole number between 1000000000 and 9999999999.

**Here is the full code:**

**Sub GenerateWholeRandom()Randomize ‘ Initialize the random number generatorDim randomWholeNumber As DoublerandomWholeNumber = CDbl(Rnd) * (9999999999# – 1000000000) + 1000000000MsgBox “Random Whole Number: ” & randomWholeNumberEnd Sub**

### Important Things To Note

- Randomize initializes the VBA random number generator, and Rnd often follows it to generate random numbers.
- Using a specific seed value with Randomize ensures the same sequence of random numbers every time.
- VBA Random Numbers in the range offers functions to generate VBA random numbers within a specified range.
- In VBA Array random numbers, the process involves utilizing loops and the Rnd function to fill an array with randomly generated values.
- Test your VBA random number generation thoroughly to ensure that the generated values meet your expectations and validate the approach to ensure it aligns with your application’s needs.

### Frequently Asked Questions (FAQs)

**1. What is the range of random number in VBA?**

The default range of VBA random numbers generated by the Rnd function is between 0 and 1. However, using arithmetic operations to scale and shift the generated values according to your desired range, you can manipulate this range.

**2. How do you repeat random numbers in VBA?**

To repeat VBA random numbers, you can use the Randomize statement to initialize the random number generator with a specific seed value. It will ensure that the sequence of VBA random numbers remains consistent across multiple executions of your code.

**3. Why is random numbers in VBA not working?**

If VBA random numbers are not working as expected, it could be due to several reasons:

• **Incorrect usage of the Randomize statement:** Ensure it’s placed before generating random numbers.**•** **Uninitialized random number generator:** Always initialize with Randomize or a fixed seed.**•** **Misuse of data types:** Ensure the data type can handle the range and calculations.**•** **Repetitive seed values**: Using the same seed value with Randomize will result in the same sequence of random numbers.**•** **Incorrect formulae:** Double-check your arithmetic operations for scaling and shifting the random numbers.

**Download Template**

This article must be helpful to understand the **VBA Random Numbers**, with its formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to **VBA Random Numbers**. Here we learn to generate random numbers in excel using VBA code, with examples and points to remember. You may learn more from the following articles –

## Leave a Reply