In XYZ.xla, I have seven functions named XYZ1 through XYZ7.

XYZ1 through XYZ6 execute successfully and return expected results. They
also display in the Excel cell as Upper Case; regardless of whether they're
keyed-in as lower or upper case.

XYZ7, also, executes successfully and returns the expected result. However,
whether keyed-in in lower or upper case, it displays in its cell as "xyz7"
instead of "XYZ7". What might cause this reversion to lower case of a UDF
name?

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


"silver23" wrote:

In XYZ.xla, I have seven functions named XYZ1 through XYZ7.

XYZ1 through XYZ6 execute successfully and return expected results. They
also display in the Excel cell as Upper Case; regardless of whether they're
keyed-in as lower or upper case.

XYZ7, also, executes successfully and returns the expected result. However,
whether keyed-in in lower or upper case, it displays in its cell as "xyz7"
instead of "XYZ7". What might cause this reversion to lower case of a UDF
name?

AFAIK, VBA functions don't capitalize the way intrinsic Excel functions do.
XL4 functions do take the capitalization as in the function definition.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"silver23" wrote in message
...
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


"silver23" wrote:

In XYZ.xla, I have seven functions named XYZ1 through XYZ7.

XYZ1 through XYZ6 execute successfully and return expected results. They
also display in the Excel cell as Upper Case; regardless of whether
they're
keyed-in as lower or upper case.

XYZ7, also, executes successfully and returns the expected result.
However,
whether keyed-in in lower or upper case, it displays in its cell as
"xyz7"
instead of "XYZ7". What might cause this reversion to lower case of a
UDF
name?

I didn't look at your code, but I did search google for the function name
changing case and got a few hits. Here are a couple:

http://groups.google.co.uk/groups?th...%40tkmsftngp05
http://groups-beta.google.com/group/...30e0415b856151
(one line in your browser)

And this worked for me in simple testing.

I created a UDF in a workbook (not an xla).

Option Explicit
Function XYZ1(str As String) As String
XYZ1 = "hi"
End Function

When I used this UDF in a cell, it converted to lower case.

I did this with an empty cell selected.
insert|name|Define
XYZ1
(all upper case)

My cell formulas that had that UDF changed to #ref! errors.

Then I deleted the name.
insert|name|define|select XYZ1 and hit the delete key.

My #ref! errors evaluated correctly and when I selected that cell, the UDF was
upper case.

Maybe it'll work for you.




silver23 wrote:

In XYZ.xla, I have seven functions named XYZ1 through XYZ7.

XYZ1 through XYZ6 execute successfully and return expected results. They
also display in the Excel cell as Upper Case; regardless of whether they're
keyed-in as lower or upper case.

XYZ7, also, executes successfully and returns the expected result. However,
whether keyed-in in lower or upper case, it displays in its cell as "xyz7"
instead of "XYZ7". What might cause this reversion to lower case of a UDF
name?

--

Dave Peterson

Thanks!

I opened the original spreadsheet used to build the .XLA;
then clicked-off the addin;
in the VB editor, updated the XYZ7 function name to lowercase "xyz7";
filed it down and entered XYZ7 into a cell ... result = "xyz7".
Re-opened the VB Editor; changed "xyz7" function name back to "XYZ7".
Filed the macro. In the cell, "xyz7" entered and ... result = "XYZ7".

This isn't documented anyway but the rule I follow is that the first
time you use a UDF, select it from the function wizard. Apparently, XL
remembers the capitalization of a name when it is first encounters it.
So, if you use the fx wizard, it will use the same capitalization as in
your VBA code. On subsequent uses XL will stick with that
capitalization.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article ,
says...
Thanks!

I opened the original spreadsheet used to build the .XLA;
then clicked-off the addin;
in the VB editor, updated the XYZ7 function name to lowercase "xyz7";
filed it down and entered XYZ7 into a cell ... result = "xyz7".
Re-opened the VB Editor; changed "xyz7" function name back to "XYZ7".
Filed the macro. In the cell, "xyz7" entered and ... result = "XYZ7".