Random Numbers In Excel

This page describes how to work with random numbers in Excel and VBA.

Introduction

Both Excel and VBA have limited support for random numbers. In Excel, you can use the RAND worksheet function to return a random number, D, where 0  <= D < 1. In the Analysis Tool Pack add-in, the is a function named RANDBETWEEN that will return an random integer between two specified numbers. VBA has its own random function, Rnd, which returns a random number, D, where 0  <= D < 1. Building upon the RAND and Rnd functions, we can devise some useful formulas and functions.

Random Numbers In Excel Formulas

The Excel worksheet function RAND returns a random value D where 0 <= D < 1.
To return a decimal number (including a fractional part after the decimal point) between to numbers, use the following formula:

=RAND()*(High-Low)+Low

where Low is the smallest value in the desired range and High is the largest value in the desired range. The formula will return a value between Low and High. To return an integer (whole number with no fractional portion), use

=TRUNC(RAND()*(High-Low)+Low)

The RAND function is a supported array function so RAND alone or either of the formulas above can be used in array formulas.

It should be noted that RAND is a volatile function, which means it will be recalculated any time any calculation is made. This means that the value of the function will change with each calculation. If you want the random number to change only under certain circumstances, you can use a formula with a circular reference to create a random number that is recalculated only under certain circumstances.

=IF(ReCalc, RAND()*(High-Low)+Low,C3)

In this formula C3 is the cell containing the formula, and RecCalc is a value that indicates whether to recalculate the random number. ReCalc can be a function or a cell reference. In either case, ReCalc should be TRUE or a non-zero numeric value to cause the formula to be recalculated, or FALSE or zero to prevent the formula from being recalculated. In order to allow circular references, go to the Tools menu, choose Options, then the Calculation tab. There, check the Iteration box and set Maximum Iterations to 1. This formula will recalculate the random number result only when the value of ReCalc is TRUE.

Random Numbers In VBA

VBA provides a function named Rnd that returns a value D where where 0 <= D < 1. To get a random number (a real number with a decimal portion) within a specified range of values, use code like the following. Change the value of Low and High to lower and upper limits of the range of allowable values.

Dim Low As Double
Dim High As Double
Low = 11 '<<< CHANGE AS DESIRED
High = 20 '<<< CHANGE AS DESIRED
R = (High - Low) * Rnd() + Low

The code above will put in the variable R an integer between 11 and 20. To get an integer (whole number) between two numbers, use:

Dim Low As Double
Dim High As Double
Low = 11 '<<< CHANGE AS DESIRED
High = 20 '<<< CHANGE AS DESIRED
R = Int((High - Low + 1) * Rnd() + Low)

This will put in R a whole number between Low and High.

Getting An Array Of Random Longs

The function described in this section, RandomLongs, will return an array of random Long Integers. The function has the following declaration.

Public Function RandomLongs(Minimum As Long, Maximum As Long, _
    Number As Long, Optional ArrayBase As Long = 1, _
    Optional Dummy As Variant) As Variant

This function will return an array of random Long Integers between the values of Minimum and Maximum. The Number parameter indicates how many values to return. The ArrayBase parameter indicates the LBound of the resulting array. If ArrayBase is 0, the function returns an array with bounds (0,Number - 1). If ArrayBase is 1, the funcion returns an array with bounds (1, Number). The Dummy parameter is used only when this funciton is called from a worksheet cell (discussed later). If the function is called from within VBA, the Dummy parameter is ignored. If there is an error in parameter values, such as Minimum > Maximum or Number less than 1, the function will return Null. To test whether the result from the function is a valid array, use the IsArrayAllocated function on the Is Array Allocated page.

It is possible that there will be duplicated values in the result array. If you need unique, non-duplicated values, use the UniqueRandomLongs function described later on this page. The code for RandomLongs is shown below:

RandomLongs

Public Function RandomLongs(Minimum As Long, Maximum As Long, _
    Number As Long, Optional ArrayBase As Long = 1, _
    Optional Dummy As Variant) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' RandomLongs
' This returns an array containing elements whose values are between the Minimum and
' Maximum parameters. The number of elements in the result array is specified by the
' Number parameter. For example, you can request an array of 20 Longs between 500 and
' 1000 (inclusive).
'
' The ArrayBase parameter is used to specify the LBound of the ResultArray. If this
' is omitted, ResultArray is 1-based.
'
' It is possible that there will be duplicate values in the result array. If you need
' non-duplicated values, use UniqueRandomLongs.
'
' The Dummy argument is to be used only when the function is called from a worksheet.
' Its purpose is to allow you to use the NOW() function as the Dummy parameter to force
' Excel to calculate this function any time a calculation is performed. E.g.,
'       =RandomLongs(100,199,10,NOW())
' If you don't want to recalulate this function on every calculation, omit the Dummy
' parameter.  The Dummy argument serves no other purpose and is not used anywhere
' in the code.
'
' The function returns an array of Longs if successful or NULL if an error occurred
' (invalid input parameter).
'
' Note: The procedure creates its own array of size (Maximum-Minium+1), so very large
' differences between Minimum and Maximum may cause performace issues.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim SourceArr() As Long
Dim ResultArr() As Long
Dim SourceNdx As Long
Dim ResultNdx As Long

'''''''''''''''''''''''''''''''''''''
' Ensure the parameters are valid.
'''''''''''''''''''''''''''''''''''''
If Minimum > Maximum Then
    RandomLongs = Null
    Exit Function
End If
If Number > (Maximum - Minimum + 1) Then
    RandomLongs = Null
    Exit Function
End If
If Number <= 0 Then
    RandomLongs = Null
    Exit Function
End If

''''''''''''''''''''''''''''''''''''''''''
' Redim The Arrays.
' SourceArray will contain all the numbers
' between Minimum and Maximum thus having
' (Maximum-Minimum+1) elements. The LBound
' of SourceArray is Minimum and the UBound
' of SourceArray is Maximum.
' ResultArray will have Number elements.
' The LBound of ResultArray is ArrayBase
' and the UBound of ResultArray is
' (Base+Number-1).
''''''''''''''''''''''''''''''''''''''''''
ReDim SourceArr(Minimum To Maximum)
For SourceNdx = Minimum To Maximum
    SourceArr(SourceNdx) = SourceNdx
Next SourceNdx
ReDim ResultArr(ArrayBase To (ArrayBase + Number - 1))
'''''''''''''''''''''''''''
' Reset the random number
' generator.
'''''''''''''''''''''''''''
Randomize

For ResultNdx = LBound(ResultArr) To UBound(ResultArr)
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ' Set the SourceIndex to a random number between
    ' Minimum and Maximum, which are the lower and
    ' upper bounds of SourceArr.
    '''''''''''''''''''''''''''''''''''''''''''''''''
    SourceNdx = Int((Maximum - Minimum + 1) * Rnd + Minimum)
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ' Take the value of SourceArr(SourceNdx) and put
    ' it in the ResultArr array.
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ResultArr(ResultNdx) = SourceArr(SourceNdx)
Next ResultNdx

''''''''''''''''''''''''''''''''
' Return ResultArr as the result
''''''''''''''''''''''''''''''''
RandomLongs = ResultArr

End Function

Getting An Array Of Unique, Non-Duplicated Values

This section describes the UniqueRandomLongs function.

Public Function UniqueRandomLongs(Minimum As Long, Maximum As Long, _
    Number As Long, Optional ArrayBase As Long = 1, _
    Optional Dummy As Variant) As Variant

This function returns an array of unique, non-duplicated, Long Integers. The Minimum and Maximum parameters indicate the lower and upper bounds of the values of the numbers. The numbers in the returned array will always be between Minimum and Maximum (inclusive). The Number parameter indicates the number of values to return. The ArrayBase parameter indicates the LBound of the result array. ArrayBase should be either 0 or 1; the default is 1. If ArrayBase is 0, the result array has bounds of (0, Number -1). If ArrayBase is 1, the result array has bounds of (1, Number). The Dummy parameter is used only when this function is called from a worksheet cell (discussed later). If the function is called from within VBA, the Dummy parameter is ignored. If there is an error in the parameters, such as Minimum > Maximum or Number less than 1, the function returns Null. To test whether the result from the function is a valid array, use the IsArrayAllocated function on the Is Array Allocated page.

The code for UniqueRandomLongs is shown below.

UniqueRandomLongs

Public Function UniqueRandomLongs(Minimum As Long, Maximum As Long, _
            Number As Long, Optional ArrayBase As Long = 1, _
            Optional Dummy As Variant) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' UniqueRandomLongs
' This returns an array containing elements whose values are between the Minimum and
' Maximum parameters. The number of elements in the result array is specified by the
' Number parameter. For example, you can request an array of 20 Longs between 500 and
' 1000 (inclusive).
' There will be no duplicate values in the result array.
'
' The ArrayBase parameter is used to specify the LBound of the ResultArray. If this
' is omitted, ResultArray is 1-based.
'
' The Dummy argument is to be used only when the function is called from a worksheet.
' Its purpose is to allow you to use the NOW() function as the Dummy parameter to force
' Excel to calculate this function any time a calculation is performed. E.g.,
'       =UniqueRandomLongs(100,199,10,NOW())
' If you don't want to recalulate this function on every calculation, omit the Dummy
' parameter. The Dummy argument serves no other purpose and is not used anywhere
' in the code.
'
' The function returns an array of Longs if successful or NULL if an error occurred
' (invalid input parameter).
'
' Note: The procedure creates its own array of size (Maximum-Minium+1), so very large
' differences between Minimum and Maximum may cause performace issues.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim SourceArr() As Long
Dim ResultArr() As Long
Dim SourceNdx As Long
Dim ResultNdx As Long
Dim TopNdx As Long
Dim Temp As Long

''''''''''''''''''''''''''''''''''''''
' Test the input parameters to ensure
' they are valid.
''''''''''''''''''''''''''''''''''''''
If Minimum > Maximum Then
    UniqueRandomLongs = Null
    Exit Function
End If
If Number > (Maximum - Minimum + 1) Then
    UniqueRandomLongs = Null
    Exit Function
End If
If Number <= 0 Then
    UniqueRandomLongs = Null
    Exit Function
End If

Randomize
''''''''''''''''''''''''''''''''''''''''''''''
' Redim the arrays.
' SourceArr will be sized with an LBound of
' Minimum and a UBound of Maximum, and will
' contain the integers between Minimum and
' Maximum (inclusive). ResultArray gets
' a LBound of ArrayBase and a UBound of
' (ArrayBase+Number-1)
''''''''''''''''''''''''''''''''''''''''''''''
ReDim SourceArr(Minimum To Maximum)
ReDim ResultArr(ArrayBase To (ArrayBase + Number - 1))
''''''''''''''''''''''''''''''''''''''''''''
' Load SourceArr with the integers between
' Minimum and Maximum (inclusive).
''''''''''''''''''''''''''''''''''''''''''''
For SourceNdx = Minimum To Maximum
    SourceArr(SourceNdx) = SourceNdx
Next SourceNdx

''''''''''''''''''''''''''''''''''''''''''''''
' TopNdx is the upper limit of the SourceArr
' from which the Longs will be selected. It
' is initialized to UBound(SourceArr), and
' decremented in each iteration of the loop.
' Selections from SourceArr are always in the
' region including and to the left of TopNdx.
' The region above (to the right of) TopNdx
' is where the used numbers are stored and
' no selection is made from that region of
' the array.
''''''''''''''''''''''''''''''''''''''''''''''
TopNdx = UBound(SourceArr)
For ResultNdx = LBound(ResultArr) To UBound(ResultArr)
    ''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Set SourceNdx to a random number between 1 and
    ' TopNdx. ResultArr(ResultNdx) will get its value from
    ' SourceArr(SourceNdx). Only elements of SourceArr
    ' in the region of the array below (to the left of)
    ' TopNdx (inclusive) will be selected for inclusion
    ' in ResultArr. This ensures that the elements in
    ' ResultArr are not duplicated.
    ''''''''''''''''''''''''''''''''''''''''''''''''''
    SourceNdx = Int((TopNdx - Minimum + 1) * Rnd + Minimum)
    ResultArr(ResultNdx) = SourceArr(SourceNdx)
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Now, swap elements SourceNdx and TopNdx of SourceArr,
    ' moving the value in SourceArr(SourceNdx) to the region
    ' of SourceArr that is above TopNdx.  Since only elements
    ' of SourceArr in the region below TopNdx (inclusive) are
    ' possible candidates for inclusion in ResultArr, used
    ' values are placed at TopNdx to ensure no duplicates.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Temp = SourceArr(SourceNdx)
    SourceArr(SourceNdx) = SourceArr(TopNdx)
    SourceArr(TopNdx) = Temp
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Decrment TopNdx. This moves the effective UBound of SourceArr
    ' downwards (to the left), thus removing used numbers from the
    ' possibility of inclusion in ResultArr. This ensures we have
    ' no duplicates in the ResultArr.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    TopNdx = TopNdx - 1
Next ResultNdx

''''''''''''''''''''''''''''''
' Return the result array.
''''''''''''''''''''''''''''''
UniqueRandomLongs = ResultArr

End Function

Example Usage

The following code demonstrates using the UniqueRandomLongs function in VBA.

Sub DemoUniqueRandomLongs()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DemoUniqueRandomLongs
' This demonstrates the UniqueRandomLongs function.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Res As Variant
Dim Min As Long
Dim Max As Long
Dim N As Long

'''''''''''''''''''''''''''''
' Get 20 non-duplicated Longs
' each of which is between
' 101 and 200.
'''''''''''''''''''''''''''''
Min = 101
Max = 200
N = 20

Res = UniqueRandomLongs(Minimum:=Min, Maximum:=Max, Number:=N)

If IsArrayAllocated(Res) = False Then
    Debug.Print "Error from UniqueRandomLongs."
Else
    For N = LBound(Res) To UBound(Res)
        Debug.Print Res(N)
    Next N
End If

End Sub

Function IsArrayAllocated(V As Variant) As Boolean
''''''''''''''''''''''''''''''''''''''''''
' Ensure we have an allocated array.
''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    IsArrayAllocated = Not (IsError(LBound(V)) And _
        IsArray(V)) And (LBound(V) <= UBound(V))
End Function

Using The RandomLongs And UniqueRandomLongs Functions On A Worksheet

Both the RandomLongs and the UniqueRandomLongs functions may be used as Array Formulas on a worksheet. Since these functions return arrays of values, you must array-enter the functions into a range of cells. The size of this range must be the same as the value of the Number parameter. For example, select range A1:K10 and type the formula

=UniqueRandomLongs(100,199,10)

and then press CTRL SHIFT ENTER rather than just ENTER. This will return an array of 10 numbers between 100 and 199 into the range A1:K10. By default, the functions return a horizontal array; that is, an array that is in one row spanning several columns. If you want the result in a vertical range, a single column spanning several rows, you must transpose the result array:

=TRANSPOSE(UniqueRandomLongs(100,199,10))

Since these are array formulas, you must select the result range, type the formula, and then press CTRL SHIFT ENTER rather than just ENTER. If you do this properly, Excel will display the formula enclosed in curly braces { }.

If you enter the functions as show above, Excel will not recalculate and update the functions and their return values when it does a calculation. You have two options to control when Excel will recalculate the functions. The first method, which will cause the functions to recalculate when any calculation is made, is to include NOW() as the final argument to the function. This is the Dummy parameter described in the VBA section of this page.

=UniqueRandomLongs(100,199,10,NOW())

Because NOW() is a volatile function, it will cause a recalculation of the formula whenever Excel makes any calculation. The actual value of the NOW() is not used. The sole purpose of NOW() is to force a calculation.

You can also use the last parameter, the Dummy parameter, to specify a cell that controls calculation. For example:

=UniqueRandomLongs(100,199,10,C1)

The reference to cell C1 will cause the formula to be updated whenver the value of cell C1 is changed.

Random Elements From A Range Of Worksheet Cells

You can also create a function that returns a number of elements in random order and without duplicates from a range of worksheet cells. The function below does just this. You can call as an array formula with a formula like

=RandsFromRange(A1:A10,5)

where A1:A10 is the list of elements from which to pull the values and 5 is the number of values to return. Enter the formula in a range with as many cells as you specify to return, and press CTRL SHIFT ENTER rather than just ENTER. The VBA code is shown below:

Function RandsFromRange(InputRange As Range, GetNum As Long) As Variant
    Dim ResultArr() As Variant
    Dim SourceArr() As Variant
    Dim TopNdx As Long
    Dim ResultNdx As Long
    Dim SourceNdx As Long
    Dim Temp As Variant

    ''''''''''''''''''''''''''''
    ' Allow an input range of only one column or one row.
    ''''''''''''''''''''''''''''
    If InputRange.Columns.Count > 1 And InputRange.Rows.Count > 1 Then
        RandsFromRange = CVErr(xlErrRef)
        Exit Function
    End If
    
    If GetNum > InputRange.Cells.Count Then
        RandsFromRange = CVErr(xlErrValue)
        Exit Function
    End If
    
    ReDim ResultArr(1 To InputRange.Cells.Count)
    SourceArr = InputRange.Value
    Randomize
    TopNdx = UBound(ResultArr)
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' The logic here is identical to logic for the 
    ' UniqueRandomLongs function described above. See the
    ' comments for that procedure for a description of 
    ' the code below.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    For ResultNdx = LBound(ResultArr) To UBound(ResultArr)
        SourceNdx = Int(TopNdx * Rnd + 1)
        ResultArr(ResultNdx) = SourceArr(SourceNdx, 1)
        Temp = SourceArr(SourceNdx, 1)
        SourceArr(SourceNdx, 1) = SourceArr(TopNdx, 1)
        SourceArr(TopNdx, 1) = Temp
        TopNdx = TopNdx - 1
    Next ResultNdx
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Return the ResultArr array back to the caller. 
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    If IsObject(Application.Caller) = True Then
        If TypeOf Application.Caller Is Excel.Range Then
            If Application.Caller.Columns.Count = 1 Then
                RandsFromRange = Application.Transpose(ResultArr)
            Else
                RandsFromRange = ResultArr
            End If
        Else
            ' should never happen. do nothing.
        End If
    Else
        RandsFromRange = ResultArr
    End If
        
End Function

Downloads

You can download a bas code module here. You can also download a complete workbook file with the code and examples.

This page last updated: 11-August-2007