The advantage of using these properties to return an object is that you don't need to know which cell, worksheet, or workbook is active, and you don't need to provide a specific reference to it. This allows you to write VBA code that isn't specific to a particular workbook, sheet, or range. For example, the following instruction clears the contents of the active cell, even though the address of the active cell isn't known:
ActiveCell.ClearContents
The example that follows displays a message that tells you the name of the active sheet:
MsgBox ActiveSheet.Name
If you want to know the name and directory path of the active workbook, use a statement like this:
MsgBox ActiveWorkbook.FullName
If a range on a worksheet is selected, you can fill the entire range with a value by executing a single statement. In the following example, the Selection property of the Application object returns a Range object that corresponds to the selected cells. The instruction simply modifies the Value property of this Range object, and the result is a range filled with a single value.
Selection.Value = 12
If something other than a range is selected (such as a ChartObject or a Shape), the preceding statement generates an error because ChartObject and Shape objects don't have a Value property.
The following statement, however, enters a value of 12 into the Range object that was selected before a non-Range object was selected. If you look up the RangeSelection property in the Help system, you find that this property applies only to a Window object.
ActiveWindow.RangeSelection.Value = 12
To find out how many cells are selected in the active window, access the Count property. Here's an example:
MsgBox ActiveWindow.RangeSelection.Count
Understanding methods
Methods are the actions that can be performed with an object. It helps to think of methods as verbs. You can paint your house, so in VBA, that translates to something like house.paint.
A simple example of an Excel method is the Select method of the Range object.
Range("A1").Select
Another is the Copy method of the Range object.
Range("A1").Copy
Some methods have arguments that can dictate how they are applied. For instance, the Paste method can be used more effectively by explicitly defining the Destination argument.
ActiveSheet.Paste Destination:=Range("B1")
More about arguments
An issue that often leads to confusion among new VBA programmers concerns arguments. Some methods use arguments to clarify further the action to be taken, and some properties use arguments to specify additionally the property value. In some cases, one or more of the arguments are optional.
Consider the Protect method for a workbook object. Check the Help system, and you'll find that the Protect method takes three arguments: Password, Structure, and Windows. These arguments correspond to the options in the Protect Structure and Windows dialog box.
If you want to protect a workbook named MyBook.xlsx, for example, you might use a statement like this:
Workbooks("MyBook.xlsx").Protect "xyzzy", True, False
In this case, the workbook is protected with a password (argument 1). Its structure is protected (argument 2) but not its windows (argument 3).
If you don't want to assign a password, you can use a statement like this:
Workbooks("MyBook.xlsx").Protect , True, False
The first argument is omitted, and we specified the placeholder by using a comma.
You can make your code more readable by using named arguments. Here's an example of how you use named arguments for the preceding example:
Workbooks("MyBook.xlsx").Protect Structure:=True, Windows:=False
Using named arguments is a good idea, especially for methods that have many optional arguments and also when you need to use only a few of them. When you use named arguments, you don't need to use a placeholder for missing arguments.
For properties (and methods) that return a value, you must use parentheses around the arguments. For example, the Address property of a Range object takes five optional arguments. Because the Address property returns a value, the following statement isn't valid because the parentheses are omitted:
MsgBox Range("A1").Address False ' invalid
The proper syntax for such a statement requires parentheses as follows:
MsgBox Range("A1").Address(False)
You can also write the statement using a named argument:
MsgBox Range("A1").Address(RowAbsolute:=False)
These nuances will become clearer as you gain more experience with VBA.
Deep Dive: Working with Range Objects
Much of the work that you will do in VBA involves cells and ranges in worksheets. That being the case, let's take some time to use the Range object as a case study on how to explore and get familiar with a specific object.
Finding the properties of the Range object
Open the Visual Basic Editor and then go up to the menu and click Help Microsoft Visual Basic for Applications Help. You'll be taken to the Microsoft Developer Network (MSDN) website. While on MSDN, search for the word Range to see the page for the Range object. There you will discover that the Range object exposes three properties that can be used to manipulate your worksheets via VBA.
The Range property of a Worksheet or Range class object
The Cells property of a Worksheet object
The Offset property of a Range object
The Range property
The Range property returns a Range object. If you consult the Help system for the Range property, you learn that this property has two syntaxes.
object.Range(cell1) object.Range(cell1, cell2)
The Range property applies to two types of objects: a Worksheet object or a Range object. Here, cell1 and cell2 refer to placeholders for terms that Excel recognizes as identifying the range (in the first instance) and delineating the range (in the second instance). The following are a few examples of using the Range property.
You've already seen examples like the following one earlier in the chapter. The instruction that follows simply enters a value into the specified cell. In this case, it puts the value 12.3 into cell A1 on Sheet1 of the active workbook.
Worksheets("Sheet1").Range("A1").Value = 12.3
The Range property also recognizes defined names in workbooks. Therefore, if a cell is named Input, you can use the following statement to enter a value into that named cell:
You've already seen examples like the following one earlier in the chapter. The instruction that follows simply enters a value into the specified cell. In this case, it puts the value 12.3 into cell A1 on Sheet1 of the active workbook.
Worksheets("Sheet1").Range("A1").Value = 12.3
The Range property also recognizes defined names in workbooks. Therefore, if a cell is named Input, you can use the following statement to enter a value into that named cell:
Worksheets("Sheet1").Range("Input").Value = 100
The example that follows enters the same value in a range of 20 cells on the active sheet. If the active sheet isn't a worksheet, the statement causes an error message.
ActiveSheet.Range("A1:B10").Value = 2
The next example produces the same result as the preceding example:
Range("A1", "B10") = 2
The sheet reference is omitted, however, so the active sheet is assumed. Also, the Value property is omitted, so the default property (which is Value for a Range object) is assumed. This example also uses the second syntax of the Range property. With this syntax, the first argument is the cell at the top left of the range, and the second argument is the cell at the lower right of the range.
The following example uses the Excel range intersection operator (a space) to return the intersection of two ranges. In this case, the intersection is a single cell, C6. Therefore, this statement enters 3 in cell C6:
Range("C1:C10 A6:E6") = 3
Finally, if the range you're referencing is a noncontiguous range (a range where not all the cells are adjacent to each other), you can use commas to serve as a union operator. For example, the following statement enters the value 4 in five cells that make up a noncontiguous range. Note that the commas are within the quote marks.
Range("A1,A3,A5,A7,A9") = 4
So far, all the examples have used the Range property on a Worksheet object. As mentioned, you can also use the Range property on a Range object. For example, the following line of code treats the Range object as if it were the upper-left cell in the worksheet, and then it enters a value of 5 in the cell that would be B2. In other words, the reference returned is relative to the upper-left corner of the Range object. Therefore, the statement that follows enters a value of 5 into the cell directly to the right and one row below the active cell:
ActiveCell.Range("B2") = 5
Fortunately, you can access a cell relative to a range in a much clearer waythe Offset property. We discuss this property after the next section.
The Cells property
Another way to reference a range is to use the Cells property. You can use the Cells property, like the Range property, on Worksheet objects and Range objects. Check the Help system, and you see that the Cells property has three syntaxes.
object.Cells(rowIndex, columnIndex) object.Cells(rowIndex) object.Cells
Some examples demonstrate how to use the Cells property. The first example enters the value 9 in cell A1 on Sheet1. In this case, we're using the first syntax, which accepts the index number of the row (from 1 to 1048576) and the index number of the column (from 1 to 16384):
Worksheets("Sheet1").Cells(1, 1) = 9
Here's an example that enters the value 7 in cell D3 (that is, row 3, column 4) in the active worksheet:
ActiveSheet.Cells(3, 4) = 7
You can also use the Cells property on a Range object. When you do so, the Range object returned by the Cells property is relative to the upper-left cell of the referenced Range. Confusing? Probably. An example may help clear up any confusion. The following instruction enters the value 5 in the active cell. Remember, in this case, the active cell is treated as if it were cell A1 in the worksheet.
ActiveCell.Cells(1, 1) = 5
NOTE
The real advantage of this type of cell referencing will be apparent when you explore variables and looping (in Chapter 3, VBA Programming Fundamentals). In most cases, you don't use actual values for the arguments; rather, you use variables.
To enter a value of 5 in the cell directly below the active cell, you can use the following instruction:
ActiveCell.Cells(2, 1) = 5
Think of the preceding example as though it said this: Start with the active cell and consider this cell as cell A1. Place 5 in the cell in the second row and the first column.
The second syntax of the Cells property uses a single argument that can range from 1 to 17,179,869,184. This number is equal to the number of cells in an Excel worksheet. The cells are numbered starting from A1 and continuing right and then down to the next row. The 16,384th cell is XFD1; the 16,385th cell is A2.
The next example enters the value 2 into cell SZ1 (which is the 520th cell in the worksheet) of the active worksheet:
ActiveSheet.Cells(520) = 2
To display the value in the last cell in a worksheet (XFD1048576), use this statement:
MsgBox ActiveSheet.Cells(17179869184)
You can also use this syntax with a Range object. In this case, the cell returned is relative to the Range object referenced. For example, if the Range object is A1:D10 (40 cells), the Cells property can have an argument from 1 to 40 and can return one of the cells in the Range object. In the following example, a value of 2000 is entered in cell A2 because A2 is the 5th cell (counting from the top, to the right, and then down) in the referenced range:
Range("A1:D10").Cells(5) = 2000
NOTE
In the preceding example, the argument for the Cells property isn't limited to values between 1 and 40. If the argument exceeds the number of cells in the range, the counting continues as if the range were taller than it actually is. Therefore, a statement like the preceding one could change the value in a cell that's outside the range A1:D10. The statement that follows, for example, changes the value in cell A11:
Range("A1:D10").Cells(41) = 2000
The third syntax for the Cells property simply returns all cells on the referenced worksheet. Unlike the other two syntaxes, in this one, the return data isn't a single cell. This example uses the ClearContents method on the range returned by using the Cells property on the active worksheet. The result is that the content of every cell on the worksheet is cleared.
ActiveSheet.Cells.ClearContents
Getting information from a cell
If you need to get the contents of a cell, VBA provides several properties. The following are the most commonly used properties: