overflow error

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
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?
 
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
 
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
 
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
 
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.
 
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
 
Back
Top