UDF's, XLA's and #NAME!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim - 13 Dec 2007 14:52 GMT

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

Reply to this Message

Dave Peterson - 13 Dec 2007 15:34 GMT

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?

> 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
[quoted text clipped - 14 lines]
> TIA,
> Jim

Signature

Dave Peterson

Reply to this Message

Jim - 13 Dec 2007 17:18 GMT

Yes, it works! You're a genuis.

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

Thanks,
Jim

> After you've removed the function and placed it into the .xla, try this:
>
[quoted text clipped - 36 lines]
>> TIA,
>> Jim

Reply to this Message

Dave Peterson - 13 Dec 2007 18:05 GMT

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.

> Yes, it works! You're a genuis.
>
[quoted text clipped - 47 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

g- - 14 Dec 2007 06:07 GMT

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-
gwkenny@yahoo.com
___________________________________

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

Reply to this Message