Randomize Statement in VBA
VBA Randomize statement is a simple one-liner statement that we add before applying the RND function. Whenever a workbook is reopened Randomize statement provides a new seed number to the RND function depending upon the computer’s system time.
Before I talk about the Randomize statement let me introduce you to a simple RND function with VBA.
As a worksheet function “RAND”, in VBA “RND” too will generate random numbers which are greater than 0 but less than 1.
Now take a look at the syntax of the “RND” function.
[Number]: We can pass the argument in three ways.
- If we pass the number as <0, it keeps generating the same random number every time.
- If we pass the number as 0, it will repeat the most recent number it has given.
- If we pass the number >0, it keeps giving you different random numbers i.e. the next random number in the sequence.
For an example look at the below code.
Sub RND_Example() Debug.Print Rnd End Sub
When I run the code in the immediate window I can see below number.
Similarly, when I execute this code for 3 more times I can see the below numbers.
Now I will close the workbook and reopen it.
Now I will go back to the visual basic editor window.
Now the immediate window is empty and clean.
Now again I will execute the code four times and see what are the numbers we will get in the immediate window.
We got the same numbers as we got above.
This doesn’t look like a random number because every time we reopen the file we tend to get the same numbers starting from scratch.
So, how do we generate random numbers irrespective of whether the workbook reopened or not?
We need to use the “Randomize” statement.
How to Use VBA Randomize Statement?
To get random numbers all we need to do is to add the simple one-liner “Randomize” before the RND function.
Sub Randomize_1() Randomize Debug.Print Rnd End Sub
Now I will run the code 4 times and see what I get.
It has generated the above numbers in my local window.
Now I will close the file and reopen the file once again.
As usual, we start with a clean slate in the visual basic window.
Now I will again execute the code and see what numbers we get this time.
Wow!!! We got different numbers this time around.
Since we added the statement Randomize before the RND function we get different random numbers every time we reopen the file.
This looks like a random number isn’t it???
Random Numbers Greater Than One
As we have seen “RND” function can generate numbers from 0 to 1 only. But in order to generate numbers greater than one random number we need to use “RANDOM BETWEEN” which is available with worksheet function class.
So, to generate random numbers greater than one we need to use the below code.
Sub Randomize_2() Randomize Debug.Print Rnd * 100 End Sub
Now I will execute the code and see what we get.
Like this, we can use the “Randomize” statement in VBA to generate random numbers every single time we reopen the excel file.
This has been a guide to VBA Randomize. Here we discuss how to use randomize statements in VBA to generate random numbers greater than one with example and downloadable excel template. You can learn more about VBA from the following articles –