Still, XYZ7's name (although it continues to work well) does not revert to
Uppercase when it is keyed-in and executed.
Is there something in XYZ7's VB code (below) that causes the Excel
Application to not honor XYZ7 name reversion? Any suggested improvements are
welcome too. Thanks in advance.
Public fn As WorksheetFunction
Public Function XYZ7(todaysohlc As Range, _
minus1ohlc As Range, _
minus2ohlc As Range, _
highrange As Range, _
lowrange As Range) As String
'Inputs: 1 - todaysohlc is a single horizontal row with four cells _
containing open high low and close _
2 - minus1ohlc is yesterday's single horizontal row with four cells _
containing open high low and close _
3 - minus2ohlc is day before yesterday's single horizontal row with
four cells _
containing open high low and close _
4 - highrange is 9 columns of previous highs, not including today's _
5 - lowrange is 9 columns of previous lows, not including today's
'If any cell isn't numeric, error
Dim myohlc As Variant, _
myminus1ohlc As Variant, _
myminus2ohlc As Variant, _
myhighrange As Variant, _
mylowrange As Variant
Dim i As Long, todaybar As Double, yesterdaybar As Double
Set fn = Application.WorksheetFunction
With todaysohlc
If .Rows.Count > 1 Then
XYZ7 = "OHLC > ONE ROW"
Exit Function
End If
If .Columns.Count <> 4 Then
XYZ7 = "OHLC NE 4 COLS"
Exit Function
End If
End With
myohlc = fn.Transpose(fn.Transpose(todaysohlc))
If fn.Count(myohlc) <> UBound(myohlc) Then
XYZ7 = "OHLC NOT NUMERIC"
Exit Function
End If
With minus1ohlc
If .Rows.Count > 1 Then
XYZ7 = "OHLC > ONE ROW"
Exit Function
End If
If .Columns.Count <> 4 Then
XYZ7 = "OHLC NE 4 COLS"
Exit Function
End If
End With
myminus1ohlc = fn.Transpose(fn.Transpose(minus1ohlc))
If fn.Count(myminus1ohlc) <> UBound(myminus1ohlc) Then
XYZ7 = "OHLC NOT NUMERIC"
Exit Function
End If
XYZ7 = ""
todaybar = myohlc(2) - myohlc(3)
yesterdaybar = myminus1ohlc(2) - myminus1ohlc(3)
'todaybar = todaybar - yesterdaybar
Dim netbar As Double
netbar = todaybar - yesterdaybar
If netbar < 0.0001 Then
Exit Function
End If
With minus2ohlc
If .Rows.Count > 1 Then
XYZ7 = "OHLC > ONE ROW"
Exit Function
End If
If .Columns.Count <> 4 Then
XYZ7 = "OHLC NE 4 COLS"
Exit Function
End If
End With
myminus2ohlc = fn.Transpose(fn.Transpose(minus2ohlc))
If fn.Count(myminus2ohlc) <> UBound(myminus2ohlc) Then
XYZ7 = "OHLC NOT NUMERIC"
Exit Function
End If
'If today's bar-size is gt yesterday-1 bar-size, then continue; else exit
If (myohlc(2) - myohlc(3)) > (myminus2ohlc(2) - myminus2ohlc(3)) Then
Else: Exit Function
End If
With highrange
If .Rows.Count < 9 Then
XYZ7 = "HIGHS < 9"
Exit Function
End If
If .Rows.Count > 9 Then
XYZ7 = "HIGHS > 9"
Exit Function
End If
If .Columns.Count <> 1 Then
XYZ7 = "HIGHS NE 1 COL"
Exit Function
End If
End With
myhighrange = fn.Transpose(highrange)
If fn.Count(myhighrange) <> UBound(myhighrange) Then
XYZ7 = "HIGHS NOT NUMERIC"
Exit Function
End If
With lowrange
If .Rows.Count < 9 Then
XYZ7 = "LOWS < 9"
Exit Function
End If
If .Rows.Count > 9 Then
XYZ7 = "LOWS > 9"
Exit Function
End If
If .Columns.Count <> 1 Then
XYZ7 = "LOWS NE 1 COL"
Exit Function
End If
End With
mylowrange = fn.Transpose(lowrange)
If fn.Count(mylowrange) <> UBound(mylowrange) Then
XYZ7 = "LOWS NOT NUMERIC"
Exit Function
End If
'If fn.Count(mylowrange) <> fn.Count(myhighrange) Then
' XYZ7 = "LOW COLS NE HIGH COLS"
' Exit Function
'End If
XYZ7 = ""
'If Buy Conditions are met, signal a "Buy"
If myohlc(4) < myohlc(1) Then 'Q. Today's close <
today's open?
If myminus1ohlc(4) > myminus1ohlc(1) Then 'Q. Yesterday's close >
open?
For i = LBound(myhighrange) To UBound(myhighrange) 'A. yes
If myohlc(2) > myhighrange(i) Then 'Q. highest high today?
Else: Exit Function 'A. no===>
End If
Next i
XYZ7 = "BUY"
Exit Function
End If
End If
'If Sell Conditions are met, signal a "Sell"
If myohlc(4) > myohlc(1) Then 'Q. Today's close >
today's open?
If myminus1ohlc(4) < myminus1ohlc(1) Then 'Q. Yesterday's close <
open?
For i = LBound(mylowrange) To UBound(mylowrange) 'A. yes
If myohlc(3) < mylowrange(i) Then 'Q. lowest low today?
Else: Exit Function 'A. no===>
End If
Next i
XYZ7 = "SELL"
Exit Function
End If
End If
End Function
data sample:
11.79 12.36 11.54 11.86
11.77 12.16 11.63 11.78
12.60 12.78 12.17 12.59
12.65 13.24 12.39 12.56
12.43 12.50 11.96 12.05
12.06 12.13 11.60 11.64
11.98 12.25 11.75 11.95
12.32 12.79 11.98 12.59
12.89 13.03 12.44 12.53
12.60 13.16 12.40 12.96
13.09 13.39 12.59 12.62 BUY
12.68 13.11 12.31 12.85
13.74 14.23 13.40 13.85
13.58 13.92 13.15 13.51
13.49 13.88 12.84 12.90
13.19 13.70 12.77 12.97
12.98 13.80 11.87 13.75 SELL
