How to store VBA Macros for Office 2007 to have them always available and share them with others?

Question

I have created some useful VBA macros for Word, Excel Outlook and PowerPoint 2007.

Now I want to

• have them always available in the corresponding application via keyboard shortcut or icon to click and
• also have a possibility to share them easily with colleagues so if anything gets updated that they can easily use the new version.
• have an overview over changes (version control)

Already creating custom commands in the "ribbon" in Office 2007 seems very difficult for me and I have the problem that when I store the macros in a certain file, this file has to be opened to make the macros work - I'd like to have that open in the background. Does anyone know a good tutorial or book which covers that?

Can I create my own AddIn? If I remember correctly, and AddIn can not be modified directly any more, so if the code is constantly growing I'd need to save the source code in one place and the addin in another - is that true?

Answer 1

With all due respect, Luiz is mistaken. You do not need Visual Studio to create add-ins, though you can certainly USE it for this.

In PowerPoint, you write VBA code and save it in a PPT or PPTM file, depending on your PowerPoint version. The code can use the old CommandBars object model to create command bars and buttons (which appear on an Add-ins tab in 2007/2010/2015).

You can then save as a PowerPoint Add-in (PPA or PPAM again depending on version and compatibility needs). The end user can install the add-in as they would any other, or you can create an installer or push the necessary registry settings out via group policy or whatever to multiple users. Installing an add-in via the registry involves only two or three settings.

Once installed, the add-in's toolbars/functionality would be available in the app and to any file open within it, not restricted to any particular document file.

Updates? Copy a new PPA/PPAM file to replace whatever one's already in place. The only restriction is that PowerPoint can't be running when you do that.

And back to due respect for Luiz ... he's correct, a third-party tool would be necessary to do version control. That or what a lot of us do if highly granular control isn't necessary; before making any changes, save the source file to a new file; each file's name includes the date it was created. It's also useful to include a constant that's set to the same date. An "About" command in the add-in can then display version or date or whatever you like so you can verify that the user has the correct version.

I have more detailed information about this on my Powerpoint FAQ site:

Creating and Installing Add-ins, Toolbars, Buttons http://www.pptfaq.com/index.html#name_Creating_and_Installing_Add-ins-_Toolbars-_Buttons

Answer 2

For Excel, your macros should be stored in the Personal Macro Workbook (PERSONAL.XLSB), which is by default hidden. Word has a Normal template which (IIRC) can store macros. Outlook has a similar thing, but I can't remember what it's called. PowerPoint is the anomaly and as far as I remember doesn't seem to have a place to store global macros.

I use SyncBackSE to sync my Excel macro workbook between computers. I don't update Outlook macros frequently enough to need a way of syncing them, but you could probably do something similar. Syncing can only be done when Excel is closed, as it locks the workbook. I don't know how you'd implement version control.

Answer 3

If I'm not mistaken, you need Visual Studio (and Visual Studio Tools for Office) to create AddIns.

If you want the commands to be always available you can try to import them to a Macro enabled model and overwrite Normal.dotm (at least in Word). Just alert them before doing that.

Who are your colleagues? Are they in the same company? Do you share a network with them? If so, create a startup script that will overwrite the users' Normal.dotm.

If you're not on the same network, try services like Dropbox. Share a folder with them. In the folder, create your macro enabled files and a batch to overwrite the users' Normal.dotm.

Another option is to leave normal.dotm and create simple model documents leaving them available on the network/cloud and instruct your colleagues to use those files when they want to use your enhanced versions.

Now... version control? You'll have to use 3rd party tools for that.