Determining If Access To The VB Project Is Allowed

Category: General VBA | [Item URL]

As you may know, you can write VBA code that manipulates Visual Basic project components. For example, your code can insert and delete modules, or even create a UserForm on the fly. If your Excel application modifies the Visual Basic project, you need to be aware of a security setting introduced in Excel 2002: Trust access to Visual Basic Project.

This setting is changed in Excel's Security dialog box, accessible via the Tools - Macro - Security command.

By default, this setting is turned off. And, of course, it cannot be changed via code. This means that VBA code that attempts to access the VB project will fail, and the user will be presented with an unfriendly message from Excel.

Unfortunately, there is no direct way to programmatically determine the value of this setting. The only way to do it is to trap an error and then take appropriate action. The VBA code listed below does just that.

' Make sure access to the VBProject is allowed
Dim VBP As Object ' as VBProject
If Val(Application.Version) >= 10 Then
    On Error Resume Next
    Set VBP = ActiveWorkbook.VBProject
    If Err.Number <> 0 Then
        MsgBox "Your security settings do not allow this procedure to run." _
          & vbCrLf & vbCrLf & "To change your security setting:" _
          & vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." & vbCrLf _
          & " 2. Click the 'Trusted Sources' tab" & vbCrLf _
          & " 3. Place a checkmark next to 'Trust access to Visual Basic Project.'", _
          vbCritical
        Exit Sub
    End If
End If

This code first checks the Excel version number. If Excel 2002 or later is in use, it attempts to create an object variable (VBP). If an error occurs, then the user is presented with instructions on how to change the setting (see below) and the procedure ends.