Prev: Error message on opening Excel after deleting add-in file
Next: The VBA IDE will discard changes without warning
From: Dave Peterson on 30 Apr 2010 19:32
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).





Prof Wonmug wrote:
>
> 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.

--

Dave Peterson
First  |  Prev  | 
Pages: 1 2
Prev: Error message on opening Excel after deleting add-in file
Next: The VBA IDE will discard changes without warning