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

##### Table of contents

###### Key Takeaways

- Randomize is used to initialize the random number generator in VBA. It ensures that the sequence of random numbers generated by the Rnd function is different each time your code runs.
- If you provide a seed value as an argument to Randomize, it initializes the random number generator based on that seed.
- After Randomize, you can use the Rnd function to generate pseudo-random numbers between 0 and 1.
- Rnd is often used in conjunction with Randomize to produce different sequences of random numbers.

**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()RandomizeDim diceResult As IntegerdiceResult = 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()RandomizeDim diceResult As IntegerdiceResult = 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()RandomizeDim redValue, greenValue, blueValue As IntegerredValue = 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:

- Start from the last element of the array.
- Generate a random index between 0 and the current index (inclusive).
- Swap the element at the current index with the element at the randomly chosen index.
- 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)

**1. Why is it important to use VBA Randomize before generating random numbers?**

Randomize initializes the random number generator’s seed, ensuring varied and unpredictable sequences.

Without Randomize, the default seed may lead to repeated sequences in subsequent runs.

Initializing with Randomize provides a fresh starting point for the pseudo-random number generation process.

**2. Can I specify a seed value for the random number generator using VBA Randomize?**

Yes, you can specify a seed value for the random number using VBA randomize collection by adding the seed value after adding the VBA as shown below.**Sub Random_Seed()Randomize 123Dim randomNumber As DoublerandomNumber = RndDebug.Print randomNumberEnd Sub**

If you run this code snippet, setting the seed to 123 will give you a predictable sequence of random-like numbers between 0 and 1.

**3. What is the purpose of the VBA Rnd function in conjunction with VBA Randomize?**

• Randomize initializes the random number generator, while Rnd generates pseudo-random numbers between 0 and 1.

• Randomize with or without a seed provides a starting point, making subsequent random sequences reproducible.

• Rnd utilizes the initialized generator to produce diverse sequences of pseudo-random numbers.

**4. Are there any alternatives to VBA Randomize for random number generation in VBA?**

• Rnd can be used without Randomize, but it uses the system time as the default seed, leading to potential repetition.

• Excel functions like RANDBETWEEN or RAND can be employed for simpler random number generation in Excel VBA.

• Windows API calls, like RtlGenRandom, can be used for cryptographic-grade random numbers in VBA.

### 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 –

## Leave a Reply