Prantick Bala
VBA Randomize function is an inbuilt Math or Trigonometry function of Excel which allows you to change the seed value used by the random number generator of the Rnd function. If you don’t use the Randomize function before using the Rnd function, the Rnd function uses the same number as a seed value when it is applied for the first time. So, the Rnd function will generate the same number as the first time when you will use the function for the second time. But If you use the Randomize function before the Rnd function, the seed will be different from the first time. As a result, you will get a random number every time you use the Rnd function
The image above gives a general overview of the VBA Randomize function. Throughout the article, you will get to know more details and applications of the function.
📂 Download Practice Workbook
Introduction to the VBA Randomize Function
❑ Objective
The Randomize function or statement initializes the random number generator of the Rnd function.
❑ Syntax
Randomize([Number])
❑ Argument Explanation
| Argument | Required/Optional | Explanation |
|---|---|---|
| Number | Optional | Supply the seed value for the Rnd function. If this argument is omitted, the system timer’s value is applied as the new seed value. |
❑ Output
VBA Randomize function doesn’t return any output. It sets the seed value for the Rnd function.
❑ Version
This function is available from Excel 2000. So you can use this VBA function in any newer version of Excel.
Examples of Using the VBA Randomize Function in Excel
Now, let’s see some examples of how we can use the VBA Randomize function in Excel.
1. Get New Random Number Every Time Using VBA Randomize
The main application of the VBA Randomize function is to supply a new seed for the Rnd function. So, we can generate random numbers every time we use the Rnd function by using the Randomize function before it. First,
➤ Press ALT+F11 to open the VBA window.
➤ Go to the Insert tab of the VBA window and select Module.
It will open the Module(Code) window. Now,
➤ Type the following code in the Module(Code) window,
Sub VBA_Randomize()
Randomize
Debug.Print Rnd
End SubThe code will give a random number every time you run the code.
Now, to view the output of the code, you have to open the Immediate Window.
➤ Go to the View tab and click on the Immediate Window.
It will open the Immediate Window.
At this time,
➤ Go back to the Module(Code) window and press F5.
As a result, you will see a number will be added in the Immediate Window.
Every time you press F5 a new number will be added in the Immediate Window.
➤ Press F5, 3 more times.
As a result, you will get a total of 4 numbers in the Immediate Window.
➤ Delete the codes and Close the VBA window.
Now, If you repeat the same procedures, you will see this time you will get 4 different random numbers. For this time the Randomize function has supplied a different seed value, as a result, the Rnd function has generated different numbers.
If you observe you can see all the numbers in this example lie between 0 to 1. This is because the Rnd Function returns numbers between 0 to 1. In the following example, I’ll show you how you can generate a number greater than 1.
Read More: Excel Formula to Generate Random Number (5 examples)
2. VBA Randomize to Get Random Number Greater Than 1
To get random numbers greater than 1,
➤ Type the following code in the Module(Code) window,
Sub VBA_Randomize()
Randomize
Debug.Print Rnd*10
End SubThe code will generate a random number between 1 to 10.
After that,
➤ Press F5.
As a result, a random number between 1 to 10 will be added in the Immediate Window.
Now, with each F5 press, a new random number will be added in the Immediate Window.
Here, I’ve pressed F5 3 more times after the first time, so total of 4 numbers are showing in the Immediate Window.
To get random numbers between 1 to 100,
➤ Type the following code in the Module(Code) window,
Sub VBA_Randomize()
Randomize
Debug.Print Rnd*100
End SubThe code will generate a random number between 1 to 100.
After that,
➤ Press F5.
As a result, a random number between 1 to 100 will be added in the Immediate Window.
➤ Press F5 three more times, to add 3 new random numbers between 1 to 100.
Read More: How to Use VBA Rnd in Excel (4 Methods)
3. Get Random Integer with VBA Randomize Function
In the previous examples, all the numbers were decimal numbers. Now, I’ll show how you can get random Integer numbers.
➤ Type the following code in the Module(Code) window,
Sub VBA_Randomize()
Dim Random_num As Integer
Randomize
Random_num = Int(Rnd * 100)
Debug.Print Random_num
End SubHere, Instead of directly printing the output, first I’ve defined the output as Random_num and then printed Random_num. The code will print an integer in the Immediate Window after pressing F5.
➤ Press F5.
As a result, a random integer between 1 to 100 will be added in the Immediate Window.
Now, with each F5 press, a new random integer will be added in the Immediate Window.
Here, I’ve pressed F5 3 more times after the first time, so a total of 4 numbers are showing in the Immediate Window.
Read More: How to Use VBA Int Function in Excel ( 3 Examples)
Similar Readings:
- How to Use VBA LTrim Function in Excel (4 Examples)
- Use Log Function in Excel VBA (5 Suitable Examples)
- How to Use VBA Abs Function in Excel (9 Examples)
- Use VBA Val Function in Excel (7 Examples)
- How to Use VBA Round Function in Excel (6 Quick Uses)
4. Get Random Integer in Between Any Two Numbers
To get a random integer between two numbers 1200 and 1500,
➤ Type the following code in the Module(Code) window,
Sub VBA_Randomize()
Randomize
Debug.Print Int ((1500-1200+1)*Rnd+1200)
End SubThe code will give a random integer between 1200 to 1500 every time you run the code.
➤ Press F5.
As a result, a random integer between 1200 to 1500 will be added in the Immediate Window.
Now, with each F5 press, a new random integer between 1200 and 1500 will be added in the Immediate Window.
Here, I’ve pressed F5 4 more times after the first time, so a total of 5 numbers are showing in the Immediate Window.
Read More: How to Use VBA IsNumeric Function (9 Examples)
5. Get Random Decimal In Between Any Two Numbers
You can generate a random decimal between any two numbers using VBA Randomize and display the output in a message box instead of the Immediate Window.
To generate a random decimal between 1200 and 1500 and show the output in a message box,
➤ Type the following code in the Module(Code) window,
Sub VBA_Randomize()
Dim Random_num As Double
Randomize
Random_num = (1500 - 1200) * Rnd + 1200
MsgBox Random_num
End SubThe code will generate a random decimal between 1200 and 1500 and will display the output in a message box.
After inserting the code,
➤ Press F5.
As a result, a random decimal will be shown in a message box.
Related Content: How to Use VBA StrComp in Excel (5 Common Examples)
💡 Things to Remember
📌 If the VBA Randomize function is not called before the Rnd function, the Rnd function may return the same random number value every time. As a result, there’s a chance you won’t get a really random number.
📌 VBA Randomize can be used with a variety of functions. However, when combined with the Rnd function, it gives the desired result.
Conclusion
I hope now you know the details of the VBA Randomize function and how to apply the function for different outputs. If you have any confusion, please feel free to leave a comment.
