G
Guest
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.
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.