This Access tutorial explains how to use Access Rnd Function to generate random number and create custom Function for random decimal number.
Access Rnd Function to generate random number
Access 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).
Syntax of Access 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 Access Rnd Function – generate random number
It is not useful to use 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 |
Access VBA 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
|
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
