Visual Basic for Applications/A PRNG for VBA

Summary

 * A pseudo-random number generator (PRNG), if run for long enough, generates a characteristic sequence that is based on its algorithm. This sequence repeats forever and is invariant.  The Rnd function of VBA, if placed in a loop without a parameter, and without making use of Randomize at all, will generate 16,777,216 values between zero and unity, then start again at the beginning, making a repeating sequence with a cycle length of 16,777,216.  The only option for the user is to choose the starting point within that one sequence.  This is done by choosing a start value or seed.  In the case of Rnd, the start value is chosen in two ways: by default, using the system timer, or with a user-set number.  Again, the start parameters that are set by the user do not make new sequences, they just decide which bit of that one long sequence will be used. Linear Congruential Generators (LCG), the type used by Microsoft's Rnd function are described in detail at Linear congruential generator.
 * The maximum cycle length for a single stage LCG is equal to its modulus. For combined generators, the maximum cycle length is equal to the least common multiple of the cycle lengths of the individual generators. A well-designed generator will have the maximum cycle length and consist only of unique values throughout its sequence, but not all generators are well-designed.  The above link describes the design values required to make an LCG with a maximum cycle length over all of its starting values.
 * The code module below contains the Wichmann-Hill (1982) CLCG (combined LCG) in VBA and is fully functional. It is called RndX and is used in conjunction with its own RandomizeX. It has a much longer repeat cycle than Microsoft's Rnd. A summary of the most useful settings for RndX is given, with additional details for those who need them in a drop box.  Sadly, this author lacks the tools and knowledge for any serious testing of number generators, so the offerings below are likely to be of interest only to beginners.
 * Long-cycle generators are awkward to study in Excel. The cycles of both Microsoft's Rnd and the user function RndX are much too long to write a complete cycle to a single worksheet column.  The solution is either to list only parts of long streams or to make a number generator with a cycle short enough for a full listing.  Listing in a single column this way allows confirmation of the repeat cycle length, then after trimming the rows to a complete set, counting rows after the removal of duplicates will confirm for the skeptical that all of the values are unique. A module is included with procedures to list one section of the Wichmann-Hill implementation, that fits into about 30269 rows or so, and another with a very simple generator for further testing, that fits into just 43.

Microsoft's Rnd algorithm
Microsoft's Visual Basic for Applications (VBA), at present uses a linear congruential generator (LCG) for pseudo-random number generation in the Rnd function. Attempts to implement the Microsoft algorithm in VBA failed owing to overflow. The following is its basic algorithm. x1 = ( x0 * a + c ) MOD m  and; Rnd = x1/m where: Rnd = returned value m = modulus = (2^24) x1 = new value x0 = previous value (initial value 327680) a = 16598013 c = 12820163 Repeat length = m = (2^24) = 16,777,216 Similarities will be noticed between Microsoft's Rnd and the one below, described by Wichmann-Hill (1982), in which a sum of three LCG expressions is used in the production of each output number. The combination of expressions gives RndX, with the coded values, its much improved cycle length of: Cycle length = least_common_multiple(30268, 30306, 30322) = 30268 * 30306 * 30322 / 4 = 6,953,607,871,644

VBA Code - Wichmann-Hill (1982)
A reminder about module level variables may be in order. Module level variables hold their values between procedure runs. In fact they will retain values until the VBA is no longer used at all or the code is edited. The code has been laced with resets for these variables, to ensure starting with intended values, as opposed to old stored ones from the previous top procedure runs.

On a cautionary note; although this algorithm has improved properties over the resident Rnd, the applications on which these generators are run are not particularly secure. Consider also that the output of all LCG coding is entirely predictable if the starting value is ever known. In fact, if any part of such a stream is known, then it is possible for those who intend harm to find the entire stream by comparing it with stored values. These facts when taken together limit the use of such a VBA implementation to study or non-critical applications.

That said, these are likely to be the most useful parameter configurations: In each case RandomizeX should only be called once, before and outside any generator loop that contains RndX. This advice also applies to the Microsoft function Rnd and its companion Randomize.
 * To produce outputs with an unpredictable start point, and a different start point each time it is run:
 * Call RandomizeX without any parameter before calling RndX, also without any parameter. This uses the system timer.
 * To produce outputs from a large set of start points, repeatable, and chosen by a user parameter:
 * Call RandomizeX with any numeric parameter before calling RndX without any parameter. Changed RandomizeX parameter values result in different start points of the standard algorithm stream.
 * To produce an unpredictable, single value, different each time it is run:
 * Call RandomizeX without any parameter before calling RndX with a parameter of zero. This uses the system timer.
 * To produce a repeatable single value, related to, and chosen by a user parameter:
 * Call RandomizeX with any numeric parameter before calling RndX with a parameter of zero. Changed RandomizeX parameter values result in different values that are peculiar to each parameter.
 * Refer to the drop box below for a complete tabulation of the parameter settings and their outcomes.

The code in this section should be saved as a separate standard module in Excel.

Simpler Tests of PRNGs
The code module below contains a stripped down version of the Wichmann-Hill (1982) algorithm, in fact using only the first of its three calculated sections. It will make several complete streams of values on Sheet1 of the workbook in which it is run, using different start values. Notice that the first values are all repeated at row 30269, as will the whole stream if extended. After producing the list, use the spreadsheet's functions for column sorting and the removal of duplicates to see that each column contains the appropriate number of unique entries. An even simpler generator with a repeat cycle of just 43 is also included that might make study more manageable, and the cycle of Microsoft's Rnd can be seen to repeat at 16777216 (+1) by running TestMSRnd.

The code in this section should be saved as a separate standard module in Excel.