Excel Macro: Enable Trust Access to the VBA Project Object Model

Enabling trust access to the VBA project object model allows you to write automated Excel programs and manipulate the VBA environment and object model.

Method 1: SendKeys

This method uses SendKeys to try this trick. However, SendKeys is unstable, so don't be surprised if it doesn't work 100% of the time.

Enable Trust Access to the VBA Project Object Model

'------------------ Modules ------------------
Sub EnableTrustAccess()
    Dim intCount As Integer
    On Error GoTo Error
    intCount = ActiveWorkbook.VBProject.VBComponents.count
    Exit Sub
Error:
    Application.SendKeys ("%tms%v~")
End Sub

How This Macro Works

We ues ActiveWorkbook.VBProject.VBComponents.count to attempt to access VBA Components, If an error occurs: Run-time error'1004: Programmatic access to Visual Basic Project is not trusted, it means that Trust Access to the VBA project object model is disabled. Next, we use SendKeys method to enable it. The parameter of %tms%v~ means:

• %tms open Trust Center Macro settings.
• %v select the Trust Access to the VBA Project Object Model check box.
• ~ apply the setting.

Please don’t run this macro directly from your VBA editor. Run this Macro on Developer tab -> Code group -> Macros.

Method 2: modify the registry

Trust Access to the VBA Project Object Model setting is stored in: AccessVBOM under HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Security, the version number 16.0 here is Office 2016 and later, and different Office versions have different values. The AccessVBOM key value is 1 to enable and 0 to disable.

Enable

Sub EnableAccessVBOM()
    Dim oWshell
    Set oWshell = CreateObject("WScript.Shell")
    oWshell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\Security\AccessVBOM", 1, "REG_DWORD"
    Set oWshell = Nothing
End Sub

Disable

Sub DisableAccessVBOM()
    Dim oWshell
    Set oWshell = CreateObject("WScript.Shell")
    oWshell.RegWrite "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\Security\AccessVBOM", 0, "REG_DWORD"
    Set oWshell = Nothing
End Sub

How to Use This Macro