Simple If statement encounter error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sing - 23 Sep 2007 13:22 GMT

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!!??

Reply to this Message

Dave Peterson - 23 Sep 2007 13:41 GMT

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

> Dear Excel Gurus,
>
[quoted text clipped - 8 lines]
>
> Aren't they the same!!??

Signature

Dave Peterson

Reply to this Message

Joel - 23 Sep 2007 14:08 GMT

Dave: There is a very simple solution
If (turnoverToday >= (150# * 1000#)) Then

> Since both 150 and 1000 are integers, excel's VBA will use store the
> intermediate result in an integer.
[quoted text clipped - 25 lines]
> >
> > Aren't they the same!!??

Reply to this Message

Dave Peterson - 23 Sep 2007 16:51 GMT

Seems pretty close to:

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

Except # is the type-declaration for Double.

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

> Dave: There is a very simple solution
> If (turnoverToday >= (150# * 1000#)) Then
[quoted text clipped - 32 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message