VBA Randomize

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Randomize Statement in VBA

VBA Randomize statement is a simple one-liner statement that we add before applying the RND function. Whenever a workbook reopens, the Randomize statement provides a new seed number to the RND function depending upon the computer’s system time.

Before discussing the Randomize statement, let me introduce you to a simple RND function with VBA.

As a worksheet function “RAND,in VBA, “RND” generates random numbers greater than 0 but less than 1.

Now, look at the syntax of the RND function.

VBA RND Syntax

[Number]: We can pass the argument in three ways.

  • If we pass the number as <0, it generates the same random number every time.
  • If we pass the number as 0, it will repeat its most recent number.
  • If we pass the number >0, it keeps giving you different random numbers, i.e., the next random number in the sequence.

Example

Look at the below code.

Code:

Sub RND_Example()

    Debug.Print Rnd

End Sub
RND Example 1.1

We can see the number below when we run the code in the Immediate window.

RND Function - Output

Similarly, we can see the numbers below when we execute this code three more times.

RND Example 1.2

Now, we will close the workbook and reopen it.

Now, we will go back to the visual basic editorVisual Basic EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more window.

RND Example 1.3

Now, the immediate window is empty and clean.

Now again, we will execute the code four times and see what the numbers we will get in the Immediate window are.

RND Example 1.5

We got the same numbers as we got above.

It does not 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.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

How to Use VBA Randomize Statement?

You can download this VBA Randomize Excel Template here – VBA Randomize Excel Template

Example #1

To get random numbers, we need to add the simple one-liner “Randomize” before the RND function.

Code:

Sub Randomize_1()
    
    Randomize
    Debug.Print Rnd

End Sub
VBA Randomize Example 1.1

Now, we will run the code 4 times and see what we get.

VBA Randomize Example 1.2

It has generated the above numbers in my local window.

Now, we will close the file and reopen the file once again.

We usually start with a clean slate in the visual basic window.

We will again execute the code and see what numbers we get this time.

Randomize -Output

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.

It looks like a random number.

Example #2

Random Numbers Greater Than One

As we have seen, the “RND” function can only generate numbers from 0 to 1. But, to generate numbers greater than one random number, we need to use “RANDOM BETWEEN,” which is available with the worksheet function class.

So, to generate random numbers greater than one, we need to use the below code.

Code:

Sub Randomize_2()

    Randomize
    Debug.Print Rnd * 100

End Sub
VBA Randomize Example 2.1

Now, we will execute the code and see what we get.

Randomize - Output 2

Like this, we can use the “Randomize” statement in VBA to generate random numbers every time we reopen the Excel file.

This article has been a guide to VBA Randomize. Here, we discuss using randomized statements in VBA to generate random numbers greater than one with an example and downloadable Excel template. You can learn more about VBA from the following articles: –