VBA Randomize

What is Randomize Statement in VBA?

In VBA (Visual Basic for Applications), the “Randomize” statement is used to initialize the random number generator. The random number generator is used in conjunction with the “Rnd” function to generate pseudo-random numbers in your VBA code.

After initializing the random number generator, you can use the “Rnd” function to generate random numbers. The “Rnd” function returns a value between 0 and 1. 

For more, see this example below.

Create a subroutine to print random numbers. The VBA Randomize statement initializes the random number generator. Then, the Rnd function uses the random values generated by the Randomize statement and generates a number between 0 and 1. It is printed in the Immediate tab.

Subsequent iterations will give different numbers, as shown.

Syntax

The VBA Randomize syntax is as follows: Randomize [Seed]

“Seed” is an optional argument that allows you to specify a starting point for the random number sequence. If you omit the “Seed” argument, the system time is often used as the default seed.

Here’s a brief explanation of how it works:

• If you use the “Randomize” statement without providing a seed, it initializes the random number generator based on the system time in which the VBA randomize timer works if you set up a delay with the VBA Wait function.
• If you use the “Randomize” statement with a seed, it initializes the random number generator based on the specified seed value.

How to use Randomize Statement in VBA?

You can see how to effectively use the VBA Randomize statement by following the steps below.

Step 1: Select the “Developer” option in the Excel toolbar. You can customize Excel ribbons if you can’t see them in your workbook.

Under the Developer tab, click on the “Visual Basic” option in the left-hand corner. It will open the VBA Editor.

The editor will be empty at first. You’ll need to add a new module by clicking “Insert” on the VBA toolbar. Select “Module” in the drop-down.

A new blank module opens, and you can start your coding journey in Excel VBA.

To start, see the example below to generate a set of random numbers under a FOR-loop. By using the VBA Randomize statement combined with the Rnd function, you can generate numbers between 0 and 1.

Step 2: Start by naming your subroutine to demonstrate the example described above.

Step 3: Call the VBA Randomize statement.

It will start the random number generator for the subroutine. You need to declare it only once at the start of the subroutine.

Step 4: Initialize a VBA Double variable. A double variable is declared since it can store more numbers after the decimal point.

Step 5: Start a FOR-loop starting from 1 to 5.

This loop is used to generate a random number 5 times.

Step 6: In the FOR-loop, initialize the double variable to the VBA Rnd function.

Step 7: Print the double variable.

Step 8: Continue the FOR-loop.

Code:

Sub SimulateDiceRoll()
Randomize
Dim diceResult As Integer
diceResult = Int((6 * Rnd) + 1)
MsgBox “You rolled a ” & diceResult, vbInformation, “Dice Roll”
End Sub

Step 9: Click “F5” or the “Run” icon on the activity bar in the Excel VBA Module. A pop-up window comes up.

Select the subroutine you need to run. The example subroutine is “Random_Example”. So, that is double-clicked, which runs the subroutine and prints the output in the Immediate tab, as shown.

Examples

See the different ways in which the VBA Randomize statement can be used in interesting circumstances.

Example 1

Suppose you’re playing a board game, and you want to develop an agent who can roll dice for you. You want to make it unpredictable so that winning the game against your VBA program will be difficult. You can randomize the numbers between 1 and 6 and make the program pick a random number every turn. You can code this using the VBA Randomize statement by following the steps below.

Step 1: Name the subroutine that will choose a random number between 1 and 6.

Step 2: Call the Randomize statement immediately after you name the subroutine.

Step 3: Initialize an integer variable.

Despite the Randomize statement giving out numbers with decimals, you need an Integer since dice have whole numbers, not fractions.

Step 4: Provide the value for the integer variable.

In Int((6 * Rnd) + 1)

• Here, to get numbers randomly between 1 and 6, you multiply the random number generated between 0 and 1 with the Rnd function with six and then add the product with 1. 
• All of this is inside an Int() function in VBA, which will convert the result into an integer. 
• It takes the integer number closest to the decimal and rounds it off to that integer. It is how you’ll get numbers between 1 and 6 randomly generated.

Step 5: Print the generated number in a MsgBox function.

The vbInformation is a constant that can be used in the Message Box function to include an information icon, as seen below.

Code:

Sub SimulateDiceRoll()
Randomize
Dim diceResult As Integer
diceResult = Int((6 * Rnd) + 1)
MsgBox “You rolled a ” & diceResult, vbInformation, “Dice Roll”
End Sub

Step 6: Run the above subroutine by clicking on the “green arrow” or the “Run” button on the VBA toolbar. It will pop up a message box, as seen below.

If you run the subroutine multiple times, you’ll get different numbers.

Example #2

In this example, you’ll be able to change a given cell’s interior color with random amounts of red, green, and blue colors using the VBA Interior color using the RGB function.

For a given cell A1, you can use the Randomize function to get different shades of red, green and blue interior colors. Follow the steps below to learn how to do so.

Step 1: Name the subroutine which will change a given cell’s background color.

Step 2: Declare the VBA Randomize the list of colors.

Step 3: Define 3 integer values to define the red, green, and blue parts of the color index.

Step 4: Get the value of red by multiplying 256 with the random number generated between 0 and 1 using the Rnd function.

The value of the RGB function in VBA is by multiplying the maximum number of pixels, that is, 256, with any number and adding it to the RGB function in your preferred slot in RGB, whose arguments are (red, green, and blue).

Step 5: Get the green part of the RGB function by multiplying 256 with the random number generated.

The Int function in VBA converts any decimal to the nearest integer value. Since the RGB function accepts only whole numbers, you’ll need to use the Int function.

Step 6: Similarly, calculate the value of the blue component in the RGB function similar to the previous 2 steps.

Step 7: With the ActiveSheet function (it means that your cursor is currently active in that worksheet), set the interior color of cell “A1” with the RGB function with the calculated component values in their respective slots.

Code:

Sub RandomBackgroundColor()
Randomize
Dim redValue, greenValue, blueValue As Integer
redValue = Int(256 * Rnd)
greenValue = Int(256 * Rnd)
blueValue = Int(256 * Rnd)
ActiveSheet.Range(“A1”).Interior.Color = RGB(redValue, greenValue, blueValue)
End Sub

Step 8: Run the above subroutine by pressing “F5”. Then go to the worksheet to view the result.

If it is run multiple times:

Example #3

Suppose you have an array of elements that you want to rearrange. You can use a VBA randomize array on the numbers in the array and change their order using an algorithm. The algorithm used here is the Fisher-Yates algorithm.

The Fisher-Yates algorithm works by iterating through the array from the last element to the first. During each iteration, it randomly selects an element from the remaining unshuffled elements and swaps it with the current element. Here is a step-by-step description of the Fisher-Yates shuffle algorithm:

1. Start from the last element of the array.
2. Generate a random index between 0 and the current index (inclusive).
3. Swap the element at the current index with the element at the randomly chosen index.
4. Move to the previous index and repeat steps 2-3 until you reach the first element of the array.

This will be implemented in Excel VBA by following the steps below.

Step 1: Create a subroutine to implement the Fisher-Yates algorithm.

Step 2: Initialize the VBA Randomize array statement.

Step 3: Define an array and initialize it with some elements.

Step 4: Initialize an iterative variable and a temporary variable.

Step 5: Initialize a FOR-loop through the array using the UBound and LBound which means the highest and lowest index respectively in the array.

+ 1: This is added to the lower bound to ensure that the loop iterates one extra time, including the element at the lower bound itself.

Step -1: This specifies that the loop should decrease the loop variable i by 1 in each iteration, moving from the upper bound towards the lower bound.

Step 6: Generate a random number between 0 and 1 in the FOR loop. To do so, initialize an Integer variable.

Step 7: Add 1 to the iterative variable and multiply with the random variable between 0 and 1 with the Rnd function.

The Int function is used to convert the result into an integer closer to the integer, in this case, either 0 or 1. With this random index as 0 or 1, the swapping of elements will take place.

Step 8: Using the temporary variable, swap the elements between the iterative variable and the random index generated (0 or 1st position).

Step 9: Continue the FOR-loop. In each iteration, the size of the array decreases due to adding “Step -1” in the loop in the earlier steps.

Step 10: Declare a string variable.

Step 11: Now, get the shuffled array of elements into a string and separate them with commas using the VBA Join function.

Code:

Sub ShuffleArray()
Randomize
Dim myArray() As Variant
myArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

' Shuffle the array using the Fisher-Yates algorithm
Dim i As Integer
Dim temp As Integer

For i = UBound(myArray) To LBound(myArray) + 1 Step -1
    Dim randomIndex As Integer
    randomIndex = Int((i + 1) * Rnd)
    temp = myArray(i)
    myArray(i) = myArray(randomIndex)
    myArray(randomIndex) = temp
Next i

Dim resultString As String
resultString = Join(myArray, ", ")
Debug.Print resultString

End Sub

Step 12: Run the above subroutine. The shuffled array will be printed in the Immediate tab.

If you run the shuffling algorithm multiple times, then it will differ in every iteration.

Important Things To Note

• Call Randomize once at the beginning of your code or subroutine to initialize the random number generator.
• Use the Rnd function after calling Randomize to generate random numbers.
• If you need reproducibility, consider providing a seed value to Randomize.
• Recognize that VBA random number generator generates pseudo-random numbers based on an initial seed.
• Avoid using Rnd without first calling Randomize to initialize the random number generator.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA RANDOMIZE. Here we explain the how to use randomize statement in Excel VBA and syntax along with examples & downloadable excel template. You can learn more from the following articles –

• VBA ROUNDDOWN
• VBA CALCULATE
• VBA String to Integer