VBA Random

To generate random numbers, use the Randomize statement and the Rnd function.

Random number generation is used in programming across a variety of fields in a variety of applications. Some applications of random number generation are monte carlo simulations, cryptography, testing sorting algorithms, generating sample data, adding unpredictability to games, or to fairly select an individual from a group.

It is not currently possible to generate "truly random" numbers using a computer because any algorithm used to generate an outcome would be deterministic. It is arguable that there may be no such thing as "true randomness" and the entire universe may be deterministic, but for now this is a matter of philosophical debate (see Determinism). Notwithstanding, in the current paradigm of human perception it is possible to achieve a "practical randomness" by generating outcomes that are not easily predictable. An example of practical randomness might be a coin toss. The outcome of a coin toss may be determined before the coin has come to rest, but it is ostensibly beyond the capabilities of naked human perception to predict the outcome to the level of absolute certainty. Algorithms can be used to generate pseudo-random outcomes which are similarly difficult to predict under practical circumstances, although perhaps not as random as a coin toss due to the sheer complexity of the physical universe. Some algorithms are better at generating pseudo-random outcomes than others. VBA's pseudo-random number generator is not the best and it may be wise to use a better one for certain applications such as monte carlo simulations.

Randomize Statement

The Randomize statement is used to set the seed value for VBA's pseudo-random number generator. Randomize can take a numeric argument as a seed. If a seed value is not provided, Randomize will use the result of the Timer function as the seed. The Rnd function may not generate sufficiently random numbers on its own. Randomize improves the results of Rnd and should be used every time before Rnd is called.

Rnd Function

The Rnd function returns a number greater than or equal to 0 and less than 1 from a sequence of pseudo-random numbers.

When Rnd is first called after starting the application it will always return the same sequence of numbers unless Randomize is used. Using Randomize with a specific number will alter the sequence in a predictable way. Using Randomize with no argument uses the result of the Timer function as the seed value and makes the Rnd function less predictable. Restarting the application will restart the sequence.

Passing the Rnd function a number less than 0 will return the same number every time.

Passing the Rnd function 0 will return the previous number returned by the Rnd function.

Passing the Rnd function a number greater than 0 or not passing any number will return the next number in the pseudo-random number generator's sequence.

Using Rnd and Randomize

Public Function RandomLong(MinValue As Long, MaxValue As Long) As Long
    If MinValue > MaxValue Then Err.Raise 5
    Randomize
    RandomLong = Int((MaxValue - MinValue + 1) * Rnd + MinValue)
End Function

Public Function RandomDouble(MinValue As Double, MaxValue As Double) As Double
    If MinValue > MaxValue Then Err.Raise 5
    Randomize
    RandomDouble = (MaxValue - MinValue) * Rnd + MinValue
End Function

Public Sub RepeatSequence()

    'Call Rnd with negative value
    Debug.Print Rnd(-1) '0.224007

    'Call Randomize with specific number
    Randomize 1

    'Sequence will always be the same
    Debug.Print Rnd '0.3335753
    Debug.Print Rnd '6.816387E-02
    Debug.Print Rnd '0.5938293
    Debug.Print Rnd '0.7660395
    Debug.Print Rnd '0.1892894

End Sub