overflow error

E

ExcelMonkey

I am getting an overflow error with:

Dim StatusBarVariable As Variant
A =1
B = 1

StatusBarVariable = (A * B) / (15000 * 244) * 100

What can I define StatusBarVariable As to not create this error?
 
C

Chip Pearson

Because all the numeric variables fit in to Integers, VBA
attempts to do the entire calculation with Integers, but
overflows the +/- 32K limitation on 15000*24. Cast one of the
variables as a Long and VBA will do the arithmetic in longs, and
you won't get an overflow error.

StatusBarVariable = (a * b) / (15000& * 244) * 100



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
E

ExcelMonkey

Hi Chip I tried this and it still overflows:

Sub NewOne()
Dim A As Long
Dim B As Long
Dim StatusBarVariable As Variant


StatusBarVariable = (A * B) / (15000 * 244) * 100
End Su
 
E

ExcelMonkey

Or sorry should it look like this?

Sub NewOne()
Dim A As Long
Dim B As Long
Dim StatusBarVariable As Variant

A = 1
B = 2


StatusBarVariable = (A * B) / (15000 * 244) * 100
End Sub
 
J

Jim Rech

Note that Chip had you convert one of your integers to a long by adding an
ampersand to it:

15000&

You could also use CLng(15000) if you don't like ampersands.
 
D

Dana DeLouis

If you are interested... Sometimes just rearranging your equation can
prevent Excel from seeing all integers. However, it's best to declare your
variables as others have shown.

Sub NewOne()
Dim A
Dim B
Dim StatusBarVariable

A = 1
B = 2
StatusBarVariable = (A / 15000) * (B / 244) * 100
End Sub
 
Top