Workbook_Open vs. Auto_Open

I’ve seen it written that Auto_Open() was superseded by Workbook_Open() and that it is therefore redundant and only still available for backward compatibility. In fact, there are subtle differences between the two which means that, in my opinion, Auto_Open() remains a distinct and useful tool in the Excel developer toolkit. Here are the differences I know about:

The Code Container

While this difference isn’t significant, it does catch out beginners.

Auto_Open() must sit in a standard code module whereas Workbook_Open() must be housed in the ThisWorkbook class module. If either one is in the wrong place then it won’t run.

Since Auto_Open() lives in a standard code module, it is possible to generate it using the macro recorder. By contrast, Workbook_Open() cannot be generated by the macro recorder in situ although you could generate it in a standard code module and copy it across [see Patrick’s comment]. For me, the best way to create the Workbook_Open() procedure stub is to use the two dropdown boxes at the top of the code pane when the ThisWorkbook code module is active and then to manually code the contents.

  • Contrary to popular belief, Auto_Open() can be declared with a Private scope: it does not have to be Public. Setting the scope to Private will hide it from the Assign Macro dialog box’s available macro list and make it inaccessible to other code modules.

Application.EnableEvents

This is a really important difference. If Application.EnableEvents is set to False then Workbook_Open() does not get triggered. There’s a lot of sloppy VBA code around which sets EnableEvents to False but fails to set it back to True again, which means your Workbook_Open() code is vulnerable to other people’s mistakes.

Auto_Open() is unaffected by Application.EnableEvents so some developers use Auto_Open() if they want to ensure that a certain piece of code is executed when a user opens the workbook regardless of the Application.EnableEvents state. This is, of course, subject to user macro security settings.

  • Holding down the SHIFT key when opening a workbook will disable both Workbook_Open() and Auto_Open().

End User Or Automation?

Another really important difference. If a workbook is opened programmatically then Auto_Open() does not get triggered whereas Workbook_Open() does. This means that Auto_Open() is an easy way to segregate code which should only be executed for end-users and not during automation.

  • If you want to disable Workbook_Open() during automation then set Application.EnableEvents to False before opening the workbook.
  • If you want to run Auto_Open() (and other auto methods) during automation then use the Workbook.RunAutoMacros() method.

Call Order

If your workbook has both Workbook_Open() and Auto_Open() procedures then it’s important to know that it is the Workbook_Open() method that gets called first.

  • Workbook_Open() and Auto_Open() are both called before the customUI OnLoad() callback.
Advertisements

About Colin Legg

RAD Developer Microsoft MVP - Excel 2009 - 2014
This entry was posted in Microsoft Excel and tagged , . Bookmark the permalink.

2 Responses to Workbook_Open vs. Auto_Open

  1. Patrick Matthews says:

    “By contrast, Workbook_Open() cannot be generated by the macro recorder and must be manually written.”

    Disagree. If one wants to leverage the macro recorder, simply record as normal, and then copy-paste the recorded code into a proper Workbook_Open event sub in the ThisWorkbook class module.

    Like

  2. Colin Legg says:

    You’re right, I guess you could generate it in a standard code module and then copy it across. I’ll edit my post to make it clear that it can’t be generated in situ. Thanks Patrick.

    Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s