×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA Variables
3

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?

RE: VBA Variables

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

CODE

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.

RE: VBA Variables

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.

RE: VBA Variables

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.

RE: VBA Variables

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: Eng-Tips.com Forum Policies

RE: VBA Variables

(OP)
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.

RE: VBA Variables

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: Eng-Tips.com Forum Policies

RE: VBA Variables

The type declaration character for LONG variables is &.
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

PS this is also why:

? 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

(OP)
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.

RE: VBA Variables

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.

RE: VBA Variables

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
www.motagg.com

RE: VBA Variables

(OP)
Thank you all for all your help.

RE: VBA Variables

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.

RE: VBA Variables

(OP)
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.

RE: VBA Variables

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.

RE: VBA Variables

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

Robert Mote
www.motagg.com

RE: VBA Variables

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

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

RE: VBA Variables

(OP)
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.

RE: VBA Variables

I thought we went through that already.  In a actual physical processor, running assembly code, integer operands are passed through an integer ALU.  Neither compiler nor assembler can know what the intent is, so the default ALU matches the type of the variable.

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

But, if the result of the operation is known to be of type LONG, even if the operands are of type INT, shouldn't LONG ALU be utilized?

RE: VBA Variables

(OP)
IRstuff, I think "melone" explained my doubt in a more clear way. I'm declaring as LONG the variable in which I am going to store a result of an operation, like "melone" said, shouldn't ALU match the dimension of the variable I'm using to save the information?

RE: VBA Variables

No, because it's not the same step.  The results of the ALU reside in the ALU, and are not in memory.  The "=" causes the transfer of the ALU contents to the memory location specified by the LONG variable.

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

(OP)
Thanks IRstuff, now I get it, now I know why. Thank you all for all your helpful information.

RE: VBA Variables

Wouldn't the disassembly look closer to:

LOAD RA, A    ;INT register
LOAD RB, B    ;LONG register
ADD RA,RB,RB     ;RA + RB and stored into RB
 

RE: VBA Variables

Depends on the ALU architecture and the microcode.  Most HLLs were written when ALUs had a separate results register.  And, to maintain compatibility, the least common denominator applies, so any processor that does a combo instruction is generally not supported, except with inline code, since it would otherwise result in different behavior with an older processor.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: VBA Variables

Sorry to beat a dead horse - just thought of another angle that may or may not be of interest.

? 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

I tried to look through the replys to see if this had been noted before - but since the calculation apperently causes an overflow due to the calculation being performed on integer values instead of long values - then if carfreak had just used his pocket calculator to comput 2000*365=730000

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

Sure, but most LLL programmers are quite keenly aware of overflow and spend quite a bit of time manipulating their algorithms to reduce overflow.  

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

(OP)
MortenA, in fact, the example I used is the one provided by the help function of the VBA editor. I was searching for some information on the overflow error and since it's a very short and clear example I used it.
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources