VBA Variables 3

[carfreak01]

Does anybody know why if I write this code in a command button:
Private Sub CommandButton1_Click()
Dim x As Long
x = 2000 * 365
Worksheets("Sheet1").Range("A1").Value = x
End Sub
The program detects an Overflow error,
But if I type the following code:

Private Sub CommandButton1_Click()
Dim x As Long, b As Long, c As Long
b = 2000
c = 365
x = b * c
Worksheets("Sheet1").Range("A1").Value = x
End Sub
It runs perfectly?

 

[electricpete]

Interesting. I tried to change 2000 to 2000.0 to see what would happen and the editor window put in 2000#. Then the following code works. Don't ask me why

Private Sub CommandButton1_Click()
    Dim x As Long
    x = 2000# * 365
    Worksheets("Sheet1").Range("A1").Value = x
End Sub

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

A google search indicated that # is indicated to show that a value is double precision. (which I think normally applies to floating point values).

Try in the immediate window:

? 2000 * 365

It gives an overflow. I think that vba reads 2000 and 365 as integer (not a long). And it tried to combine two integers using integer logic and overflows around 32000 or 64000. So you have to ensure the operands are something other than integer by:
1 - assigning them to a variable as you di
2 - making them floating point as I did.
3 - other ways...I'm sure there are more ways

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

The cutoff is around 32000
? 16000 * 2 (works fine)
? 17000 * 2 (gives an overflow)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

16383*2 is the limit, since that's one less than 2^14

As for the problem, the problem is indeed an arithmetic overflow. In the original example, two integers are multiplied together, then assigned to a long integer, but it overflows in the multiplication, since the processor expects an integer, not a long integer, answer.

In the second example, the values are assigned to long integers to begin with, so the multiplication results in a long integer answer.

This can be directly proven by dimensioning a and b as integer, the result will be an overflow as in the original example.

TTFN

FAQ731-376

 

[carfreak01]

MMmm, that's odd, I'm already declaring x as long, why would the processor expect anything different from what I'm dimensioning?
So should the processor expect a long integer result from a long integer variables operation? For example: b = 300000, c = 150000
x=b/c
This does not generates an error.

 

[IRstuff]

You're confused. Declaring x to be LONG does nothing to the results of the multiplication of two INTs. One is before, the other is after.

Your constants are smaller than 32767, hence, they are INT. Their product is INT. You then assign the INT result to a LONG.

You can check this yourself by placing your constants on the watchlist. They show up as INT, not LONG. Change to 200000, it becomes LONG, and the resultant must be LONG, so no overflow.

TTFN

FAQ731-376

 

[joerd]

The type declaration character for LONG variables is &.
So [tt]? 2000*365[/tt] gives an overflow (two INTs) but [tt]? 2000&*365[/tt] works, because 365 gets automatically converted to a LONG before the multiplication, and the result is a LONG as well.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[joerd]

PS this is also why:
[tt]
? 5/10 - 4/10 - 1/10
-6.7762635780344E-21

? 5\10 - 4\10 - 1\10
0
[/tt]

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[carfreak01]

IRstuff, you're right, I'm confused, so it doesn't matter if the variables are long enough to be integers, if the result is going to be a long integer, the variables should be declare as long integer, is that correct? like for example:
a = 32767
b = 32767
x = a * b
a and b should be dim as long even if their value range falls in the integer category.

 

[electricpete]

interesting joerd. Makes sense.

carfreak - it is not the assignment that causes the problem, it is evaluation of the expression on the rhs of the equal sign.

Try it in the immediate window:
? 2000 * 365
You get an overflow error even though there was no assignment... it comes from evaluating the expression.

Another interesting thing is to put in your code:
Const b=2000
you will see that vba creates an integer variable (not a long variable) to store this constant.

Interestingly, if you said
b=2000
c = 365
without any type declaration
(or preceded by dim b as variant, c as variant)
then you get a variant variable b which will not cause any problem when evaluating b*c

Here's my take: when the VBA interpretter (or compiler or whatever) sees a constant it has to make a choice of what type to create: integer, long, double, variant etc, (it doesn't know the results of the calc when it makes that assignment). In your original example, vba makes a choice which results in efficient and compact storage of variables (integer takes up the least space to store the constant 365). Perhaps if it treated constants as a variant type, these errors would not occur, but then the code would be less compact and slower to execute. You'd think that in making the decision what type of storage to create for a constant, vba might be smarter to anticipate what is going to be done with that constant after it is stored... but it's not always that smart.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[IRstuff]

They need to be declared LONG, if any assignable value or mathematical operation might result in a LONG.

TTFN

FAQ731-376

 

[rtmote]

That is very useful stuff guys.
I give a star to IRstuff. That makes sense, I was closing on it but quite there.
I give a star to Joerd too, that something I didn't know at all.

Robert Mote

http://www.motagg.com

 

[carfreak01]

Thank you all for all your help.

 

[electricpete]

Mmmm.

I agree star for joerd. Type declaration characters:
# after a numeric constant makes it a double
& after a numeric constant makes it a long
those are the only two I know (learned them in this thread). But at least I know there is such a thing if i should ever need it.

I'm perplexed that irstuff got a star and I didn't. I kind of thought he said the same thing I. I guess it must have been style points or artistic expression or something like that.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[carfreak01]

And also you can declare it like this:
CLng(2000) and it will be declare as long
CStr(x) for string....etc. You can check it in the "Members of Conversion" in the VBA editor.

 

[electricpete]

Cool. Those are more user-friendly than trying to remember a character. And for quick access of course, type in the editor "conversion." (conversion followed by period) which gives a pulldown menu of the members of conversion.



=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[rtmote]

Aaah Electricpete, wasn't trying to offend. Irstuff, I read first and the penny dropped! You got a star too!

Robert Mote

http://www.motagg.com

 

[electricpete]

Aaah thanks. I guess it pays to be a squeaky wheel.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[carfreak01]

Now I know that the dimension of the variables have to match the dimension of the result of the operation in which they are involved even if they are in a different category by themselves. But I still don't know why.