UDF Not Recognized

[Smartin]

.... Not for the usual reason.

I have a UDF that lives in a standard module in an add-in. The add-in
loads automatically. I have one workbook with two worksheets that call
the UDF. For some time I had no problems, but recently I tweaked one
of the worksheets. Now, that worksheet no longer recognizes the UDF
(returns #NAME). The other, untweaked worksheet works fine.

I made a copy of the UDF in the module where it resides, gave it a
different name, and adjusted the worksheet to call the new copy. Works
great. But surely I don't need to keep two identical copies of a UDF
in the same module... What could be going on here?

For what it's worth the add-in has dozens of UDFs (in other modules)
that work just fine. This is Ex2003 by the way.

 

[Dave Peterson]

This is just a guess at how to fix it -- not an explanation.

I'd get rid of the second UDF.

Then on the troublesome worksheet, I'd change the function calls back to the
original name.

My bet is that will fix the problem.

If it doesn't, try selecting all the cells.
Edit|replace
what: = (equal sign)
with: =
replace all

I bet (er, I hope) that excel will see the change (even the function call in the
formula change) and reevaluate the formula.

If it doesn't, then the ='s to ='s may work.

But without more info about that UDF and its code, I wouldn't venture a guess
why it broke.

 

[Smartin]

This is just a guess at how to fix it -- not an explanation.

I'd get rid of the second UDF.

Then on the troublesome worksheet, I'd change the function calls back to the
original name.

My bet is that will fix the problem.

If it doesn't, try selecting all the cells.
Edit|replace
what:  =  (equal sign)
with:  =
replace all

I bet (er, I hope) that excel will see the change (even the function callin the
formula change) and reevaluate the formula.

If it doesn't, then the ='s to ='s may work.

But without more info about that UDF and its code, I wouldn't venture a guess
why it broke.

Thanks for the suggestions, Dave. They are good ones for the books. I
now recall using the replace = with = trick to fix UDF calls in the
past. However, no luck with any of that today. I did make some headway
though. In the process of deleting and re-typing UDF calls I noticed
at one point the function I wrote as
=SmoothY(...)
turned into
=MyAddIn!SmoothY(...)
without my prompting.

Then I remembered -- I originally developed the function in the
workbook, then moved it later to the add-in. Near the same time I
moved the location of the add-in from a network to which I am not
always attached to the local C:. It seems the worksheet is confused
about where the function is located. I searched the installed projects
to ensure there is no duplication of the function name "SmoothY" -- it
only exists in the add-in. But I was able, at least temporarily, to
fix the worksheet by prepending "MyAddIn!" to the function call.

I will see how it goes over the next few update cycles (which includes
on and off the network).

Thanks again for your thoughts!

 

[Ron Rosenfeld]

Thanks for the suggestions, Dave. They are good ones for the books. I
now recall using the replace = with = trick to fix UDF calls in the
past. However, no luck with any of that today. I did make some headway
though. In the process of deleting and re-typing UDF calls I noticed
at one point the function I wrote as
=SmoothY(...)
turned into
=MyAddIn!SmoothY(...)
without my prompting.

Then I remembered -- I originally developed the function in the
workbook, then moved it later to the add-in. Near the same time I
moved the location of the add-in from a network to which I am not
always attached to the local C:. It seems the worksheet is confused
about where the function is located. I searched the installed projects
to ensure there is no duplication of the function name "SmoothY" -- it
only exists in the add-in. But I was able, at least temporarily, to
fix the worksheet by prepending "MyAddIn!" to the function call.

I will see how it goes over the next few update cycles (which includes
on and off the network).

Thanks again for your thoughts!

Somehow, between your add-in and your workbook, you have what I call
"name confusion". You are probably using the same UDF name in two
different locations. It could be the same function (name) in two
different modules; or maybe even a module that has the same name as
your UDF. Fix that and you should be OK.