Overflow Error in Excel VBA with Type Double

Question

I have run into an overflow error in Excel VBA and cannot find my way around it. While Microsoft's documentation indicates that the range for doubles should reach ~1.8E308, I am receiving an overflow error for numbers significantly lower than that 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 attempting to create a pseudo-random number generator that can take in any 'seed' value up to and including 2 ^ 31 - 1. The for loop should be able to iterate at least 9,999 times (i.e. n = 10000). If the overflow error is not encountered within the first few iterations, it most likely will not be encountered for any subsequent iteration.

As you can see, I am converting each integer to a double before any calculation. I am aware of the fact that arrays substantially increase the byte size of the calculation, but that does not appear to be the current issue as I directly copied the example calculation above into the immediate window and still received the overflow error. My attempts to find a solution online have resulted in no avail, so I would really appreciate any input. Thanks in advance!

42. Mod 2. ^ 31 also crashed, unlike 42. Mod (2. ^ 31 -1) ... maybe an undocumented (google gave nothing) feebleness of mod. Who uses that anyway? O.O — Pierre
– Pierre, Commented Oct 12, 2016 at 15:51
@DavidZemens Thanks for the link! Funny thing is, I've already tried creating my own function, still results in an overflow error. Also, the pseudo-random number generator works in worksheet form with no overflow, but I need to create a VBA function for it. — B. Woodruff
– B. Woodruff, Commented Oct 12, 2016 at 16:07

Community · Accepted Answer · 2018-05-08 21:49:10Z

3

Try using Chip Pearson's XMod function:

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

As he notes:

You can also get overflow errors in VBA using the Mod operator with very large numbers. For example,
Dim Number As Double
Dim Divisor As Double
Dim Result As Double

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

Code for the function:

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

Additional details:

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

edited May 8, 2018 at 21:49

CommunityBot

11 silver badge

answered Oct 12, 2016 at 16:00

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

B. Woodruff Over a year ago

Thanks again! I had tried creating then using a similar function and still received the overflow error. Now I've copied and pasted Pearson's.. Still receiving an overflow error.

B. Woodruff Over a year ago

Wow, nvm.. In my initial attempt after creating Pearson's function, I was converting to longs and not doubles x). The PRNG function now works syntatically and semantically. Thank you!

Collectives™ on Stack Overflow

Overflow Error in Excel VBA with Type Double

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related