Excel VBA Rnd Function to generate random number

This Excel tutorial explains how to use Excel VBA Rnd Function to generate random number and create custom Function for random decimal number.

You may also want to read:

Excel RAND RANDBETWEEN Function to generate random number

Access Excel Generate random HKID Hong Kong ID card number

Assign unique random number using Excel

Excel VBA Rnd Function to generate random number

Rnd Function is for use in Access, Access VBA, Excel VBA, it is used to generate random number larger than 0 and smaller than 1 (1 and 0 exclusive).

Excel worksheet use RAND Function and RANDBETWEEN Functions, which have quite different behavior.

Syntax of Excel VBA Rnd Function

Rnd[(number)]

 Number Rnd generates Not supplied or any positive number Generate a new random number 0 Return the last generated number Any negative number Generate a new random number and use it every time

Example of Excel VBA Rnd Function – generate random number

It is not useful to use Excel VBA Rnd Function alone, because we ultimately want to generate a random number between two specific numbers, but not a random number between 0 and 1.

Use the below formula to generate a number bounded by lowerbound and upperbound (both inclusive).

`Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)`
 Formula Explanation Int((10-1+1)*Rnd()+1) Generate random number between 1 and 10 Int((200-100+1)*Rnd()+100) Generate random number between 100 and 200

VBA custom Function – generate random decimal number

If you think the formula is too complicated, you can use the below custom Function. The purpose of this custom Function is to simulate Excel worksheet Function RandBetween, which allows you to simply use upperbound and lowerbound as Function arguments.

This custom Function offers an optional parameter that allows you to generate random integer or decimal number.

VBA code of custom Function – generate random decimal number

```Public Function wRandomNumber(lowerbound, upperbound, Optional rndType = 1) As Double
Randomize
rndVariable = Rnd
If rndType = 1 Then
wRandomNumber = Int((upperbound - lowerbound + 1) * rndVariable + lowerbound)
ElseIf rndType = 2 Then
If (upperbound - lowerbound + 1) * rndVariable + lowerbound <= upperbound Then
wRandomNumber = (upperbound - lowerbound + 1) * rndVariable + lowerbound
Else
Do While (upperbound - lowerbound + 1) * rndVariable + lowerbound > upperbound
rndVariable = Rnd
If (upperbound - lowerbound + 1) * rndVariable + lowerbound <= upperbound Then
wRandomNumber = (upperbound - lowerbound + 1) * rndVariable + lowerbound
End If
Loop
End If
End If
End Function```

Syntax of custom Function – generate random decimal number

`wRandomNumber(lowerbound, upperbound, [rndType])`
lowerbound The minimum random number
upperbound The maximum random number
rndType Optional, indicate whether the random number is decimal or integer

 value description 1 (default) Return a random integer 2 Return a random decimal number

Example of custom Function – generate random decimal number

 Formula Explanation =wRandomNumber(1,100) Return a random integer between 1 and 100 (both inclusive) =wRandomNumber(100,200,2) Return a random decimal number between 100 and 200 (both inclusive)

Outbound References

https://support.office.com/en-us/article/Rnd-Function-503CD2E4-3949-413F-980A-ED8FB35C1D80

Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist