Overflow puzzle

  • Thread starter Thread starter David Myle
  • Start date Start date
D

David Myle

Hi all,

My VBA editor returns a Run time overflow error for 300*8520000
but calculates without any fuss
2556*10^6
Yet both figures are the same viz: 2556000000
The same editor has no problem evaluating 2556*10^300

What gives?
 
? 252*8520000
2147040000
works, but 253*8520000
doesn't because it exceeds the maximum size of a long

? 300#*8520000
2556000000

works because it forces conversion to Double

2556*10^6

does the same for the same reason.

That would be my guess.
 
When both operands are long, the return data type of the * operator is
Long. Both your operands fit within the Max Long, but the result of the
multiplication doesn't, hence the overflow.

The ^ operator returns a double, which will coerce the result to a
double, and the return data type of the * operator will be a Double.

You could make the first work by making one or the other operand a
double, e.g.,

300#*8520000

or

300*8520000#
 
Hi Tom,

Still stumped because the following generates same overflow error. You
would imagine the declaration will fix the problem.

Sub test()
Dim x As Double
x = 253 * 8520000
MsgBox x
End Sub
 
That doesn't force the calculate to be performed as double - just the
results, but the error has already occured.

Sub test()
Dim x As Double
x = 253# * 8520000
MsgBox x
End Sub
 
Your diagnosis and solution are spot on! As a check, a SINGLE coercion of
x = 253! * 8520000 also rides the error. Many thanks

DM
 
You have already received answers that work, but for those of us who
are not as up to speed with type-declaration-characters {g}, consider
x = CDbl(253) * 8520000
'above double coercion needed to avoid Long overflow error

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top