VBA conversion confusion

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

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
 
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
 
Another way:

If (Application.Round((d1 + d2 + d3 + d4), 1) = s) Then
foobar = False
Else
foobar = True
End If


--
regards/pozdrav!
Berislav

************************************************************
ROT13 - email address (e-mail address removed)
 
Thanks Berislav and Charles for the responses.

I was really looking for a bit more insight as to how it happens in
the first place. In general, I also know that it is poor coding
practice to do a equality compare with floating point variables in a
logical branch. What I had forgotten was why, and mistakenly thought
this was a VBA specific issue. It isn't and two dope slaps to me for
forgetting.

The "why" relates to how floating point numbers are stored by binary
computers. See:

http://www.nuvisionmiami.com/books/asm/workbook/floating_tut.htm

for a good introduction to the subject. MS also has a more general
tutorial entitled "(Complete) Tutorial to Understand IEEE
Floating-Point Errors" under knowledge base article 42980.

Basically this boils down to the following: just because you can
represent a decimal (e.g. 0.1) in base 10 with a small number of
digits, DON'T go assuming that the same is true for base 2 which is
what your program uses.

Now, it might be fun to be able to check if a decimal can be exactly
represented exactly in IEEE 64-bit floating point format, and what the
error remainder is if not. Anyone know of such an online utility?

Jeff

=====================
 
Back
Top