VBA conversion confusion
 

I suppose this is old hat to Excel VBA pro's but it caught me by
surprise. What is there about the default Variant type that makes the
comparison fail (but only for SOME inputs, d1=0.9,d2=0.1,d3=0,d4=0 is
ok!?). Also, uncommenting the dim statement fixes the issue.

Can anyone please explain? (Excel 2000 9.0.4402 SR-1)

Jeff

===
Function foobar()
' Dim d1 As Double, d2 As Double, d3 As Double, d4 As Double, s as
Double
s = 1
d1 = 0.7
d2 = 0.1
d3 = 0.1
d4 = 0.1
If (d1 + d2 + d3 + d4 = s) Then
foobar = False
Else
foobar = True
End If
End Function

VBA conversion confusion
 

Hi Jeff,

looks like you are hitting the binary floating point problem:

any decimal number may not have an exact binary floating point
representation, so doing exact comparisons on decimal numbers is not
recommended.

The solution is to use a tolerance:

If abs(d1 + d2 + d3 + d4 - s)<0.000000001 Then
foobar=True
else
foobar=false
endif


regds
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

"Jeff" wrote in message
om...
I suppose this is old hat to Excel VBA pro's but it caught me by
surprise. What is there about the default Variant type that makes the
comparison fail (but only for SOME inputs, d1=0.9,d2=0.1,d3=0,d4=0 is
ok!?). Also, uncommenting the dim statement fixes the issue.

Can anyone please explain? (Excel 2000 9.0.4402 SR-1)

Jeff

===
Function foobar()
' Dim d1 As Double, d2 As Double, d3 As Double, d4 As Double, s as
Double
s = 1
d1 = 0.7
d2 = 0.1
d3 = 0.1
d4 = 0.1
If (d1 + d2 + d3 + d4 = s) Then
foobar = False
Else
foobar = True
End If
End Function