Object Properties and Methods in Excel 2016 VBA Programming - dummies

Object Properties and Methods in Excel 2016 VBA Programming

By John Walkenbach

Although knowing how to refer to objects is important, you can’t do any useful Excel VBA programming by simply referring to an object. To accomplish anything meaningful, you must do one of two things:

  • Read or modify an object’s properties.

  • Specify a method of action to be used with an object.

With literally thousands of properties and methods available, you can easily be overwhelmed. Just remember, you’ll never need to use most of the available properties and methods.

Object properties

Every object has properties. You can think of properties as attributes that describe the object. An object’s properties determine how it looks, how it behaves, and even whether it is visible. Using VBA, you can do two things with an object’s properties:

  • Examine the current setting for a property.

  • Change the property’s setting.

For example, a single-cell Range object has a property called Value. The Value property stores the value contained in the cell. You can write VBA code to display the Value property, or you may write VBA code to set the Value property to a specific value. The following macro uses the VBA built-in MsgBox function to bring up a box that displays the value in cell A1 on Sheet1 of the active workbook:

This message box displays a Range object’s Value property.
This message box displays a Range object’s Value property.
Sub ShowValue()
 Contents = Worksheets(“Sheet1”).Range(“A1”).Value
 MsgBox Contents
End Sub

By the way, MsgBox is a very useful function. You can use it to display results while Excel executes your VBA code.

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.92
End Sub

After Excel executes this procedure, cell A1 on Sheet1 of the active workbook contains the value 994.92. If the active workbook does not 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.

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.Count
End Sub

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”).ClearContents
End 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 was omitted when the Range objects were referenced. This can be done safely because a statement to activate Sheet1 was used. (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. The second statement in the CopyOne procedure could be written like this:

Range(“A1”).Copy Destination:=Range(“B1”)

Notice the little prompt as the statement is being typed. That prompt shows the official name of the argument.

The VBE displays a list of arguments while you type.
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.Add
End Sub

As you may 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 will be the active workbook.

Object events

There is one more topic that you need to know about: events. Objects respond to various events that occur. For example, when you’re working in Excel and you activate a different workbook, a Workbook Activate event occurs. You could, for example, have a VBA macro that is designed to execute whenever an Activate event occurs for a particular Workbook object.

Excel supports many events, but not all objects can respond to all events. And some objects don’t respond to any events. The only events you can use are those made available by the programmers of Microsoft Excel.