Dick Kusleika - Excel VBA Programming For Dummies

The code in the preceding example displays the current setting of a cell’s Value property. What if you want to change the setting for that property? 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.

A collection is also an object. This means that a collection also has properties. For example, you can determine how many workbooks are open by accessing the Count property of the Workbooks collection. The following VBA procedure displays a message box that tells you how many workbooks are open:

Sub CountBooks() MsgBox Workbooks.CountEnd Sub

For more about collections, see the previous section, “ Referring to objects.”

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.

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 11and also in Part 4.

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

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 using Excel 2013 or later, you must be connected to the internet to use the VBA Help system (previous versions don’t have this requirement). You can, however, download the VBA Help system from Microsoft’s website. Do a web search for download excel VBA documentation, and you’ll find it.

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.

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 second drop-down list is where you enter a search string. For example, if you want to look at all Excel objects that deal with borders, type borderin the second field and click the Search button. (It has a pair of binoculars on it.) The Search Results window displays everything in the object library that contains the text border . If you see something that looks like it might be of interest, select it and press F1 for more information online.