#NAME error on all VBA module code

[Philip Mark Hunt]

I recently restructured my directories and now all the VBA code produces a
#NAME? error. It is all correctly located in Module 1 of the spreadsheet
where the code is being called from, and all worked quite happily before.

I have read and tried to understand and follow through on the advice against
similar posts to this one, but all the way I have things set up is exactly as
those advices direct.

Additional thoughts and advice would be welcome please.

With thanks in anticipation

Philip Hunt

 

[Dave Peterson]

Could it be as simple as you don't have macros enabled?

If no, what does the formula that causes the name error look like?

 

[Joel]

The error is probably caused by the following as stated in the excel help

Referring to another worksheet that does not exist. (xlErrName)


Click on one of the cells with the error and check the pathname in the
formula.

You can fix the problem either by edit links or just use the worksheet
find/replace to change the path in the formulas.

 

[Philip Mark Hunt]

Dear Dave and Joel

Thank you for pitching in.

Yes, macros are enabled.

The formula call reads as follows -

=Summarise_Holdings("Yes",M88:Z88,D88,E88)

There is no pathname other than the above clearly evident to me. Maybe I
need to go look at some settings within the spreadsheet / workbook / Excel
globally that I am not aware of / not aware of how to gain access to such?
Please advise accordingly.

Summarise_Holdings is just one of about a dozen VBA UDFs I have written for
this workbook and they are all sitting in Modules:Module1 in the code area.
When I right click on a worksheet's name tab and click View Code, all the
code is correctly sitting in Module 1. None of the UDFs now work, nothing
can be 'seen', so to say, by the worksheets of the main workbook.

Thank you for your suggestions.

 

[Dave Peterson]

Somethings to try...

If you select an offending cell and hit F2 (to edit it) and Enter (to fool excel
into thinking you've made a change), does it evaluate correctly?

If yes, I'd try selecting all the cells (on all the sheets???):
Edit|replace
what: =
with: =
replace all

If that doesn't work, if you change the function name (in the VBE) to something
unique:

Function yyyy_Summarise_Holdings(...

And then change the formula to use that newly named function, does that work?

Then maybe this will help you. I've used it when I develop a UDF in a workbook,
then decide that I want to use an Addin to hold the UDF code instead. The test
workbook has trouble determining where the function code really lives.

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

 

[Philip Mark Hunt]

Dear Dave

I tried all of your ideas but nothing worked. I tried to create an xlam
from the xlsm but despite reading the instructions carefully (both online &
in a quality text book I have), I ended up with a file that seemed to be
empty.

The instructions were clearly not idiot proof enough for me. The idea of
trying the add-in way appeals but I just don't seem to be able to work out
how to do it just now.

Any further ideas would be welcome.

It really does seem strange that this has happened, because all I did was
move the directory up one level in a tree.

Thank you

Philip

 

[Dave Peterson]

If the original problem was that you moved the UDF from your normal excel
workbook to a UDF and want to use the functions in the UDF, then try that second
suggestion again.

It's always worked for me (and several others).

If the original problem wasn't that, then I'm out of ideas. Sorry.

Dear Dave

I tried all of your ideas but nothing worked. I tried to create an xlam
from the xlsm but despite reading the instructions carefully (both online &
in a quality text book I have), I ended up with a file that seemed to be
empty.

The instructions were clearly not idiot proof enough for me. The idea of
trying the add-in way appeals but I just don't seem to be able to work out
how to do it just now.

Any further ideas would be welcome.

It really does seem strange that this has happened, because all I did was
move the directory up one level in a tree.

Thank you

Philip

 

[Philip Mark Hunt]

Dear Dave

Thank you for giving it a go.

I would like to try that Addin (.xla) approach, but I just don't seem to be
able to work out how to create an Addin successfully.

If there are truly small step by small step !!! instructions available to
learn how to take the code in a VBA Module and copy and paste it into a new
Addin then I would welcome them.

Best regards

Philip

 

[Dave Peterson]

Close excel
Find the workbook with the code
Copy it to a new name (say PhilsUtils.xls)
Open excel
Open that new file
Remove all the worksheets but one
Clear all the cells on that remaining worksheet
Open the VBE
Delete the modules you don't need
Delete the procedures you don't need
Back to excel
File|SaveAs|choose Addin
and save as PhilsUtils.xla

Close excel and reopen it
Open philsUtils.xla
(allow macros to run)
On that new workbook, try one of your UDF's:
=myfunc(A77)
(or whatever you need)

Dear Dave

Thank you for giving it a go.

I would like to try that Addin (.xla) approach, but I just don't seem to be
able to work out how to create an Addin successfully.

If there are truly small step by small step !!! instructions available to
learn how to take the code in a VBA Module and copy and paste it into a new
Addin then I would welcome them.

Best regards

Philip