How to use the RND Function in VBA
RND function is used to generate a random number between 0 and 1. Generating a random number has applications in domains such as gambling, statistical sampling, computer simulation, or to — generally speaking — create unpredictable results for any other purpose.
Of course, most of the time we want to be able to specify the range within which we generate the random number. In this article, we will see how we can do that, and we will also see how we can generate random numbers without repetition.
Number is optional and could have the following values:
|Less than 0||Returns same random number on each call using [Number] as the seed.|
|Equal to 0||Returns the most recent random number|
|Greater than 0 (default)||Returns the next generated random number. This applies if blank.|
Example 1: Generating a random number
Sub Generate_Random_Number() ‘Set the maximum and minimum numbers of the range that we’re selecting the random number from maxNumber = 100 minNumber = 1 randomNumber = Int(maxNumber * Rnd) + minNumber MsgBox randomNumber End Sub
If you run this code multiple times, you will notice a pattern. It seems like the series of random numbers repeat themselves every so often. If you stop generating the numbers for some time, then return later to generating them, you will notice that you’re getting the same numbers in the same order again.
Hence, We also need to use
Randomize, which is a function that changes the seeding of RND every time it runs. The seed can be thought of as a number that initializes the randomizing process. Utilizing
Randomize without any parameters will by default use the system current time to set the seed.
Below is the code after adding the seed. Note that if you specify a particular number, then you can reproduce the results and others can reproduce it as well, which is sometimes desirable in research.
Sub Generate_Random_Number() ‘Set the maximum and minimum numbers of the range that we’re selecting the random number from maxLimit = 100 minLimit = 1 Randomize randomNumber = Int(maxLimit * Rnd) + minLimit MsgBox randomNumber End Sub
Now every time you generate the number, it will be indeed random, you will not get any patterns. But what if you want to get unique random numbers? Follow the next example to see how you can achieve that!
Example 2: Generating a unique list of random numbers
We will utilize VBA collection in order to store the random numbers we generate.
NumberCount will allow us to specify the number of unique random numbers we want to generate.
Function UniqueRandomNumbers(NumberCount As Long, LowerLimit As Long, UpperLimit As Long) As Variant Dim RndList As Collection, i As Long, uniqueList() As Long UniqueRandomNumbers = False If NumberCount&amp;gt; (UpperLimit - LowerLimit + 1) Then Exit Function Set RndList = New Collection Randomize Do On Error Resume Next i = CLng(Rnd * (UpperLimit - LowerLimit) + LowerLimit) RndList.Add i, CStr(i) On Error GoTo 0 Loop Until RndList.Count = NumberCount ReDim uniqueLIst(1 To NumberCount) For i = 1 To NumberCount uniqueList(i) = RndList (i) Next i Set RndList = Nothing UniqueRandomNumbers = uniqueList Erase uniqueList End Function
The following procedure tests the function above. It places the list of unique random numbers that we get from the function into column one of the active sheet, spanning row 3 to row 202 (a total of 200 rows).
Sub Test_Procedure() Dim rndNumberList As Variant rndNumberList = UniqueRandomNumbers(50, 1, 100) Range(Cells(3, 1), Cells(50 + 2, 1)).Value = _ Application.Transpose(rndNumberList) End Sub
The output of the code is shown below.