VBA long overflow

Question

Hi I am trying to do some maths with my VBA excel (prime factorization) and I am hitting the limit of the long data type (runtime error 6 Overflow).

Is there any way to get around this and still stay within VBA? (I am aware that the obvious one would be to use another more appropriate programming language) Thanks for help in advance!

Answer 1

You can use Decimal data type. Quick hint from google: http://www.ozgrid.com/VBA/convert-to-decimal.htm

Answer 2

Thank you, it works in so far that I am able to get the big numbers into the variables now. However when I try to apply the MOD function (bignumber MOD 2 for example) it still fails with error message runtime error 6 Overflow.

Answer 3

MOD is trying to convert your DECIMAL type to LONG before operating on it. You may need to write your own MOD function for the DECIMAL type. You might try this:

r = A - Int(A / B) * B

where A & B are DECIMAL subtype of VARIANT variables, and r might have to be that large also (depending on your needs), though I only tested on a long.

Answer 4

This is my Decimals.cls (VB6):

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Decimals"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
Attribute VB_Ext_KEY = "SavedWithClassBuilder6" ,"Yes"
Attribute VB_Ext_KEY = "Top_Level" ,"Yes"
Option Explicit

'local variable(s) to hold property value(s)
Private mvarDec As Variant 'local copy

Public Property Let Dec(ByVal vData As Variant)
'used when assigning a value to the property, on the left side of an assignment.
'Syntax: X.Dec = 5
    mvarDec = CDec(vData)
End Property

Public Property Get Dec() As Variant
Attribute Dec.VB_UserMemId = 0
'used when retrieving value of a property, on the right side of an assignment.
'Syntax: Debug.Print X.Dec
    Dec = CDec(mvarDec)
End Property

and this is a testing program. The class has been setup so that you don't have to qualify with .Dec() on get and let.

Dim dec1 As New Std.Decimals
Dim dec2 As New Std.Decimals
Dim dec3 As New Std.Decimals
Dim modulus As New Std.Decimals

Sub main()
    dec1 = "1000.000000001"
    dec2 = "1000.00000000000001"
    dec3 = dec1 + dec2
    Debug.Print dec1
    Debug.Print dec2
    Debug.Print dec3
    Debug.Print dec3 * dec3
    Debug.Print dec3 / 10
    Debug.Print dec3 / 100
    Debug.Print Sqr(dec3)
    modulus = dec1 - Int(dec1 / dec2) * dec2
    Debug.Print modulus
End Sub

and sample run

 1000.000000001 
 1000.00000000000001 
 2000.00000000100001 
 4000000.000004000040000001 
 200.000000000100001 
 20.0000000000100001 
 44.721359550007 
 0.00000000099999 
 1000.000000001 
 1000.00000000000001 
 2000.00000000100001 
 4000000.000004000040000001 
 200.000000000100001 
 20.0000000000100001 
 44.721359550007 
 0.00000000099999

Answer 5

Here is my "big multiply" routine for multiplying arbitrarily large numbers (eg 100 characters long). It works by splitting the input numbers, which are strings, into chunks of 7 digits (because then it can cross multiply them and store the results in Doubles).

eg bigmultiply("1934567803945969696433","4483838382211678") = 8674289372323895422678848864807544574

Function BigMultiply(ByVal s1 As String, ByVal s2 As String) As String

Dim x As Long
x = 7

Dim n1 As Long, n2 As Long, n As Long
n1 = Int(Len(s1) / x + 0.999999)
n2 = Int(Len(s2) / x + 0.999999)
n = n1 + n2

Dim i As Long, j As Long
ReDim za1(n1) As Double
i = Len(s1) Mod x
If i = 0 Then i = x
za1(1) = Left(s1, i)
i = i + 1
For j = 2 To n1
 za1(j) = Mid(s1, i, x)
 i = i + x
Next j

ReDim za2(n2) As Double
i = Len(s2) Mod x
If i = 0 Then i = x
za2(1) = Left(s2, i)
i = i + 1
For j = 2 To n2
 za2(j) = Mid(s2, i, x)
 i = i + x
Next j

ReDim z(n) As Double
Dim u1 As Long, u2 As Long
Dim e As String
e = String(x, "0")

For u1 = 1 To n1
 i = u1
 For u2 = 1 To n2
   i = i + 1
   z(i) = z(i) + za1(u1) * za2(u2)
 Next u2
Next u1

Dim s As String, y As Double, w As Double, m As Long
m = n * x
s = String(m, "0")
y = 10 ^ x
For i = n To 1 Step -1
 w = Int(z(i) / y)
 Mid(s, i * x - x + 1, x) = Format(z(i) - w * y, e)
 z(i - 1) = z(i - 1) + w
Next i
'truncate leading zeros
For i = 1 To m
 If Mid$(s, i, 1) <> "0" Then Exit For
Next i
If i > m Then
 BigMultiply = ""
Else
 BigMultiply = Mid$(s, i)
End If

End Function