VBA do loop with double variable-CodePudding

I am attempting to set up a list from double type x to double type y, incremented by .001.

dblx = .093
dbly = .103

do while dblx <= dbly
    "INSERT STATEMENT for dblx"
    dblx = dblx   .001
loop

Now this works until I get to .102.

If dbly < .102 I have no issues but >= .102 the last iteration never occurs.

Am I going crazy?? I know .103 <= .103! But for some reason Access doesn't?

At first I thought it may have been the variable type, they are both doubles. I've stepped through, changed the read in values to hard set values, attempted different ranges of values... Again anything less than .102 doesn't give issues, but as soon as my "anchor value" (Max threshold) is greater than or equal to .102 then the last iteration of the do loop never works

CodePudding user response：

The answer is to use the Decimal Type, which is a bit awkward as the have to be declared as variant and then assigned using cdec.

Sub TestDecimal()

    Dim dblx As Variant
    Dim dbly As Variant
    dblx = CDec(0.093)
    dbly = CDec(0.103)
    
    Do While dblx <= dbly
    Debug.Print dblx
    dblx = dblx   cdec(0.001)
Loop
    
End Sub

Output

CodePudding user response：

Floating point arithmetic is not precise. E.g. you might get 0.103000000001213 instead of 0.103. Therefore add a little value (epsilon) to the tested end value

Sub Test()
    Const Eps = 0.00000001
    Dim dblx As Double, dbly As Double
    
    dblx = 0.093
    dbly = 0.103
    
    Do While dblx <= dbly   Eps
        Debug.Print dblx, dbly
        dblx = dblx   0.001
    Loop
End Sub

CodePudding user response：

I would trust your gut on this one, and still remember that the computer is only doing what you told it to do.

If I were to bet on the problem, whatever code that happens inside "INSERT STATEMENT for dblx" is causing you to miss the final increment.

For a sanity check, I went ahead and tested the code:

Sub check_double()
    Dim dblx As Double
    Dim dbly As Double
    
    dblx = 0.093
    dbly = 0.103
    
    Do While dblx <= dbly
        dblx = dblx   0.001
        Debug.Print dblx
    Loop
End Sub

This yeilded an output of:

CodePudding user response：

If you have four decimals or less, use Currency for such tasks to avoid the floating point errors:

Public Function LoopTest()

    Dim dblx    As Currency
    Dim dbly    As Currency
    
    dblx = 0.093
    dbly = 0.103
    
    Do While dblx <= dbly
        ' "INSERT STATEMENT for dblx"
        dblx = dblx   0.001
        Debug.Print dblx
    Loop

End Function

Output: