Contact Me via Email

Inserting VBA Code (Macro) to be Associated with an Excel Worksheet

Site Home Page

Excel Tutorials

  This page provides guidance on setting up to insert Visual Basic for Applications (VBA) code to be associated with Events on an individual Excel worksheet, or to be associated in general with that worksheet.

VBA code is the language of Excel Macros.  Virtually anything that can be done with a formula may be accomplished with VBA.  But in some cases, a single well-written cell function can replace many lines of VBA code.

Feel free to browse around.  - j.latham (MS MVP [2006], MOS Master)
                                                                           Member IEEE
                                                                           Member Carnegie-Mellon Software Engineering Institute
                                                                           Member ASTD

Copyright © 2006 by J. L. Latham, All Rights Reserved.

Begin the process to add a code module to be associated with an Excel worksheet by opening the Visual Basic Editor.  From the Excel menu bar, choose
Macro | Visual Basic Editor (or simply press [Alt]+[F11])

Other 'start code' examples:  Associate Code with Workbook Events    Insert general purpose VBA Code module

First - this is a shortcut to get it done quickly - right click on the worksheet's name Tab and choose View Code -
that replaces all of the instructions below this.  The following instructions are provided as an alternative method.
Begin the process to add a code module to be associated with an Excel worksheet by opening the Visual Basic Editor.  From the Excel menu bar, choose
Macro | Visual Basic Editor (or simply press [Alt]+[F11])

The Excel Visual Basic Editor will open.  If this is the first time that code has been added to it in this workbook, the main portion of the screen will be empty.

If you cannot see the "Project - VBAProject" window, choose
View | Project Explorer
from the menu bar, or press [Ctrl]+[R] to bring it into view.

To create code to be associated with a worksheet's event(s), simply double-click on the sheet name in the Project window that you wish to provide code for.

Notice that initially the code window showing the module opens up without specifying that the code is for a worksheet event.  You can write general purpose code at this point that would be private to this worksheet and could be called using normal methods of running a macro from that worksheet.

Your screen may not show the words Option Explicit at the top of the module.  This statement indicates that all user defined constants and/or variables must be declared with a Dim statement before they are used.  This is a good habit to get into as it will help prevent errors that are often difficult to see, such as typographic errors in a name.  To have Excel automatically require declaration of constants/variables when a module is created, choose

Tools | Options
from the menu bar and check the box next to the Require Variable Declaration entry on the [Editor] tab.

But we want to associate some code with one of this worksheet's events, so...

.
Choose Worksheet from the pull-down list at the top of the code module window.

Excel will automatically begin a code section for the _SelectionChange event.  You can either ignore it completely, even leaving it in the module because it really doesn't do anything without any code inside of it.  To specify one of the other eight (8) events currently available for an Excel Worksheet, just choose the one you need from the procedure list.  Note: This sample is from Excel 2003 (Office 11) and the list may not be the same in all versions of Excel.

You are now ready to add code to be associated with the event you've chosen.  Place your code between the Private Sub and End Sub statements.

That code will execute each time that event fires for that sheet.