How to replace a macro with an addin??

[Guest]

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

 

[Brian Baulsom]

Have you defined the function as Public ? ie :-

Public Function MyFunction()

End function


Regards
BrianB


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Edwin Tam]

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 Edito
2) Type the function

Function testing(
testing = 12
End Functio

3) Save the file as an add-in
4) Close the file and quit Exce
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 Ta
(e-mail address removed)


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

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

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

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

I'm using Excel 2002 (from Office XP)

Thanks
Spam Hate

 

[Guest]

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