CODEWITHSUNDEEP
vbaexcel
CustomX
CustomX

Reputation: 10113

Excel VBA - Call macro using add in

I have added a toolbar menu for my macro, so I can just press the button and it runs my macro from any excel document. Every time I run the macro though, it opens the source file containing the macro. Is there a way that it won't open the source file and just run the macro? (even opening and closing wouldn't too much of an issue, but I'd prefer not opening it at all)

I haven't been able to find any information about this, so all help to get me started is appreciated.

Upvotes: 0

Views: 1338

Answers (2)

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

USE YOUR PERSONAL MACRO WORKBOOK

Open the VBEditor and find the module containing your macro.
Copy that code.

Now in the VBProject Panel on the left, locate your PERSONAL.XLS or PERSONAL.XLSB project. This is the project where you store macros you want available at all times. Add a module to that project and put your code into it.

Now update your "button" so that it points to the macro in that workbook and you're good to go.

On the off chance your PERSONAL.XLS project does not exist, use the macro recorder to record a "junk" macro and be sure to set it to "Store Macro In: Personal Macro Workbook"... that will create it for you the first time.

Upvotes: 2

Steve Rindsberg
Steve Rindsberg

Reputation: 3528

You can't pull code out of the air to run it; Excel's going to have to open the file the code's stored in to run the code.

If I understand it correctly, you can create an Excel add-in from your code and have the add-in load automatically when Excel starts. Your code will always be available in that case.

This seems like a good place to start:

http://msdn.microsoft.com/en-us/library/aa140990(v=office.10).aspx

Upvotes: 3

Related Questions