Help! Cannot calculate 123 * 456

  • Thread starter Thread starter Duncan
  • Start date Start date
D

Duncan

Tracked the problem down to a single type of calculation that fails,
even in the debug screen.

Try entering in immediate mode:

? 123 * 456

Any easy ways to avoid runtime error 6 with sums of this type? I don't
want to start creating variables, constants etc in a section of code
that performs a long series of simple pre-set calculations that need
to be 'human readable'.

Thanks everyone!

Using Excel 2003 vba.
 
Hi

It looks like VBA decides which data type to use for answer by source
numbers. As both 123 and 456 are interpreted as integers, the result will be
integer too. Integer data type is limited to range -32 768 - 32 767 , but
your result falls outside of it.

Use
? 123.0 * 456
or
? 123 * 456.0 instead
 
An Integer is reserved for the result, but the calculation exceeds +/-32k
causing an overflow error.

Append either or both those numbers with an & to coerce to a Long, eg

? 123& * 456

Regards,
Peter T
 
? Clng(123) * 456

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
? Clng(123) * 456

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...

Thanks everyone. Yet another reason for me to hate Microsoft today.
 
Why would it be a reason to hate Microsoft? When you are trying to do
something outside of the integer size range without taking the time or effort
to change the data type so the calculation will work. Microsoft is not to
blame for your failure to use proper programming.
Neal
 
That seems kind of a harsh response Neal, especially in light of the fact
that you can type things like these into the Immediate window and get the
expected results...

? 12 & "34"

? 1.4 / 1.7

That last one, which looks like two Singles being divided (in the same way
123/456 looks like two Integers being divided), will happily return a Double
as an answer. Personally, I think the "multiplying two Integers produces an
Integer result" is a programming flaw at the core of VB's calculating and/or
coercion routines. More than likely it stems from when they first created
the VB that VBA eventually was modeled after and which followed the memory
restricted days of BASIC from which VB derives. In those early days, the
only numeric data types were Integer, Single and Double... there was no Long
data type back then (you used Double if you had to handle values greater
than an Integer could handle)... Integer calculations that produced a result
too large to store in an Integer produced an error. I think Microsoft
maintained that error condition in its core calculating engine even after
they added the Long data type to the mix (never going back and adjusting the
core routines to account for it).
 
Think I'm more with Neal on this one, particularly as regards the hate
thing, it's just the way it is. If the OP had pressed Help when the error
message came up all would have been explained. Read and absorb for ten
seconds, move on.

It's not just historical Integers, same error occurs with Longs -

? 50000& * 50000

above errors with overflow as result is outside the scope of a Long
(that & isn't necessary to coerce a +32k non-decimal to a Long, included
only for emphasis)

but this works
? 50000# * 50000

Regards,
Peter T
 
Because the way integer (both Integers and Longs) are stored is to different
from the way floating point values (Singles and Doubles) are stored, they
appear to need different calculating routines to handle them at the core
(seems logical). In the same way Integers used to overflow the type of
memory used to house them in the old days, Longs do the same today (you
really wouldn't want VB moving data, and calculation methods, back and forth
between integers and floating point values as a series of chained
calculations occurred as that would negate the speed advantage integer
calculations have over floating point calculations).

However, the point of my posting was that in the same way one *expects*
singles and doubles to be promoted back and forth as in my 1.4/1.7 example
(which produces 0.823529411764706 as an answer)... their storage methods
being similar (except for size) as would be the underlying code
calculations... one would *expect* the same promotion to occur between
Integer and Long values (again, similar storage methods, except for size,
and similar underlying code calculations).

Now don't get me wrong, I fully favor and advocate variable declarations...
no question that should be done at **all** times; however I would note that
the OP's original question started off by complaining about the problem in
the Immediate window (although the rest of his text did seem to be concerned
with the code windows themselves). But even in the code window, floating
point values are handled "correctly" whereas integer values aren't. Maybe I
should clarify my use of the word "correctly". Try this code in a code
window...

Sub Test()
Dim Answer As Double
Answer = 1.4 / 1.7
MsgBox Answer
End Sub

Although 1.4 and 1.7 can fit into a Single, VB doesn't treat them as Singles
for the purpose of calculations... apparently VB actually assumes them to be
Doubles when a data type is not specified. Now try this...

Sub Test2()
Dim Answer As Long
Answer = 123 * 345
MsgBox Answer
End Sub

This subroutine produces an overflow error. Rather that treat the Integer
"looking" numbers as Longs for the purposes of calculations (in the same way
that the Single looking values 1.4 and 1.7 are treated as Doubles rather
than Singles for the purpose of calculations), VB assumes because the can
fit in an Integer, they must be treated as Integers... period... and VB
further assumes a value is a Long only if it can't fit in an Integer. I
think that this distinction integer values is a "flaw" at VB's core; or at
least it is not in keeping with the core VB logic that was used with Singles
and Doubles all the way back to the beginning roots of the BASIC language.
 
Rick,
I agree my response may have been alitte harsh but I get so tired of people
blaming microsoft for things that they could have controled. The OP stated
"I don't
want to start creating variables, constants etc". To me this make the
problem a human one not a software or microsoft one. If your going to take
the time to program something do it correctly, or don't expect the results to
be what you want.

I do appreciate the examples you gave as they helped me to understand an
issue I was having.
Neal
 
The OP stated "I don't want to start creating variables, constants etc".
To me this make the problem a human one not a software or microsoft one.
Click to expand...

Ah, I see where you were coming from now. I don't disagree with you on that
part of your response, but I still think there is a "flaw" at the core of
things in the way VB handles integer constants (see my response to Peter T
elsewhere in this thread for more detail).
 
I agree the OP might "reasonably" have expected a result without error,
similarly it's not unreasonable to expect that certain results not to be off
by a tad due to floating point issues. But that's computers!
However, the point of my posting was that in the same way one *expects*
singles and doubles to be promoted back and forth as in my 1.4/1.7 example
(which produces 0.823529411764706 as an answer)... their storage methods
being similar (except for size) as would be the underlying code
calculations... one would *expect* the same promotion to occur between
Integer and Long values (again, similar storage methods, except for size,
and similar underlying code calculations).
Click to expand...

I assume what's going on here is when any division is involved, which
includes evaluating a decimal, a double is reserved. IOW even if all values
involved in the calculation are integers (small i) a double is reserved if a
\ or a . exists.

? vartype(1 / 1) ' 5 double

clearly the above result is an integer but that's only known after the
evaluation, vs -
? vartype(1 * 1) ' 2 integer


Also, I assume, the reason automatic "promotion" does not occur from Integer
to Long to Double is for the sake of efficiency, so as not to allocate
unnecessary space. I think it's fair to keep in mind that when VB was in the
process of being evolved the same code had to accommodate both 16-bit and
32-bit systems.

Regards,
Peter T
 
Rick,
I agree my response may have been alitte harsh but I get so tired of people
blaming microsoft for things that they could have controled. The OP stated  
"I don't
want to start creating variables, constants etc". To me this make the
problem a human one not a software or microsoft one. If your going to take
the time to program something do it correctly, or don't expect the results to
be what you want.

I do appreciate the examples you gave as they helped me to understand an
issue I was having.
Neal
Click to expand...

As the OP I think it is solely at the door of Microsoft when VBA
cannot do an apparently straightforward sum that a ZX81 achieves
without a second thought, and it has only 1K of RAM and has every
reason to save every byte. Forcing the user to add definitions where
none should reasonably be expected just makes code more messy to read.
 
Back
Top