Installing a VBA macro in Excel 2007

Question

I've got a VBA macro in an Excel 2003 spreadsheet and I'd like to 'install' it on a machine running Excel.

I created a 'Trusted Location' on the local machine, and I know how to copy the module to the existing workbook and assign a key combination to invoke it, but I don't know how to make the macro appear automatically when someone starts Excel. Help?

Answer 1

I'm not sure what you mean by 'appear', but you can execute it automatically by calling it from

Private Sub Workbook_Open()

End Sub

in the ThisWorkbook object module. Is that what you're looking for?

Answer 2

I remember the old way was to save your macro in your Personal.xls workbook - then it would be accessible any time you opened Excel - has this changed for 2007?

Answer 3

I remember the old way was to save your macro in your Personal.xls workbook - then it would be accessible any time you opened Excel - has this changed for 2007?

That was a way. A better way was (and is) to create an Add-in, which you can then enable & disable via Tools->Add-ins. (An Add-in will remain enabled even if you close and re-start Excel). You can save any .XLS file as an Add-in (.XLA).

Within your Add-in you could just use an Auto_Open method:

Private Sub Auto_Open()

    DoStuff

End Sub

...or, you could hook up the Workbook_Open event, as Ryan suggests.

Of course, since the user can disable the Add-in, you also want to do the reverse in Auto_Close (or in the Workbook_BeforeClose event).

Actually, if you use events it'd be better to use the Workbook_AddinInstall and Workbook_AddinUninstall events. Using those is slightly more "correct", and also has the benefit that the 'close' event doesn't fire if you close Excel and then hit Cancel when prompted to save.

Answer 4

The simplest solution is ( both for xl2003 and xl2007 ) to copy your xls containing the macro to the path = Application.StartupPath from your client machine ( can build a simple vbscript installer which instantiates excel and retrieves this information ).

This way your macro will be available in any workbook opened since the xls files located in startuppath are loaded at excel startup.