Section 26.2. Understanding Macro Code

26.2. 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 in Section 26.2.2 later in this chapter.)

26.2.1. The Anatomy of a Macro

A good place to start learning about the guts of a macro is the FormatRow macro shown in the last chapter, which automatically highlights a row with a light green background and 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/2004 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 color of the selected cells (to light green), and line 10 sets the type of background fill (to solid). Both of these lines use the Selection object.

Finally, line 11 returns to the ActiveCell object, and uses its Offset command to jump down two rows from the current cell . Line 12 marks the end of the macro code routine.

Altogether, this macro doesn't do much, but the code is quite dense, and fairly difficult to read on first sight. The problem isn't the VBA languagein fact, the only language-specific details in this example are the Sub and End Sub statements (not to mention all those odd periods, which are explained in the next section).

The real complexity comes from understanding all the different objects that are available. To write a macro like this, you need to know that there's an ActiveCell object that lets you select rows and move from row to row, and that there's a Selection object that lets you adjust formatting. These details (and many more that aren't shown in this macro) make up Excel's object model . If you want to perform any task in a macro, from printing a document to saving a worksheet, you need to first figure out which object can do your bidding.

26.2.2. Objects 101

In many programming languages, including VBA, everything revolves around objects. So what exactly is an object?

In the programming world, an object is nothing more than a convenient way to group together some related features. For example, in the FormatRow macro, two objects are front and center: one named ActiveCell, and one named Selection. The ActiveCell object bundles together everything you might want to do with the current cell, including editing, selecting, and moving from cell to cell. The Selection object offers other features for modifying a group of selected cells, including ways to change their borders, background colors, and font.

Programmers embraced objects long ago because they're a great way to organize code (not to mention a great way to share and reuse it). You might not realize it at first, but working with the ActiveCell object is actually easier than memorizing a few dozen different commands to accomplish the same tasks . Once you learn about ActiveCell, you immediately know what object you need to use for any task related to the current cell.

You can use objects in a number of different ways. Altogether, you interact with objects in three ways:

  • Properties . Properties are pieces of information about an object. You change properties to modify the object or how it behaves. For example, the FormatRow macro uses the ColorIndex property to change the background color of a row.

  • Methods . Methods are actions you can perform with an object. For example, the FormatRow macro uses the Select method to select the current row.

  • Events . Events are notifications that an object sends out, which you, as the macro-programmer, can respond to. This chapter doesn't look at events, but you can use them to react to certain actions that someone using your worksheet takes (like saving a workbook).

In the next section, you'll take a closer look at how to use properties and methods.

26.2.3. Using Properties and Methods

So how do you change properties or use methods? The answer is the lowly period. For example, imagine you have a Car object that provides a StartIgnition method. In this case, you use the following syntax to start your engine:


The same technique works with properties, but it tends to look a little different. With properties, you typically want to perform one of two actions. Either you want to retrieve information about the property, or you want to change the property. To change the property value, you use the equal sign (=). For example, the following line of code changes the number of people in a car by modifying the Passengers property:

 Car.Passengers = 2 

Tip: Think of the equal sign as an arrow pointing to the left. It takes whatever information is on the right side (in this case, the number 2) and stuffs it in whatever receptacle is on the left side (in this case, the Passengers property of the Car object).
The With Statement

Once you find the right object, you'll probably need to use several of its properties or methods. To save the effort of typing in the object name each time, you can use a With block . A With block starts off by identifying an object that you want to use. The following statements (up until the final End With) don't need to include the object name. Instead, they can skip straight to the period, and use it to start the line.

For example, the FormatHeader macro uses the following statements to set the formatting of the current selection:

 Selection.Interior.ColorIndex = 35 Selection.Interior.Pattern = xlSolid 

You can rewrite this using the With statement as follows :

 With Selection.Interior     .ColorIndex = 35     .Pattern = xlSolid End With 

Either way, the result is the same. But it helps to be familiar with the With statement, because the macro recorder uses it frequently. In fact, if you look at the original FormatHeader code that the macro recorder generated, you'll find that it uses a With block.

Surprisingly, this is just about everything you need to know about objects. Of course, if you look back at the FormatRow example, you'll notice that none of the lines look quite as simple as the previous two Car object examples. That's because you need to use multiple objects at once to accomplish many Excel tasks. Excel is so feature-laden that its developers needed to divide and subdivide its features into dozens of different objects to make them more manageable.

For example, consider the following statement (line 9 from the FormatRow macro):

 Selection.Interior.ColorIndex = 35 

In this example, there are two objects at work. The Selection object contains another object named Interior. Technically, Interior is a property of the Selection object. Unfortunately, the Selection object doesn't give you any way to change the background color of the selected cells. To do that, you need to use the ColorIndex property, which is a part of the Interior object. That's why this statement has two periods. The first one accesses the Interior object, and the second period accesses the ColorIndex property.

To make life even more interesting, consider line 8, which has three periods:


In this case, the ActiveCell object has a property called Rows, which is also an object. The Rows object has a property named EntireRow, which is yet another object. The EntireRow object is the one you wantit provides the Select method that highlights the current row.

In short, understanding the basics of objects really isn't that difficult, but you could spend days wandering around Excel's family of objects trying to find the ones you need. Excel includes objects that represent worksheets, workbooks, cells, ranges, selections, charts , the Excel window, and more. The next section tells you how to get started.

26.2.4. Hunting for Objects

Finding the objects you need isn't trivial, and if you do more than a little bit of macro code editing, you'll want to invest in a dedicated reference book. However, the Visual Basic editor gives you two tools to help get you started.

The first tool is the comprehensive Help menu that identifies every object in the Excel family. To view this information, head to the Visual Basic editor menu bar, and then choose Help Microsoft Visual Basic Help. Depending on how you configured Excel when you first installed it, Excel may warn you that the help is not installed and then ask whether you want to install it. Choose Yes to continue (you may or may not need the original Office setup CD).

Once the help is installed, or if it's already available, a Visual Basic Help task pane appears on the right side of the window. It displays the detailed table of contents shown in Figure 26-5.

Figure 26-5. Excel's Visual Basic help is comprehensive, but not necessarily easy to learn from. The section you want is named Microsoft Excel Visual Basic Reference (shown here).

Help contains three notable sections. The Programming Concepts section includes some dense, but illustrative examples that demonstrate a few common tasks. The Collections and Objects sections dissect every available Excel object (in alphabetical order), describing their various properties and methods.

Once you've found the object you want to use, the Visual Basic editor tries to help you out while you're typing in the code that uses the object. Every time you press the period after typing in a valid object name, a pop-up menu appears with a list of all the properties and methods for that object (see Figure 26-6). If you don't know the exact name of the property or method you want to use, you can scroll through the list, and then select it. Of course, you still need to know how to use the property or method, but this feature helps you get started.

Figure 26-6. When you press the period key, a Visual Basic feature called IntelliSense shows you the available properties and methods for the current object. Properties are represented with a hand and page icon, while methods are shown with what appears to be a flying green eraser.

Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185 © 2008-2017.
If you may any questions please contact us: