You can get detailed information about a keyword by selecting the word and pressing F1. 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. 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.
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 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.TailLength = 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.)
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 27-4, appears.
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 27-4, 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 27-5 shows the members of the Range class.
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 nonconsecutive 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 SubThe Str function in VBA converts a numeric value to a text value so that we can concatenate the word Sheet with the appropriate number.
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.
Many methods in VBA have arguments, words that act like adverbs, allowing you to specify options for an action to be performed. Arguments for our mythical robodog’s wag method, for example, might include 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 use either of two syntaxes to specify arguments.
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 _
WagRate := 3, _
WagTime := 3600, _
WagArc := 180You assign a value to an argument by using a colon and an equal sign, and you separate arguments with commas.
Note
An underscore character at the end of a line tells VBA that the line after the underscore is part of the same statement. Using this symbol breaks a statement over multiple lines; in this case, it 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.