View Full Version : [SOLVED:] VBA operator problem
douglashk
09-01-2016, 05:33 AM
169851698616988
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
09-01-2016, 05:39 AM
Need to see the formula in F2
douglashk
09-01-2016, 05:46 AM
1698916990 thank you so much!!
Greetings and Welcome to VBAX Douglas.:hi:
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
09-01-2016, 06:23 AM
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
And one more problem is The output is "yes" when i declare the variable as single but "" when they are double
:banghead: 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
09-01-2016, 06:59 AM
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
09-01-2016, 07:56 AM
Then again, when Single,
Debug.Print day2close - average
returned 3.814697E-06
and when Double returned 0.
douglashk
09-01-2016, 07:57 AM
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" :dunno:dunno
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. :ipray::ipray:
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
09-01-2016, 08:27 AM
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
16996
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
09-01-2016, 08:29 AM
GTO, I was responding to JKwan who questioned whether it had to do with precision. I think it does.
p45cal
09-01-2016, 08:32 AM
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
09-01-2016, 08:42 AM
many thanks, I really learnt a lots today!!:friends:
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
Douglas - BTW, when posting code, using the code tags and indentation makes it easier for us to read. like:
'Your code here...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.