Introduction to custom macros in Excel

Book cover image This article was written by Curtis Frye, the author of Microsoft Office Excel 2003 Programming Inside Out. Visit Microsoft Learning to buy this book.

Many tasks you perform in Excel, such as entering sales data for a particular day or adding a formula to a worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.), are done just once. Other tasks, like changing the format of a range of cells, are repeated often but can be done quickly by using tools in Excel. However, you often have one or two tasks that you perform frequently and that require a lot of steps to accomplish.

For example, you might want to highlight several important cells in a worksheet every time you present a report to your colleagues. Rather than go through a lengthy series of steps to do this manually, you can create a macro, or a series of recorded actions, to perform the steps for you. Or, if you are familiar with the Microsoft Visual Basic® for Applications (VBA) programming language, you can write a custom macro from scratch. In either case, after you create a macro, you can run it, edit it, or delete it as needed.

This article introduces you to object-oriented programming and the Visual Basic Editor, the environment in which you can write your own Excel macros. When you are ready to try your hand at writing a custom macro, see Frank Rice's Super-Easy Guide to the Microsoft Office Excel 2003 Object Model for an in-depth look at the process.

Using the Visual Basic Editor

The Visual Basic Editor is a powerful tool that lets you extend the power and versatility of macros beyond anything that can be done through recording alone. To open it, point to Macro on the Tools menu, and then click Visual Basic Editor. (Or, press ALT+F11.)

To open a specific macro, point to Macro on the Tools menu, and then click Macros, (Or, press ALT+F8.) In the Macro dialog box, select the macro that you want to edit or view, and then click Edit to open the Visual Basic Editor.

Object-oriented programming in the Visual Basic Editor

In the Visual Basic Editor, you use object-oriented programming to create your own programs. A program is, in principle, nothing more than a set of instructions that the computer runs in a specified order.

In an object-oriented programming language, every aspect of your computer code is based on the things in your environment. Those "things" (not surprisingly) are represented as objects, and all actions and data are encapsulated within those objects. In Excel, objects can be workbooks, worksheets, ranges of cells, and external files.

In general, there are four aspects of objects that you use to flesh out a program: properties, methods, events, and collections.

Properties

In brief, properties are variables that describe some aspect of the object in which they are included. A common object property in Excel is Name, which holds the identifying value that you or Excel assign to a workbook, worksheet, cell range, or other object. For example, if you change the name of a worksheet (by using VBA code or by right-clicking the worksheet tab), you change the value that is stored in the Name property.

You can set new values for some worksheet properties (like Name) directly in VBA. To change other worksheet properties, you may need to record an action in Excel (for example, the steps to protect a cell range) or use a method (as described in the next section).

In VBA, properties are referred to in a program by using dot notation, where the object name is written first, the property name is written second, and the two elements are separated by a period. For example, to change the name of a worksheet, you use the Worksheet.Name property.

Methods

A method is an action that an object "knows" how to perform. For example, you probably know that the worksheet displayed in the Excel window is referred to as the active worksheet. In Excel VBA code, you can target the worksheet that you want to affect by first calling its Activate method. After the Activate method runs, the worksheet to which it is attached moves to the front of the worksheets in the Excel window and becomes available for editing.

As with properties, methods are called by using dot notation. To recalculate all of the formulas on a worksheet, for example, you call the Worksheet.Calculate method.

Events

Just as a property is a quantifiable attribute of an object, and a method is an action that an object knows how to take, an event is an action that an object "recognizes" as having happened. For example, Excel 2003 knows about the following events (among many others):

  • A workbook is opened or closed.
  • A worksheet is activated or deactivated.
  • A workbook is saved.
  • A chart is clicked.
  • A key (or combination of keys) is pressed.
  • Data is typed into a cell.
  • The formulas on a worksheet are recalculated.
  • A hyperlink is followed.

Excel includes several event handlers, or code routines, that watch for particular actions. When one of those actions does occur, and you've told Excel what you want it to do when the event occurs, Excel runs the code in your event handler. For example, if after creating a new workbook, you want Excel to display all open workbooks as a cascaded set of windows, you can create the following event handler:

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
   Application.Windows.Arrange xlArrangeStyleCascade
End Sub

Don't worry if you're not sure what each and every element of the event handler routine does. For now, concentrate on the middle line of code, which tells Excel to arrange its windows by using the cascade style. You can get the same result by using menu commands in Excel (Window menu, Arrange command, Cascade subcommand). However, if it's an action that you want to happen every time a particular event occurs, you can use VBA to make it happen automatically — and save yourself the trouble.

Collections

The final element of object-oriented programming with which you should be familiar is the collection. As the name implies, a collection is a group of objects of the same type that are contained within another object. For example, a workbook contains a collection of one or more worksheets. If you want to make the same change to every worksheet in a workbook, you can step through each worksheet in the collection and make the change programmatically.

If you've created programs before, you've probably run into the For…Next loop, which lets you repeat a set of instructions several times by using something like the following sequence. (This example adds the directory path of the active workbook to the right section of the footer on the first three worksheets.)

For i = 1 to 3
   Worksheets(i).PageSetup.RightFooter = Path
Next i

The problem with hard-coding (that is, assigning a set value to) the upper limit of this For…Next loop is that you must change the code every time you add or delete a worksheet. That's no big deal once or twice, but if you're managing a lot of code, you'll inevitably forget to change it in a few places. This causes errors that you then must fix. Worse yet, those errors might not be noticed until the proofreader discovers that the first 500 printed copies of your annual report aren't formatted correctly (and you've turned off your wireless phone as you relax on the beach).

You could use a bit of code to discover the number of worksheets in your workbook, but there's a simpler way to do it: Use a For Each…Next loop instead. A For Each…Next loop finds the number of objects in a collection, such as worksheets in a workbook, and steps through each occurrence. Using this technique, the preceding code can be written as follows:

For Each Wksht in Worksheets
    Wksht.PageSetup.RightFooter = Path
Next Wksht
      

Instead of incrementing the value in a standard For…Next loop, the For Each…Next loop simply searches for the next member of the Worksheets collection and stops when it doesn't find one.

The Visual Basic Editor and its windows

The Visual Basic Editor displays different information in a variety of windows. The following windows are important to know about when you start writing macros: the Project Explorer window, the Properties window, and the Code window.

Project Explorer window

When you open the Visual Basic Editor directly, you can use the Project Explorer window to select the macro you want to work on. The Project Explorer presents all projects (groupings of code) and the macros they contain in a tree view, which works like the tree view in Windows® Explorer. The root or base objects in the tree structure are the current workbook that you are working in, plus any other workbooks and add-ins that might be open.

The main elements that are used by VBA projects are each stored in a separate folder within each project. Those elements include:

  • Objects (described earlier in this article)
  • Modules, which contain the macro code associated with a worksheet
  • Class modules, which are definitions of the user-defined objects that you created for your workbook
  • User forms, which provide a visible surface on which you can place graphical controls, such as buttons, images, and text areas

In the following example, the current workbook is identified as VBAProject. The three worksheets included in the workbook (Sheet1, Sheet2, and Sheet3), along with the entire workbook (ThisWorkbook), appear in the Microsoft Office Excel Objects folder. Any macros programmed in VBA or recorded appear in the Modules folder. Any class modules or user forms appear in a Class Modules folder or Forms folder, respectively.

Project Explorer window, Properties window, and Code window

Properties window

Directly below the Project Explorer window is the Properties window, which is used to examine and change the various properties associated with the selected object. For modules, usually the Name property is the only one available. Worksheets have additional properties that can be changed, such as StandardWidth and DisplayPageBreaks. Changing properties in the Properties window is most often done when working with user forms.

 Note   The name that appears for an object in the Name property box of the Properties window is not necessarily the same as the name of the corresponding object in Excel. The two names may look similar, but they may also differ because of default naming rules that the Visual Basic Editor uses for objects. (For example, names must have no spaces, must begin with a letter, can't be a reserved word, and must be no more than 31 characters in length.) Even when names are different, however, they may still be linked together internally to ensure the proper object is referenced.

Code window

The Code window is the largest window within the Visual Basic Editor and is topped with two drop-down boxes. You use the drop-down box on the left — the Object box — to select an object to work on. When you are working with code only, the box displays the default General object. The second drop-down box — the Procedure box — is used to select individual macros within the current module. As macros are added to and deleted from the module, they are also added to and deleted from the Procedure box.

Code window

You can also use the UP ARROW and DOWN ARROW keys to scroll through the code listings until you reach the macro you want. As you scroll through the code, the Procedure box is updated to reflect the current macro.

More information

To get started creating your own Excel macros, see the Super-Easy Guide to the Microsoft Office Excel 2003 Object Model for detailed instructions.

 
 
Applies to:
Excel 2003