Excel VBA Programming For Dummies. Dick Kusleika
The following macro changes the value in cell A1 by changing the cell’s Value property:
Sub ChangeValue() Worksheets("Sheet1").Range("A1").Value = 994.92End Sub
After Excel executes this procedure, cell A1 on Sheet1 of the active workbook contains the value 994.92. If the active workbook doesn’t have a sheet named Sheet1, the result of executing that macro is an error message. VBA just follows instructions, and it can’t work with a sheet that doesn’t exist.
Each object has its own set of properties, although some properties are common to many objects. For example, many (but not all) objects have a Visible property. Most objects also have a Name property.
Some object properties are read-only properties, which means that your code can get the property’s value, but it can’t change it. For example, a Workbook object has a Name property that returns the workbook’s name. You can’t change the Name property directly because it’s read-only. To change a workbook’s name, you must use the Save or SaveAs methods.
Sub CountBooks() MsgBox Workbooks.CountEnd Sub
For more about collections, see the previous section, “Referring to objects.”
Taking action with object methods
In addition to properties, objects have methods. A method is an action you perform with an object. A method can change an object’s properties or make the object do something.
This simple example uses the ClearContents method on a Range object to erase the contents of 12 cells on the active sheet:
Sub ClearRange() Range("A1:A12").ClearContentsEnd Sub
Some methods take one or more arguments. An argument is a value that further specifies the action to perform. You place the arguments for a method after the method, separated by a space. Multiple arguments are separated by a comma.
The following example activates Sheet1 (in the active workbook) and then copies the contents of cell A1 to cell B1 by using the Range object’s Copy method. In this example, the Copy method has one argument, which is the destination range for the copy operation:
Sub CopyOne() Worksheets("Sheet1").Activate Range("A1").Copy Range("B1")End Sub
Notice that the worksheet reference is omitted when referring to the Range objects. You can do this safely due to the statement to activate Sheet1 (using the Activate method).
Another way to specify an argument for a method is to use the official name of the argument followed by a colon and an equal sign. Using named arguments is optional, but doing so can often make your code easier to understand, particularly if the method takes many parameters. The second statement in the CopyOne procedure could be written like this:
Range("A1").Copy Destination:=Range("B1")
Figure 4-2 shows the little prompt that appears as I type a statement. That prompt indicates the official name of the argument.
FIGURE 4-2: The VBE displays a list of arguments while you type.
Because a collection is also an object, collections have methods. The following macro uses the Add method for the Workbooks collection:
Sub AddAWorkbook() Workbooks.AddEnd Sub
As you might expect, this statement creates a new workbook. In other words, it adds a new workbook to the Workbooks collection. After you execute this macro, a fresh workbook is the active workbook.
Triggering actions with object events
This section briefly touches on one more topic that you need to know about: events. Events are specific things that you do inside Excel that Excel is constantly listening for (such as switching sheets or changing cells). For example, when you type a value into a cell, a Worksheet Change event occurs. You could, for example, have a VBA macro that is designed to execute whenever someone changes the value of a particular cell.
A lot of objects have events, but not all objects do. Some objects have a ton of events to listen for, while others have just a few. The only events you can use are those made available by the programmers of Microsoft Excel. Events are a very cool feature of the Excel object model, and I dig into them in Chapter 11 and also in Part 4.
Finding Out More from VBA Resources
This chapter introduces the wonderful world of objects, properties, methods, and events. You find out more about these concepts in the chapters that follow. If you just can’t get enough, you might also be interested in these three excellent tools:
VBA’s Help system
The Object Browser
Auto List Members
Using VBA’s Help system
The VBA Help system describes every object, property, and method available to you, and also provides sample code. This is an excellent resource for finding out about VBA, and it’s more comprehensive than any book on the market. But it’s also very boring to read.
If you’re working in a VBA module and want information about a particular object, method, or property, move the cursor to the word you’re interested in and press F1. In a few seconds, you’ll see the appropriate Help topic displayed in your web browser, complete with cross-references, and perhaps even an example or two.
Figure 4-3 shows part of a screen from the VBA Help system — in this case, for the Copy method of the Range object.
FIGURE 4-3: An example from VBA’s Help system.
Using the Object Browser
The VBE includes another tool known as the Object Browser. As the name implies, this tool lets you browse through the objects available to you. To access the Object Browser, press F2 when the VBE is active (or choose View ⇒ Object Browser). You see a window like the one shown in Figure 4-4.
The drop-down list at the top contains a list of all currently available object libraries. Figure 4-4 shows All Libraries. If you want to browse through Excel’s objects, select Excel from the drop-down list.
The