Excel Addin Function gives #NAME? Errors

[Maurice Elliott]

Running Microsoft Office 2003 SP1 on Windows XP.

I have an Excel add-in function XYZ (Name changed to protect the guilty!)
that takes three numbers in and returns a numeric result. Others in my group
have installed the add-in on their machines and routinely use and develop
workbooks that refer to this function in cell formulas. The .xla file
containing the code resides in their C:\Program Files\Microsoft Office
2003\OFFICE11\Library directory. (In actual fact, there is a set of four
related functions, all of which appear to exhibit the same behaviour)

Sometimes, we use this function in one or more cells inside an iterative
loop in our calculations (Iterations turned on in the Tools/Options dialog
Calculation tab). Occasionally we run into problems where the function does
not seem to be recognized, even though the add-in is listed in the
Tools/Addins list, and is checked. I'm looking at an example right now. The
symptoms are as follows:

1. The cells inside the iterative loop, and cells that refer to them, all
show "#NAME?" instead of a result. These did not appear while the workbook
was being created, only after it was saved (on another machine) and reloaded
(on my machine).
2. If I enter a formula in a previously-empty cell in this workbook, such as
"=XYZ(1,2,3)", the result appears as "#NAME?"
3. If I open a new workbook in the same Excel session and enter the same
formula in an empty cell in the new workbook it works as expected.
4. I can use the formula builder ('fx' button) in both workbooks. In both
cases, the function XYZ IS listed in the 'User Defined Functions' list, and
its syntax is displayed correctly. As I enter the arguments (hard-wired
numbers, no cell references) in the problem workbook, the dialog box never
displays a value for the result, even after I've entered a value for the
final argument. And when I click on the OK button, the dialog box closes,
leaving "#NAME?" in the cell. In the new (clean) workbook, the dialog
proceeds identically, except that a value (correct) displays for the result
after I've entered the final argument, and the cell shows the same, correct,
result after the dialog box closes.

I've tried un-checking the name in the Add-Ins list, closing and re-opening
Excel in that state, and then re-checking the item. I tried changing the name
of the .xla file and switching to that copy of the file, and I've copied the
file into the add-ins directory in my settings, as Microsoft suggests in some
of its documentation. None of these actions led to any change in behaviour.
On occasion, I've received a warning to the effect that "a file with the same
name already exists at this location, do you want to overwrite it?" as I
checked the add-in in the list. (What am I overwriting here, I ask myself!) I
have tried answering both Yes and No, but there seems to be no difference.

Can anyone suggest what is happening here, and what I might try to solve the
problem? Any suggestions/hints/help will be gratefully received.

 

[Niek Otten]

No idea, but I'd try to rebuild the dependency tree with CTRL+ALT+SHIFT+F9

 

[Maurice Elliott]

Thanks for your suggestion. Tried it, but nothing seemed to change - still
#NAME? in the same cells.

Regards . . . . /Maurice.

P.S. I've also tried the old "Save As' trick, but it didn't help either.

 

[MrShorty]

I don't know if this will help, but make sure the link to the add-in is
to the correct file in the correct location. Open the Links dialog
(Edit->Links) and make certain the desired add-in is referenced and
referenced to the correct location. If it's got the wrong location for
the file, you can correct it using the "change source" button.

 

[Maurice Elliott]

That's a good thought. Thanks for suggesting it. However, what do you do when
the Edit->Links menu item is disabled (grayed-out)? That was something else
wierd about this workbook (especially when a clean workbook in the same excel
session doesn't have the same problem - it freaks me out!).

Thanks for taking the time to reply. I'm still running around in circles
trying to make sense of what's going wrong.

Regards . . . . /Maurice Elliott.

 

[Maurice Elliott]

Some more clues, and a fix/workaround:

I copied the VBA code from the addin into a new code module attached
directly to the bad workbook. Nothing changed immediately, but when I pointed
to each cell containing a reference to the XYZ function and pressed F2 and
Enter, the #NAME? was replaced by the correct value. Once I had done that for
all references, the bad workbook seemed to work OK (It did not, in fact,
contain an iterative loop, by the way).

I then went to the clean workbook (in the same Excel session), and used the
Insert Function dialog box to insert a formula referencing function XYZ into
a cell. I noticed that the list of UDFs in the dialog box contained two
references to XYZ - one simply 'XYZ' and the other to 'Bad Workbook!XYZ'. I
chose the simple reference.

I then went back to the bad workbook and removed the module containing the
copy of the addin code. The formulas in the bad workbook immediately went bad
(#NAME?) again, but the formula in the clean workbook stayed OK.

Then I found the workaround: I copied all the cells from the bad workbook
and pasted them into the clean workbook. Nothing changed immediately, but
when I refreshed each formula in the clean workbook (using the F2/Enter
trick) that referred to function XYZ, the cells changed to their proper
values and remained that way. I now have a workbook that calculates properly
(until the next time, whenever that is!)

So the heat is off. Thanks to those of you who took the trouble to post a
suggestion. And if anyone can shed any insight into what the heck went wrong
here, I'd be glad to learn it.

Regards . . . . /Maurice Elliott.