I’ve always found Excel to be a powerhouse for sorting data and performing complex calculations. But sometimes, what you need is a bit of randomness – whether it’s for running simulations, generating data for testing purposes, or just for an element of surprise. That’s where VBA comes into play, enabling you to create random numbers right within Excel. It’s fascinating because, in the realm of programming with VBA, randomness isn’t just a party trick; it can be a crucial part of a robust spreadsheet solution.
Delving into Excel’s capabilities, I’ve realized that the Developer tab is your gateway to crafting these random pieces of data through coding in VBA. The process isn’t just hitting a magic button; it’s about understanding properties and macros that work behind the scenes. With a few lines of code, you can turn your practice workbook into an advanced Excel exercise, testing out various scenarios with just a flick of the Randomize function. Whether you’re an Excel enthusiast or a developer looking to sharpen your skills, mastering random number generation in VBA is both useful and quite enjoyable.
RND Function
When I need to generate random numbers in VBA, I turn to the RND
function. With just a simple call to Rnd()
, it gives me a random number between 0 and 1. Here’s how it works in practice:
Range("A1") = Rnd() ' Puts a random number in cell A1
However, this approach has a catch. If I use RND
for a range of cells, it repeats the same random number across all cells, which isn’t what I want if I need a set of unique random numbers.
To overcome this, I make use of a For Next Loop. This little trick loops through each cell and assigns a unique random number to each:
Sub vba_random_number()
Dim i As Long
For i = 1 To 10
ActiveCell.Value = Rnd()
ActiveCell.Offset(1, 0).Select
Next i
End Sub
Above is a complete module I use when I need to fill 10 cells with individual random numbers. Neat, right?
Now, a couple of other points to know about Rnd()
:
 No Arguments Needed:
Rnd()
operates without any arguments. However, if you want to, the optional seed parameter can tweak the starting point of the pseudorandom sequence.  Randomize for Freshness: To ensure fresh random numbers each time, I precede
Rnd()
with theRandomize
statement. It prevents the same sequence of numbers from repeating each session.  Return Values: Always gets me a single floatingpoint value, and that’s between 0 (inclusive) and 1 (exclusive).
So, Rnd()
is like the cool kid of the pseudorandom number generating world in VBA—it’s easy to use and fairly reliable for most casual needs, as long as I remember its quirks.
Random Number Between Two Numbers
In VBA, generating a random number within specific upper and lower bounds can be accomplished with a blend of the Rnd
and Int
functions. Here’s my method that doesn’t involve loops for uniquest numbers but is simple for generating a single random integer:
Sub vba_random_number()
Dim upperbound As Integer
Dim lowerbound As Integer
Dim myRnd As Integer
upperbound = 45 ' Set the upper bound
lowerbound = 2 ' Set the lower bound
myRnd = Int((upperbound  lowerbound + 1) * Rnd + lowerbound)
Range("A1") = myRnd ' Output the random number
End Sub
 Upper Bound (upperbound): The maximum value; set it to 45 in this case.
 Lower Bound (lowerbound): The minimum value; set it to 2 here.
What really happens is that Rnd
generates a decimal number between 0 and 1, which when multiplied by the range between upperbound and lowerbound, results in a decimal number between those two limits. The Int
function then trims off the decimal part, ensuring the result is a whole number.
Using Worksheet Functions in a VBA Code to Generate a Random Number
When I’m working in VBA, I like to harness the power of worksheet functions. Sometimes, I need to generate random numbers, and Excel has got some nifty functions for this. Let’s talk about how I use them.

RandBetween
: This function is pretty straightforward. Suppose I want a random number between 1000 and 2000. In the worksheet, I’d simply write:Range("A1") = WorksheetFunction.RandBetween(1000, 2000)
Here, A1 is set to a random number where 2000 is the maximum and 1000 is the minimum.

RandArray
: This is a step into the dynamic realm of arrays. It lets me fill an entire range with random numbers in a snap. For example:Range("A1:B10") = WorksheetFunction.RandArray(10, 2, 1000, 2000, True)
In the above code, I fill cells A1 through B10 with random numbers between 1000 and 2000. Nifty, right?
What’s cool about RANDARRAY
is its flexibility. Unlike RandBetween
, it lets me spill random numbers across a range of cells with just one line of code. And let me tell you, it’s a timesaver.