Introduction - VBA Project Object Model

In this tutorial, I show you one way to programmatically enable trust access to the VBA project object model using a macro. Enabling trust access to the VBA project object model allows you to write macros that can add new macros to a workbook. You’ll be able to add modules, write new macros and run these macros from your own macro.

That’s right. You can make a macro that writes more macros. I’ll show you how to do this in a few days!

The method I’m showing you today is just one of a couple ways to use a macro to enable trust access to the VBA project object model. This method uses SendKeys to attempt the trick, but another common method is to tweak the registry itself to toggle access to the object model. As I’ve said before, SendKeys is flaky so don’t be surprised if it doesn’t work 100% of the time.


A Question of Ethics

This macro is a lot longer than it needs to be. The reason for this is because I feel it crosses the ethical boundary to automatically enable/disable access to the VBA Project Object Model for a user without his/her consent.

I’m going to be perfectly honest with you. Unscrupulous people can abuse the code I’m about to present to gain access to the object model and write macros to plant viruses, worms and other malware on someone’s computer. Don’t be one of those people.

Because I’m not a jerk, I present numerous message boxes throughout the macro to let the user know exactly what I’m doing. You can modify the code to eliminate the message boxes, but you’ll have to do this of your own accord - I don’t sanction it.

Another reason this macro is longer than it has to be is because I don’t just check to see if the user wants me to enable access to the object model. I also check if he/she wants me to revoke access. Again, you can change this to forcefully enable access, but for ethical reasons you’ll have to work at it. Sorry!


Example - VBA Project Object Model

Macro to Toggle Trust Access to the VBA Project Object Model

#If VBA7 Then
    Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Sub CheckTrustAccess()
Dim strStatus, strOpp, strCheck As String
Dim bEnabled As Boolean
If Not VBAIsTrusted Then
'ask the user if they want me to try to programatically toggle trust access. If I fail, give them directions.
    bEnabled = False
    strStatus = "DISABLE"
    strOpp = "ENABLE"
    v = MsgBox("Trust Access to the VBA Project Object Model is " & strStatus & "D." & Chr(10) & Chr(10) & _
     "Would you like me to attempt to " & strOpp & " it?", vbYesNo, strOpp & " Trust Access?")
Else
    bEnabled = True
    strStatus = "ENABLE"
    strOpp = "DISABLE"
    v = MsgBox("Trust Access to the VBA Project Object Model is " & strStatus & "D." & Chr(10) & Chr(10) & _
     "Would you like me to attempt to " & strOpp & " it?", vbYesNo, strOpp & " Trust Access?")
End If

    If v = 6 Then
        'try to toggle trust
        Call ToggleTrust(bEnabled)
        If VBAIsTrusted = bEnabled Then
            'if ToggleTrust fails to programatically toggle trust
            MsgBox "I failed to " & strOpp & " Trust Access." & Chr(10) & Chr(10) & _
            "To " & strOpp & " this setting yourself:" & Chr(10) & Chr(10) & _
            Space(5) & "1) Click " & Chr(145) & "File-> Options-> Trust Center-> Trust Center Settings" & Chr(146) & Chr(10) & _
            Space(5) & "2) Click Macro Settings" & Chr(10) & _
            Space(5) & "3) Toggle the box next to ""Trust Access to the VBA project object model""", vbOKOnly, "Auto " & strOpp & " Failed"
            End
        Else
            MsgBox "I successfully " & strOpp & "D Trust Access." & Chr(10) & Chr(10) & _
            "To " & strStatus & " this setting yourself:" & Chr(10) & Chr(10) & _
            Space(5) & "1) Click " & Chr(145) & "File-> Options-> Trust Center-> Trust Center Settings" & Chr(146) & Chr(10) & _
            Space(5) & "2) Click Macro Settings" & Chr(10) & _
            Space(5) & "3) Toggle the box next to ""Trust Access to the VBA project object model""", vbOKOnly, "Auto " & strOpp & " Failed"
        End If
    Else
        MsgBox "To manually " & strOpp & " Trust Access:" & Chr(10) & Chr(10) & _
            Space(5) & "1) Click " & Chr(145) & "File-> Options-> Trust Center-> Trust Center Settings" & Chr(146) & Chr(10) & _
            Space(5) & "2) Click Macro Settings" & Chr(10) & _
            Space(5) & "3) Toggle the box next to ""Trust Access to the VBA project object model""", vbOKOnly, "How to " & strOpp & " Trust Access"
        End
    End If

If VBAIsTrusted Then
    'if you want to write your own macro, do it here. You only get here if access is trusted
End If

End Sub

Private Function VBAIsTrusted() As Boolean
Dim a1 As Integer
On Error GoTo Label1
a1 = ActiveWorkbook.VBProject.VBComponents.Count
VBAIsTrusted = True
Exit Function
Label1:
VBAIsTrusted = False
End Function

Private Sub ToggleTrust(bEnabled As Boolean)
Dim b1 As Integer, i As Integer
Dim strkeys As String
On Error Resume Next
    Do While i <= 2 'try to sendkeys 3 times
        Sleep 100
    strkeys = "%tms%v{ENTER}"
        Call SendKeys(Trim(strkeys)) 'ST%V{ENTER}")
        DoEvents
        If VBAIsTrusted <> bEnabled Then Exit Do 'successfully toggled trust
        Sleep (100)
        i = i + 1
    Loop
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

How to use this Macro

This tutorial is part of my Code Library series, so I’m not going to provide a detailed explanation of how it works, but I will tell you how to use the macro.

The macro you want to run is titled CheckTrustAccess. However, because this macro utilizes the SendKeys function, don’t run it directly from your VBA editor. All it will do is print characters to your screen, like this:

SendKeys from VBA Editor

You don’t want to do that… Instead, you’ll need to switch over to Excel and view the Macro window (keyboard shortcut Alt-F8), select CheckTrustAccess, then click Run.

Alt-F8 Macro Window


How to Manually Enable/Disable Trust Access to the VBA Project Object Model

If you want to manually toggle access to the VBA Project Object Model, which is what the example macro does automatically, follow these steps:

  1. Open your Excel application
  2. Click File -> Options -> Trust Center -> Trust Center Settings
  3. Click Macro Settings
  4. Toggle the box next to “Trust Access to the VBA project object model”

When in doubt, leave it unchecked. Unchecked is the default option and it’s the safest from a security and vulnerability standpoint. Here’s a screenshot to help point you in the right direction:

Enable Trust Access to the VBA Project Object Model

That’s all for this tutorial. Share this article with the world on Twitter and Facebook!

When you’re ready to take your VBA to the next level, subscribe using the form below.