excel - Excel VBA 中出现 Double 类型溢出错误

Question

我在 Excel VBA 中遇到了溢出错误，并且无法找到解决该问题的方法。虽然 Microsoft 的文档表明 double 的范围应达到 ~1.8E308，但我收到了明显低于该阈值的数字的溢出错误。我的代码如下:

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 

我正在尝试创建一个伪随机数生成器，它可以接受任何“种子”值，最多可达 2 ^ 31 - 1。for 循环应该能够迭代至少 9,999 次(即 n = 10000 )。如果在前几次迭代中没有遇到溢出错误，则很可能在任何后续迭代中都不会遇到溢出错误。

如您所见，在进行任何计算之前，我将每个整数转换为 double 值。我知道数组大大增加了计算的字节大小，但这似乎不是当前的问题，因为我直接将上面的示例计算复制到即时窗口中，但仍然收到溢出错误。我尝试在网上寻找解决方案但没有结果，所以我非常感谢任何意见。提前致谢!

Answer 1

尝试使用 Chip Pearson 的 XMod 函数:

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

正如他所说:

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.

函数代码:

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