15. Random Numbers

15.1 Overview

A sequence of numbers that takes random values is called a random number. Random numbers are used in situations such as selecting samples randomly. In numerical computation, examples where random numbers are necessary include the Monte Carlo method and simulation of probabilistic phenomena.

Generating random numbers on a computer means obtaining random numbers using a program based on a certain algorithm. Therefore, they are not completely random and possess some regularity. As a result, they are sometimes referred to as pseudo-random numbers.

The advantages of pseudo-random numbers include the ability to generate them whenever needed and the capacity to completely reproduce results for verification purposes. The term pseudo-random numbers generally refers to random numbers that follow a uniform distribution.

Excel provides standard functionality to generate pseudo-random numbers. In VBA, Rnd function and Randomize statement are available, while worksheet functions include RAND, RANDARRAY, and RANDBETWEEN.

15.2 Methods for Generating Random Numbers

15.2.1 Linear Congruential Method

This is a classic and widely-used method for generating pseudo-random numbers.

By selecting an integer initial value \(X_0\) within the range of \(0\) and \(m\), a sequence of integers is generated using the following recurrence relation:
\[
X_{i+1} = (aX_i + c) \space mod \space m
\] Here, \(0 \leq a \lt m\) and \(0 \leq \lt m\).

Random number sequences can be generated by appropriately choosing the parameters \(a\), \(c\) and \(m\). The period of the random number sequence and its statistical properties (randomness) are determined by how these parameters are chosen.

The same initial value \(X_0\) (referred to as “seed”) always produces the same random number sequence. Thus, it can be seen that the sequence repeats at most every \(m\) steps. For this reason, it is preferable to select \(m\) as a sufficiently large value. Additionally, to make the calculation of \(mod \space m\) in the recurrence relation faster by leveraging overflow, \(m = 2^{32}\) is often chosen for a 32-bit word computer.

When real-valued random numbers are needed, the generated integer random numbers are converted by dividing by \(m\) through floating-point arithmetic.

In the VBA function Rnd, the parameters \(m = 2^{24}\), \(a = 16598013\) and \(c = 12820163\) are used. The period is \(2^{24}\). It returns single-precision floating-point (Single) numbers within the range \([0, 1)\) as the function value.

One disadvantage of the linear congruential method is that when m is a power of 2, the lower-order bits are not random. For example, the resulting random numbers may consist only of odd numbers, only of even numbers, or alternate between odd and even numbers (the least significant bit is fixed at either 1 or 0, or alternates between 1 and 0).

However, the randomness of the higher digits is good. Therefore, by using only the upper 16 bits, for example, a better level of randomness can be achieved. Additionally, when converting to real random numbers, the periodicity of the lower bits is not usually a significant issue.

15.2.2 Mersenne Twister (MT)

This is a relatively new method developed in Japan and published in a 1998 paper. It has an extremely long period (2¹⁹⁹³⁷ – 1), good statistical properties, and is also very fast. It is well known for being adopted as the standard random number generator in Python and C++11, among others. This method is also used by Excel’s RAND worksheet function.

15.3 Method for Generating Random Numbers in Excel VBA

15.3.1 Method for Generating Random Numbers Using Rnd function

In Excel VBA, random numbers can be generated using the Rnd function.

The behavior of the Rnd function varies depending on the value of its argument.

• Argument omitted (Rnd()) or positive (Rnd(1), Rnd(2), etc.): Generates a new random number
• Argument is 0 (Rnd(0)): Returns the last generated value without generating a new random number
• Argument is negative (Rnd(-1), Rnd(-2), etc.): Resets the seed based on the argument value and then generates a new random number

Therefore, to make the results reproducible later for verification, first call Rnd with a negative argument, and then call it without an argument thereafter. By specifying the same negative value later and running the process again, the same results can be reproduced.

If the seed is not set (i.e., Rnd is never called with a negative argument), generating new random numbers with the Rnd function always produces the same results. In other words, the first call immediately after starting Excel returns 0.7055475, and all subsequent calls also return a predetermined sequence of values.

Excel VBA provides a Randomize statement. When used without an argument, it resets the seed to a different, appropriate value each time. In other words, if you execute Randomize before generating random numbers with Rnd, the values produced by subsequent calls to Rnd cannot be predicted in advance.

Note that calling Randomize with a specified value, such as Randomize 10, might seem as though it would allow the same results to be reproduced later, but this is not the case. This is because Randomize resets the seed using both the value of the last generated random number and the specified value.

15.3.2 Method for Generating Random Numbers Using XLPack

In general, using the Rnd function is sufficient. However, for cases where double-precision floating-point numbers are required or particularly long periods and high-quality randomness are needed, the following VBA functions implementing the Mersenne Twister are provided: InitGenrand, GenrandInt32, GenrandInt31 and GenrandReal53.

InitGenrand initializes the generator using the value of its argument (the seed). By using the same seed value, the same results can be reproduced later.

GenrandInt32, GenrandInt31 and GenrandReal53 generate pseudorandom numbers as signed 32-bit integers, unsigned 31-bit integers, and 53-bit real numbers in the interval [0, 1), respectively. These functions should normally be initialized before use. However, if they are called without initialization, they are automatically initialized with a seed of 5489.

Example

First, perform initialization using InitGenrand, then call GenrandInt32 to generate ten 32-bit integer random numbers.

An example VBA program is shown below.

Sub Start()
    Dim Seed As Long, I As Long
    Seed = 13
    Call InitGenrand(Seed)
    For I = 1 To 10
        Debug.Print GenrandInt32()
    Next
End Sub

The following results were obtained.

  
-954760878 
-1686456144 
 1020231754 
-603726320 
-754717978 
-459635870 
-147106060 
 769458329 
-117677332 
-1036873798 

Although the Mersenne Twister generates random numbers quickly, in this case the overhead of function calls from VBA is significant, resulting in slower random number generation. Therefore, it is not suitable for applications that require a very large number of random numbers.