VBA实现Linear Congruential Generator遇Error '6'溢出问题求助

你猜的完全没错！问题确实出在循环里的a * seed乘法运算上。VBA里的Long是32位有符号整数，最大值只有2^31 - 1（也就是2147483647）。咱们算一下第二次循环的数值就懂了：
第一次循环后，seed变成了(48271 * 1234) mod 2147483647 = 59566414，第二次循环时48271 * 59566414的结果接近287亿，远远超出了Long的容纳上限，直接触发了Error 6溢出。

下面给你两种实用的解决方案：

方法1：用64位整数类型（最简单高效） #

从Office 2010开始，VBA支持LongLong（64位有符号整数），它的最大值是9223372036854775807，完全能装下咱们的乘法结果。只需要新增一个临时变量存储中间运算即可：

Sub test()
    Dim a As Long, c As Long, period As Long
    Dim seed As Long, sample As Long, max As Long
    Dim i As Long
    Dim temp As LongLong ' 新增64位临时变量
    seed = 1234
    sample = 2
    max = 100
    a = 48271
    c = 0
    period = 2 ^ 31 - 1
    For i = 1 To sample
        temp = CLngLng(a) * seed + c ' 用LongLong计算超大中间值
        seed = temp Mod period ' 取模后转回Long类型
    Next i
End Sub

方法2：无溢出模运算技巧（兼容旧版Office） #

如果需要适配Office 2010之前的版本，没法用LongLong，可以利用模运算的性质拆分乘法，全程避免数值溢出：

Sub test()
    Dim a As Long, c As Long, period As Long
    Dim seed As Long, sample As Long, max As Long
    Dim i As Long
    seed = 1234
    sample = 2
    max = 100
    a = 48271
    c = 0
    period = 2 ^ 31 - 1
    For i = 1 To sample
        seed = ModMult(a, seed, period) + c
        seed = seed Mod period ' 确保结果在合法范围内
    Next i
End Sub

' 自定义函数：计算 (a * b) mod m，全程不触发32位溢出
Function ModMult(a As Long, b As Long, m As Long) As Long
    Dim result As Long
    result = 0
    a = a Mod m
    Do While b > 0
        If b And 1 Then ' 判断b是否为奇数
            result = (result + a) Mod m
        End If
        a = (a * 2) Mod m
        b = b \ 2 ' 右移一位，等价于除以2取整
    Loop
    ModMult = result
End Function

这个自定义函数通过二进制拆分乘法，把大乘法转化为多次小加法和移位，每一步都取模，全程不会超过Long的范围。

内容的提问来源于stack exchange，提问作者Bmb58