Learning the Basics of Visual Basic

You can get detailed information about a keyword by selecting the word and pressing F1. In Figure 26-4, for example, if you click anywhere within the keyword Sub and press F1, the Visual Basic Editor presents a Help screen containing an entry for the Sub statement, as shown in Figure 26-5.

Figure 26-5: You can display detailed Help information about any VBA keyword by clicking in the word and pressing F1.

Many Help topics for VBA keywords include one or more examples of the keyword as you might use it in working code. You can copy this code, paste it into a module, and edit the resulting text to meet your needs.

Objects, Methods, and Properties

To VBA, every item in the Excel environment is an object rather than an abstract set of data structures or an arrangement of pixels on the screen. Objects can contain other objects. At the top of the hierarchy, the largest object within the Excel object model is the Excel application. Objects contained within this largest container include workbooks. Workbooks contain worksheets and chart sheets, worksheets contain ranges (and can also contain chart objects), and so on.

The first executable (noncomment) statement in the CompanyAddress macro after the Sub statement is the following:

 Range("A6").Select 

This line illustrates an important characteristic of VBA code. The syntax of many statements specifies first an object and then an action. An object can be a range, a worksheet, a graphic object, a workbook, or any of the more than 100 types of objects in Excel. Here, we specify a range object (the absolute cell reference A6) and an action (select).

The behaviors, or sets of actions, that an object "knows" how to perform are called the methods of the object. Methods are like verbs. To understand this concept, imagine you are programming a robotic dog through VBA. To cause the dog to bark, you might use the following statement:

 Dog.Bark 

Robotic dogs, however, are (or ought to be) capable of more than just barking. For example, you might want the dog to understand the following statements:

 Dog. Sit Dog. RollOver Dog. Fetch 

The tricks your robodog can perform, such as barking, rolling over, and fetching, are its methods. The list of methods an object can perform depends on the object. A range object, for example, supports almost 80 different methods that you can use to copy and paste cells, sort, add formatting, and so on.

Like objects in the "real" world, objects in VBA also have properties. If you think of objects as the nouns of VBA and methods as the verbs, then properties are the adjectives. A property is a quality, characteristic, or attribute of an object, such as its color or pattern. Characteristics such as your robodog's color, the number of spots on its back, the length of its tail, and the volume of its bark are among its properties.

You set a property by following the name of the property with an equal sign and a value. Continuing the robotic dog example, you could set the length of the dog's tail with the following:

 Dog.Tail Length = 10 

in which TailLength is a property of the Dog object.

For example, the following executable statement in our CompanyAddress macro:

 ActiveCell.FormulaR1C1 = "Coho Winery" 

changes one of the properties, FormulaR1C1, of the active cell, setting that property to the value Coho Winery.

The remaining statements in the CompanyAddress macro consist of two more cell-selection and text-entry couplets. The macro selects cells A7 and A8 and enters text in each cell. (The last line, which selects cell A9, is there only because Excel moves the selection down a row by default after you type something in a cell.)

The Object Browser

You can view the various types of objects, methods, and properties available to Excel by clicking View, Object Browser (or pressing F2) in the Visual Basic Editor. The window displayed on the right of the screen, as shown in Figure 26-6, appears. Select Excel from the drop-down list of libraries at the top of the Object Browser.

Figure 26-6: The Object Browser displays the classes of objects belonging to the Excel application.

On the left is a list of the various classes of objects available to Excel. You can think of a class as a template or description for a type of object; a specific chart, for example, would be an object that is an instance of the Chart class. In VBA, classes belong to a project or library. As shown in Figure 26-6, the Object Browser lists the object classes belonging to the library Excel.

If you scroll down the classes and select a class-the Range class, for example-the right pane of the Object Browser lists the properties and methods (called the members of the class) belonging to that object. Figure 26-7 shows the members of the Range class.

Figure 26-7: Here the Object Browser shows the Range object and some of the Range object's methods and properties.

Collections of Objects

You can have more than one instance of the same VBA object. Together, such instances comprise a collection. You identify each instance in a collection of objects by either its index value (its position within the collection) or its name. For example, the collection of all sheets in a workbook is as follows:

 Sheets() 

In addition, a specific instance of a sheet, the third one in the collection, is as follows:

 Sheets(3) 

If the third sheet were named Summary, you could also identify it as follows:

 Sheets("Summary") 

In VBA, each item in a collection has its own index, but the index numbers for an entire collection are not necessarily consecutive. If you delete one instance of an object in a collection, VBA might not renumber the index values of the remaining instances. For example, if you delete Sheets(3) from a collection of 12 sheets in a workbook, you don't have any guarantee that VBA will renumber Sheets(4) through Sheets(12) to fill the gap.

In other programming languages, you might use a For ... Next construction such as the following to repeat an operation many times:

 For n = 1 to 12 ' Activate each sheet     Sheets(n).Activate Next n 

If you run this code in a VBA macro after deleting Sheets(3), VBA displays an error message and stops the macro because Sheets(3) no longer exists. To allow for non-consecutive indexes, VBA offers For Each... Next, a control structure that applies a series of statements to each item in a collection, regardless of the index numbers. For example, suppose you'd like to label each sheet in the active workbook by typing the text Sheet 1, Sheet 2, and so on, in cell A1 of each sheet. Because you won't, in general, know how many sheets any given workbook contains, you might use the following VBA code:

 Sub EnterSheetNum()     n = 0     for Each Sheet In Sheets()         n = n + 1         Sheet.Activate         Range("A1").Select         ActiveCell.FormulaR1C1 = "Sheet" + Str(n)     Next End Sub 

Manipulating an Object's Properties Without Selecting the Object

The code just listed activates each sheet in turn, then selects cell A1 on that sheet, and finally assigns a new value to that cell's FormulaR1C1 property. This sequence of steps mimics the steps you would follow if you were working manually. In VBA, everything but the last step in the sequence is unnecessary. That is, you can replace the following instructions:

 Sheet.Activate Range("A1").Select ActiveCell.FormulaR1C1 = "Sheet" + Str(n) 

with a single instruction:

 Sheet.Range("A1").FormulaR1C1 = "Sheet" + Str(n) 

The benefit of this change is that it enables the macro to run faster, because Excel is no longer required to activate sheets and select cells.

Naming Arguments to Methods

Many methods in VBA have arguments that let you specify options for the action to be performed. If the Wag method of the Tail object of our mythical robodog has arguments (for example, WagRate, the number of wags per second; WagTime, the duration of wagging in seconds; and WagArc, the number of degrees of arc in each wag), you can specify them using either of two syntaxes.

In the first syntax, which is often called the by-name syntax, you name each argument you use, in any order. For example, the following statement wags the tail three times per second for an hour, over an arc of 180 degrees. (It also assumes that our particular robodog is a member of a collection of such creatures and is named Fido.)

 Robodogs("Fido").Tail.Wag _     WagRage: = 3, _     WagTime: = 3600,_     WagArc: = 180 

You assign a value to an argument by using a colon and an equal sign, and you separate arguments with commas.

Note 

The underscore character at the end of the first three lines tells VBA that the line after the underscore is part of the same statement. Using this symbol makes the list of supplied arguments easier to read. You must always precede the underscore with a space character.

In the second syntax, which is often called the by-position syntax, you type arguments in a prescribed order. For example, the preceding statement expressed in the by-position syntax looks like this:

 Robodogs("Fido"). Tail. Wag(3, 3600, 100) 

Notice that the list of arguments is surrounded by parentheses. The by-position syntax isn't as easy to read as the by-name syntax because you have to remember the order of arguments, and when you review the code later, you won't have the argument names to refresh your memory about their settings.

Note 

The macro recorder records arguments by position rather than by name, which can make it more difficult to understand recorded macros than manually created ones in which you've named the arguments. Similarly, when you select a VBA keyword and press F1, the Help topic that appears describes the keyword using by-position syntax.