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).
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 Rothstein" wrote in message
...
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 (MVP - Excel)
"Peter T" <peter_t@discussions wrote in message
...
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
"Rick Rothstein" wrote in message
...
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).
--
Rick (MVP - Excel)
"Neal Ostrander" wrote in
message ...
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
"Duncan" wrote:
On 12 Sep, 11:32, "Bob Phillips" wrote:
? Clng(123) * 456
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in
my
addy)
"Duncan" wrote in message
...
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.
Thanks everyone. Yet another reason for me to hate Microsoft today.