VBA Step 0.01 Is Not Working

[MrExcel]

I am working with a home-grown Better Scenario Manager macro. The user can enter a starting value, ending value, and a step value and I loop through the various input values.

While testing, I noticed that this particular set of inputs isn't working

For i = 0.05 to 0.06 step 0.01
    Debug.Print i
Next i

It prints 0.05 but never prints 0.06. If I watch the i variable, it reports 0.06 after the loop, so the floating point comparison of (0.05+0.01) to (0.06) must be stinging me here.

My solution was to add a "little bit" to the MaxV as below. Any other ideas?

MinV = cells(i, 4).Value
MaxV = cells(i, 5).Value
StepV = cells(i, 6).value
Maxv = MaxV + StepV / 2
For i = MinV to MaxV Step StepV
    ' ... Do Stuff
Next i

Bill

 

[MrKowz]

MrExcel,

That would appear to be the case.

Instead of adding half the step value, I was able to get it to work by adding a very tiny decimal number (assuming StepV won't be declared in that small of a step):

Public Sub testmrexcel()
Dim MinV    As Double, _
    MaxV    As Double, _
    StepV   As Double, _
    i       As Double
 
MinV = 0.05
MaxV = 0.06
StepV = 0.01
For i = MinV To MaxV + (1 * 10 ^ -8) Step StepV
    Debug.Print i
Next i
End Sub

Edit: What is strange is that when I tried to go even more precise (0.005, 0.006, 0.001), it worked fine without any adjustments.

I'll see if I can come up with anything else.

 

[MrKowz]

I found a potential solution that keeps you from needing to add a small bit of value. Coerce the values into the decimal data type by first declaring those variables as a variant, and using CDec. From what I can understand from the Excel help files, the decimal data type does not use floating-point, but rather a simple power of 10.

Public Sub testmrexcel()
Dim MinV    As Variant, _
    MaxV    As Variant, _
    StepV   As Variant, _
    i       As Variant
    
MinV = CDec(0.05)
MaxV = CDec(0.06)
StepV = CDec(0.01)
For i = MinV To MaxV Step StepV
    Debug.Print i
Next i
End Sub

 

[Fazza]

Dim i As Currency

 

[shg]

I agree with Fazza. Currency is a scaled integer with precision exactly 0.0001.

 

[MrExcel]

EXCELlent! Thank you.