Overflow error in Excel VBA with double type

Question

I encountered an overflow error in Excel VBA and cannot find a way around this. Although the Microsoft documentation indicates that the doubling range should reach ~ 1.8E308, I get an overflow error for numbers well below this threshold. My code is as follows:

Public Function Fixed_Sample_Nums(ByVal n As Long, seed As Long) As Double()

    Dim x() As Double, y() As Double, i As Long
    ReDim y(1 To n)
    ReDim x(1 To n)

    x(1) = (CDbl(48271) * seed) Mod CDbl(2 ^ 31 - 1)

    For i = 2 To n
        x(i) = (CDbl(48271) * CDbl(x(i - 1))) Mod (CDbl(2 ^ 31 - 1))
        y(i) = CDbl(x(i)) / CDbl(2 ^ 31 - 1)
    Next i

    Fixed_Sample_Nums = y

End Function

'I receive the error in the first iteration of the for loop with 
'seed equal to any value >= 1 (i.e. w/ seed = 1): 

Debug.Print((CDbl(48271) * CDbl(48271)) Mod (CDbl(2 ^ 31 - 1))) 

'results in an overflow error 

I am trying to create a pseudo-random number generator that can take any "seed" value up to 2 and 31 inclusive. The for loop should be able to iterate at least 9999 times (i.e. n = 10000). If an overflow error does not occur during the first few iterations, it will most likely not be encountered for any subsequent iteration.

, double . , , , . , . !

Answer 1

Chip Pearson XMod:

x(i) = XMod((CDbl(48271) * seed), CDbl(2 ^ 31 - 1))

:

VBA Mod . ,

Dim Number As Double
Dim Divisor As Double
Dim Result As Double

Number = 2 ^ 31
Divisor = 7
Result = Number Mod Divisor ' Overflow error here.

:

Function XMod(ByVal Number As Double, ByVal Divisor As Double) As Double
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' XMod
' Performs the same function as Mod but will not overflow
' with very large numbers. Both Mod and integer division ( \ )
' will overflow with very large numbers. XMod will not.
' Existing code like:
'       Result = Number Mod Divisor
' should be changed to:
'       Result = XMod(Number, Divisor)
' Input values that are not integers are truncated to integers. Negative
' numbers are converted to postive numbers.
' This can be used in VBA code and can be called directly from 
' a worksheet cell.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Number = Int(Abs(Number))
    Divisor = Int(Abs(Divisor))
    XMod = Number - (Int(Number / Divisor) * Divisor)
End Function

:

http://www.cpearson.com/excel/ModFunction.aspx