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 Next 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) Loop SuperMod = a Mod b End Function
MsgBox SuperMod("654654321902010548705495321500805040401121210900506078798456849816241968462130000051204789540453745898347543753489751261610093245892737467263468234623689898054456361110010151021101827", 99999999)
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) Loop SuperMod2 = a - Int(a / b) * b End Function
This version allows for:
MsgBox SuperMod2("654654321902010548705495321500805040401121210900506078798456849816241968462130000051204789540453745898347543753489751261610093245892737467263468234623689898054456361110010151021101827", 999999999999999)
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.
IF NOT, WHAT OTHER TECHNIQUE CAN I PERUSE FOR ARBITRARILY SIZED DIVISORS?