Comparing double values

[Edward Ulle]

What is the best method for comparing double values. The following loop
produces one too many values.

Dim dblStep As Double
Dim dblT As Double
Dim dlbTMax As Double

dblStep = 0.005
dblTMax = 0.060
dblT = 0.0

While dblT < dblTMax
MsgBox Str(dblT)
dblT = dblT + dblStep
Wend

Produces:
0.000
0.005
0.010
0.015
0.020
0.025
0.030
0.035
0.040
0.045
0.050
0.055
0.060 <- This one should not have been produced.

 

[Tom Ogilvy]

Dim dblStep As Double
Dim dblT As Double
Dim dblTMax As Double ' correct misspelling
Dim dblStart As Double
Dim i as Long

dblStep = 0.005
dblTMax = 0.060 + .000001
dblT = 0.0
dblStart = 0.0
i = 1
While dblT < dblTMax
MsgBox Str(dblT)
dblT = dblStart + (i * dblStep)
i = i + 1
Wend

Doubles don't always represent a value exactly. Adding a double can
accumulate error.

 

[Edward Ulle]

Tom,

I suspected as much but in your opinion, if I am dealing with small
numbers what is a minimum tolerance, say 10 to the minus 10 or is that
too small?

 

[Tom Ogilvy]

for doubles, VBA/Excel uses the IEEE standard which is approximately 15
digits of precision, so the error is usually around the last couple of
digits. How that translates into your "epsilon" would depend on your
numbers.

Here is an illustrative example of accumulating the results of imprecision:

Sub abcd()
Dim v As Double
v = 0#
For i = 1 To 300
v = v + CDbl(1 / 3)
Next
Debug.Print Format(v, "0.0000000000000000000")
Debug.Print Format(CDbl(1 / 3) * 300, "0.0000000000000000000")

End Sub