What is the best way to package and distribute an Excel application

Accepted answer +10 upvotes

Simply move your code into an Excel Addin (XLA) – this gets loaded at startup (assuming it’s in the %AppData%MicrosoftExcelXLSTART folder) but if it’s a addin, not a workbook, then only your macros and defined startup functions will be loaded.

If the functions depend on a spreadsheet itself, then you might want to use a combination of templates and addins.

I’m distributing part of an application like this, we have addins for Word, Excel and Powerpoint (XLA, PPA, DOT) and also Office 2007 ‘ribbon’ versions (DOTM, XLAM and PPAM)

The addin startup code creates toolbar buttons if they’re not found, this means in any workbook/document/etc they can simply hit the toolbar button to run our code (we have two action buttons and one button that displays a settings dialog)

Templates aren’t really the way to go for VBA code, Addins are definitely the way to go…

So to load the toolbars on startup we’re using something like.. (checking to see if toolbar exists though – code will run for each worksheet that is opened, but toolbars are persistent for the user session)

Public Sub Workbook_Open()
     ' startup code / add toolbar / load saved settings, etc.
End Sub

hope that helps 🙂