Excel and also Excel VBA have no built-in support for arbitrary precision arithmetic. There are a few very large add-ins that can be installed to do these sorts of calculations where the operands are hundreds to thousands of digits long.

For recreation I am attempting to code in VBA similar functions without creating a big-integer class.

I am not a mathematician. But I have discovered that it was fairly easy to implement functions for Addition, Subtraction, and Multiplication.

For example, here is my function for addition for arbitrarily large operands:

Function AddStrings$(ByVal a$, ByVal b$)

    Dim carry&, i&, k&, ka&, kb&, p&, pLen&, s$, z&, subt
    Const size& = 28

    ka = Len(a): kb = Len(b)
    Select Case kb
        Case Is < ka: k = ka: If k > kb Then b = String$(k - kb, "0") & b
        Case Else:    k = kb: If k > ka Then a = String$(k - ka, "0") & a
    End Select
    s = Space$(k + 2)

    p = k + 1
    For i = -Int(-k / size) To 1 Step -1
        p = p - size
        pLen = size: If p < 1 Then p = 1: pLen = k Mod size
        subt = CDec(Mid$(a, p, pLen)) + Mid$(b, p, pLen)
        If carry Then subt = subt + carry
        z = Len(subt)
        Select Case z
            Case pLen:      carry = 0: Mid$(s, p, pLen) = subt
            Case Is > pLen: carry = 1: Mid$(s, p, pLen) = Mid$(subt, 2)
            Case Else:      carry = 0: Mid$(s, p, pLen) = String$(pLen - z, "0") & subt
        End Select
    If carry Then s = "1" & s
    AddStrings = Trim$(s)

End Function

This function seems to work perfectly regardless of the size of operands I give it. It breaks the operands down into 28-digit chunks and iteratively processes them.

My multiplication routine works perfectly as well. It uses 14-digit chunks.

However, Division and Mod have me scratching my head.

My strategy that worked well for the Addition, Subtraction, and Multiplication was segmenting the operand strings into a number of digits that would fit into VBA native data types and doing the calculations piecemeal. This worked very well and I can now do those calculations for string values hundreds of digits long.

But this does NOT work for division or modulus.

By tinkering I have discovered that for modulus I can have the dividend any length and get the correct result through iteration of the native Mod function, as long as the divisor fits into a native data type.

For example this function works for HUGE dividends but only for divisors up to eight digits in length:

Function SuperMod(ByVal a$, b$)
    Do While Len(a) > 9
        a = Mid$(a, 1, 9) Mod b & Mid$(a, 10)
    SuperMod = a Mod b
End Function

For example:

MsgBox SuperMod("654654321902010548705495321500805040401121210900506078798456849816241968462130000051204789540453745898347543753489751261610093245892737467263468234623689898054456361110010151021101827", 99999999)

...correctly displays: 84245606

But attempting even one more digit in the divisor results in an error because the native Mod function cannot handle divisors that large.

My goal is to figure out a way where the divisor can be any arbitrary length, just like the dividend currently can be.

I was able to figure out a different way where the divisor can be up to 15-digits with an arbitrarily sized dividend by replacing a Mod b with a - Int(a / b) * b. But this fails for divisors larger than 15 digits because the native division and Int() functions fail at that point:

Function SuperMod2(ByVal a$, b$)
    Do While Len(a) > 15
        a = Mid$(a, 1, 15) - Int(Mid$(a, 1, 15) / b) * b & Mid$(a, 16)
    SuperMod2 = a - Int(a / b) * b
End Function

This version allows for:

MsgBox SuperMod2("654654321902010548705495321500805040401121210900506078798456849816241968462130000051204789540453745898347543753489751261610093245892737467263468234623689898054456361110010151021101827", 999999999999999)

...correctly displays: 194745570864677

VBA does support a Decimal (28-digits of precision) data subtype for Variant variables, but for some reason, that does not help in this case. But even if it did, it would just extend by roughly twice the size of divisors this method can handle.

What sort of math manipulation can I employ to allow for divisors of arbitrary size both for modulus and for division?

I do not see how to chunk-ify both operand strings like I was able to do for Addition, Subtraction, and Multiplication to iteratively process the results.

Everything I find by searching (even here on Stack) refers to large dividends. As you can see, I have that figured out. My problem is arbitrarily large DIVISORS.

I would very much appreciate anyone taking the time to point me in the right direction.

IS CHUNKING THE DIVISOR OPERAND TO ITERATE DIVISION POSSIBLE? As shown and outlined, that technique works fine for Addition, Subtraction, and Multiplication. I cannot see how to make it work for the DIVISOR operand in Division or Mod.


  • $\begingroup$ Sorry but so what? This question even needs to be edited substantially before to be asked in ComputerScience.SE for example and it seems unsuitable for CompSci.SE. $\endgroup$ – Alone Programmer May 18 '20 at 20:48
  • $\begingroup$ Sorry, but WHAT to you? The question is very clear. $\endgroup$ – Xyrph May 18 '20 at 20:53
  • $\begingroup$ Your down-vote is lazy. $\endgroup$ – Xyrph May 18 '20 at 21:16
  • $\begingroup$ Sorry but even at the end there is no clear question. Can you please edit your question to have an actual question with ? at the end? $\endgroup$ – Alone Programmer May 18 '20 at 21:27
  • $\begingroup$ I edited the question. $\endgroup$ – Xyrph May 18 '20 at 21:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.