# 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.

### Search for Tips

### All Tips

### Browse Tips by Category

### 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