UDF's, XLA's and #NAME!

[Jim]

I initially developed some UDF's in an XLS, which was used for testing. All
was fine. An XLA was then created with these UDF's. In a new XLS, these
UDF's also work fine.

But in the original XLS used for development I removed the UDF's from the
XLS, expecting that Excel would find the UDF's in the XLA. But references
to any of these UDF's produces #NAME!. If I qualify the reference with
"NameOfXLA.xla!MyUDF()" it then works. BUT, Excel removes the
"NameOfXLA.xla!" from the formula (I guess because the name MyUDF is unique
so it's not needed?). The next time you edit the formula it's back to
#NAME!. Grrrr...

(Yes, the AddIn is registered.)

Any way to remedy this?

TIA,
Jim

 

[Dave Peterson]

After you've removed the function and placed it into the .xla, try this:

In the workbook that lost that function (call it myFunc).

Insert|Name|Define
MyFunc
refers to $a$1
(of the activesheet)

Then delete that newly created name via Insert|Name|define.

Then finally, select the cells with the function (or all the cells)
edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

Did it work?

 

[Jim]

Yes, it works! You're a genuis.

Any ideas on my other post, "UDF arguments"?

Thanks,
Jim

 

[Dave Peterson]

Nope.

You can use:
=myFunct(
and hit ctrl-shift-A
to see the variable names -- so choose them wisely.

Ctrl-a will show you a dialog box that may be useful, too.

 

[g-]

Hi Jim:

I hit this wall too. There seems to be a "pointer" that has the XLS
continually try to find the UDF in the XLS and does not look outside to the
XLA.

I had to move the worksheets/modeles in the original XLS into a new file and
then everything worked fine.

g-
(e-mail address removed)
___________________________________

Need holiday money. Got any jobs, big or small, drop me a line! Thanks!