VBA – How to programmatically enable access to the VBA object model using macros


    Under certain circumstances a programmer may need to access the VB object model for an Office application and interact with its VBIDE interface (clean-up missing references, back-up macro code ... etc).
    As described in http://support.microsoft.com/kb/282830, the code may fail with the following errors:

 

http://support.microsoft.com/kb/282830

Programmatic access to Office VBA project is denied
-------------------------------------------------------------------

From Visual Basic or VBA:

   Run-time error '6068': Programmatic Access to Visual Basic Project is not trusted

From Microsoft Visual C++, Microsoft Foundation Classes (MFC), or ActiveX Template Library (ATL):

   HRESULT = 0x800A17B4 (-2146822220) "Programmatic Access to Visual Basic Project is not trusted"

... if 'Trust access to the VBA project object model' setting is not turned ON.

    If we run a ProcMon while changing this option for any Office program, we will notice that the application will write an entry into this registry key: "HKEY_CURRENT_USER\Software\Microsoft\Office\<App.Version>\<App>\Security\AccessVBOM". Now that we know where it is stored, we can try to programmatically set its value using a VBA macro, but the setting will not be taken into account until the application restarts. And if you try to restart the application, you will notice that your modification will be lost!   

    It seems that the Office application will remember what was the value for this key when it first started. If while the application is running you attempt to modify the 'AccessVBOM' key by any method (macro, script, manually editing the key ...etc), except for going in 'Options' > 'Trust Center' > 'Trust Center Setting' > 'Macro Settings', your change will be discarded on exit. Since you need to exit the application and load it again for the setting to become effective, you cannot programmatically enable / disable access to VB object model.

    But if we set our key's value when the target Office application is not running, then the modified setting will be taken into account. 

   

    In case you have to run your macro in an environment where you don't know for sure if an user has enabled his 'Trust access to the VBA project object model' setting, you can execute the macro samples from below. What the code performs first is a check to make sure that the registry key exists by calling the 'TestIfKeyExists(strRegPath)' function. Next it performs these actions:
-  if the function returns FALSE, it will write a .VBS script file in the working folder;
    > then a message box is going to be displayed announcing that the application will be restarted for some setting to be effective;
    > control is handed over to the 'WriteVBS' method which will write a set of VBScript commands into a text file;
> when it finishes, it fires up the VB script from the VB macro via the SHELL object;
> finally, the Word application shuts down;
   

    > but the VB script continues to run, and it will wait for the user to click a message box (I used a message box to avoid complicating my code too much .. otherwise I would have needed some timers in order for the script to be delayed enough, because Word has to have a chance to close down properly);
    > the script takes care of writing this registry entry "[HKEY_LOCAL_MACHINE/Software/Microsoft/Office/<APP VER>/<App>/Security/AccessVBOM"=dword:00000001" (<APP VER> can be '12.0' for Office 2007 and '14.0' for Office 2010 and <App> can be 'Word' or 'Excel' ..etc);

 

- if the function evaluates to TRUE, it means that we have access to the VB object model and we cycle all references to prove that everything works (of course ... in the real life scenario, this is the place where you should execute your code);

 

The following code sample targets the Word 2007 application.

 

The script I have offered is just a proof of concept and should not be put into production without a thorough testing!

Microsoft is not responsible if your users will lose data because of this code. It’s your responsibility to test it before deployment in your organization.

 

 

' ==============================================================
' * Please note that Microsoft provides programming examples
' * for illustration only, without warranty either expressed or
' * implied, including, but not limited to, the implied warranties of
' * merchantability and/or fitness for a particular purpose. Any of
' * the code provided use by you 
in this blog is at your own risk.
'===============================================================

Sub CheckIfVBAAccessIsOn()

'[HKEY_LOCAL_MACHINE/Software/Microsoft/Office/10.0/Excel/Security]
'"AccessVBOM"=dword:00000001

Dim strRegPath As String
strRegPath = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Word\Security\AccessVBOM"

If TestIfKeyExists(strRegPath) = False Then
'   Dim WSHShell As Object
'   Set WSHShell = CreateObject("WScript.Shell")
'   WSHShell.RegWrite strRegPath, 3, "REG_DWORD"
   MsgBox "A change has been introduced into your registry configuration. The Word application will now restart."
   WriteVBS
   Application.Quit
End If

 

Dim VBAEditor As Object     'VBIDE.VBE
Dim VBProj    As Object     'VBIDE.VBProject
Dim tmpVBComp As Object     'VBIDE.VBComponent
Dim VBComp    As Object     'VBIDE.VBComponent

Set VBAEditor = Application.VBE
Set VBProj = Application.ActiveDocument.VBProject
Dim counter As Integer

For counter = 1 To VBProj.References.Count
Debug.Print VBProj.References(counter).FullPath
'Debug.Print VBProj.References(counter).Name
Debug.Print VBProj.References(counter).Description
Debug.Print "---------------------------------------------------"
Next

End Sub

 

Function TestIfKeyExists(ByVal path As String)
 Dim WshShell As Object
 Set WshShell = CreateObject("WScript.Shell")
 On Error Resume Next
 WshShell.RegRead path

    If Err.Number <> 0 Then
       Err.Clear
       TestIfKeyExists = False
    Else
       TestIfKeyExists = True
    End If
 On Error GoTo 0
End Function

 

Sub WriteVBS()
Dim objFile     As Object
Dim objFSO      As Object
Dim codePath    As String

codePath = ActiveDocument.path & "\reg_setting.vbs"

Set objFSO  = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(codePath, 2, True)

objFile.WriteLine (" On Error Resume Next")
objFile.WriteLine ("")
objFile.WriteLine ("Dim WshShell")
objFile.WriteLine ("Set WshShell = CreateObject(""WScript.Shell"")")
objFile.WriteLine ("")
objFile.WriteLine ("MsgBox ""Click OK to complete the setup process.""")
objFile.WriteLine ("")
objFile.WriteLine ("Dim strRegPath")
objFile.WriteLine ("Dim Application_Version")
objFile.WriteLine ("Application_Version = """ & Application.Version & """")
objFile.WriteLine ("strRegPath = ""HKEY_CURRENT_USER\Software\Microsoft\Office\"" & Application_Version & ""\Word\Security\AccessVBOM""")
objFile.WriteLine ("WScript.echo strRegPath")
objFile.WriteLine ("WshShell.RegWrite strRegPath, 1, ""REG_DWORD""")
objFile.WriteLine ("")
objFile.WriteLine ("If Err.Code <> o Then")
objFile.WriteLine ("   MsgBox ""Error"" & Chr(13) & Chr(10) & Err.Source & Chr(13) & Chr(10) & Err.Message")
objFile.WriteLine ("End If")
objFile.WriteLine ("")
objFile.WriteLine ("WScript.Quit")

objFile.Close
Set objFile = Nothing
Set objFSO  = Nothing

'run the VBscript code
' > The macro will fail to execute the VB script if you use a
'   [codepath] which contains blanks!
'
' > To fix this issue, we add a pair of double quotes (" ") around
'   [codepath];

Shell "cscript " & chr(34) & codePath & chr(34), vbNormalFocus

End Sub

 

Here is how the VB script will look like once it is written to a TXT file:

VB Script listing                                                    -------------------------------------------------------------------

On Error Resume Next

Dim WshShell
Set WshShell = CreateObject("WScript.Shell")

MsgBox "Click OK to complete the setup process."

Dim strRegPath
Dim Application_Version
Application_Version = "12.0"
strRegPath = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application_Version & "\Word\Security\AccessVBOM"
WScript.echo strRegPath
WshShell.RegWrite strRegPath, 1, "REG_DWORD"

If Err.Code <> 0 Then
   MsgBox "Error" & Chr(13) & Chr(10) & Err.Source & Chr(13) & Chr(10) & Err.Message
End If

WScript.Quit

 

Thank you for reading my article! Bye 🙂

 

Comments (22)

  1. Tones says:

    Hi cristib,

    I cant figure out why the sentence:

    Shell "cscript " & codePath, vbNormalFocus

    wont work.

    However, if I double click on the reg_setting.vbs or run it throught the cmd the code will do it work.

    Any suggestion?

  2. Hello Tones,

      I am very sorry for the late reply. Could you try to open a separate CMD propmpt window from VB ?

      Shell "c:WINDOWSSystem32cmd /k cscript " & codePath, vbNormalFocus

      … it should keep the CMD window open and you will be able to see the error.

      This troubleshooting action assumes the error is somewhere in the SHELL code, but if it is in the VBA code, we have to get the exact error message to be able to suggest any other solution.

    Have a great day,

    Cristian

  3. Macarius says:

    All this code could've been reduced to 10 lines. Sheesh–this is why MS SW is so f-ing bloated…

  4. Hi Macarius,

       Yes, the code could have been more compact, but this blog is intended to help programmers understand different Office issues .. After the basic concepts are understood, each programmer may optimize it and remove the lines of code which are not needed.

    Thank you for your feedback 🙂

  5. FZ says:

    whenever I open an excel app with VBA code running in the background, I get Run-time error -2147024894(80070002)

    Invalid root in registry key

    HKEY_LOCAL_MACHINESoftwareMicrosoftOffice12.0ExcelSecurityAccessVBOM

    I can open the same file in other machine and opens fine.

    Here is the debug window shows the code…

    I might have changed some setting in the Trust-setting.

    any suggestion.

    If allowAccess Then

           On Error Resume Next

           regOptExists = scriptHost.RegRead(HKLM_VBOM)

           On Error GoTo 0

           If Not IsEmpty(regOptExists) Then scriptHost.RegDelete HKLM_VBOM

           scriptHost.RegWrite HKCU_VBOM, 1, "REG_DWORD"

  6. @FZ … Can you check if you are running the script in a x64bit machine? If YES, then the correct registry path is: "HKEY_LOCAL_MACHINESoftwareWow6432NodeMicrosoftOffice12.0ExcelSecurityAccessVBOM"

  7. VBA Samurai says:

    Hi Cristian,

    This is exactly what I needed. Unfortunately, since I don't know VB Script (only VBA) I will have to learn it. But I'm just happy that changing these settings is possible.

    Also, that Macarius person seem very angry. It's too bad that internetizens are too busy criticizing others' hard work rather than trying to learn and improve their own skills and / or life.

    Appreciate this blog.

    I learned a lot from it.

  8. Mike says:

    Hi Cristian,

    With Excel 14, it seems that the registry key AccessVBOM always exists (0 if VBE access is not allowed and 1 if it is allowed).

    Given this, checking the existence of the key will always return true, and the code line:

    Set VBAEditor = Application.VBE

    will naturally fail if the value of AccessVBOM is 0

    I will appreciate it very much if you teach me how to check the value of the key rather than so that I can modify the code accordingly.

  9. Mike says:

    Hi Cristian,

    2nd posting:

    I got the code to read the registry key value. (Code below) and I changed the app. name to Excel instead of Word in your code.

    'reads the value for the registry key i_RegKey

    'if the key cannot be found, the return value is ""

    Function RegKeyRead(i_RegKey As String) As String

    Dim myWS As Object

     On Error Resume Next

     'access Windows scripting

     Set myWS = CreateObject("WScript.Shell")

     'read key from registry

     RegKeyRead = myWS.RegRead(i_RegKey)

    End Function

    The problem now is that when the WriteVBS exist, the script window also closes before the application quits. This brings us back to where we started: Upon exit the key  value (which is now  1 as shown in regedit) is overwritten to its original which is 0.

    I don't know why script window closed early.

    Any suggestions are highly appreciated.

  10. Hello Mike,

       After executing 'WriteVBS' and executing its code, a MessageBox should be displayed by the VBScript which would freeze its code until the Office application shuts down. In this way the 'AccessVBOM' value will not be overwritten .. there are other ways in which we could delay the code execution until Office shuts down, but this is the simplest method.

       Are you sure you are not closing this message before your Office application shuts down?

       P.S. Sorry for the delay in reply, you can send me an email at: cristib@mi…ft.com for any other comments and I will try to help.

    Best wishes,

    Cristian

  11. Miroslav Danazhiev says:

    Hi guys,

    I would like to ask you, I am trying to set additionally to "HKEY_LOCAL_MACHINESoftwareWow6432NodeMicrosoftOffice12.0ExcelSecurityAccessVBOM", "VBAWarnings"=dword:00000001. But for some reason the AccessVBOM works just fine, but the VBAWarnings doesnt. I need to set it for multiple PCs to has enabled the macros. Please advise if you can.

    KR,

    Miro

  12. Pascal says:

    Hi,

    Same problem as Mike : the script window closes before excel quits (and no msg box is oppened).

    But when I lauches it mannually, the script works fine (with the msgbox waiting for a clic).

    Any help would be greatly welcome

    Regards,

    Pascal

  13. Pascal says:

    Found my error : I had spaces used in codePath

    With:

       Shell "cscript """ & codePath & "", vbNormalFocus

    it worked fine

    Many thanks

  14. When I designed my demo program to write to a helper VBS script file, I thought it would be a good idea to host it in the same location where the macro-enabled Office file is opened from (this is because the alternative was to use a hard-coded path such as C:Temp, but that is risky since not everyone might be allowed to write there .. on the other hand, the location from where a macro-enabled file is opened, should allow users read/write rights).

    After the VBS file is written, it is executed using a programmatic equivalent of the DOS command-line.

    However, if you open the macro-enabled Office file from a location containing spaces in its path (example: [C:Program Files], [C:Documents and Settings]), things will go wrong, since the command-line interpreter will consider that the words following a space character are command-line arguments.

    So, instead of executing the VBS script stored here: C:Documents and Settings<username>Desktopreg_setting.vbs, the command-line engine will try to run: C:Documents with these two additional parameters: [and]  [Settings<username>Desktopreg_setting.vbs] … which is obviously going to fail.

    The solution is to make sure all paths containing spaces are surrounded by double-quotes: “C:Documents and SettingsTreyDesktopreg_setting.vbs”.

    To fix this issue, you should modify the routine WriteVBS as shown here ( chr(34) will add a quote in the file path ):


    ….

                  Shell "cscript " & chr(34) & codePath & chr(34), vbNormalFocus


  15. Gary M says:

    Any way to hide or minimize the  black CMD prompt window?

  16. Martin G says:

    I chose another solution.

    I check the registry key value (also the Wow6432Node if needed), and if the VBA object model isn't trusted, I show a message, telling the user that this is required, and how to enable it.

    And then I exit.

    My point is: Make it up to the user to handle the setting, don't try to force/sneak it through behind thier back.

    (Remember that this option potentially opens the computer up for some types of attack – there is a reason this option exists, and is disabled by default.)

  17. Killerjoe_UK says:

    A better way still would be to programmatically enable access to the VBA Object Model to run your code then reset it on completion to ensure security is maintained.

  18. Ettanin says:

    I just want to tell you that this code violates the Programmer’s Prime Directive: Never to interfere with the settings of the user and to respect their settings unless they consent to change these manually.

    In light of Locky and other Encryption Trojans, such snippets of code are a dangerous weapon, especially against those that need to use macros to some degree.

  19. Indu says:

    Hi Cristib,

    I am facing the same problem, i.e to enable access to the VBA object model using macros.
    Here you have illustrated it for Word application. I need it for MATLAB.
    The problem statement is ” To invoke VBA code from MATLAB”.

    Could you please provide some suggestion

    1. Hi Indu,

      I am not sure how Matlab works … did you try contacting their support?

      Thx,
      Cristian

  20. Lowell says:

    Really appreciate you sharing this article.Thanks Again. Really Great.

Skip to main content