Properties and Methods

It's easy to be overwhelmed with properties and methods; there are literally thousands available. In this section, I describe how to access properties and methods of objects.

Object properties

Every object has properties. For example, a Range object has a property called Value . You can write VBA code to display the Value property or write VBA code to set the Value property to a specific value. Here's a procedure that uses the VBA MsgBox function to pop up a box that displays the value in cell A1 on Sheet1 of the active workbook:

 Sub ShowValue()     Msgbox Worksheets("Sheet1").Range("A1").Value End Sub

Note	The VBA `MsgBox` function provides an easy way to display results while your VBA code is executing. I use it extensively throughout this book.

The code in the preceding example displays the current setting of the Value property of a specific cell: cell A1 on a worksheet named Sheet1 in the active workbook. Note that if the active workbook does not have a sheet named Sheet1 , the macro generates an error.

What if you want to change the Value property? The following procedure changes the value displayed in cell A1 by changing the cell's Value property:

 Sub ChangeValue()     Worksheets("Sheet1").Range("A1").Value = 123.45 End Sub

After executing this routine, cell A1 on Sheet1 has the value 123.45.

You might want to enter these procedures into a module and experiment with them.

Note

Most objects have a default property. For a Range object, the default property is the Value property. Therefore, you can omit the .Value part from the preceding code, and it has the same effect. However, it's usually considered good programming practice to include the property in your code, even if it is the default property.

The statement that follows accesses the HasFormula and the Formula properties of a Range object.

 If Range("A1").HasFormula Then MsgBox Range("A1").Formula

I use an If-Then construct to display a message box conditionally: If the cell has a formula, then display the formula by accessing the Formula property. If cell A1 does not have a formula, nothing happens.

The Formula property is a read-write property, so you can also specify a formula by using VBA:

 Range("D12").Formula = "=RAND()*100"

Object methods

In addition to properties, objects also have methods. A method is an action that you perform with an object. Here's a simple example that uses the Clear method on a Range object. After you execute this procedure, A1:C3 on Sheet1 is empty and all cell formatting is removed.

 Sub ZapRange()     Worksheets("Sheet1").Range("A1:C3").Clear End Sub

Specifying Arguments for Methods and Properties

An issue that often leads to confusion among new VBA programmers concerns arguments for methods and properties. Some methods use arguments to further clarify the action to be taken, and some properties use arguments to further specify the property value. In some cases, one or more of the arguments are optional.

If a method uses arguments, place the arguments after the name of the method, separated by commas. If the method uses optional arguments, you can insert blank placeholders for the optional arguments. Read on to discover how to insert these placeholders.

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 Workbook 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

Notice that the first argument is omitted and that I specified the placeholder by using a comma.

Another approach, which makes your code more readable, is to use 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, there is no 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 arguments, all of which are optional. Because the Address property returns a value, the following statement is not 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)

The statement could also be written by using a named argument:

 MsgBox Range("A1").Address(rowAbsolute:=False)

These nuances will become clearer as you gain more experience with VBA.

If you'd like to delete the values in a range but keep the formatting, use the ClearContents method of the Range object.

Most methods also take arguments to define the action further. Here's an example that copies cell A1 to cell B1 by using the Copy method of the Range object. In this example, the Copy method has one argument (the destination of the copy). Notice that I use the line continuation character sequence (a space followed by an underscore ) in this example. You can omit the line continuation sequence and type the statement on a single line.

 Sub CopyOne()     Worksheets("Sheet1").Range("A1").Copy _        Worksheets("Sheet1").Range("B1") End Sub