PDA

View Full Version : Solved: Problem with decimals in VBA code



Johannes
08-30-2004, 02:14 PM
I frequently have problems with decimals when I perform calculations with VBA.
To illustrate the problem I just ran the following:

a= 27.45
b= 27.00
c= a -b

and c is not 0.45 but 0.4499999999.

As you probably understand already, I'm just a novice at this, so I'm sure there's an easy and obvious way around this. but I'll be overjoyed if anyone can enlighten me..

Thanks in advance !

Jacob Hilderbrand
08-30-2004, 02:18 PM
Excel has trouble with numbers, what can I say. You can force the precission that you want though.

c = Round(a - b, 2)

Johannes
08-30-2004, 02:31 PM
Thanks, that's so obvious that I should have thought of it myself. My problem is really that when I use numbers as conditions in loops or comparisons, for example

Do While a > b

even if I have rounded off a and b to 2 decimals in advance of the Do While statement, I experience incorrect results because of the (lack of) rounding off..

Johannes

Jacob Hilderbrand
08-30-2004, 02:32 PM
For more information check here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;78113&Product=xlw

And here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;214118&Product=xlw

Jacob Hilderbrand
08-30-2004, 02:34 PM
You can try something like:


Do

'Code

a = some number
b = some number
c = Round(a,2)
d = Round(b,2)

'More Code

Loop While c > d

mdmackillop
08-30-2004, 03:06 PM
Hi,
You could also try

Do While a > b - 0.01 (or b + 0.01) as appropriate

MD

Johannes
09-01-2004, 09:01 AM
Thanks guys. I'm not convinced that I'm covered in all situations, but at least I have a few more ideas about how to handle this situation.