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.

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers