overflow with double precision

[MetLife]

Hi,

I am getting the error "overflow", when I assign a variable of type double a number >32,000. What is going on?

dim guess as double
guess = 73000 'no overflow error
guess = 73 * 1000 'gets an overflow error

I remember this error happening in the past.

 

[Eric W]

I don't recall the whole story offhand, but it has to do with intermediate results. 73 and 1000 are implicitly defined as integer, so there's an intermediate result which is eventually assigned to "guess". The intermediate result is what overflowed. You can get around it by defining the 2 multipliers as double, either with the Dim, or the # symbol:

Sub TEST1()
Dim guess As Double
Dim a As Double, b As Double

    a = 73
    b = 1000
    
    guess = a * b     ' works
    
    guess = 73# * 1000#    ' works
    
    guess = 73 * 1000     ' fails
    
End Sub

 

[MetLife]

Thanks!

 

[joeu2004]

@EricW.... You've almost got it (the explanation). The problem is: 73 and 1000 are small enough for VBA to consider type Integer. Consequently, the arithmetic is done as type Integer. Thus, the product cannot exceed 32767.

It is sufficient to put the "#" suffix (implicit type Double) on one constant. 73# * 1000 will coerce 1000 to type Double, and Double arithmetic will be performed.

PS.... If you cannot remember these "magic" suffixes, simply type 73.0 or 1000.0. The decimal point is sufficient for VBA to recognize the constant as type Double, even though the decimal fraction is zero.

In fact, in this case, it is sufficient to use the "&" suffix to cause type Long arithmetic to be done.

But it really does not matter much in modern computers.