Creating Global Macros in Excel 2007 and 2010

Ever since I wrote the A Quick Method for Adding Custom Ribbon Elements post and then expanded on it with the Creating a Custom Styles Group post for my VBA for Dummies readers, I’ve received a number of e-mails about global macros. I should explain that you can add Ribbon elements to a particular file or to Excel as a whole. When you add them to Excel as a whole, you need to create a global macro, rather than one that sits in a particular workbook or the button will be useless most of the time.

Before you can do anything, you need to create a global macro. The following steps describe how to perform this task.

  1. Choose Record Macro on the Developer tab or click the Record Macro button in the lower left corner of the Excel window. You’ll see the Record Macro dialog box shown here.
    GlobalMacro01
  2. Type a name for the macro in the Macro Name field (the example uses GlobalSayHello).
  3. (Optional)Type a value in the Shortcut Key field when you plan to use this macro regularly.
  4. Choose Personal Macro Workbook in the Store Macro In field. This is an essential requirement for storing the macro in the right place.
  5. Type a description for your macro. The example uses, “This is test macro; delete it later.”
  6. Click OK. Excel creates the macro for you.
  7. Choose Stop Recording on the Developer tab or click Stop in the lower left corner of the Excel window.
  8. Choose Visual Basic on the Developer tab or press Alt+F11 to open the Visual Basic editor. Notice that there is a new PERSONAL.XLSB entry in the Project window. When you drill down into this project, you see that there is a Module1 entry as shown here.
    GlobalMacro02
  9. Double click Module1. You’ll see an editor window appear with code that looks like this:
    GlobalMacro03
  10. Type your code as normal. The example uses the simple message box statement: MsgBox “Hello from GlobalSayHello”.

When you save this macro, Vista and Windows 7 users will find a new file in the C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART folder of your system, where UserName is the name of the user. (Windows XP users will find a similar \Documents and Settings folder entry.) Whenever Excel loads, it also loads this macro, so the macro is always accessible. You create an entry on the Ribbon for it as you would any other macro. The difference is that this macro is named PERSONAL.XLSB!GlobalSayHello, where the part before the exclamation mark (!) is the name of the file and the part after is the name of the macro. It’s possible to customize the entry as you would any other Ribbon entry as shown here for this example.

GlobalMacro04

Global macros are essential when you need to have a macro available at all times. This technique is also essential for certain types of control additions that have required callbacks, such as toggle buttons (a puzzle I’m still working to solve, but more on that later). Let me know if you have any questions about this technique at John@JohnMuellerBooks.com.