Looping Through Ranges Efficiently In Custom Worksheet Functions

Category: VBA Functions | [Item URL]

If you create custom worksheet functions using VBA, this tip describes how to write efficient looping code.

Consider the following custom worksheet function.

Function CountBetween(InRange, Lower, Upper)
    TheCount = 0
    For Each Cell In InRange
        If Cell.Value >= Lower And Cell.Value <= Upper _
          Then TheCount = TheCount + 1
    Next Cell
    CountBetween = TheCount
End Function

This function returns the number of cells in a range that fall between two values. The first argument is a range, the second argument is the lower comparison value, and the third argument is the upper comparison value. If you wanted to count the number of values between 1 and 5 in the range A1:A20, you could use this formula:

  =CountBetween(A1:A20,1,5)

This function works fine in most situations. However, try entering the following formula and see what happens:

  =CountBetween(A:A,1,5)

You'll find that evaluating this function seems to take forever since it will loop through all cells in the range -- even those that are beyond the worksheet's "used range."

My original approach to solving this problem was to use the SpecialCells method to create a subset of the input range that consisted only of nonempty cells. However, I discovered that SpecialCells is off-limits inside of a worksheet function.

I eventually learned the solution. The function below uses the Intersect function to create a new range object that consists of the intersection of the UsedRange and the input range.

Function CountBetween2(InRange, Lower, Upper)
    Set SubSetRange = Intersect(InRange.Parent.UsedRange, InRange)
    TheCount = 0
    For Each Cell In SubSetRange
        If Cell.Value >= Lower And Cell.Value <= Upper Then _
            TheCount = TheCount + 1
    Next Cell
    CountBetween2 = TheCount
End Function

The addition of the Set statement solves the problem. You'll find that this function works equally fast with either of these formulas:

  =CountBetween(A1:A20,1,5)
  =CountBetween(A:A,1,5)

This technique can be adapted to any custom worksheet function that accepts a range argument and loops through each cell in the range.