VBA calculate with numbers in range 10^40

Question

I asked a similar question yesterday, but for smaller numbers. So today again :-)

I am trying to calculate: A random number in the range 10^40 mod a random number in the range of 100. I saved the first random number as a Variant because the number is too big to be saved as an Integer.

The result of the solution should be something between 1 and 100, so it could be saved as an Integer.

For example calculation: 3545123008254150481059068660418190917230 mod 97 = 37. How to calc this by using VBA.

My idea is to break down the numbers into smaller numbers (prim factorization) and calculate the modulo on smaller numbers, so we don't need to calculate in the range of 10^40.

Here the solution with smaller numbers:

Sub test_this()
    Debug.Print find_modulus("300400000717120000131495", 97)
End Sub

Function find_modulus(large_number As String, divisor As Long)
    Dim quotient As Variant, integer_quotient As Variant
    quotient = CDec(large_number)
    integer_quotient = Int(quotient / divisor) * divisor
    find_modulus = quotient - integer_quotient
End Function

The Output in this example is 1 and it is correct.

But with this calculation I get errors...
For example calculation: 3545123008254150481059068660418190917230 mod 97 = 37. How to calc this by using VBA.

Per my comment on the previous question, if you're taking `a mod b` for two random numbers and `b` is no greater than 100, why does `a` need to be so large? — jsheeran, Jan 05 '23 at 12:11
I don't know which number a is, but I know b. By the operation a mod b I want to validate a number, so a needs to be this large cause I don't know the size of the number. — Fabian Müller, Jan 05 '23 at 12:13
I believe this is off-topic for this group, but I suggest you download the [xNumbers](https://www.thetropicalevents.com/Xnumbers60.htm) add-in and examine the code for examples of how to work with numbers that are greater than VBA precision allows. It has run for me in all versions of Excel, although in later versions it causes an extra Excel instance to be opened. — Ron Rosenfeld, Jan 05 '23 at 12:14
VBA cannot handle such large numbers, even not with datatype Decimal. I would say your best bet is to look for a language/tool that can handle this (eg Python). You could create a mini program and call that from VBA. — FunThomas, Jan 05 '23 at 12:17
May be worth clarifying in the question that you're using a known value of `b` instead of a random number. Would it make sense to split the string into several numbers, each less than 1 billion, and verify the mod of those numbers separately? In fact, since you're presumably performing this verification against a known number, would it be correct to describe these numbers as "arbitrary" instead of "random"? — jsheeran, Jan 05 '23 at 12:18
thanks for your ideas. I will try it with a python script. yes arbitrary would be a better way to describe it. — Fabian Müller, Jan 05 '23 at 12:33
What is the motivation? A random number in the range `0 to 10^40` mod `97` is for all practical purposes indistinguishable from a random number in the range `0 to 96`. In principle, there is an extremely small modulo bias, but it is unlikely to be an issue even if generating millions of random numbers. — John Coleman, Jan 05 '23 at 12:47

Toddleson · Accepted Answer · 2023-01-05T14:49:38.557

3

You can do the division in chunks, treating large numbers as a string and chopping them up into smaller pieces which can be processed in sequence.

To start with your example: 3545123008254150481059068660418190917230 Mod 97. We can take the first 9 digits "354512300". The Mod of this is "95". We prepend that onto the string and then take 9 digits to form the next sequence, "958254150". Repeat that for the entire string and eventually your last section is "55230", whose modulus is 37!

Function find_modulus(ByVal number As String, ByVal divisor As Long) As Long
    While Len(number) > 9
        number = (CLng(Left(number, 9)) Mod divisor) & CStr(Mid(number, 10))
    Wend
    find_modulus = CLng(number) Mod divisor
End Function

edited Jan 05 '23 at 14:49

answered Jan 05 '23 at 14:43

Toddleson

4,321
1
6
26

Great. Is there a specific reason why you're using the old `While...Wend`? What is the significance of the number `9`? Couldn't it be up to `15`? – VBasic2008 Jan 05 '23 at 15:03
1

@VBasic2008 I like `While Wend` and it doesn't seem to be functionally different from `Do While Loop`. If there's no difference, I prefer more excuses to use `Wend`. On that note, thank you for this comment, which allows me another opportunity to say `Wend`. – Toddleson Jan 05 '23 at 15:20
@VBasic2008 I think my older version of VBA might not have LongLong. I get errors after 9 digits. Looking at the [data type summary](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary), I think my Excel only has 4-byte Longs. – Toddleson Jan 05 '23 at 15:23
LOL. I hoped for something like that. Figured out the '9-issue'. It's related to the *Long* data type (`CLng`). – VBasic2008 Jan 05 '23 at 15:26
I don't see mathematically why this works, and I doubt it works in general. – President James K. Polk Jan 05 '23 at 16:12
2

`Wend` is possibly the most poetic keyword in any major programming language. I used to use `While ... Wend` for that reason but became more prosaic in my old age and switched to `Do While`. – John Coleman Jan 05 '23 at 17:07
1

@PresidentJamesK.Polk this is the digital equivalent to [long division](https://www.splashlearn.com/math-vocabulary/division/long-division#:~:text=Long%20Division%20is%20an%20instrument,that%20will%20be%20left%20ungrouped.). The grade-school method that is taught as the way to divide two numbers without a calculator. This is mathematically sound. – Toddleson Jan 05 '23 at 17:14
Super! Voted it up. Long division was the key... I studied and enjoyed it but I did not think of it when looked to the question... Good for you! – FaneDuru Jan 06 '23 at 09:26

VBasic2008 · Answer 2 · 2023-01-05T14:52:15.840

Get Remainder From Large Integer

Sub GetLargeRemainderTEST()
    
    Const LargeInteger As String = "3545123008254150481059068660418190917230"
    Const Divisor As Long = 97

    Debug.Print GetLargeRemainder(LargeInteger, Divisor) ' 37

End Sub

Function GetLargeRemainder( _
    ByVal LargeInteger As String, _
    ByVal Divisor As Long) _
As Long
    
    Dim Dividend As String
    Dim Remainder As Long
    
    Do While Len(LargeInteger) > 0
        Dividend = IIf(Remainder = 0, "", CStr(Remainder)) _
            & Mid(LargeInteger, 1, 1)
        Remainder = CLng(Dividend) Mod Divisor
        'Debug.Print Dividend, Remainder, LargeInteger
        LargeInteger = Mid(LargeInteger, 2, Len(LargeInteger) - 1)
    Loop

    GetLargeRemainder = Remainder

End Function

Debug.Print Result

3              3            3545123008254150481059068660418190917230
35             35           545123008254150481059068660418190917230
354            63           45123008254150481059068660418190917230
635            53           5123008254150481059068660418190917230
531            46           123008254150481059068660418190917230
462            74           23008254150481059068660418190917230
743            64           3008254150481059068660418190917230
640            58           008254150481059068660418190917230
580            95           08254150481059068660418190917230
958            85           8254150481059068660418190917230
852            76           254150481059068660418190917230
765            86           54150481059068660418190917230
864            88           4150481059068660418190917230
881            8            150481059068660418190917230
85             85           50481059068660418190917230
850            74           0481059068660418190917230
744            65           481059068660418190917230
658            76           81059068660418190917230
761            82           1059068660418190917230
820            44           059068660418190917230
445            57           59068660418190917230
579            94           9068660418190917230
940            67           068660418190917230
676            94           68660418190917230
948            75           8660418190917230
756            77           660418190917230
776            0            60418190917230
0              0            0418190917230
4              4            418190917230
41             41           18190917230
418            30           8190917230
301            10           190917230
109            12           90917230
120            23           0917230
239            45           917230
451            63           17230
637            55           7230
552            67           230
673            91           30
910            37           0
 37

score 0 · Answer 3 · answered Jan 08 '23 at 10:49

Just for the sake of optimizing, the next versions allow larger chunks. But most of the credit must go to the once firstly thinking of Long Division as a way to solve the issue:

'optimized way, using CLngLng conversion (working in 64 bit systems)
'based on the fact that the maximum accepted by Mod is LongLong(9223372036854775807^):
Function LargeNo_ModCLngLng(largeNo As String, div As Long) As Long
    While Len(largeNo) > 18
        largeNo = (CLngLng(left(largeNo, 18)) Mod div) & CStr(Mid(largeNo, 19))
    Wend
    LargeNo_ModCLngLng = CDec(largeNo) Mod div
End Function

'optimized way, using CDec (working in all systems):
'arithmetically calculating the modulus:
Function LargeNo_ModDec(largeNo As String, div As Long) As Long
    While Len(largeNo) > 23
        largeNo = CDec(left(largeNo, 23)) - Int(CDec(left(largeNo, 23)) / div) * div & CStr(Mid(largeNo, 24))
    Wend
    LargeNo_ModDec = CDec(largeNo) - Int(CDec(largeNo) / div) * div
End Function

They can be tested in the next way:

Private Sub Test_LargeNumbers_Modulus()
    Debug.Print LargeNo_ModCLngLng("3545123008254150481059068660418190917230", 97)
    Debug.Print LargeNo_ModDec("3545123008254150481059068660418190917230", 97)
End Sub

VBA calculate with numbers in range 10^40

3 Answers3

Get Remainder From Large Integer