Personal Macro Workbook

A macro trapped in one .xlsm file is a private note. A macro you can run in any workbook is a tool. Two vehicles carry your utilities out of a single file.

The Personal Macro Workbook (PERSONAL.XLSB) is your private toolbox. It's a hidden workbook that Excel opens automatically every session, because it's stored in the special XLSTART folder (%AppData%\Microsoft\Excel\XLSTART\). You create it the first time you record a macro and choose "Store macro in: Personal Macro Workbook" in the Record Macro dialog, and Excel builds PERSONAL.XLSB for you. From then on, any Sub you put in its modules is callable from every workbook on that machine. It's saved as .xlsb (binary) for speed. Use it for personal conveniences you don't need to share: a "format this range my way" macro, a "paste values" shortcut, your own navigation helpers.

The .xlam add-in is how you distribute a tool to other people. Take a finished .xlsm, delete everything but the code, and Save As ▸ Excel Add-in (.xlam). Once it's installed (File ▸ Options ▸ Add-ins ▸ Manage: Excel Add-ins ▸ Go ▸ Browse), its code loads every session without a prompt. The add-in workbook itself is hidden (ThisWorkbook.IsAddin = True), so the model-checker inside it has to talk to ActiveWorkbook. An .xlam is the right home for the model-checker. Teammates install it once and get a "Check this model" button everywhere.

How do you add a button for your macro in Excel?

Exposing the button. A macro no one can find is a macro no one runs. There are two ways to surface it:

• Quick Access Toolbar (QAT): the simple route, with no code. File ▸ Options ▸ Quick Access Toolbar ▸ choose "Macros" ▸ add your Sub ▸ pick an icon. One click and you're done, though it's set per machine.
• A custom ribbon tab: the professional route for a distributed add-in. It's defined in customUI XML embedded in the .xlam, which you edit with the free Office RibbonX Editor. The XML declares a button and names a callback sub, and VBA supplies the callback:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
  <ribbon>
    <tabs>
      <tab id="tabAnalyst" label="Analyst Tools">
        <group id="grpModel" label="Model QA">
          <button id="btnCheck" label="Check Model"
                  size="large" imageMso="CheckSpelling"
                  onAction="OnCheckModel"/>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

' The ribbon callback. Its signature is fixed: it MUST take one IRibbonControl.
Public Sub OnCheckModel(ByVal control As IRibbonControl)
    RunModelCheck ActiveWorkbook          ' run our checker on the user's front workbook
End Sub

Don't treat the callback signature (ByVal control As IRibbonControl) as optional. The ribbon calls it with that argument and no other. If the signature doesn't match, the button does nothing at all, without so much as an error message. That's a hard bug to chase, because there's nothing to see. Notice ActiveWorkbook again. The ribbon button belongs to the add-in, but it has to check the workbook the user is actually looking at.