Microsoft Office Tutorials and References
In Depth Information
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 effi-
cient:
Function FORMULACOUNT(rng)
cnt = 0
Set WorkRange = Intersect(rng, rng.Parent.UsedRange)
If WorkRange Is Nothing Then
FORMULACOUNT = 0
Exit Function
End If
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. Note the If-Then construct that
checks if the WorkRange is Nothing. That will be the case if the argument for the function is outside of the used
range. In such a case, the function returns 0, and execution ends.
Search JabSto ::




Custom Search