Dutch Gemini's Weblog Pages

December 1, 2009

Excel code execution stops with msoAutomationSecurityByUI

Filed under: Excel,Programming,VBA — dutchgemini @ 11:36 am

When you open a workbook containing macro code via Excel’s regular menu, depending on your Macro Security settings you may receive a popup message or security banner asking you what you want to do with this workbook.

If however you open the same workbook via VBA macro code, Excel assumes the workbook is safe and macros are allowed to run. The macro security setting in this case is ignored. This is equivalent to a macro security level set to Low.

This seams contradictory but as I will explain, this appears to be necessary for Excel to continue executing macro code.

I have an Excel Add-In that creates a custom CommandBar. One of the CommandBarButtons can be used to open workbooks. In the associated VBA macro, I use a built-in Excel dialog to let the user search for a workbook. This workbook is then opened programatically using:

' Initialisation.
' {...}

' Load using standard Excel dialog.
wFileToOpen = Application.GetOpenFilename(FileFilter:=wFileFilter, _
                                          FilterIndex:=1, _
                                          Title:="Open Project File", _

' Open the workbook.
Application.Workbooks.Open Filename:=wFileToOpen

' Rest of code.
' {...}

The same Add-In has defined a public variable hooked to the Application object using:

' Create a public variable and hook it to the Application.
Public WithEvents ExcelApplication As Application

Set ExcelApplication = New Application

In the Application’s WorkbookOpen event, the workbook is checked for contents that enable/disable some of the buttons on the custom CommandBar.

To copy the behaviour of Excel’s Macro Security, I have included in my VBA code the following statement:

' Set the macro security level to the User Interface setting.
Application.AutomationSecurity = msoAutomationSecurityByUI

' Now open the workbook.
Application.Workbooks.Open Filename:="NameOfFile.xls"

' Rest of code.
' {...}

Indeed, when I open a workbook with my VBA code, Excel asks me what I want to do with the file.

Now, if I select to enable macros, Excel continues to run my VBA code, it triggers the WorkbookOpen event and does what I programmed to do.

If instead I select to disable macros, Excel stops running my code, no event is triggered but I get a Run-time error '1004': Application-defined or object-defined error just before Excel goes idle.

If I open the workbook using Excel’s menu and select to disable macros, Excel does trigger the WorkbookOpen event, does run my code and does what I programmed to do.

this lets me presume that as soon as you select to disable macros in a workbook, Excel does not only prevent macro code in the workbook you are opening to run, but macro code in any workbook, including the Add-In.

My advice? If you want to open workbooks programmatically with VBA code, set the macro security level to Low. Otherwise open the workbook with Excel’s menu. This wil guarantee that events are triggered as expected.



Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: