VBA isn't subtracting properly
 

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

VBA isn't subtracting properly
 

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/2...cision-problem.

Regards,

That One Guy.

That One Guy wrote:
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

VBA isn't subtracting properly
 

On Wed, 11 Feb 2009 17:38:49 -0800, That One Guy wrote:

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