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

microsoft-excel-2007microsoft-office-2007microsoft-powerpoint-2007microsoft-word-2007vba

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?

Best Answer

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.

Related Solutions

Outlook – What would an Outlook 2007 macro to automate Paste Special – Unformatted Text look like

With lots of help from author Sue Mosher, I have finally managed to get a macro working on Outlook 2007 to automate the mouse clicks of Paste – Paste Special – Unformatted Text! WOO-HOO!!!!

Most of what I cut and paste into Outlook email messages is formatted. Pasting with CTRL-V or by clicking the Paste button keeps that formatting intact. Clicking Paste – Paste Special – Unformatted Text isn’t a big deal, but it takes a couple of seconds every time I do it – and I do it a LOT. So this little thing will be a timesaver for me.

The VBA subroutine:

Sub Paste_Special_Unformatted()
    Dim objDoc As Word.Document
    Dim objSel As Word.Selection
    On Error Resume Next
    ' get a Word.Selection from the open Outlook item
    Set objDoc = Application.ActiveInspector.WordEditor
    Set objSel = objDoc.Windows(1).Selection
    ' now do what you want with the Selection
    objSel.PasteSpecial Link:=False, DataType:=wdPasteText
    Set objDoc = Nothing
    Set objSel = Nothing
End Sub

To get it to work, I also needed to open the VBA editor in Outlook 2007, click Tools – References, and enable the reference for Microsoft Word 12.0 Object Library. I then linked the macro to a custom button in the QAT toolbars for creating new messages and replies and it worked just fine!

I may have mentioned this before, but WOO-HOO!!!!

Thank you, Sue!

Mac – How to execute an Excel 2010 addin

Your summary is the right way to do it (except the terminology in #4, the addin gets loaded with excel, not the spreadsheet. But if you see it in the list of installed addins just by starting excel, then its ok.) It must be that there is a bug in the macro and its not doing what you expect to see. To verify this, start VBA (load the Developer tab from the excel menu) by clicking on the Visual Basic icon in the developer's tab. This should show your addin under modules. This will list your macro code hopefully with its comment showing they keyboard shortcut. With the first line selected, hit f9 to hit a breakpoint. Then go back to excel and enter your short cut key. If all is installed ok, then the VB debugger should come up and stop at the breakpoint in your macro.

Best Answer

Related Solutions

Outlook – What would an Outlook 2007 macro to automate Paste Special – Unformatted Text look like

Mac – How to execute an Excel 2010 addin

Related Question