VBA Variables
VBA Variables
(OP)
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?
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?





RE: VBA Variables
CODE
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.
RE: VBA Variables
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.
RE: VBA Variables
? 16000 * 2 (works fine)
? 17000 * 2 (gives an overflow)
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: VBA Variables
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: Eng-Tips.com Forum Policies
RE: VBA Variables
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.
RE: VBA Variables
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: Eng-Tips.com Forum Policies
RE: VBA Variables
So ? 2000*365 gives an overflow (two INTs) but ? 2000&*365 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: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: VBA Variables
? 5/10 - 4/10 - 1/10
-6.7762635780344E-21
? 5\10 - 4\10 - 1\10
0
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: VBA Variables
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.
RE: VBA Variables
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.
RE: VBA Variables
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: VBA Variables
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
www.motagg.com
RE: VBA Variables
RE: VBA Variables
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.
RE: VBA Variables
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.
RE: VBA Variables
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: VBA Variables
Robert Mote
www.motagg.com
RE: VBA Variables
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: VBA Variables
RE: VBA Variables
To prevent overflow, it's up to the programmer to ensure that a LONG ALU is invoked, which requires at least one operand to be LONG.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: VBA Variables
RE: VBA Variables
RE: VBA Variables
My memory is pretty spotty, but the assembler code would look something like:
LOAD RA, A ;INT register
LOAD RB, B ;INT register
ADD RA,RB ;INT addition
STORE C, RALU ;INT loaded into LONG
There are valid arguments for either convention, but what we have is what we have, and is used by millions of programmers. Most programmers learn this in programming class and know to typecast to LONG if there's a possibility that the result will overflow.
Bear in mind, that the compiler, or Excel, doesn't "know" that your result will overflow, and both are designed to generate the fastest operational code, which is to do an INT Add and store the result to a LONG.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: VBA Variables
RE: VBA Variables
LOAD RA, A ;INT register
LOAD RB, B ;LONG register
ADD RA,RB,RB ;RA + RB and stored into RB
RE: VBA Variables
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: VBA Variables
? 2*(2&*17000) returns 68000
? 2&*(2*17000) causes an error.
It reminds us that expressions are evaluated in an order determined by the parsing algorithm. The stuff in paranetheses is evaluated first. And in the above case this evaluation is not sensitive to the context (the context being what will the results of the stuff in brackets be multiplied by)
Likewise an assignment statment is evaluated and excuted in an order. We evaluate the right hand side, then we plug it into the left. The evaluation of the rhs is not particularly sensitive to the context (the context being what type of variable are we going to plug it into).
High level languages are sometimes very sensitive to the context and protect us from errors by analysing the context very carefully. Lower level languages do not in general protect us as much.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: VBA Variables
and used the following code:
Private Sub CommandButton1_Click()
Dim x As Long
x = 730000
Worksheets("Sheet1").Range("A1").Value = x
End Sub
then he never would have discovered this?
Best regards
Morten
RE: VBA Variables
Even in regular C, one of the reasons for having typecasting was to provide a means of preventing overflow.
TTFN
FAQ731-376: Eng-Tips.com Forum Policies
RE: VBA Variables
Tomorrow when I get to my computer I'll post the code I was having problems with and that I already solve thanks to the info you all provided.