Increment for or do while loop by 0.001 (not 1)

[Han]

What I am trying to do: increment for loop or do while loop by 0.001
----------------------
My for loop (I don't know how to increment by 0.001, this code just
increments by 1):
----------------------
For X = 0 To 10 'For X between 0 and 10 incremented by 0.001
For Y = 0 To 10 'For X between 0 and 10 incremented by 0.001
Fnew = X * Y * Exp(1 - 2 * X) * Sin(Y - X) * (1 - Y ^ 2) 'Given
function
If Fold <= Fnew Then 'If previous value of function is less
than current value of f then...
xmax = X 'Store value of X for max value of F
ymax = Y 'Store value of y for max value of F
Fold = Fnew 'Update the function
End If 'End if statement
Next 'End inner for loop (Y loop)
Next 'End outer for loop (X loop)

----------------------
My do while loop (I tried incrementing by 0.001, but the answer is not
what I want):
----------------------
Do While X <= 10
Do While Y <= 10
Fnew = X * Y * Exp(1 - 2 * X) * Sin(Y - X) * (1 - Y ^ 2) 'Given
function with new X and Y value
If Fold <= Fnew Then 'If previous value of function is less
than current value of f then...
xmax = X 'Store value of X for max value of F
ymax = Y 'Store value of y for max value of F
Fold = Fnew 'Update the function
End If 'End if statement
Y = Y + 0.001
If Y > 10 Then Exit Do
Loop
X = X + 0.001
If X > 10 Then Exit Do
Loop

 

[Tom Ogilvy]

this should get you started:

Sub abc()
Dim i As Double
Dim sStr As String
For i = 1 To 2 Step 0.1
sStr = sStr & i & vbNewLine
Next
MsgBox sStr
End Sub

 

[NickHK]

Tom,
I have noticed that using something like :
Dim i As Double
For i = 1 To 10 Step 0.1
Debug.Print i
Next

you cannot use :
If i=6.2 Then
because of accuracy issues. e.g.
............
5.8
5.9
6
6.1
6.19999999999999
6.29999999999999
6.39999999999999
..........
Using a Currency type avoids this .

NickHK
P.S. Seems strange that if you add a line "If i > 6 Then Stop" in the loop,
then in the immediate window "?i+0.1" the result is 6.2 exactly. But
"?i+0.1=6.2" shows False. Confused ?
So a For/Next loop like this is not really adding 0.1 each time, but using
integers really then scaling the counter ?

 

[Jerry W. Lewis]

That is because 0.1, 0.001, and the vast majority of other terminating
decimal fractions are non-terminating binary fractions that can only be
approximated.

Using the Currency type does not avoid the issue altogether, it just
restricts it to where you would expect it. For intance, presumably you would
understand why

Dim i As Currency
i=1/3
i=1-3*i

does not have i as zero at the end.

Jerry

 

[Han]

Thank you everyone for all your input. Using "step" and "currency"
worked for my application.

That is because 0.1, 0.001, and the vast majority of other terminating
decimal fractions are non-terminating binary fractions that can only be
approximated.

Using the Currency type does not avoid the issue altogether, it just
restricts it to where you would expect it. For intance, presumably you would
understand why

Dim i As Currency
i=1/3
i=1-3*i

does not have i as zero at the end.

Jerry

 

[Han]

Thank you everyone for you input. Using "step" and "currency" works
for my application. It runs slow (takes a few minutes), but it
functions.

That is because 0.1, 0.001, and the vast majority of other terminating
decimal fractions are non-terminating binary fractions that can only be
approximated.

Using the Currency type does not avoid the issue altogether, it just
restricts it to where you would expect it. For intance, presumably you would
understand why

Dim i As Currency
i=1/3
i=1-3*i

does not have i as zero at the end.

Jerry

 

[NickHK]

I fully expected such approximation, but seemed strange why it did not
become apparent until 6.2
But also the loop cannot really be 1+0.1+0.1........+0.1 because then "i"
would fixed at maximum 1 decimal place

NickHK

That is because 0.1, 0.001, and the vast majority of other terminating
decimal fractions are non-terminating binary fractions that can only be
approximated.

Using the Currency type does not avoid the issue altogether, it just
restricts it to where you would expect it. For intance, presumably you
would
understand why

Dim i As Currency
i=1/3
i=1-3*i

does not have i as zero at the end.

Jerry

Tom,
I have noticed that using something like :
Dim i As Double
For i = 1 To 10 Step 0.1
Debug.Print i
Next

you cannot use :
If i=6.2 Then
because of accuracy issues. e.g.
............
5.8
5.9
6
6.1
6.19999999999999
6.29999999999999
6.39999999999999
..........
Using a Currency type avoids this .

NickHK
P.S. Seems strange that if you add a line "If i > 6 Then Stop" in the
loop,
then in the immediate window "?i+0.1" the result is 6.2 exactly. But
"?i+0.1=6.2" shows False. Confused ?
So a For/Next loop like this is not really adding 0.1 each time, but
using
integers really then scaling the counter ?

 

[Jerry W. Lewis]

Excel displays a maximum of 15 digits, but it takes 17 digits to uniquely
specify a binary floating point value. The discrepancys begin at 1.2, but
don't accumulate sufficiently to be visible in the 15th digit until after 6.

You can see this by changing your output statement to
Debug.Print i - Round(10 * i, 0) / 10
or by using the D2D function from
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465

Jerry

 

[stevebriz]

Han,

I got this to work by doing some mods...it works for me with the step
0.001 but then you have to round the xmax and ymax as they come out
with 0.214999999.. and 9.9999...
RUn time for me with the changes was 1 minute 08 secs
I am not sure what your application is for this ...but to speed things
up..I would think ( if it is possible) to try and limit the ranges you
must run this over.
Anyway hope this helps.... if you want my form with the code ..just
shoot me a email.
Steve