Excel VBA Trust Center Settings RRS feed

  • Question

  • I am an experienced Excel VBA programmer. I work for a large bank which is presently using Excel 2007, the Windows 7 operating system, Internet Explorer 9 and Outlook 2007.  My Excel VBA program is designed to interface with a Java web form.  A subroutine populates the web form automatically.  Once the web form is complete, an email is sent in Outlook 12 containing a large portion of data inputted into the Java web form.  The Excel VBA subroutines generally work without a hitch, as long as the user is properly trained and macros have been enabled in the Trust Center Settings.

    I have a client who is experiencing the following error: "Run Time Error 1004: Programmatic Access to VBA Project is Not Trusted". The issue is, macros have been enabled on their machine for the Excel/VBA workbook. The VBA subroutines operate flawlessly until, at some point, this error occurs. In my 20 years of Excel VBA programming, I have never experienced this particular error during processing; it has always occurred as the result of the user not invoking macros at the beginning. 

    Any advice you can offer for this issue is greatly appreciated.


    Thursday, February 12, 2015 12:53 PM

All replies

  • In most situations you can run a little routine to check if access to the VBA Project is allowed, if not advise the user what to do and abort 

    Sub abc()
    If ProjectIsNA Then
        Exit Sub
    End If
    ' do stuff
    End Sub
    Function ProjectIsNA() As Boolean
        On Error Resume Next
        If Len(ThisWorkbook.VBProject.Name) Then
        End If
        If Err.Number Then
            MsgBox "This app needs access to VBA Project, please tick -" & vbCr & _
                   "Options, Trust Center, Trust Center Settings, Macro settings, Trust Access to VBA Project"
        End If
    End Function

    However in some cases the the setting may be subject to group policy settings,

    Plan security settings for VBA macros for Office 2013

    Thursday, February 12, 2015 3:03 PM
  • Thanks, Peter.  This will alert my users that they have been taken out of enable mode for macros.  However, I still am curious as to what the actual root cause of the issue is.  I have simply never experienced anything like this before!  Thanks Again.  Willburr9
    Thursday, February 12, 2015 5:24 PM
  • BTW, it works great!  You're the best, Peter..
    Thursday, February 12, 2015 5:29 PM
  • It's not an issue but by design, even if inconvenient. By default on installation access is disabled and for the user or admin to decide if it should be allowed. Free access to be able to write malicious code in user's workbooks is in one sense an obvious security hole. That said I have never heard of it being exploited or any harm done going back well into last century, at least not deliberately! 

    Thursday, February 12, 2015 9:09 PM
  • @Willburr9

    Using VBA it is possible to write code that generate code. For example: you can create modules, edit and delete codes and classes, etc. You can do that only if you check the setting Peter said.

    I believe your code doesn't have any kind of code with that behavior, as you said most users don't have problem with it.

    I would guess that the heavy interaction in Internet Explorer's object model with Java and so on would trigger this case, but I wonder exactly how.

    Felipe Costa Gualberto -

    Friday, February 13, 2015 6:03 PM
  • Thanks, Felipe.  FYI, I have invoked the JavaWebStart 1.0 Type library to ensure that any keywords I use in VBA are recognized.  I have also given a trusted colleague who is experiencing the error a copy of the Excel/VBA application which allows access to the VBA Editor.  When the error occurs, he will be able to click 'debug' and find the exact location of the offending VBA code.  I will post any follow-up on this Forum regarding the specific VBA code which causes the error.
    Tuesday, February 17, 2015 8:50 PM
  • I agree, Peter! However, I guess it's better to be safe than sorry. 
    Tuesday, February 17, 2015 9:13 PM
  • The error appears to be fixed. I added 'On error resume next' in the following snippet, which is designed to invoke the user's version of the Outlook DLL (late binding):

    Sub TestOutlookReference()
        Dim obj As Object
        Dim sFind As Boolean
        Application.DisplayAlerts = False
        On Error Resume Next
        For Each obj In ThisWorkbook.VBProject.References
            If obj.Name = "Outlook" Then
                ThisWorkbook.VBProject.References.Remove obj
                ThisWorkbook.VBProject.References.AddFromFile Application.Path & "\msoutl.olb"
                sFind = True
                Exit For
            End If
        Next obj
        If sFind = False Then ThisWorkbook.VBProject.References.AddFromFile Application.Path & "\msoutl.olb"
        Application.DisplayAlerts = True
    End Sub

    Thanks to Peter and Felipe for their valuable input.  If the error happens to pop up at a later time, I will post further to the Forum.

    Wednesday, February 18, 2015 3:10 PM