O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Understanding Macro Code

Now that you've learned how macros are organized, it's time to dive in and tackle the actual macro code. In the following sections, you'll take your first look at VBA code and learn how Excel uses a special programming tool called objects. (Objects are programming tools that you use to trigger Excel features. You'll learn more about them on Objects 101.)

The Anatomy of a Macro

A good place to start learning about the guts of a macro is the FormatRow macro shown in the previous chapter, which automatically highlights a row with a light green background, and then moves down two rows.

Following is the complete VBA code for the FormatRow macro. To make it easier to analyze, each line of code has been numbered, and the code has been simplified a little from what Excel generated automatically:

1  Sub FormatRow()
2      '
3      ' FormatRow Macro
4      ' Macro recorded 3/6/2010 by Matthew MacDonald
5      '
6      ' Keyboard Shortcut: Ctrl+Shift+F
7      '
8      ActiveCell.Rows.EntireRow.Select
9      Selection.Interior.ColorIndex = 35
10     Selection.Interior.Pattern = xlSolid
11     ActiveCell.Offset(2, 0).Select
12 End Sub

Line 1 starts the macro and defines its name. Lines 2–7 are simply comments. They appear in green writing in the editor and don't actually do anything (other than convey information to the person reading the code).

The action gets started with line 8. Here, the code accesses a special object called ActiveCell, and uses it to select the entire current row. Next, line 9 changes the background ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required