VBA operator problem

[douglashk]

capture 1.jpgcapture 2.jpgCapture 3.jpg

hi, i am a newcomer and asking for help for vba operator problem. I dun know why the function output show "yes" after i input all those parameters. As 39.7 > 39.7 should be false, thank you very much.

And one more problem is The output is "yes" when i declare the variable as single but "" when they are double

[p45cal]

Need to see the formula in F2

[douglashk]

Capture 4.jpgCapture 5.jpg thank you so much!!

[GTO]

Greetings and Welcome to VBAX Douglas.

I do not know why you are getting such as result, as I wrote out the same code and the IF test fails for me. As you say, 39.7 > 39.7 should return FALSE.

For anyone's convenience, I recreated the issue and the workbook is attached.

Douglas, when posting a problem - whilst a picture may sometimes "be worth a thousand words", most often when describing code issues - posting the code (not an image of the code that the helper must recreate instead of just copying/pasting) and/or a sample workbook is the way to go.

To post a workbook, click the <Go Advance> button below the Quick Reply edit box. Then scroll down and click the <Manage Attachments> button. You will see how to then upload a file.

Hope that helps,

Mark

[douglashk]

thx GTO, this is the file i examine the macro and it shown "yes" in row 1129. idk why. I fail to upload a bas file so i attached the function and sub here. thank you so much.

Function test1(day2open As Single, day2close As Single, day1open As Single, day1close As Single) As String

Dim average As Single

average = (day1open + day1close) / 2

If day2close > average Then

test1 = "yes"

Else

test1 = ""

End If

End Function


Sub test2()

Range("F7:F2473").Formula = "=test1(b7,e7,b8,e8)"

End Sub

[GTO]

And one more problem is The output is "yes" when i declare the variable as single but "" when they are double

I am afraid if you did not add that bit in, I glossed right over it. Additionally, when I typed/replicated the code, I read from the more "zoomed in" image and did not realize we were asking about Single accuracy vs Double accuracy.

Okay - you can find better and more technically proficient answers, but here is mine (and in no particular order):

In Windows 32-bit and above, Longs and Doubles are the thing to use, unless an Integer or Single is actually required to accommodate the transition into/out of a library being accessed by a declared function. Otherwise, just use Doubles or Longs, as Windows holds onto the values this way anyways, and a conversion is required to get the Single/Integer. Again, not the most articulate explanation, but you can search MSDN for articles on such.

Your initial values are being taken from the worksheet and you are forcing a coercion by making the called function's parameters Single. In short - Worksheets "think" in Double anyways, so why would you want something else?

And finally, computers don't really "think", they return, based upon what is floating in memory, which isn't really "numbers", but binary strings. Hence, there are limits to accuracy (Google "excel floating point accuracy"), and this accuracy can be further diminished when we start coercing slight changes by changing from Double to Single.

Hope that helps a little at least,

Mark

[JKwan]

hmm, I don't know if it has to do with precision? I had the same result as you did, however, if I change SINGLE to DOUBLE, works.

[p45cal]

Then again, when Single,
Debug.Print day2close - average
returned 3.814697E-06
and when Double returned 0.

[douglashk]

Thank you for your detailed explanation, Mark.

I declared those variable as double instead of single now. but still facing the same problem in row 2085. 7.7>7.7 , "true"

coz i am testing the candlestick signal for stock and i am using those macro to test whether the signal is valid. I run the coding in column F and type the formula in column G from the text book.

In the textbook it shown 22 "yes" but my vba code output as 23 "yes" no matter I declare the variable as single or double.

Column G : =IF((E7>B7)*(E8<B8)*(E7<B8)*(E7>((B8+E8)/2)),"yes","")

Column F :

Function test1(day2open As Double, day2close As Double, day1open As Double, day1close As Double) As String


Dim rs As Double

rs = (day1open + day1close) / 2


If day2open < day2close And day1open > day1close And day2open < day1close And day2close < day1open And day2close > rs Then

test1 = "yes"

Else

test1 = ""

End If

End Function

Sub test()

Range("F7:F2447").Formula = "=test1(B7,E7,B8,E8)"


End Sub

I attached those excel file with macros in the attachment. Many thanks again.

[GTO]

Then again, when Single,
Debug.Print day2close - average
returned 3.814697E-06
and when Double returned 0.

Hi Pascal,

Maybe I am misreading "Then again...", but if you are indicating the Single as more accurate, I would disagree. 39.7 - 39.7 should return 0. If I misread your intent and you were agreeing, my apologies.

Mark

[Paul_Hossler]

Once you start doing math on floating point number, the lower order bits get involved (i.e. calculating rs)

So it might look like 3.7 = 3.7, but internally it's not exactly the same if you subtract to see the difference

Capture.JPG


Try testing for a small difference

Option Explicit

Function test1(day2open As Double, day2close As Double, day1open As Double, day1close As Double) As String
    Dim rs As Double
    
    rs = (day1open + day1close) / 2#

    If day2open < day2close And day1open > day1close And day2open < day1close And day2close < day1open And (day2close - rs) > 10 ^ -6 Then
    
    test1 = "yes"
    
    Else
    
    test1 = ""
    
    End If
    
End Function

[p45cal]

GTO, I was responding to JKwan who questioned whether it had to do with precision. I think it does.

[p45cal]

If your 'control' column is column G then you can duplicate its results by rounding everything to 3 decimal places:

Function test3(day2open As Single, day2close As Single, day1open As Single, day1close As Single) As String
Dim rs As Single
day2open = Round(day2open, 3)
day2close = Round(day2close, 3)
day1open = Round(day1open, 3)
day1close = Round(day1close, 3)
rs = Round((day1open + day1close) / 2, 3)
If day2open < day2close And day1open > day1close And day2open < day1close And day2close < day1open And day2close > rs Then
  test3 = "yes"
Else
  test3 = ""
End If
End Function

(I haven't tested with the arguments as Double.)

post posting edit: Of course, I didn't need to round everything.

[douglashk]

many thanks, I really learnt a lots today!!

[GTO]

Hi Pascal, Thank you - I figured I must be misreading or misunderstanding your comment. Sorry about that :-(

Douglas, Pascal's solution may be just the ticket. Here is what I was thinking, but admittedly, very lightly tested:

Function test1(day2open As Double, day2close As Double, day1open As Double, day1close As Double) As String
Dim rs As Double
  
  rs = (day1open + day1close) / 2
  
  If (CDec(day2open) < CDec(day2close)) _
  And (CDec(day1open) > CDec(day1close)) _
  And (CDec(day2open) < CDec(day1close)) _
  And (CDec(day2close) < CDec(day1open)) _
  And (CDec(day2close) > CDec(rs)) Then
    
    test1 = "yes"
    
  Else
    
    test1 = ""
    
  End If
  
End Function

As Paul and Pascal (p45cal) note, if you test in the Immediate window, you can see some of the effects. In the above, if I step thru the code for row 2085, ?day2close - rs (as Doubles) returns 8.88178419700125E-16 but ?cdec(day2close) - cdec( rs ) returns 0.

Mark

[GTO]

Douglas - BTW, when posting code, using the code tags and indentation makes it easier for us to read. like:

[code]

'Your code here...

[/code]