VBA isn't subtracting properly

[That One Guy]

Hi All,

I have this small function in a worksheet (as this is where the rest of
my code is written) that's just not wanting to work.

It is as follows:

Private Function fncIsCountingNumber(dSomeNumber As Double, dTotal _
As Double) As Boolean
Dim dRoundQ As Double, dQ As Double, dDiff As Double
If dSomeNumber = 0 Then
fncIsCountingNumber = False
Else
dRoundQ = Round(dTotal / dSomeNumber, 0)
dQ = dTotal / dSomeNumber
dDiff = dQ - dRoundQ
If dDiff = 0 Then
fncIsCountingNumber = True
Else
fncIsCountingNumber = False
End If
End If
End Function

I am plugging in .01 for dSomeNumber, and 4.98 for dTotal. This results
in a value of 498 for dQ, and 498 for dRoundQ (which is to be expected).
However, for some reason, my value for dDiff is 5.6843418860808E-14.
Any thought on this. Perhaps there's another method I could try? It
would be kind of lame to have to do a round(dDiff,12) to get 498-498=0,
but right now, that looks like the most promising solution.

I'm using Excel 2003 and VBA 6.3.

Thanks.

Regards,

That One Guy

 

[That One Guy]

Hi All,

Nevermind. I found my answer.

My workaround:

Since there really are only 3 significant figures anyway, I'm using
currency.

I found the reason double won't work for equitable comparisons at
http://stackoverflow.com/questions/235409/compare-double-in-vba-precision-problem.

Regards,

That One Guy.

 

[Ron Rosenfeld]

Hi All,

I have this small function in a worksheet (as this is where the rest of
my code is written) that's just not wanting to work.

It is as follows:

Private Function fncIsCountingNumber(dSomeNumber As Double, dTotal _
As Double) As Boolean
Dim dRoundQ As Double, dQ As Double, dDiff As Double
If dSomeNumber = 0 Then
fncIsCountingNumber = False
Else
dRoundQ = Round(dTotal / dSomeNumber, 0)
dQ = dTotal / dSomeNumber
dDiff = dQ - dRoundQ
If dDiff = 0 Then
fncIsCountingNumber = True
Else
fncIsCountingNumber = False
End If
End If
End Function

I am plugging in .01 for dSomeNumber, and 4.98 for dTotal. This results
in a value of 498 for dQ, and 498 for dRoundQ (which is to be expected).
However, for some reason, my value for dDiff is 5.6843418860808E-14.
Any thought on this. Perhaps there's another method I could try? It
would be kind of lame to have to do a round(dDiff,12) to get 498-498=0,
but right now, that looks like the most promising solution.

I'm using Excel 2003 and VBA 6.3.

Thanks.

Regards,

That One Guy

Your result is a consequence of the fact that computers think in binary, and
decimal often cannot be exactly expressed in binary.

Some of your options:
Round
Test for no greater than a small difference
Used the Decimal data type
--ron