Minor puzzle: some UDF calls respect mixed case, others insist on lower case

[Prof Wonmug]

I have a bunch of UDFs that I have used for years hundreds of times in
several workbooks. The names of the UDFs in the add-in modules are
coded in mixed case (RayleighMean, RndTallyLog, & ExpSF).

Up until a few days ago, whenever I would code a UDF call in a cell
(=ExpSF(...)), Excel would change the name to lower case
(=expsf(...)). I recall trying repeatedly to get Excel to leave it in
mixed case.

A few days ago, I wrote a new UDF called FmtTime. I just noticed that
all of the calls to this one function are in mixed case just like I
entered them. In fact, if I try to change it to all lower case
(fmttime), Excel changes it back to FmtTime.

What the heck is going on?

Excel is like a lot of girlfriends I've had...can't live with them or
without them.

 

[JLGWhiz]

Check your code for the UDF. If you used lower case within the code, it
might be VBA doing the conversion, rather than Excel. I know that I have
been surprised a few times by certain object variables turning up in a
differenct case than originally declared.

 

[Prof Wonmug]

Check your code for the UDF. If you used lower case within the code, it
might be VBA doing the conversion, rather than Excel. I know that I have
been surprised a few times by certain object variables turning up in a
differenct case than originally declared.

As I said in my post, the UDF names are in mixed case (see below).

 

[Helmut Meukel]

As I said in my post, the UDF names are in mixed case (see below).

In VB (and VBA) this may happen if you use the same name twice.
It happend to me when I had a User Defined Type element with the same
name as a local variable. Or a Function in one module and a variable or
UDT element in annother module with the same name. It's allowed if
there is no ambiguity, but VB will use only one case (both same mixed
case or both lowercase or both uppercase).

HTH.

Helmut.

 

[JLGWhiz]

Helmut is describing the conditions that I referred to. It is not Excel
playing tricks. It is related to your code and how the VBA compiler stores
your names, variables, etc. in memory. If code is written inconsistently in
respect to case, VBA will use its own logic to determine what is stored in
memory and that is what will be displayed. Doesn't make any difference
whether case displayed is lower, upper or mixed, what you see is what VBA
has in memory for that item. So if you want it to be consistent, then it
will be necessary to review your code to see where the anomaly occurred.

 

[Dave Peterson]

From what I can tell, excel remembers the first time you use the UDF in a
worksheet cell. It won't matter how the function is declared in your VBA code.
After you've used it once (in any workbook!), excel will remember it forever.

Unless...

You do something to make excel forget it.

This worked for me.

I created a UDF:
Function Test88() as boolean
Test88 = True
End Function

Then I used:
=TEST88()
in a cell in that workbook.

And excel remembered the case after that.

So I changed the name of my function in the code:
Function Test88xxx() as boolean

Then back to excel to create a new workbook name.
I selected any old cell I wanted.
Insert|name|define
TeSt88 (note the funny case).
And let it point at that cell.

I typed this in a cell:
=test88
and excel converted it to:
=TeSt88

Then I deleted the name (Insert|Name dialog)

I fixed the UDF function name in the VBE.

And recalculated.
(I sometimes select all the cells, edit|replace equal signs with equal signs to
force all the functions to recalc.)

And the case of my function was changed to:
=TeSt88()
and excel remembered it when I used it again (no matter how I typed it in).