VBA Random Number

Introduction to VBA Random Number

In excel we have a function called RAND which is used for generating the random numbers. So as we have RND function in VBA which is used for generating random numbers. The RND Function generates numbers which are greater than 0 but less than 1.

Syntax of Random Number in Excel VBA

The syntax for the Random Number function excel VBA is as follows:

We can see in the syntax of the RND Function above, we can only use a single number as input.

We give the input in RND Function in below 3 ways;

1. If we give number <0, then it will generate the same number every time we run the code.
2. If we give a number as 0, then it will pop up the most recent number which was generated.
3. If we give number >0, then it will keep generating different random numbers. But it will be in sequence.

How to Use Excel Random Number Function?

Let’s see the examples of Random Number in Excel VBA.

Example #1 – VBA Random Number

Step 1: This we can directly apply to VBA. For this go to the VBA window and open a module from the Insert menu as shown below.

Step 2: Once we do that we will get a window of Module in VBA. In that module, write subcategory of VBA Random Number in the same name or in any name as per your choice.

Code:

Sub RandomNumber()

End Sub

Step 3: Now choose a word or alphabet and define it as a variable and assign Double to it. Here we are considering variable Dim A and give it to Double. Selecting double instead of Integer will be useful because we will be seeing decimal values which are between 0 and 1.

Code:

Sub RandomNumber()

Dim A As Double

End Sub

Step 4: Now next we will use variable A which we defined above and we will assign it a function Rnd for generating random numbers as shown below.

Code:

Sub RandomNumber()

Dim A As Double
A = Rnd(

End Sub

Step 5: As we can see in the above screenshot, RND function has the syntax as the number and that will be a single digit number. Here we will see how giving a number less than 0 will generate what? So in brackets of RND Function insert -1 which is the most recent number less than 0.

Code:

Sub RandomNumber()

 Dim A As Double
 A = Rnd(-1)

End Sub

Step 6: At last to get the output we need the reference of cell or a message box where it will reflect the random number. Insert Msgbox command of the message box and assign the defined variable here.

Code:

Sub RandomNumber()

 Dim A As Double
 A = Rnd(-1)
 MsgBox A

End Sub

Step 7: Now we can compile the code and run it by clicking on the play button below the menu bar. We will see the first random number generated which is less than 0 which is 0.2240070104599 as shown below.

If we again run the code, it will keep on generating the same code again and again.

Example #2 – VBA Random Number

We have seen how to generate random numbers which is less than 0. There is another way to get random numbers. In this example, we will generate the random number by giving 0 as an input to RND function.

For this, we will use the same code used in example-1.

Step 1: Now in place of -1 which is between the brackets of RND Function put 0.

Code:

Sub RandomNumber()

 Dim A As Double
 A = Rnd(0)
 MsgBox A

End Sub

Step 2: We can compile the code, if required or else run directly by pressing F5 key (Shortcut Key) to run the code. We can see the RND function has generated the same number which was generated when we gave input less than 0. Which means, if we give 0 as an input RND function then it will generate the previously generated number.

And if we run the code again then it will keep on giving the same random number.

Example #3 – VBA Random Number

In this example, we will see what will happen if we give a value greater than 0. We can choose to write new code or we can make the changes in the code which we have written above. Let‘s consider the code which we have seen above.

Step 1: As we need to test number greater than 0 so in RND function use blank and add +1 for values greater than 1 as shown below.

Code:

Sub RandomNumber()

  Dim A As Double
  A = 1 + Rnd()
  MsgBox A

End Sub

Step 2: Compile the code if you want to and then run the code. We will see the random number generated is different than the number which was generated earlier. Below is the screenshot, which shows the random number is in the fraction of 1.035….41. Which is greater than 0.

If we run the same code again, we will keep on getting different random numbers. So we got just after running the code again. This time the random numbers which we got are the double infraction value compared with the random number generated earlier.

Pros of VBA Random Number

1. It seems difficult but it is easy to apply.
2. This is quite useful when we are working on generating ticket numbers on a random basis.
3. The process shown in example-3 can be implemented in many different ways.

Things to Remember

• This can be implemented in through MS Excel function also with the function name RAND.
• We the test example-2 first before we run code of example-2. Because it will actually show what was the number generated earlier.
• Testing the RND function by keeping the brackets blank will also give us a random number.
• If we assign the variable as Integer then we will be getting only whole numbers which will be same as if 0 or 1.
• If we use two-digit numbers as input in RND function then also it will generate the random number.

Recommended Articles

This is a guide to VBA Random Number. Here we discuss how to generate Random Number in VBA Excel using RND Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA InStr explained with Examples
2. VBA Integer Data Type
3. How to Select Cell using VBA Code?
4. Transpose a Range in VBA