Properties are the physical characteristics of objects, and can be measured or quantified. You and I have a height property, an age property, a bank balance property, and a name property. Some of our properties can be changed fairly easily, such as our bank balance. Other properties are more difficult or impossible to change, such as our name and age.

A worksheet Range object has a RowHeight property and a ColumnWidth property. A Workbook object has a Name property, which contains its filename. Some properties can be changed easily, such as the Range object's ColumnWidth property, by assigning the property a new value. Other properties, such as the Workbook object's Name property, are read-only. You can't change the Name property by simply assigning a new value to it.

You refer to the property of an object by referring to the object, then the property, separated by a period. For example, to change the width of the column containing the active cell to 20 points, you would assign the value to the ColumnWidthproperty of the ActiveCell using:

ActiveCell.ColumnWidth = 20

To enter the name Florence into cell C10, you assign the name to the Value property of the Range object:

Range("C10").Value = "Florence" If the Range object is not in the active worksheet in the active workbook, you need to be more specific: Workbooks("Sales.xls").Worksheets("DataInput").Range("C10").Value = 10

VBA can do what is impossible to do manually. It can enter data into worksheets that are not visible on the screen. It can copy and move data without having to make the sheets involved active. Therefore, it is very seldom necessary to activate a specific workbook, worksheet, or range to manipulate data using VBA. The more you can avoid activating objects, the faster your code will run. Unfortunately, the macro recorder can only record what you do and uses activation extensively.

In the previous examples, you have seen how to assign values to the properties of objects. You can also assign the property values of objects to variables or to other objects' properties. You can directly assign the column width of one cell to another cell on the active sheet, using:

Range("C1").ColumnWidth = Range("A1").ColumnWidth

You can assign the value in C1 in the active sheet to D10 in the sheet named Sales, in the active workbook, using:

Worksheets("Sales").Range("D10").Value = Range("C1").Value

You can assign the value of a property to a variable so it can be used in later code. This example stores the current value of cell M100, sets M100 to a new value, prints the auto-recalculated results, and sets M100 back to its original value:

OpeningStock = Range(


Range("M100").Value =



Range("M100").Value =


Some properties are read-only, which means that you can't assign a value to them directly. Sometimes there is an indirect way. One example is the Text property of a Range object. You can assign a value to a cell using its Value property, and you can give the cell a number format using its NumberFormat property. The Text property of the cell gives you the formatted appearance of the cell. The following example displays $12,345.60 in a Message box:

Range("B10").Value = 12345.6 Range("B10").NumberFormat = "$#,##0.00" MsgBox Range("B10").Text

This is the only means by which you can set the value of the Text property.

0 0

Post a comment