Finding a good replacement for Rnd in Excel VBA

Question

The Rnd function in Excel is known to be weak, whereas the RAND function in Excel is based on the Mersenne algorithm and much stronger. I've been trying to find a fast and strong alternative to Rnd, and looked at various options, including the use of Mersenne, but this requires a lot of code.

Answer 1

I presume that by "quality randomness" you intend that random numbers in a sequentially generated set be independent. Some RNGs are supurb when taken one number at a time, but have strong correlations between each random number and its successor. Easily generated by x' = (x + ir) mod 1, where ir is any irrational number such as the square root of 2. (You cannot actually keep an irrational number in computer memory, but you can get close enough for gubbermint work.) In fact, this RNG is so perfectly uniform that for a single dimension integral, the usual Monte Carlo methods for reducing variance are worthless. Weyl's theorem is powerfully at work here.

Numerical Recipes says to use a combination of two different methods. Two such methods are MWC and shifted XOR. These are both easily implemented. If you need a long period, you can get something like 2^400,000 by using a lagged MWC. Neither you, nor the sun, will last long enough to exhaust that many. For details, view the book on line, or scan for the papers by George Marsaglia.

Important point: the MWC method can deliver a zero, which will crash your normal distribution function if you are using one. So it needs to trap out zero. Also, under MWC all zeroes are not created equal because the state of the seed includes, in addition to the random number, the carry. And that is certainly NOT zero, or the algorithm would deliver nothing but zero, even after the sun goes out.

For what it's worth, I have programmed both of these methods on my handheld calculator and subjected them to obvious tests that they passed, but which RANDU failed.

Answer 2

You could use truly random numbers - as shown in my project VBA.Random.

It contains a direct replacement for Rnd:

' Returns a true random number as a Double, like Rnd returns a Single.
' The value will be less than 1 but greater than or equal to zero.
'
' Usage: Excactly like Rnd:
'
'   TrueRandomValue = RndQrn[(Number)]
'
'   Number < 0  ->  The same number every time, using Number as the seed.
'   Number > 0  ->  The next number in the pseudo-random sequence.
'   Number = 0  ->  The most recently generated number.
'   No Number   ->  The next number in the pseudo-random sequence.
'
' 2019-12-21. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RndQrn( _
    Optional ByVal Number As Single = 1) _
    As Double
    
    Static Value            As Double
    
    Select Case Number
        Case Is > 0 Or (Number = 0 And Value = 0)
            ' Return the next number in the random sequence.
            Value = CDbl(QrnDecimal)
        Case Is = 0
            ' Return the most recently generated number.
        Case Is < 0
            ' Not supported by QRN.
            ' Retrieve value from RndDbl.
            Value = RndDbl(Number)
    End Select
    
    ' Return a value like:
    ' 0.171394365283966
    RndQrn = Value
    
End Function

Also, a demo (RandomQrn.xlsm) is included to download.

This has set a Reference to Microsoft Access 16.0 Object Library which has the Nz function used. If you prefer not to have this reference, this substitute will do:

' Replacement for the function Application.Nz() of Access.
'
' 2015-12-10. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Nz( _
    ByRef Value As Variant, _
    Optional ByRef ValueIfNull As Variant = "") _
    As Variant

    Dim ValueNz     As Variant
    
    If Not IsEmpty(Value) Then
        If IsNull(Value) Then
            ValueNz = ValueIfNull
        Else
            ValueNz = Value
        End If
    End If
        
    Nz = ValueNz
    
End Function

Answer 3

Another option is to call the Excel RAND function from VBA, but this is extremely slow when done one at a time. However, the new function RANDARRAY in Excel365 allows VBA to call a large quantity of random numbers from Excel in one go, use them as needed, and go back for more when necessary. This approach is fast (only 4x slower than Rnd, and faster than Mersenne code) and compact - the code is below.

I'm sharing this in the hope of finding the best collective solution to this problem.

Function RandXL() As Single
  Static Remaining As Long, R() As Variant
  If Remaining = 0 Then 'get more numbers if necessary
    R = Application.WorksheetFunction.RandArray(1000, 1)
    Remaining = 1000
  End If
  RandXL = R(Remaining, 1)
  Remaining = Remaining - 1
End Function