How to Generate Random Number Between 2 No. in Excel VBA Easily (5 Example)

Welcome to Excel Avon

Generate Random Number in Excel VBA

DOWNLOAD USED EXCEL FILE FROM HERE>>

In today’s post, we will explain you How to Generate Random Number in Excel VBA, we have an inbuilt function called RND to generate random numbers in VBA. It only takes the number argument to generate a random number. This is also an optional parameter. This will generate random numbers greater than 0 and less than 1. Apart from this, by giving the minimum and maximum number, you can find the random number between them.

It works similar to the Excel “RAND” function. As we said in the worksheet function “RAND” in VBA, we can generate random numbers that are greater than 0 but less than 1.

If you want to generate a random number between two defined numbers, in that case, you have to write code using two VBA functions (RND + INT).

Let us now understand How to Generate Random Number in Excel VBA.

How to Generate Random Number in Excel VBA

Well, you must know that How to Generate Random Number in Excel VBA we will learn in this article. Well, It works the same as the Excel function “RAND.” But we will generate random number VBE’s, let’s understand. So, we have to go like last time, first go to the Developer Tab then click on the option of Visual Basic as shown in the image below.

On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.

Once the module is inserted, and the library is activated, we’ll write a subroutine to Generate Random Number.

Sub GenerateNumber()

Sub End

Here we are Declaring variables for minimum Number & Maximum Number.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double

End Sub

Declaring more variables which are using in the entire project for Random Number, worksheet and long.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double
Dim RndNo As Double
Dim WS As Worksheet
Dim i As Long

End Sub

Then here we will give the minimum and maximum numbers between which we want to generate random numbers.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double
Dim RndNo As Double
Dim WS As Worksheet
Dim i As Long

MaxNum = 100
MinNum = 10
End Sub

Now assign the value to the variable ‘RndNum’ through the RND Function.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double
Dim RndNo As Double
Dim WS As Worksheet
Dim i As Long

MaxNum = 100
MinNum = 10
RndNo = 2 + Rnd * (MaxNum - MinNum + 1)
End Sub

Now generate the number by Rnd function and print it, Use Debug.Print

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double
Dim RndNo As Double
Dim WS As Worksheet
Dim i As Long

MaxNum = 100
MinNum = 10
RndNo = 2 + Rnd * (MaxNum - MinNum + 1)
debug.Print RndNo
End Sub

Click on run button, and the value will be printed in the immediate window.

 

You can see in the image below that the random number has been generated.

Numbers between 10 to 100 have been generated but in decimal. Use Round function Decimal is being removed but after decimal there will be 2 digits.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double
Dim RndNo As Double
Dim WS As Worksheet
Dim i As Long

MaxNum = 100
MinNum = 10
RndNo = 2 + Rnd * (MaxNum - MinNum + 1)
Debug.Print Round(RndNo, 2)
End Sub

Click on run button, and again value will be printed in the immediate window. Now you can see we will generate 2 digits after decimal using round function.

And remove Round Function. We will use the int function to eliminate the decimal digits.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double
Dim RndNo As Double
Dim WS As Worksheet
Dim i As Long

MaxNum = 100
MinNum = 10
RndNo = 2 + Rnd * (MaxNum - MinNum + 1)
Debug.Print Int(RndNo)
End Sub

Click on run button, and the value will be printed in the immediate window.

 

Now it is printing random number without decimal digit

Now we want to print random numbers in the worksheet, set worksheet as Active sheet, and then we will make a loop which will be for the range.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double
Dim RndNo As Double
Dim WS As Worksheet
Dim i As Long

MaxNum = 100
MinNum = 10
Set WS = ActiveSheet

For i = 1 To 10
    RndNo = 2 + Rnd * (MaxNum - MinNum + 1)
Next i
End Sub

Here we will write the range of worksheet where we want RndNo print.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double
Dim RndNo As Double
Dim WS As Worksheet
Dim i As Long

MaxNum = 100
MinNum = 10
Set WS = ActiveSheet

For i = 1 To 10
    RndNo = 2 + Rnd * (MaxNum - MinNum + 1)
    WS.Range("A" & i).Value = RndNo
Next i
End Sub

Click on run button, and the value will be printed in the Activesheet.

 

Here you can see the generated random numbers. But all the numbers generated are in decimal so now we have to go to VBE one more time.

Now we will use int function so that the numbers we get will not be in decimal.

Sub GenerateNumber()
Dim MaxNum As Double
Dim MinNum As Double
Dim RndNo As Double
Dim WS As Worksheet
Dim i As Long

MaxNum = 100
MinNum = 10
Set WS = ActiveSheet

For i = 1 To 10
    RndNo = 2 + Rnd * (MaxNum - MinNum + 1)
    WS.Range("A" & i).Value = Int(RndNo)
Next i
End Sub

Here you can see that the printed number is not of decimal, by using int function we can eliminate the number of decimals.

So, I hope you have understood How to Generate Random Number in Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.

DOWNLOAD USED EXCEL FILE FROM HERE>>

LEARN MORE TOPIC IN VBA HERE

You can also see well-explained video here about How to Generate Random Number in Excel VBA