Linear Congruential Generator on VBA

Question

i'm trying to replicate the Linear Congruential Generator in VBA but my procedure returns to me an Error '6': Overflow...

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 = (a * seed + c) Mod period
    Next i
End Sub

I think the problem is in the first expression of the for cycle, in detail

a*seed

in the second step of the cycle. Any suggestion to solve the problem without splitting

a*seed

in

(100*seed+100*seed+100*seed+...+(a-100*n)*seed

@CallumDA I have tried declaring every number as Long but it still goes in overflow — Bmb58, Mar 22 '18 at 15:11
I've updated your question so the code is minimal and instantly repeatable -- since I've updated the declarations and the problem persists I've also deleted my answer for now — CallumDA, Mar 22 '18 at 15:34
@MathieuGuindon, yeah. I swapped `seed =` for `Debug.Print` and it works just fine - the output is `59566414`. Well within the limits of a `Long` — CallumDA, Mar 22 '18 at 15:54
Possibly relevant: https://stackoverflow.com/a/4378073/1188513 — Mathieu Guindon, Mar 22 '18 at 15:57

score 2 · Answer 1 · answered Mar 22 '18 at 16:15

2

You can use the decimal subtype of variant and write your own mod function for decimals:

Function DecMod(a As Variant, n As Variant) As Variant
    Dim q As Variant
    q = Int(CDec(a) / CDec(n))
    DecMod = a - n * q
End Function

Sub test()
    Dim a As Variant, c As Variant, period As Variant
    Dim seed As Variant, sample As Long, max As Long
    Dim i  As Long

    seed = CDec(1234)
    sample = 5
    max = 100

    a = CDec(48271)
    c = 0
    period = CDec(2 ^ 31 - 1)

    For i = 1 To sample
        Debug.Print seed
        seed = DecMod(seed * a + c, period)
    Next i
End Sub

Output:

answered Mar 22 '18 at 16:15

John Coleman

51,337
7
54
119

Nice workaround (+1)... don't suppose you know the reason for the original overflow? I was really hoping for an explanation! – CallumDA Mar 22 '18 at 17:03
1

@CallumDA In the original code (using `Long`), the overflow occurs in the second pass through the loop when VBA tries to evaluate `1234*59566414` as a Long. If one of the operand overflows then the entire calculation overflows even if the resulting `Mod` doesn't. It would be nice if the implementation of `Mod` was more robust. – John Coleman Mar 22 '18 at 17:40

Linear Congruential Generator on VBA

1 Answers1