Simple If statement encounter error

  • Thread starter Thread starter Sing
  • Start date Start date
S

Sing

Dear Excel Gurus,

I have just encountered the strangest error. Is it a bug in Excel VBA
compiler?

Below statement encountered overflow error (run-time error 6);
If (turnoverToday >= (150 * 1000)) Then

Below statement encounters no error;
If (turnoverToday >= (150000)) Then

Aren't they the same!!??
 
Since both 150 and 1000 are integers, excel's VBA will use store the
intermediate result in an integer.

And 150*1000=150000 is too big to fit into an integer.

You have a few choices:

If (TurnOverToday >= (CLng(150) * 1000)) Then
If (TurnOverToday >= 150& * 1000)) Then
(& is the type-declaration character for Long)

Dim myNum1 as long
dim myNum2 as long 'or make them constants
mynum1 = 150
mynum2 = 1000
If (TurnOverToday >= (mynum1 * mynum2) Then
 
Seems pretty close to:
Except # is the type-declaration for Double.

And you don't actually need to use them on each of the constants. One is
enough.
 
Back
Top