VBA Random Number

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.

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.”

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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 Range
Set rng = Range(“A1:A10”) ‘ Change to desired range
Randomize
For Each cell In rng
cell.Value = Int((100 * Rnd) + 1)
Next cell
End 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 Double
Randomize 42 ‘ Seed the random number generator with a specific value
randomNumber = Rnd(0) ‘ Generate a random number between 0 and 1 using a fixed seed
MsgBox “Random Number: ” & randomNumber
End 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 generator
Dim randomWholeNumber As Double
randomWholeNumber = CDbl(Rnd) * (9999999999# – 1000000000) + 1000000000
MsgBox “Random Whole Number: ” & randomWholeNumber
End Sub

Important Things To Note

1. Randomize initializes the VBA random number generator, and Rnd often follows it to generate random numbers.
2. Using a specific seed value with Randomize ensures the same sequence of random numbers every time.
3. VBA Random Numbers in the range offers functions to generate VBA random numbers within a specified range.
4. In VBA Array random numbers, the process involves utilizing loops and the Rnd function to fill an array with randomly generated values.
5. 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)

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 –

• VBA DateValue
• VBA UCase
• VBA Timer