Is Access to the VB Project Allowed?
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 new security setting 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.
