VBA Random Numbers

Excel VBA Random Numbers

To generate random numbers in vba we have an inbuilt function called RND. It just takes an argument a number to generate random numbers and this is also an optional parameter. It will create random numbers which are greater than 0 and smaller than 1.

This works exactly the same as the excel function “RAND”. As I told in the worksheet function “RAND”, in VBA too we can generate random numbers which are greater than 0 but less than 1.

Now take a look at the syntax of the “RND” function.

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

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

How to Generate Random Numbers using VBA Code?

Example #1

Now we will see the simple example of using the “RND” function. Follow the below steps to write the VBA code on your own.

Step 1: Declare the variable as “Integer” in VBA

Code:

Sub Rnd_Example1()

  Dim K As Integer

End Sub

Step 2: Now assign the value to the variable “k” through “RND” function.

Code:

Sub Rnd_Example1()

  Dim K As Integer
  K = Rnd()

End Sub

Step 3: Show the value returned by the variable “k” in the message box.

Code:

Sub Rnd_Example1()

  Dim K As Integer
  K = Rnd()
  MsgBox K

End Sub

Now run the excel macro and see what the result is.

 

Look what has happened.

It is showing the result as 1 where the “RND” function can return only numbers which are greater than zero but less than 1.

You must be thinking about what the wrong thing here is.

The wrong thing here is the kind of data type we have assigned to the variable “k”.

If you look back at the variable we have declared we have assigned the data type as Integer. Since we have assigned the variable as Integer, it can only show the whole numbers between -32768 to 32767.

Whenever RND returns the decimal number VBA converts the decimal number to the nearest integer i.e. 1.

So, to make the formula work properly declare the variable as “Double”.

“Double” is the data type in VBA that can hold decimal values.

Code:

Sub Rnd_Example1()

  Dim K  As Double
  K = Rnd()
  MsgBox K

End Sub

Now the code and see what the result is.

Click on ok and run one more time and see what the result is.

This time we got a different result. Since “RND” is a volatile function in nature it reproduces different results every time you execute the code.

Example #2 – Get Same Random Number Every Time

As we have seen the previous example “RND” function reproduces the result every time we execute the code. In order to get the same random number, again and again, we need to pass the argument as zero.

Code:

Sub Rnd_Example2()

  Dim K As Double
  K = Rnd(0)
  MsgBox K
 
End Sub

This will produce the same number again and again when we execute the code.

Example #3 – Generate Whole Random Number

We can also generate whole numbers by using other VBA function or other input numbers. For an example look at the below code.

Code:

Sub Rnd_Example3()

  Dim K As Double
  K = 1 + Rnd * 100
  MsgBox K

End Sub

This code will generate random whole numbers with decimal points every time we execute the code.

If you are looking at the whole numbers without decimal points then we can use the below code.

Code:

Sub Rnd_Example3()

  Dim K As Double
  K = CInt(1 + Rnd * 100)
  MsgBox K

End Sub

This will keep generating the whole numbers from 1 to 100.

Recommended Articles

This has been a guide to VBA Random Numbers. Here we learned how to generate vba random numbers using RND function along with some practical examples and downloadable excel template. Below are some useful excel articles related to VBA –

• VBA New Line
• VBA Split
• Generate Random Numbers in Excel
• How to Randomize List in Excel?