How to check from .net code whether "Trust access to the VBA project object model" is enabled or not for an Excel application?

Question

How to check from .net code whether "Trust access to the VBA project object model" is enabled or not for an Excel application?

Manually I can check it from Excel application- File>Options>Trust Center>Trust Center Settings>Macro Settings>Trust access to the VBA project object model

enter image description here

score 21 · Accepted Answer · answered Mar 14 '11 at 16:38

The short answer is that you cannot directly access this setting using the Excel object model (i.e. through PIAs). However, instead, you can check this setting from the registry in the following location (here I assume that you're using Office 2007 - version 12.0):

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security\AccessVBOM

this is a DWORD that will be 0 or 1 depending on whether the "Trust access to VBA Object Model" is enabled.

However, this setting can be overriden by another registry key located at:

HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Excel\Security\AccessVBOM

this is again a DWORD, however, if this value is 0 this means that no matter what the HKCU value is set to, then access to the VBOM will be denied. If the value in HKLM is 1 or missing, then the HKCU key will control the access to the VBOM.

Therefore, all you need to do is to check these two keys via the Registry methods in .NET.

score 4 · Answer 2 · answered Nov 24 '17 at 12:08

4

This worked for me

Function VBATrusted() As Boolean
    On Error Resume Next
    VBATrusted = (Application.VBE.VBProjects.Count) > 0
End Function

Private Sub Workbook_Open()
    If Not VBATrusted() Then
    MsgBox "No Access to VB Project" & vbLf & _
      "Please allow access in Trusted Sources" & vbLf & _
      "File > Options > Trust Center > Trust Center Settings > Macro Settings > Trust Access..."
    End If
End Sub

Source https://www.mrexcel.com/forum/excel-questions/659774-checking-if-trust-access-visual-basic-project-ticked.html

answered Nov 24 '17 at 12:08

Brad

41
2

1

Nice solution, but instead of `VBATrusted = (Application.VBE.VBProjects.Count) > 0` i would suggest using something else like `VBATrusted = (Application.VBE.CommandBars.Count) > 0` because the former check can return false results if the current XL app has no macros inside. The latter check just counts default CommandBars which returned 30 for my Excel Version 16.0 (2016). – SourceSeeker Feb 12 '18 at 12:06

score 0 · Answer 3 · answered Sep 09 '14 at 17:32

0

Search the registry for all instances of "AccessVBOM" and change the Dword settings to a 1.

That should turn it on.

answered Sep 09 '14 at 17:32

Zorro

1

4

One-line answers should be posted as comments. In addition, was does your "answer" add compared to the answer by @i_saw_drones? – honk Sep 09 '14 at 17:38

score 0 · Answer 4 · answered Feb 07 '19 at 23:25

It has been my experience that Application.VBE will (depending on OFFICE version) either be *null* or throw a COMException whenever the VBA Project Object Model is not trusted.

If one is using Office.Interop in an Add-In then Globals.ThisAddIn.Application.VBE will perform the necessary test.

I have used this proxy for years successfully.

How to check from .net code whether "Trust access to the VBA project object model" is enabled or not for an Excel application?

4 Answers4

Linked