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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"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.

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



"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.

? 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.

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.

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.

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.

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.

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

"Rick Rothstein" wrote:

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.