Moving Public Function From Workbook Module to XLA

[Bruce]

I have a function that exists in a workbook module and it works when
called from the spreadsheet. If I move the code to a separate .XLA I
get a #NAME? like it can't see the moved function. If create the
function first in the .XLA it works, then move it the a workbook
module it works, but when I move it back to the .XLA I get the
#NAME?. If I delete the call in the cell and recreate it, it does not
help.

It's somewhat obvious to me that Excel has put the name in some
internal table but how can I see this and/or fix this? The actual
problem is more complicated and I can't rename the function but what I
describe above is a simplified version of my real problem.

Thanks,
Bruce

 

[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?

 

[Bruce]

You are a genius, it worked great. The good thing about this solution
is that it's trivial to do. It's hard to explain but I'd like to be
able to do this 'move' somewhat regularly.

Thanks,
Bruce

 

[Dave Peterson]

When I'm developing a UDF, I'll put it in a test workbook. Then take as much
time as I need to get it running there.

When it's working perfektly(!), I move it to its real home and just delete that
test workbook file. If I need to test the UDF in a cell in a real workbook,
I'll just fully qualify it:

=testworkbooknamehere.xls!myfunct(....)