loop using double

  • Thread starter Thread starter heev
  • Start date Start date
H

heev

I have the following sub.

Sub test()
Dim i As Double
Dim j As Double

j = 0.2
For i = -3 To 3 Step j
Debug.Assert i <> 3
Debug.Print i
Next
End Sub

If I run this code the last step 3 will not run.
If I modify I to 2.5 it does. What's going on?
 
Sub test()
Dim i As Double
Dim j As Double

j = 0.2
For i = -3 To 3 Step j
Debug.Assert i<> 3
Debug.Print i
Next
End Sub
Click to expand...

I ran into that in one program that used a for loop with a double value
as the step size. I think it has to do with some quirks in the way Excel
handles doubles. When I ran the code instead of zero I got this value,
which is small but still greater than zero:

3.88578058618805E-16

I never did figure it out how to fix it so I added a very small amount
to the final loop value:

For n = -3 To 3.00001 Step j

You could try multiplying everything by ten then dividing your loop
counter by 10 to get the value you need:

Sub test4()
Dim n As Double, j As Double
j = 2
For n = -30 To 30 Step j
Debug.Assert n/10 <> 3
Debug.Print n / 10
Next
End Sub

Or you could try cleaning up the loop counter, maybe something like this:

Sub test5()
Dim n As Double, j As Double
j = 0.2
For n = -3 To 3 Step j
n = CDbl((CLng(n * 10)) / 10)
Debug.Assert n <> 3
Debug.Print n
Next
End Sub
 
j = 0.2
For i = -3 To 3 Step j
Debug.Assert i <> 3
    Debug.Print i
Next
End Sub

If I run this code the last step 3 will not run.
If I modify I to 2.5 it does. What's going on?
Click to expand...

This is a common side-effect of computer binary arithmetic. Most
numbers with decimal fractions cannot be represented exactly.
Consequently, results of even simple arithmetic might not be "exact",
i.e. match the internal representation of the equivalent constant.
For example, the following will output False(!) [1]:

Debug.Print 10.1 - 10 = 0.1

If the step is ostensibly non-integer, it is best to use integers for
loop controls, then compute the floating-point value within the loop.
For example, instead of i = -3.0 to 3.0 step 0.2, do:

For x = -30 To 30 Step 2: i = x / 10

PS: For broader participation, you might want to post future
inquiries using the MS Answers Forums at
http://social.answers.microsoft.com/Forums/en-US/category/officeexcel.
It's not that I like that forum. It's just that MS has ceased to
support the Usenet newsgroups. Hence, participation here is limited
to the sites that share a common newsgroup mirror, which is no longer
centralized at MS.

-----
Endnotes

[1] 10.1 is exactly
10.0999999999999,996447286321199499070644378662109375
10.1-10 is exactly
0.0999999999999996,447286321199499070644378662109375
0.1 is exactly
0.100000000000000,0055511151231257827021181583404541015625
 
I just ran this code in Excel 2003 and it ran exactly as expected.
But typically I've only seen integers allowed in loops. Excel seems
to be the exception to this rule. This code could be written as:

Sub test()
Dim i As integer
Dim j As double

j = 0.2
For i = -15 To 15
Debug.Assert i*j <> 3
Debug.Print i*j
Next

But I am not familiar with the debug command. What do Debug.Assert
and Debug.Print do?
 
Back
Top