PDA

View Full Version : Handling really big nos.



Abhijeet
03-10-2011, 09:20 AM
hi

I am trying to execute the following code to solve a mathematical problem:

Sub EU45()

Dim k As Currency
Dim k1 As Currency
Dim g As Currency
Dim i As Integer
Dim Temp As Double


i = 1
Do Until (0)

g = 1142 * i + 4 * i * i
k1 = 989 * 989 + 12 * g
k = (Application.WorksheetFunction.Power(k1, 0.5) - 989) / 6

If (k > 0) Then

Temp = k - Int(k)
If (Temp = 0) Then
MsgBox k
Exit Do
End If

End If

i = i + 1
Loop


End Sub

Getting overflow at the colored step...is there a way around..or vba is not suitable to handle such large operations? Plz advice

Thanks
Abhijeet

Tommy
03-10-2011, 10:26 AM
k1 = 989 ^ 2 + 12 * g


or

L=989
k1 = L * L + 12 * g

mancubus
03-10-2011, 01:08 PM
is it an endless loop?

you may try "Do Until i = 10"

Abhijeet
03-11-2011, 12:35 AM
The loop is trying to detect if k is and integer...once k hits the first integer value the loop will terminate..

@ Tommy: ur suggestion helps...but now overflow is happening at calculation of g...the code can handle till i=29...beyond that overflows...even tried with the decimal datatype..doesn't help...is it time to switch to some other language?? can u suggest which languages are more capable in handling large computations? or is there some other datatype in VBA that am not aware of?

Thanks
Abhijeet

GTO
03-11-2011, 01:44 AM
Maybe:

Sub EU45()

Dim k As Variant
Dim k1 As Variant
Dim g As Variant
Dim i As Variant
Dim Temp As Double


i = 1
Do Until (0)

g = CDec(1142) * CDec(i) + 4 * CDec(i) * CDec(i)
k1 = CLng(989) * CLng(989) + 12 * CDec(g)
k = (Application.WorksheetFunction.Power(k1, 0.5) - 989) / 6

If (k > 0) Then

Temp = k - Int(k)
If (Temp = 0) Then
MsgBox k
Exit Do
End If

End If

i = i + 1
Loop


End Sub

Abhijeet
03-11-2011, 12:16 PM
@ GTO: Thanks a lot dude...it worked..and I solved the mathematical problem...just reaffirms my belief that sharing knowledge is the best way to learn :)

Tommy
03-11-2011, 02:49 PM
Explicit type casting, just goes to show you can't always "trust" the "computer"

Frosty
03-11-2011, 03:57 PM
Just had a thread on this recently...
http://www.vbaexpress.com/forum/showthread.php?t=36334

This would probably be a great info item at the top of the excel forum.

Basically, when you're doing mathematical operations, VBA uses the variable type of the largest variable in the operation to "hold" the results of the whole operation before passing that on to the result of the operation.

You can test this even in the immediate window:
?32767 + 1
...will cause an overflow, but...
?32768 + 1
...will not.

Because VBA *had* to use a long to express 32768, but it could use an integer to express 32767... and performing +1 on an integer type which is already at the limit of it's datatype is an overflow.

Bottom line:
GTO's solution works, but you can also get away with simply using one explicit conversion per operation, i.e.:

g = CDec(1142) * i + 4 * i * i
k1 = CDec(989) * 989 + 12 * g

(or CCur, or whatever... just to be safe)

Abhijeet
03-15-2011, 05:02 AM
Thanks Frosty...very helpful and informative..