Multiplication with Double Data types 2

[JTBorton]

This one's got me stumped. I'm trying to do a simple multiplication and store it in my double data type variable. (I didn't just store the number because this is a conversion factor and I want others in my work group to be able to see where the conversion factor came from if they want to trace it out.)

Dim CFactor As Double

CFactor = 3600 * 100

3600*100 = 360000, well within the numeric range of a double datatype. But for some reason my code just stops dead in it's tracks when it gets to this line.

However, if I assign 3600 to the variable, then multiply it by 100, there is no problem and the code continues running fine.

Dim CFactor As Double

CFactor = 3600
CFactor = CFactor * 100

What the heck is going on here???

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

Hi,

Why store a conversion factor in a data type with imprecision?

Dim CFactor As Long, c As Long, d As Long
c = 3600
d = 100
CFactor = c * d

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JTBorton]

Skip,

I suppose we could, but that's a lot of extra variables to mess with. I just don't see why the code is crashing when I multiply the two numbers. There is no reason for it to crash.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[PHV]

CFactor = 3600# * 100#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TonyJollans]

An explanation of PHV's (correct) answer ...

The data type used in arithmetic is always the simplest possible, based on the operands. In this case, the literals, 3600 and 100, are both small enough to fit into Integers, so Integer arithmetic is used. Assignment of the result to the target (Double) variable only happens after the calculation is complete, and the Overflow happens because the result of the calculation won't fit in a temporarily assigned Integer. The '#' tells VBA that the values are Doubles - or to use Doubles to hold them - and this forces Double arithmetic to happen, and a Double to be temporarily assigned for the result. Strictly it is sufficient to append the '#' to just one - any one - of the values.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[JTBorton]

Thanks PHV, and Tony. VBA is so moody. Now I understand why VBA automatically places a # after my values when I declare and assign constants.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[lionelhill]

That's the beauty of modern high-level languages. The syntax is obvious, intuitive and natural, and the compilers/interpreters are inteligent, context-sensitive, and optimising.

(thanks, Tony, for explanation, you get a star from me too)

 

[TheAceMan1]

How are ya JTBorton . . .

You could've also cast your literals to Double:

[blue]CFactor = CDbl(3600) * CDbl(100)[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[electricpete]

That last solution is more pleasing to my eye and easier to remember

 

[JTBorton]

Ya, but that is too much typing. My fingers are just too lazy for that. If your going to go that route then at least cut it short with

CFactor = CDbl(3600 * 100)

But honestly,

CFactor = 3600 * 100#

is so much less work, and hassel. And it helps me remember for future incidents.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[strongm]

>CFactor = CDbl(3600 * 100)

This would suffer the same problem as you originally encountered.

 

[combo]

You only need to remember limitations. After:

CFactor = 3600.0 * 100.0

VBA automatically converts it to

CFactor = 3600# * 100#

combo

 

[JTBorton]

strongm (MIS)
>CFactor = CDbl(3600 * 100)

This would suffer the same problem as you originally encountered.

Well crap! That doesn't make any sense either. Regardless of how VBA does the math, it should convert the result to a double. What's the problem here? CDbl(360000) works just fine, why not this method?

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[PHV]

360000 is a Long
3600 * 100 is a multiplication of two Integer and thus the result is supposed to be an Integer which raises an overflow error.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

... why not this method?

Because the operation inside the parentheses is performed first and it hits the integer overflow problem. CDbl is performed later but, by then, the error has already occurred.

 

[DougAJ4]

A few more comments:

If you enter the number as a decimal (100.0) the VB Editor will change it to 100# for you.

If you step through the routine and click on help when it hits the problem the help gives the reason for the problem, as given earlier in the thread. (I confess to being amazed that the help was so helpful in this case).

It's really not a good idea to hard code numbers in your code. (In my opinion )

Doug Jenkins

http://newtonexcelbach.wordpress.com/