UDF

  • Thread starter Thread starter Hans Knudsen
  • Start date Start date
H

Hans Knudsen

Hello
I made a UDF and wanted to name it EF (uppercase). However, first time I entered the function, I happened by mistake to enter ef
(lowercase). Is there any way I can change it back to uppercase?

Hans Knudsen
 
Hej Hans

Making a dummy function/sub with
a single Dim-line seems to work:

Sub dummy()
Dim EF
End Sub

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

Hans Knudsen said:
Hello
I made a UDF and wanted to name it EF (uppercase). However, first time I
Click to expand...
entered the function, I happened by mistake to enter ef
 
Hans,

It doesn't make any difference, upper or lower-case, it is the same
function. Even if you name it EF, using it on a worksheet it will show as
=ef().

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hans Knudsen said:
Hello
I made a UDF and wanted to name it EF (uppercase). However, first time I
Click to expand...
entered the function, I happened by mistake to enter ef
 
Hi Hans!

I think there's a way. But this is not exhaustively tested.

It seems that the UDFs don't act the same way as Built in Functions.
If you use upper case in the UDF and when you enroll the function in
the function categories you can enter in lower or upper case and that
will stick. That "stick" will hold for anywhere in the workbook or
another workbook and will also change the case of the function if used
in a workbook that you open.

I did think that enrolling in uppercase would force a switch but that
process only seems to affect the case in the Function selection wizard
dialog.

You can stop the sticking by closing Excel and re-starting. So if
you've used your EF a lot:

Close the workbook
Close Excel
Open Excel
In a new workbook, use the function EF
Now open your original workbook and the lower case ef should all be
changed to upper case.

It now occurs to me that we might be able to emulate the switch case
approach of Excel functions if we use those functions in the
Personal.xls or Addin that creates them. I've not tried this yet but
it looks like it should work.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Hans Knudsen said:
Hello
I made a UDF and wanted to name it EF (uppercase). However, first
Click to expand...
time I entered the function, I happened by mistake to enter ef
 
One more way that worked ok for me in xl2002.

Change then name of your function (temporarily). I used EFA.

Then all your formulas will turn to name errors.

Now select a cell and give it the range name of EF.
Select A1
type EF in the namebox (to the left of the formula bar)
(don't forget to hit enter and make sure it matches the case you really want)

(Your formulas turn to Ref errors.)

Now delete that name.
Insert|Names
select it and delete it.

Back to VBA and change the function name to match what you want.

Back to excel and recalculate.

My formulas became =EF(...)
 
Thank you to all who replied.
Should I add that shortly after I posted my message I tried the following which also worked:

1. Changed the code so that all instances of EF temporarily were named EFF.
2. Used the function EFF one time in a worksheet.
3. Changed the code back (EFF replaced by EF)
4. Used the function EF in a worksheet, remembering to enter EF (uppercase) the first time.

Regards
Hans Knudsen
 
Glad you got that working.

But I had to remove all the existing =ef() formulas for that to work for me.
And when I added them back, I had to make sure the first one added was uppercase
(like you said).
 
Hi Hans!

Prevention better than cure?

Close Excel
Re-Open
Put the function in your Personal.xls file
Unhide the Personal.xls
Use the function in upper case in the Personal.xls file
Hide the Personal.xls

Now try and use your function using lower case.

Or:
Close Excel
Open Excel
Put the function in a separate file
In the (compulsory) empty sheet use the function with upper case
Save as an Addin in the Library File
Close Excel
Open Excel
Tools > Addins
Select your new Addin
Try using the new function without using upper case


Thanks for this post! We now have a way of getting UDFs to convert to
all upper case if entered in lower case. John Walkenbach (I recall)
recommends using lower case entry of functions as failure to convert
to uppercase indicates a mis-speling.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Hans Knudsen said:
Thank you to all who replied.
Should I add that shortly after I posted my message I tried the following which also worked:

1. Changed the code so that all instances of EF temporarily were named EFF.
2. Used the function EFF one time in a worksheet.
3. Changed the code back (EFF replaced by EF)
4. Used the function EF in a worksheet, remembering to enter EF (uppercase) the first time.

Regards
Hans Knudsen



"Hans Knudsen" <[email protected]> skrev i en
Click to expand...
meddelelse news:[email protected]...
 
Hi All!

And now a question.

I've found that I can use the Sheet on an addin to call my functions
in upper case and thus force conversion of entry to upper case.

How can I best achieve this for an existing Addin?


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top