UDF #name? Error

[KAHAUS]

When I save my UDF's to an .xla file they are only available to worksheets
created after the add-in was added. Worksheets created prior to the add-in
being added produce the dreaded "#NAME?" error when the function is
referenced. Even in old sheets, however, the Function Wizard/Function Help
finds and and calculates properly. It is only the worksheet that produces
the error.

This occurs both in Excel 2007 and 2003.

Any help would be appreciated.

 

[Barb Reinhardt]

Is the add in loaded when you try to access it?

HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.

 

[KAHAUS]

Yes, the add-in is loaded then the old workbook opened. In the old workbook,
functions on the sheet itself produce the #NAME? error. In the old workbook,
if I click on the fx next to the formula bar accessing the function wizard,
select the catagory "User Defined", my UDF's show as expected. Selecting a
function and filling in the required parameters, the functions calculate as
desired showing the expected result in the function wizard window. Clicking
on OK and closing the function wizard, the worksheet produces #NAME?.
Entering the function directly in the worksheet produces #NAME?. This is
true for all workbooks that were created before the add-in was installed on
the computer.

If I begin a new workbook with the add-in loaded, the UDF's work properly
and as expected. If I copy a worksheet from an old workbook (pre-addin) into
the new workbook, they work as expected.

This situation has been observed in Excel 2003 and Excel 2007 and on
multiple computers. The add-in .xla file is large, over 1.2MB, containing 60
to 70 modules and hundreds of UDF's, guessing say 14,000 lines of code.

 

[Charles Williams]

Yes I agree that Excel works that way, (don't know why!).
The easiest bypass seems to be copying the worksheets into a new workbook.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com