Distributing macros: add-ins, signatures and security

Ship macros safely with Trust Center settings, digital signatures and add-ins, and plan the migration path to Office Scripts.

Trust Center and file formats

• A file from the internet is marked with the Zone.Identifier alternate data stream and opens in Protected View. The user must unblock it, or signatures and trusted locations must cover it.
• Trusted Locations bypass the macro prompt entirely for everything in the folder. A user-writable folder is a hole, so never add a Downloads or Documents path.
• Disable all macros with notification is the practical default for a managed environment; a signed macro from a trusted publisher then runs with one click.
• Group Policy can enforce the Trust Center settings, which is the only approach that holds across a large estate.

' Inspect the security posture from the workbook itself.
Sub ReportSecurity()
    Debug.Print "VBA project protected: " & ThisWorkbook.VBProject.Protection
    Debug.Print "Automation security: " & Application.AutomationSecurity
    Debug.Print "User name: " & Application.UserName
    Debug.Print "Path: " & ThisWorkbook.Path
    Debug.Print "Has VBProject access: " & HasVbProjectAccess()
End Sub

Function HasVbProjectAccess() As Boolean
    ' Requires "Trust access to the VBA project object model" in the Trust Center.
    ' Without it, reading ThisWorkbook.VBProject raises error 1004.
    On Error Resume Next
    Dim n As Long
    n = ThisWorkbook.VBProject.VBComponents.Count
    HasVbProjectAccess = (Err.Number = 0)
    On Error GoTo 0
End Function

Signing, add-ins and distribution

' An add-in loads automatically for the user, with no workbook to open.
' Build it like this:
'   1. Develop the macros in a normal .xlsm.
'   2. Save as .xlam in the AddIns folder.
'      Application.UserLibraryPath  ->  %APPDATA%\Microsoft\AddIns
'   3. File, Options, Add-ins, Excel Add-ins, Go, Browse, select it.
'   4. Copy the .xlam to %APPDATA%\Microsoft\AddIns for each user, or
'      deploy it through Group Policy or an installer.

Sub WhereDoAddInsLive()
    Debug.Print Application.UserLibraryPath
    Dim addin As Object
    For Each addin In Application.AddIns
        Debug.Print addin.Name, addin.Installed, addin.FullName
    Next addin
End Sub

' A ribbon tab is declared in a customUI XML part. The callback names in
' that XML must be Public Subs in a standard module, with this signature:
Public Sub OnButtonAction(ByVal control As IRibbonControl)
    MsgBox "clicked " & control.Id
End Sub

' A macro the user should be able to run needs a shortcut. Do not use
' Workbook_Open to assign it silently; ask first, or document it.
Sub AssignShortcut()
    Application.OnKey "^+r", "Rebuild"          ' Ctrl+Shift+R
End Sub

Sub ReleaseShortcut()
    Application.OnKey "^+r"                     ' remove it on unload
End Sub

' Versioning: keep the code in exported .bas files under source control.
' VBA has no merge, so the exported text files are the only way to review
' a change, and the only way to roll back.
Sub ExportModules()
    Dim folder As String
    folder = ThisWorkbook.Path & "\src\"
    If Dir(folder, vbDirectory) = "" Then MkDir folder

    Dim comp As Object
    For Each comp In ThisWorkbook.VBProject.VBComponents
        Select Case comp.Type
            Case 1: comp.Export folder & comp.Name & ".bas"     ' standard module
            Case 2: comp.Export folder & comp.Name & ".cls"     ' class module
            Case 3: comp.Export folder & comp.Name & ".frm"     ' UserForm
        End Select
    Next comp
End Sub

• A digital signature proves the code came from you and has not changed since signing. Any edit invalidates it, so sign as the last step of a release.
• A self-signed certificate works on the machines that trust that certificate: install it into Trusted Root and Trusted Publishers on each of them, which is a deployment task, not a code task.
• An add-in in %APPDATA%\Microsoft\AddIns loads per user. A per-machine deployment belongs in a folder the installer controls, referenced by a Group Policy trusted location.
• Personal.xlsb in %APPDATA%\Microsoft\Excel\XLSTART holds a user's own macros. It is not a distribution mechanism: it does not exist for anyone else.

When to migrate off VBA

A hybrid is often the right answer: keep the interactive logic in VBA, and move the scheduled data pull to Power Automate or Office Scripts, which do not need a machine with Excel open.

FAQ

Related

Error handling and the classic pitfalls Automating Word, Outlook and other Office apps

Last refreshed 2026-09-18.