Share via

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

  • Article

    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 https://support.microsoft.com/kb/282830, the code may fail with the following errors:

 

https://support.microsoft.com/kb/282830Programmatic 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 keywhen 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:00000001Dim strRegPath As StringstrRegPath = "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.QuitEnd If Dim VBAEditor As Object     'VBIDE.VBEDim VBProj    As Object     'VBIDE.VBProjectDim tmpVBComp As Object     'VBIDE.VBComponentDim VBComp    As Object     'VBIDE.VBComponentSet VBAEditor = Application.VBESet VBProj = Application.ActiveDocument.VBProjectDim counter As IntegerFor counter = 1 To VBProj.References.CountDebug.Print VBProj.References(counter).FullPath'Debug.Print VBProj.References(counter).NameDebug.Print VBProj.References(counter).DescriptionDebug.Print "---------------------------------------------------"NextEnd 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 0End Function Sub WriteVBS()Dim objFile     As ObjectDim objFSO      As ObjectDim codePath    As StringcodePath = 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.CloseSet objFile = NothingSet 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), vbNormalFocusEnd Sub

 

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

VB Script listing                                                    -------------------------------------------------------------------On Error Resume NextDim WshShellSet WshShell = CreateObject("WScript.Shell")MsgBox "Click OK to complete the setup process."Dim strRegPathDim Application_VersionApplication_Version = "12.0"strRegPath = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application_Version & "\Word\Security\AccessVBOM"WScript.echo strRegPathWshShell.RegWrite strRegPath, 1, "REG_DWORD"If Err.Code <> 0 Then   MsgBox "Error" & Chr(13) & Chr(10) & Err.Source & Chr(13) & Chr(10) & Err.MessageEnd IfWScript.Quit

 

Thank you for reading my article! Bye :-)

Comments

  • Anonymous
    March 04, 2012
    The comment has been removed

  • Anonymous
    March 30, 2012
    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

  • Anonymous
    April 17, 2012
    All this code could've been reduced to 10 lines. Sheesh--this is why MS SW is so f-ing bloated...

  • Anonymous
    April 19, 2012
    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 :)

  • Anonymous
    November 14, 2012
    The comment has been removed

  • Anonymous
    November 20, 2012
    @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"

  • Anonymous
    May 09, 2013
    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.

  • Anonymous
    February 22, 2014
    The comment has been removed

  • Anonymous
    February 22, 2014
    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 StringDim 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 FunctionThe 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.

  • Anonymous
    March 08, 2014
    The comment has been removed

  • Anonymous
    April 22, 2014
    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

  • Anonymous
    June 19, 2014
    The comment has been removed

  • Anonymous
    June 19, 2014
    Found my error : I had spaces used in codePathWith:   Shell "cscript """ & codePath & "", vbNormalFocusit worked fineMany thanks

  • Anonymous
    February 01, 2015
    The comment has been removed

  • Anonymous
    February 05, 2015
    Any way to hide or minimize the  black CMD prompt window?

  • Anonymous
    May 04, 2015
    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.)

  • Anonymous
    June 30, 2015
    Nice Material !

  • Anonymous
    January 15, 2016
    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.

  • Anonymous
    May 03, 2016
    The comment has been removed

  • Anonymous
    May 25, 2016
    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

    • Anonymous
      October 24, 2016
      Hi Indu, I am not sure how Matlab works ... did you try contacting their support?Thx,Cristian

  • Anonymous
    October 23, 2016
    Really appreciate you sharing this article.Thanks Again. Really Great.