7. Members of Objects

7.1 Properties

As discussed in the previous chapter, the properties of an object refer to characteristics of an object. Each object will have a set of properties appropriate to that particular object. An example of a property is Name for a Worksheet, which is the name shown on the tab for the worksheet. Some properties are "read-only," which means you can get (read) the property's value, but not set or change (write) it. Most properties are read/write, and so the value of the property can be obtained, set or changed.

In addition to the characteristics of an object, the properties of an object include the Collections of other objects that belong to the object. That is why we can reference a collection by dot notation, e.g., ActiveWorkbook.Sheets.

7.2 Getting Properties of an Object

When we access a property of an object, we get the value of the property. In programmer-speak, we say the value of the property is "returned." Most properties return a value, such as a number. For example, if the height of row 1 is 12, the expression Rows(1).Rowheight would return 12.

Some properties return an object. In fact, a property that refers to a collection really returns a collection object. For example, when we access the Sheets property of a workbook by using the expression ActiveWorkbook.Sheets, it returns a Sheets object that is a collection of all the sheets in the workbook. The practical importance of this is that by getting the returned collection object, you have access to that collection object's properties and methods, as in Sheets.Count.

7.3 Setting Properties of an Object

In VBA you set a value for a property of an object by assignment. The equal sign ("=") is used as the operator for assignment. The value on the right is assigned to the property on the left. This process is the equivalent of entering/selecting a value on a dialog box in the program, such as for the width of a column.

If you think of what you do when you use a dialog box in a program, you see that there are several different ways to enter a value (and thus set a property). An edit field represents a value the user enters in the edit field, such as row height; a checkbox represents true/false, such as wrap text for a cell; and a drop-down list represents a choice from a restricted set of values, such as horizontal alignment for a cell.

Examples of assigning a value to a property follow (the ActiveCell.HorizontalAlignment example requires a bit more explanation below, since it uses an enumeration, xlcenter).

Rows(5).RowHeight = 20

ActiveCell.WrapText = True

ActiveCell.HorizontalAlignment = xlCenter

Since we can get the value of a property, we can use the obtained value in setting a property elsewhere. For example, if we want to set the height of row 5 to the same height as row 8, we can code Rows(5).RowHeight = Rows(8).RowHeight. Note that this does not "link" the row heights. After the assignment statement is executed, subsquently changing the height of row 8 has no effect on the height of row 5.

The above assignment statement demonstrates the principle that you can use a property wherever you can use the value that the property returns (as in a condition expression, covered later in this tutorial).

7.4 Enumerations

Many object properties can be assigned a value only from a restricted set of values, such as those that are available from a drop-down list in a dialog box. For example, the vertical alignment of a cell can only be top, bottom, center, justify or distributed. Each of these choices is actually stored in the program as a number representing the value chosen. The number that represents each possible value never varies, so it is known as a "constant."

You can use this number to set the value of the property, but numbers are hard to remember so they are almost never used. To make things simpler, VBA provides a verbal representation for each value for a property that has a limited set of possible values. These verbal representations are called enumerations. The macro code produced by recording a macro will have enumerations rather than numeric values for properties where the set of possible values is limited.

Enumerations in VBA for Excel start with the characters "xl". Most enumerations are easily understandable, as in the example above where "xlCenter" represents the value for "center" for alignment of text in a cell. When you are writing macros rather than recording them, you will need to include enumerations where necessary. Unfortunately, finding the specific enumeration you need is not always as easy as it should be in VBA Help. Usually it is simplest to record a macro, and then look at the resulting code to obtain the enumeration.

7.5 Methods

As discussed in the previous chapter, the methods of an object refer to actions that an object can take, or actions that can be done to it. Each object will have a set of methods appropriate to that particular object. An example of a method is ActiveWorkbook.Close. Using a object's method in a macro is referred to as "calling" or "invoking" the method.

7.6 Parameters and Arguments

Some methods need information about how the action should be performed. For example, when the Add method of the Sheets collection is called, the method needs to know what kind of sheet to add to the workbook, where in the workbood to add them, and how many sheets to add. In some cases providing the information to the method is obligatory; in others explicitly providing the information is optional, and if you do not provide the information a default value will be used. In programmer-speak, providing the values a method uses is called "passing" values to the method. The values that are passed to a method are called its arguments.

You will sometimes see the term parameter used to refer to the values passed to a method. For the purposes of this tutorial the distinction between parameters and arguments is not crucial, but there is a difference. Parameters is the term used to describe the values to be passed, when the method is defined. When you look at the documentation for a method in Help, the method will be described in general terms, and its parameters will be given. When the method is called, the values passed to the method are its arguments. Another way of looking at it is that each item of information you provide has a name, called a parameter, and a value, called an argument.

We can use the following code as an illustration:

Sheets.Add Before:=Sheets(2), Count:=3

Looking at the Sheets.Add method, we find it has four parameters, named "Before," "After," "Count," and "Type." (The VBA documentation will give the data types for the arguments to be passed for each of the parameters.) All of the parameters of the Add method are optional. The documentation tells us that the default value for Count is 1, and the default value for Type is xlWorksheet (rather than a chart). The documentation also tells us that if both Before and After are omitted, a new worksheet will be added before the active sheet.

In the example above, we see that arguments are passed by using a colon and equal sign (":="); multiple arguments are separated by commas. Since the Type parameter was omitted, the method will use the default value for that parameter (xlWorksheet). The parameter After is omitted since we are using the Before parameter. Thus, the example code above will add 3 worksheets before the second worksheet in the active workbook.

Macro TUT2 that you recorded earlier should show the command Sheets.Add. Inserting a worksheet manually always inserts one worksheet before the active sheet. No arguments need to be passed to the Add method, since using all the default values causes the same result.

Strictly speaking, using the parameter names with passed arguments is optional. But if you do not use named arguments your code becomes much harder to read, you have to use placeholder commas to indicate an omitted optional argument, and you must pass the arguments in the order specified in the function definition. These disadvantages outweight the slight saving in time and effort in omitting the names. The moral is, always use named arguments for object methods.

7.7 Return Values of Methods

Some object methods not only perform an action or have an action performed on them, but also return something, either a return value or an object. For example, the Add method of the Workbooks object returns a reference to the new workbook created. This reference can be assigned to a variable, as discussed in Chapter 10 of this tutorial.