Running VBA modules - VBA For Excel

An Excel/VBA application can be anything from a small routine that performs a useful service to a large application that completely shields the user from Excel s basic interface. Excel provides a variety of ways to launch an application that can be tailored to suit the system. The ways is which an Excel VBA program can be launched are as follows:

  • By using Tools > Macro > Macros, selecting the macro and clicking the Run button.
  • Invoking a macro with the Macro dialog box.

    Invoking a macro with the Macro dialog box

  • By assigning a button to the macro..
  • By assigning your VBA procedure to a built-in macro. Excel contains four built-in automatic macros, which are invoked when some event occurs.These built-in macros are:

Auto_Open invoked whenever a workbook containing the macro is opened.Auto_Close invoked whenever the workbook containing the macro is closed.OnSheetActivate invoked when you first enter data on a worksheet.OnEntry invoked when you enter a worksheet.

Using Auto_Open

The following example illustrates how the Auto_Open procedure can be used to provide a start up message.The example shown in the first listing will give a different message for each day of the week. On Monday the message given is Have a nice week , on Tuesday the message is One down, four to go! ,and so on. The procedure works by creating a variable that will contain the digits from 1 to 7 representing the days of the week. It then finds out what day it is, using a built-in function that returns the current day, and a Case statement to decide what to do for each day of the week. Don t worry about your understanding of this just yet, as long as you can see how Auto-Open() works. The line select Case dayNum is used to choose the current day and to associate the appropriate message so that it can then be output in a message box.

Listing Using the Auto_open macro

Listing Using the Auto_open macro

Sample MsgBox from

Sample MsgBox from

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

VBA For Excel Topics