Properties are essentially the characteristics of an object, which are similar to adjectives, which describe nouns. For example, the Worksheet object has a sheet name property that can be changed, and a Rows.Count row property that cannot. You refer to the property of an object by referring to the object, and then the property.

Some properties are read-only, which means that you can’t assign a value to them directly. The Text property of a cell gives you the formatted appearance of value in a cell, but you cannot overwrite or change it. Some properties have arguments that further specify the property value. Below are some examples of VBA code.

Sheets("Sheet1").Name = "MySheet"  ' rename the sheet
' return the Address of cell A1 as absolute reference ($A$1)
MsgBox Range("A1").Address(RowAbsolute:=True, ColumnAbsolute:=True)

Properties for the active object

• Only one workbook at a time can be active.
• In that workbook, onlyone sheet can be active.
• A “sheet” can be either a worksheet or a chart sheet
• And if the sheet is a worksheet, one cell is the active cell (even if a multicell range is selected).

VBA knows about active workbooks, worksheets, and cells and lets you refer to these active objects in a simplified manner. This method of referring to objects is often useful because you won’t always know the exact workbook, worksheet, or range on which you want to operate. VBA makes object referencing easy by providing properties of the Application object. For example, the Application object has an ActiveCell property that returns a reference to the active cell. The following instruction assigns the value 1 to the active cell:

ActiveCell.Value = 1

Here is another example.

Sub TypeNumberTwo()
   ActiveCell.Value = 2
   MsgBox (ActiveCell.Value & " is the value.")
End Sub

In the preceding example, we omitted the reference to the Application object and to the active worksheet because both are assumed. This instruction will fail if the active sheet isn’t a worksheet. For example, if VBA executes this statement when a chart sheet is active, the procedure halts, and you get an error message. If a range is selected in a worksheet, the active cell is a cell within the selected range. In other words, the active cell is always a single cell (never a multicell range). page 45.

Some Properties of the Application Object

Named Arguments

Consider the Protect method for a workbook object. The Protect method takes three arguments: password, structure, and windows. You may want to specify all three arguments. If you only wan to specify the last two arguments, what do you do with the password? You could omit it and just add the comma, or you could use named arguments, which makes you code more readable. Using named arguments is a best practice, especially for methods that have many optional arguments and 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.

Workbooks("MyExcel.xlsx").Protect "secret", True, False
Workbooks("MyExcel.xlsx").Protect , True, False
Workbooks("MyExcel.xlsx").Protect Structure:=True, Windows:=False

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. The line below returns $A1.

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