Thanks for your reply, Edwin.

The function in the .xla Add-in IS available to all worksheets (except
the original one).

Using your example, Function testing() was initially developed within
the workbook called First_book. After that, an identical function was
generated in Add-In.xla. Now if I delete the function that is within
First_book, all references to testing() evaluate as #NAME, even newly
entered calls(references). New references to testing() in any other
workbook work fine. That is they find the function in the add-in.

I looks like First_book continues to expect testing() to be a function
defined within First_book and does not look in Add-In.xla to find it
after the 'local' copy is removed.

Since you seem willing, maybe it would be best to send you
First_book.xls and Add-in.xla to demonstrate the issue.

Thanks for your assistance.
Spam Hater


On Mon, 26 Apr 2004 01:56:02 -0700, Edwin Tam
wrote:

I assume that, in your situation, you tried to achieve the followings:

1) You produces a worksheet function in VBA.
2) You saved the file as an add-in and wanted to share it with others.

It should be easy.

Let's do the following experiment.

1) In a new workbook, insert a new Module in VBA Editor
2) Type the function:

Function testing()
testing = 123
End Function

3) Save the file as an add-in.
4) Close the file and quit Excel
5) Start Excel and load the XLA file. (No need to do it through Tools, Add-in. May just double-click its icon to open it.)
6) In a worksheet, in a cell, type:

=testing()

The cell should return the answer "123".


So, from the above experiment, we know that:
- All worksheet fuctions stored inside an XLA can be available to all worksheets in opened workbooks.


If you still have problem, you may send me your add-in and I can have a look for you.

Regards,
Edwin Tam




----- Spam Hater wrote: -----

I've searched/sorted through 60,000 messages here, but didn't find an
solution to my problem.

I created my first VBA macro in a particular spreadsheet and it works
great. So great that I've been requested to share it with others. I
generated an .xla file with the macros in it and have successfully
used my function from there and shared it with others.

The problem: I can't remove the local macro module from the original
spreadsheet and use the addin. When I remove the original VBA module,
all reference to the function result in #NAME and will not recognize
the function from the AddIn (which is selected in the Tools/AddIn
menu).

I'm using Excel 2002 (from Office XP).

Thanks,
Spam Hater