Add-in function has #NAME error

[Don]

I am using Excel 2000 and after I loaded my first custom-written addin, the
addin function is apparently not recognized because when I put the function
into a cell I get a #NAME error.

The function name appears in the function drop-down list (I think this is
called the Name Box) under "user defined" as you would expect. And I can
pick it from there to put it into a cell. This eliminates any typo problem,
but as I said I still get a #NAME error.

In summary, I can pick the addin function from the Name Box and yet I get
the #NAME error. I think "#NAME" means the item is unknown and yet it's in
the Name Box. How could it be unknown? Makes no sense.

Any ideas?

 

[Niek Otten]

It is not called Name Box, it is called Function Wizard.
What is your formula? It would help to give the code of your function.
Anyway, you need an opening and closing bracket after the function name in
your formula, even if the function has no arguments.
Do post the code of the function; we may be able to help.

 

[Dave Peterson]

Maybe you mistyped something else in the the function?

=myfunc(a1,averag(c3:c6))

Would cause an error because =average() was spelled incorrectly.

Do you have any workbook/worksheet names that are the same?

Did you name a module by the same name as the function name?

 

[Don]

Thanks everyone for the ideas. I just noticed that the addin works correctly
in other worksheets. Apparently it doesn't work in just one worksheet.

The worksheet where it doesn't work originally had these same functions
explicitly coded and then removed when I made the addin. So I'm guessing
there is a conflict somewhere. I'll try changing the addin function names
and see what happens.

Thanks again.

 

[Dave Peterson]

That is a problem and one you can avoid by developing in a test workbook--one
that can be deleted after you move the code to its real home.

But you can "fix" the workbook that had the UDF and now returns that Name error.

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?

 

[Don]

Actually I did develop the addin using a new empty workbook.

I simply copied the functions that were explicitly defined in workbook A
into the new workbook (let's call it workbook B). Then I saved the addin
created in workbook B and deleted the associated modules in workbook A.

After I loaded the addin into Excel it did not work in workbook A (the #NAME
error). It did work in other workbooks.

Anyway, I re-created the addin with new names and now it works in all the
workbooks.

Thanks again.

P.S. I did the above before reading your note so I was not able to try your
latest suggestion.