Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Many of the custom functions that you develop will work with the data contained in a cell or in a range of cells. Recognize that a range can be a single cell or a group of cells. This section describes some key concepts to make this task easier. The information in this section is intended to be practical, rather than comprehensive. If you want more details, consult the online help.

The For Each-Next Construct

Your Function procedures often need to loop through a range of cells. For example, you may write a function that accepts a range as an argument. Your code needs to examine each cell in the range and do something. The For Each-Next construct is very useful for this sort of thing. The syntax of the For Each-Next construct is

For Each element In group [instructions] [Exit For] [instructions] Next [element]

The following Function procedure accepts a range argument and returns the sum of the squared values in the range:

Function SUMOFSQUARES(rng as Range) Dim total as Double Dim cell as Range total = 0 For Each cell In rng total = total + cell ^ 2 Next cell SUMOFSQUARES = total End Function

The following is a worksheet formula that uses the SumOfSquares function:

=SumOfSquares(A1:C100)

In this case, the function's argument is a range that consists of 300 cells.

Referencing a Range

VBA code can reference a range in a number of different ways:

• Using the Range property

• Using the Cells property

• Using the Offset property

THE RANGE PROPERTY

You can use the Range property to refer to a range directly by using a cell address or name. The following example assigns the value in cell A1 to a variable named Init. In this case, the statement accesses the range's Value property.

Init = Range("A1").Value

In addition to the Value property, VBA enables you to access a number of other properties of a range. For example, the following statement counts the number of cells in a range and assigns the value to the Cnt variable:

Cnt = Range("A1:C300").Count

The Range property is also useful for referencing a single cell in a multicell range. For example, you may create a function that is supposed to accept a single-cell argument. If the user specifies a multicell range as the argument, you can use the Range property to extract the upper-left cell in the range. The following example uses the Range property (with an argument of "A1") to return the value in the upper-left cell of the range represented by the cell argument.

Function Square(cell as Range) CellValue = cell.Range("A1").Value Square = CellValue ^ 2 End Function

Assume that the user enters the following formula:

=Square(C5:C12)

The Square function works with the upper-left cell in C5:C12 (which is C5) and returns the value squared.

THE CELLS PROPERTY

Another way to reference a range is to use the Cells property. The Cells property accepts two arguments (a row number and a column number), and returns a single cell. The following statement assigns the value in cell A1 to a variable named FirstCell:

FirstCell = Cells(1, 1).Value

The following statement returns the upper-left cell in the range C5:C12:

UpperLeft = Range("C5:C12").Cells(1,1)

TotalCells = Cells.Count

The following statement uses the Excel COUNTA function to determine the number of nonempty cells in the worksheet:

NonEmpty =WorksheetFunction.COUNTA(Cells)

THE OFFSET PROPERTY

The Offset property (like the Range and Cells properties) also returns a Range object. The Offset property is used in conjunction with a range. It takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or right), negative (up or left), or zero. The following example returns the value one cell below cell A1 (that is, cell A2) and assigns it to a variable named NextCell:

NextCell = Range("A1").Offset(1,0).Value

The following Function procedure accepts a single-cell argument and returns the sum of the eight cells that surround it:

Function SumSurroundingCells(cell) Dim Total As Double Dim r As Integer, c As Integer Total = 0 For r = -1 To 1 For c = -1 To 1 Total = Total + cell.Offset(r, c) Next c Next r SumSurroundingCells = Total - cell End Function

This function uses a nested For-Next loop. So, when the r loop counter is –1, the c loop counter goes from –1 to 1. Nine cells are summed, including the argument cell, which is Offset(0, 0). The final statement subtracts the value of the argument cell from the total.

The function returns an error if the argument does not have eight surrounding cells (for example, if it's in row 1 or column 1).

To better understand how the nested loop works, following are nine statements that perform exactly the same calculation:

Total = Total + cell.Offset(-1, -1) ' upper left Total = Total + cell.Offset(-1, 0) 'left Total = Total + cell.Offset(-1, 1) 'upper right Total = Total + cell.Offset(0, -1) 'above Total = Total + cell.Offset(0, 0) 'the cell itself Total = Total + cell.Offset(0, 1) 'right Total = Total + cell.Offset(1, -1) 'lower left Total = Total + cell.Offset(1, 0) 'below Total = Total + cell.Offset(1, 1) 'lower right

Some Useful Properties of Ranges

Previous sections in this chapter give you examples that used the Value property for a range. VBA gives you access to many additional range properties. Some of the more useful properties for function writers are briefly described in the following sections. For complete information on a particular property, refer to Excel's online help.

THE FORMULA PROPERTY

The Formula property returns the formula (as a string) contained in a cell. If you try to access the Formula property for a range that consists of more than one cell, you get an error. If the cell does not have a formula, this property returns a string, which is the cell's value as it appears in the formula bar. The following function simply displays the formula for the upper-left cell in a range:

Function CELLFORMULA(cell) CELLFORMULA = cell.Range("A1").Formula End Function

You can use the HasFormula property to determine whether a cell has a formula.

THE ADDRESS PROPERTY

The Address property returns the address of a range as a string. By default, it returns the address as an absolute reference (for example, $A$1:$C$12). The following function, which is not all that useful, returns the address of a range:

Function RANGEADDRESS(rng) RANGEADDRESS = rng.Address End Function

For example, the following formula returns the string $A$1:$C$3:

=RANGEADDRESS(A1:C3)

The formula below returns the address of a range named MyRange:

=RANGEADDRESS(MyRange)

THE COUNT PROPERTY

The Count property returns the number of cells in a range. The following function uses the Count property:

Function CELLCOUNT(rng) CELLCOUNT = rng.Count End Function

The following formula returns 9:

=CELLCOUNT(A1:C3)

THE PARENT PROPERTY

The Parent property returns an object that corresponds to an object's container object. For a Range object, the Parent property returns a Worksheet object (the worksheet that contains the range).

The following function uses the Parent property and returns the name of the worksheet of the range passed as an argument:

Function SHEETNAME(rng) SHEETNAME = rng.Parent.Name End Function

The following formula, for example, returns the string Sheet1:

=SHEETNAME(Sheet1!A16)

THE NAME PROPERTY

The Name property returns a Name object for a cell or range. To get the actual cell or range name, you need to access the Name property of the Name object. If the cell or range does not have a name, the Name property returns an error.

The following Function procedure displays the name of a range or cell passed as its argument. If the range or cell does not have a name, the function returns an empty string. Note the use of On Error Resume Next. This handles situations in which the range does not have a name.

Function RANGENAME(rng) On Error Resume Next RANGENAME = rng.Name.Name If Err.Number <> 0 Then RANGENAME = "" End Function

THE NUMBERFORMAT PROPERTY

The NumberFormat property returns the number format (as a string) assigned to a cell or range. The following function simply displays the number format for the upper-left cell in a range:

Function NUMBERFORMAT(cell) NUMBERFORMAT = cell.Range("A1").NumberFormat End Function

THE FONT PROPERTY

The Font property returns a Font object for a range or cell. To actually do anything with this Font object, you need to access its properties. For example, a Font object has properties such as Bold, Italic, Name, Color, and so on. The following function returns TRUE if the upper-left cell of its argument is formatted as bold:

Function ISBOLD(cell) ISBOLD = cell.Range("A1").Font.Bold End Function

Function ISREDBKGRD(cell) ISREDBKGRD = cell.Range("A1").Interior.Color = vbRed End Function

THE COLUMNS AND ROWS PROPERTIES

The Columns and Rows properties work with columns or rows in a range. For example, the following function returns the number of columns in a range by accessing the Count property:

Function COLUMNCOUNT(rng) COLUMNCOUNT = rng.Columns.Count End Function

THE ENTIREROW AND ENTIRECOLUMN PROPERTIES

The EntireRow and EntireColumn properties enable you to work with an entire row or column for a particular cell. The following function accepts a single cell argument and then uses the EntireColumn property to get a range consisting of the cell's entire column. It then uses the Excel COUNTA function to return the number of nonempty cells in the column.

Function NONEMPTYCELLSINCOLUMN(cell) NONEMPTYCELLSINCOLUMN = WorksheetFunction.CountA(cell.EntireColumn) End Function

You cannot use this function in a formula that's in the same column as the cell argument. Doing so will generate a circular reference.

THE HIDDEN PROPERTY

The Hidden property is used with rows or columns. It returns TRUE if the row or column is hidden. If you try to access this property for a range that does not consist of an entire row or column, you get an error. The following function accepts a single cell argument and returns TRUE if either the cell's row or the cell's column is hidden:

Function CELLISHIDDEN(cell) If cell.EntireRow.Hidden Or cell.EntireColumn.Hidden Then CELLISHIDDEN = True Else CELLISHIDDEN = False End If End Function

You can also write this function without using an If-Then-Else construct. In the following function, the expression to the right of the equal sign returns either TRUE or FALSE-and this value is assigned returned by the function:

Function CELLISHIDDEN(cell) CELLISHIDDEN = cell.EntireRow.Hidden Or _ cell.EntireColumn.Hidden End Function

The Set Keyword

An important concept in VBA is the ability to create a new Range object and assign it to a variable-more specifically, an object variable. You do so by using the Set keyword. The following statement creates an object variable named MyRange:

Set MyRange = Range("A1:A10")

After the statement executes, you can use the MyRange variable in your code in place of the actual range reference. Examples in subsequent sections help to clarify this concept.

The Intersect Function

The Intersect function returns a range that consists of the intersection of two other ranges. For example, consider the two ranges selected in Figure 24-2. These ranges, D3:D10 and B5:F5, contain one cell in common (D5). In other words, D5 is the intersection of D3:D10 and B5:F5.

Figure 24-2: Use the Intersect function to work with the intersection of two ranges.

The following Function procedure accepts two range arguments and returns the count of the number of cells that the ranges have in common:

Function CELLSINCOMMON(rng1, rng2) Dim CommonCells As Range On Error Resume Next Set CommonCells = Intersect(rng1, rng2) If Err.Number = 0 Then CELLSINCOMMON = CommonCells.CountLarge Else CELLSINCOMMON = 0 End If End Function

The CELLSINCOMMON function uses the Intersect function to create a range object named CommonCells. Note the use of On Error Resume Next. This statement is necessary because the Intersect function returns an error if the ranges have no cells in common. If the error occurs, it is ignored. The final statement checks the Number property of the Err object. If it is 0, no error occurs, and the function returns the value of the CountLarge property for the CommonCells object. If an error does occur, Err.Number has a value other than 0, and the function returns 0.

The Union Function

The Union function combines two or more ranges into a single range. The following statement uses the Union function to create a range object that consists of the first and third columns of a worksheet:

Set TwoCols = Union(Range("A:A"), Range("C:C"))

The Union function takes between 2 and 30 arguments.

The UsedRange Property

The UsedRange property returns a Range object that represents the used range of the worksheet. Press Ctrl+End to activate the lower-right cell of the used range. The UsedRange property can be very useful in making your functions more efficient.

Consider the following Function procedure. This function accepts a range argument and returns the number of formula cells in the range.

Function FORMULACOUNT(rng) cnt = 0 For Each cell In rng If cell.HasFormula Then cnt = cnt + 1 Next cell FORMULACOUNT = cnt End Function

In many cases, the preceding function works just fine. But what if the user enters a formula like this one?

=FORMULACOUNT(A:C)

The three-column argument consists of 3,145,728 cells. With an argument that consists of one or more entire columns, the function does not work well because it loops through every cell in the range, even those that are well beyond the area of the sheet that's actually used. The following function is rewritten to make it more efficient:

Function FORMULACOUNT(rng) cnt = 0 Set WorkRange = Intersect(rng, rng.Parent.UsedRange) For Each cell In WorkRange If cell.HasFormula Then cnt = cnt + 1 Next cell FORMULACOUNT = cnt End Function

This function creates a Range object variable named WorkRange that consists of the intersection of the range passed as an argument and the used range of the worksheet. In other words, WorkRange consists of a subset of the range argument that only includes cells in the used range of the worksheet.