ERLANDSEN DATA CONSULTING Excel & VBA Tips

# 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)
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, cl As Range, i As Long
' create 50 random numbers between 1 and 100
varrRandomNumberList = UniqueRandomNumbers(50, 1, 100)
' show results in one column
Range(Cells(3, 1), Cells(50 + 2, 1)).Value = _
Application.Transpose(varrRandomNumberList)
' show results in one row
Range(Cells(3, 3), Cells(3, 52)).Value = varrRandomNumberList

' get 16 random numbers and populate 16 cells
varrRandomNumberList = UniqueRandomNumbers(16, 1, 100)
For Each cl In Range("A1:D4")
i = i + 1
cl.Formula = varrRandomNumberList(i)
Next cl
Set cl = Nothing

End Sub
```

This function will not work in Excel 5/95.

Document last updated 2003-03-18 12:45:07      Printerfriendly version