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

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

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

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

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.

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.

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

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

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.

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

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.

After inserting the code,

➤ Press F5.

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

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

Download Practice Workbook

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.