VBA Programming Series...33

Some Useful Range Object Properties

A Range object has dozens of properties. You can write VBA programs non-stop for the next 10 years and never use them all. In this section, I briefly describe some of the more commonly used Range properties. For complete details, consult the Help system in the VBE.

Some Range properties are read-only properties, which means that you can have your code look at their values, but you can’t have it change them (“look, but don’t touch”). For example, every Range object has an Address property (which holds the range’s address). You can access this read-only property, but you can’t change it.


By the way, the examples that follow are typically statements rather than complete procedures. If you’d like to try any of these (which you should), create a Sub procedure to do so. Also, many of these statements work prop-erly only if a worksheet is the active sheet.

The Value property

The Value property represents the value contained in a cell. It’s a read-write property, so your VBA code can either read or change the value.

The following statement displays a message box that shows the value in cell

A1 on Sheet1:

MsgBox Worksheets(“Sheet1”).Range(“A1”).Value

It stands to reason that you can read the Value property only for a single-cell

Range object. For example, the following statement generates an error:

MsgBox Worksheets(“Sheet1”).Range(“A1:C3”).Value

You can, however, change the Value property for a range of any size. The fol-lowing statement enters the number 123 into each cell in a range:

Worksheets(“Sheet1”).Range(“A1:C3”).Value = 123

Value is the default property for a Range object. In other words, if you omit a property for a Range, Excel uses its Value property. The following statements both enter a value of 75 into cell A1 on Sheet1:

Worksheets(“Sheet1”).Range(“A1”).Value = 75 Worksheets(“Sheet1”).Range(“A1”) = 75

The Text property

The Text property returns a string that represents the text as displayed in a cell — the formatted value. The Text property is read-only. For example, sup-pose that cell A1 contains the value 12.3 and is formatted to display two deci-mals and a dollar sign ($12.30). The following statement displays a message box containing $12.30:

MsgBox Worksheets(“Sheet1”).Range(“A1”).Text

But the next statement displays a message box containing 12.3:

MsgBox Worksheets(“Sheet1”).Range(“A1”).Value

If the cell contains a formula, the Text property returns the result of the for-mula. If a cell contains text, then the Text property and the Value property will always return the same thing, because text (unlike a number) can’t be formatted to display differently.

The Count property

The Count property returns the number of cells in a range. It counts all cells, not just the nonblank cells. Count is a read-only property (think about it for a second, and you’ll understand why). The following statement accesses a range’s Count property and displays the result (9) in a message box:

MsgBox Range(“A1:C3”).Count

The Column and Row properties

The Column property returns the column number of a single-cell range. It’s sidekick, the Row property, returns the row number of a single-cell range. Both are read-only properties. For example, the following statement displays 6 because cell F3 is in the sixth column:

MsgBox Sheets(“Sheet1”).Range(“F3”).Column

The next expression displays 3 because cell F3 is in the third row:

 

MsgBox Sheets(“Sheet1”).Range(“F3”).Row

If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range, and the Row prop-erty returns the row number of the first row in the range.

Don’t confuse the Column and Row properties with the Columns and Rows properties. The Column and Row properties return a single value. Columns and Rows properties return a Range object. What a difference an “s” makes.

The Address property

Address, a read-only property, displays the cell address for a Range object in absolute notation (a dollar sign before the column letter and before the row number). The following statement displays the message box shown in Figure.

MsgBox Range(Cells(1, 1), Cells(5, 5)).Address

The HasFormula property

The HasFormula property (which is read-only) returns True if the single-cell range contains a formula. It returns False if the cell does not have a formula. If the range consists of more than one cell, VBA returns True only if all cells in the range contain a formula, or False if all cells in the range don’t have a formula. The property returns a Null if there is a mixture of formulas and nonformulas in the range. Null is kind of a no-man’s land: The range contains a mixture of formulas and values.

You need to be careful when you work with properties that can return Null.

More specifically, the only data type that can deal with Null is Variant.

For example, assume that cell A1 contains a value and cell A2 contains a for-mula. The following statements generate an error because the range doesn’t consist of all formulas or all nonformulas:

Dim FormulaTest As Boolean

FormulaTest = Range(“A1:A2”).HasFormula

The Boolean data type can handle only True or False. Null causes it to complain and throw up an error message. To fix this type of situation, the best thing to do is make sure that the FormulaTest variable is declared as a Variant rather than as a Boolean. The following example uses VBA’s handy TypeName function (along with an If-Then statement) to determine the data type of the FormulaTest variable. If the range has a mixture of formulas and nonformulas, the message box displays Mixed!

Dim FormulaTest As Variant

FormulaTest = Range(“A1:A2”).HasFormula

If TypeName(FormulaTest) = “Null” Then MsgBox “Mixed!”

The Font property

As I note earlier in previous article (see “The Cells property”), a property can return an object. The Font property of a Range object is another example of that concept at work. The Font property returns a Font object.

A Font object, as you may expect, has many accessible properties. To change some aspect of a range’s font, you must first access the range’s Font object and then manipulate the properties of that object. This may be confusing, but maybe this example will help.

The following statement uses the Font property of the Range object to return a Font object. Then the Bold property of the Font object is set to True. In plain English, this makes the cell display in boldface:

Range(“A1”).Font.Bold = True

Truth is, you don’t really need to know that you’re working with a special Font object that’s contained in a Range object. As long as you use the proper syntax, it will work just fine. Often, recording your actions while you record a macro will tell you everything you need to know about the proper syntax.

The Interior property

Here’s yet another example of a property that returns an object. A Range object’s Interior property returns an Interior object (strange name, but that’s what it’s called). This type of object referencing works the same way as the Font property (which I describe in the preceding section).

For example, the following statement changes the Color property of the

Interior object contained in the Range object:

Range(“A1”).Interior.Color = 8421504

In other words, this statement changes the cell’s background to middle gray. What’s that? You didn’t know that 8421504 is middle gray? For some insights into Excel’s wonderful world of color, see the sidebar, “A quick & dirty color primer.”

The Formula property

The Formula property represents the formula in a cell. This is a read-write property, so you can access it to insert a formula into a cell. For example, the following statement enters a SUM formula into cell A13:

Range(“A13”).Formula = “=SUM(A1:A12)”

Notice that the formula is a text string and is enclosed in quotation marks.

If the formula itself contains quotation marks, things get a bit tricky. For example, let’s say you want to insert this formula by using VBA:

=SUM(A1:A12)&” Stores”

This formula displays a value, followed by the word Stores. To make this for-mula acceptable, you need to replace every quotation mark in the formula with two quotation marks. Otherwise, VBA will get confused, and claim that there’s a syntax error (because there is!). So here’s a statement that will enter a formula that contains quotes:

Range(“A13”).Formula = “=SUM(A1:A12)&”” Stores”””

By the way, you can access a cell’s Formula property even if the cell doesn’t have a formula. If a cell has no formula, the Formula property returns the same as its Value property.

If you need to know whether a cell has a formula, use the HasFormula property.

Be aware that VBA “speaks” U.S. English. This means that in order to put a formula in a cell, you must use the U.S. syntax. To use your own locale formula syntax in VBA, check the FormulaLocal property.

The NumberFormat property

The NumberFormat property represents the number format (expressed as a text string) of the Range object. This is a read-write property, so your VBA code can change the number format. The following statement changes the number format of column A to a percent with two decimal places:

Columns(“A:A”).NumberFormat = “0.00%”

Follow these steps to see a list of other number formats. Better yet, turn on the macro recorder while you do this:

1. Activate a worksheet.

2. Access the Format Cells dialog box by pressing Ctrl+1.

3. Click the Number tab.

4. Select the Custom category to view and apply some additional number format strings.