» Return random numbers using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions Return random numbers

The worksheetfunction RAND() will return a decimal value greater than or equal to 0 and less than 1.
Here are some examples on how to use this function :
=RAND()*100 Returns a decimal value between 0 and 100 =RAND()*(100-50)+50 Returns a decimal value between 50 and 100 =ROUND(RAND()*100,0) Returns an integer value between 0 and 100.

Unique random numbers

With the user defined function below you can create a liste of unique random numbers:

Function UniqueRandomNumbers(NumCount As Long, LLimit As Long, ULimit As Long) As Variant
' creates an array with NumCount unique long random numbers in the range LLimit - ULimit (including)
Dim RandColl As Collection, i As Long, varTemp() As Long
    UniqueRandomNumbers = False
    If NumCount < 1 Then Exit Function
    If LLimit > ULimit Then Exit Function
    If NumCount > (ULimit - LLimit + 1) Then Exit Function
    Set RandColl = New Collection
    Randomize
    Do
        On Error Resume Next
        i = CLng(Rnd * (ULimit - LLimit) + LLimit)
        RandColl.Add i, CStr(i)
        On Error GoTo 0
    Loop Until RandColl.Count = NumCount
    ReDim varTemp(1 To NumCount)
    For i = 1 To NumCount
        varTemp(i) = RandColl(i)
    Next i
    Set RandColl = Nothing
    UniqueRandomNumbers = varTemp
    Erase varTemp
End Function

' example use:
Sub TestUniqueRandomNumbers()
Dim varrRandomNumberList As Variant
    varrRandomNumberList = UniqueRandomNumbers(50, 1, 100)
    Range(Cells(3, 1), Cells(50 + 2, 1)).Value = _
        Application.Transpose(varrRandomNumberList)
End Sub