Capitalize Formulas in Excel... 2

[jmrdaddy]

When a formula is typed in a cell in Excel the formula is automatically capitalized. i.e. "=average(A1:A25)" becomes "=AVERAGE(A1:A25)". Is there a way to automatically change the case of a homemade formula like Excel does? i.e. "=myhomemadeformula(A1)" becomes "=MYHOMEMADEFORMULA(A1)". Things I've tried like below haven't worked.

ActiveCell.Formula=UCase(ActiveCell.Formula)

 

[SkipVought]

Hi,

Is this a problem? I believe it uses the u/l case format as the function is coded on the Function statement.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[jmrdaddy]

It isn't a problem.
Just wanted to know how it worked and figured I'd get answers to my question here.

If the name of the function is all lower case or all upper case, Excel changes the formula.

If the function is named "MYHOMEMADEFUNCTION" then
"=myhomemadefunction(A1)" => "=MYHOMEMADEFUNCTION(A1)"
and
"=MYHOMEMADEFUNCTION(A1)" => "=MYHOMEMADEFUNCTION(A1)"

If the function is named "myhomemadefunction" then
"=myhomemadefunction(A1)" => "=myhomemadefunction(A1)"
and
"=MYHOMEMADEFUNCTION(A1)" => "=myhomemadefunction(A1)"

If the function is named with upper and lower case, it doesn't change the case.

If the function is named "MyHomeMadeFunction" then
"=myhomemadefunction(A1)" => "=myhomemadefunction(A1)"
and
"=MYHOMEMADEFUNCTION(A1)" => "=MYHOMEMADEFUNCTION(A1)"

Thanks for the quick response.

 

[SkipVought]

Funny, mine did exactly that!
[tt]
=MyHomeMadeFunction()
[/tt]
copied out of the cell

Function MyHomeMadeFunction()
   MyHomeMadeFunction = Now
End Function

What version of Excel are you running?

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[jmrdaddy]

Excel 2003

I don't know I could be wrong.

 

[SkipVought]

2003 here too!

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[jmrdaddy]

Well, now it changes upper case to lower case even if the name of the function is upper case. I've tried caps and no caps and partial caps. I suppose I'm just not meant to find out why. %-)

 

[byundt]

Here is a procedure that works for me with Excel 2003:
1) Create a new name for your function in the VBA Editor
2) Use the Insert...Function menu item (or the Fx icon) to enter the function in a worksheet formula
3) Fill in your input parameters and let the function calculate

You may now type a formula using the function, and the capitalization will remain set by whatever was in the original VBA code. This will remain true for that workbook even if you delete the original formula.

It is the alternative procedure of typing a formula using the function name that guarantees that the function name will be lower case letters. So don't do this.

Next, you may be wondering how to make it always be like this when you use the function in other workbooks. One approach is to install the function in a .xla with a worksheet containing the function in a formula. You can't see the worksheet, but Excel is aware of it--and will retain the capitalization the way you intend.