Creating Excel Macros Which Run Automatically


As well as macros that can be run manually, in Excel VBA, it is possible to create macros which are executed automatically when a certain event takes place in the Excel environment. One such event is the opening of a workbook. There are two principal methods of creating macros which run when a particular workbook is opened, both of which involve placing code inside the workbook itself.

Perhaps the simplest technique is to create an event-handling macro within the code module of the workbook. The second is to create a macro in a regular module and give it the special name “Auto_Open”.

If you are taking the event-handling approach, double-click the “ThisWorkbook” object in the Project Explorer window of the Visual Basic Editor. This opens the code window of the workbook object. Next, choose “Workbook” from the drop-down menu in the top left of the code window. Excel will automatically create the default event-handling subroutine for a workbook object which just happens to be the “Open” event. Your code window should now contain the following subroutine:

Private Sub Workbook_Open()

End Sub

Now all you have to do is to insert the code you would like to run when the workbook is opened.

Let us turn now to look at the second method. Here, you must begin by inserting a regular VBA module, by choosing Module from the Insert menu. Inside the module, enter the following code:

Sub Auto_Open()

End Sub

Here, once more, just insert any code you would like to execute when the workbook is opened.

Although both of the above techniques achieve a similar result, there is one key difference between them. The “Auto_Open” macro will only execute if you manually open the file: if the file is opened programmatically by another macro, the “Auto_Open” macro will be ignored. By contrast, the event-handling macro will run whenever the workbook is opened, either manually or programmatically.

When creating code which opens a workbook with an “Auto_Open” macro inside it, you can still launch the macro by writing a line of code similar to the following.

Application.Workbooks(“WorkbookX.xlsm”).RunAutoMacros xlAutoOpen

You can find out more about Excel VBA training courses, visit Macresource Computer Training, an independent computer training company offering Excel VBA training courses in London and throughout the UK.

Leave a Comment

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!