Logical comparison not working in VBA ????
 

Pasted below is an excerpt from VBA code I'm working on. For some reason it
will not properly execute this logic. When StopLong = 0.085 it cannot make
the first If..then logical comparison. Passes right over it. Have been using
VBA for 10years and have no clue whats going on.

For BuyLim = 0.001 To 0.07 Step 0.002
For GainSell = 0.001 To 0.08 Step 0.003
For StopLong = 0.001 To 0.09 Step 0.003

If StopLong = 0.085 Then
If GainSell = 0.001 Then
If BuyLim = 0.001 Then
weird = False
End If
End If
End If

Next
Next
Next

Logical comparison not working in VBA ????
 

On Jul 14, 11:43*am, VTengineer
wrote:
Pasted below is an excerpt from VBA code I'm working on. For some reason it
will not properly execute this logic. When StopLong = 0.085 it cannot make
the first If..then logical comparison. Passes right over it. Have been using
VBA for 10years and have no clue whats going on.

For BuyLim = 0.001 To 0.07 Step 0.002
For GainSell = 0.001 To 0.08 Step 0.003
For StopLong = 0.001 To 0.09 Step 0.003

If StopLong = 0.085 Then
* * If GainSell = 0.001 Then
* * * * If BuyLim = 0.001 Then
* * * * * * weird = False
* * * * End If
* * End If
End If

Next
Next
Next

Maybe the problem is that you are comparing real numbers for equality
- always dangerous due to possible round off error. Maybe replace

If StopLong = 0.085 Then

by

If Abs(StopLong - 0.085) < 0.00001 Then

and see what happens.

-scattered

Logical comparison not working in VBA ????
 

You are seeing roundoff error. The variable never hits .085 exactly.
--
Gary''s Student - gsnu200794


"VTengineer" wrote:

Pasted below is an excerpt from VBA code I'm working on. For some reason it
will not properly execute this logic. When StopLong = 0.085 it cannot make
the first If..then logical comparison. Passes right over it. Have been using
VBA for 10years and have no clue whats going on.

For BuyLim = 0.001 To 0.07 Step 0.002
For GainSell = 0.001 To 0.08 Step 0.003
For StopLong = 0.001 To 0.09 Step 0.003

If StopLong = 0.085 Then
If GainSell = 0.001 Then
If BuyLim = 0.001 Then
weird = False
End If
End If
End If

Next
Next
Next

Logical comparison not working in VBA ????
 

For BuyLim = 0.001 To 0.07 Step 0.002
For GainSell = 0.001 To 0.08 Step 0.003
For StopLong = 0.001 To 0.09 Step 0.003

If Abs(StopLong - 0.085) < 0.000000001 Then
If Abs(GainSell - 0.001) < 0.000000001 Then
If Abs(BuyLim - 0.001) < 0.000000001 Then
Condition = True
End If
End If
End If

Next
Next
Next
--
__________________________________
HTH

Bob

"VTengineer" wrote in message
...
Pasted below is an excerpt from VBA code I'm working on. For some reason
it
will not properly execute this logic. When StopLong = 0.085 it cannot make
the first If..then logical comparison. Passes right over it. Have been
using
VBA for 10years and have no clue whats going on.

For BuyLim = 0.001 To 0.07 Step 0.002
For GainSell = 0.001 To 0.08 Step 0.003
For StopLong = 0.001 To 0.09 Step 0.003

If StopLong = 0.085 Then
If GainSell = 0.001 Then
If BuyLim = 0.001 Then
weird = False
End If
End If
End If

Next
Next
Next

Logical comparison not working in VBA ????
 

Maybe the problem is that you are comparing real numbers for equality
- always dangerous due to possible round off error. Maybe replace

If StopLong = 0.085 Then

by

If Abs(StopLong - 0.085) < 0.00001 Then

and see what happens.

Just to add to your response... this is a very old problem and the solution
you offered has been the preferred method around it. Here is a link that
goes into more detail... the code may look odd, but if you scroll down to
the bottom of the page and see the programming languages it applies to, I
think you will understand why. As I said, this is a very old problem.<g

Rick

Logical comparison not working in VBA ????
 

Thanks.
Seems obvious now.....


"Rick Rothstein (MVP - VB)" wrote:

Maybe the problem is that you are comparing real numbers for equality
- always dangerous due to possible round off error. Maybe replace

If StopLong = 0.085 Then

by

If Abs(StopLong - 0.085) < 0.00001 Then

and see what happens.

Just to add to your response... this is a very old problem and the solution
you offered has been the preferred method around it. Here is a link that
goes into more detail... the code may look odd, but if you scroll down to
the bottom of the page and see the programming languages it applies to, I
think you will understand why. As I said, this is a very old problem.<g

Rick

Logical comparison not working in VBA ????
 

On Jul 14, 11:43*am, VTengineer
wrote:
Pasted below is an excerpt from VBA code I'm working on. For some reason it
will not properly execute this logic. When StopLong = 0.085 it cannot make
the first If..then logical comparison. Passes right over it. Have been using
VBA for 10years and have no clue whats going on.

For BuyLim = 0.001 To 0.07 Step 0.002
For GainSell = 0.001 To 0.08 Step 0.003
For StopLong = 0.001 To 0.09 Step 0.003

If StopLong = 0.085 Then
* * If GainSell = 0.001 Then
* * * * If BuyLim = 0.001 Then
* * * * * * weird = False
* * * * End If
* * End If
End If

Next
Next
Next

Another approach:

If you want to use equality and StopLength, etc. are variants you can
use the CDec() function to make VBA treat these as decimal numbers.
The round-off error that prevents StopLong from reaching 0.085 exactly
is due to the fact that singles and doubles are in base 2 and that
many numbers which have a finite decimal expanson in base 10 (e.g.
0.003 or 0.085) have an infinite (though repeating) decimal exapansion
in base 2 hence can't be precisely represented by a single/double.
CDec uses (I believe) base 10 in its representation of numbers, hence
no round off error would build in this case. In any event, the
following code fragment works:

Sub test()
Dim StopLong As Variant
For StopLong = CDec(0.001) To 0.09 Step 0.003
If StopLong = 0.085 Then MsgBox "This works"
Next

End Sub

The problems with this approach a
1) Variants and decimal subtypes are less efficient
2) Variants might switch sub-types unexpectedly, thus leading to
difficult to maintain code.

On the other hand, if you know that you are dealing with decimal
numbers there might be a gain in readability in writing the code in
such a way that comparisons can be expressed in a straightforward
manner.

-scattered