How to Use VBA Randomize Function in Excel (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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

vba randomize

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])

syntax

❑ 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 Sub

The code will give a random number every time you run the code.

vba randomize

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.

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.

vba randomize

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.

vba randomize

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

vba randomize

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 Sub

The code will generate a random number between 1 to 10.

vba randomize

After that,

➤ Press F5.

As a result, a random number between 1 to 10 will be added in the Immediate Window.

output

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.

vba randomize

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 Sub

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

vba randomize

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 Sub

Here, 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.

Integer

➤ Press F5.

As a result, a random integer between 1 to 100 will be added in the Immediate Window.

vba randomize

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.

Output

Read More: How to Use VBA Int Function in Excel ( 3 Examples)


Similar Readings:


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 Sub

The code will give a random integer between 1200 to 1500  every time you run the code.

in a range

➤ Press F5.

As a result, a random integer between 1200 to 1500 will be added in the Immediate Window.

vba randomize

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.

range number

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 Sub

The code will generate a random decimal between 1200 and 1500 and will display the output in a message box.

vba randomize

After inserting the code,

➤ Press F5.

As a result, a random decimal will be shown in a message box.

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


Related Articles

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo