Using Custom Functions in Dynamic Ranges
Named ranges are great. They enable us to assign a name to a collection of cells and use that name in code and worksheet functions. However, named ranges tend to be static. That is, they tend to represent a fixed set of cells, such as, A1:E100. But sometimes it would be useful to have the range's set of cells be dynamic. Instead of the range representing a fixed set of cells, we would like to dynamically determine which cells are included in the range based on some custom logic.
Excel already lets us do this. It allows us to embed functions inside the definition of a named range and have the functions determine on-the-fly which cells are included in the range. But it's not just native Excel functions (like IF, OFFSET, etc.) that we can embed into a named range defintion. We can also embed our own, custom functions; we can write a custom function that uses our own application logic to dynamically determine at calculation time which cells are in a named range.
This gives us the capability to point the named range at different cells without actually changing the named range's definition.
In this posting, I'll talk about dynamic ranges and embedding custom functions into named range defintions.
Getting Started with Dynamic Ranges
Let's take a simple example. Let's say that we have a column of numbers in the range 'Example 1'!$C$1:$C$100 and we want to find the cells in this range that are higher than some value, X = 34,999.
Using dynamic ranges, we can create a named range (called, say, Range 1) with a definition of:
=GreaterThan('Example 1'!$C$1:$C$100, 34999)
We also define a UDF called GreaterThan as:
Public Function GreaterThan(Rng As Range, Limit As Long) As Range
Dim Cell As Range
Dim ResultRange As Range
For Each Cell In Rng
If Cell.Value2 > Limit Then
If ResultRange Is Nothing Then
Set ResultRange = Cell
Else
Set ResultRange = Application.Union(ResultRange, Cell)
End If
End If
Next
Set GreaterThan = ResultRange
End Function
The UDF iterates over the source range testing each cell. If the cell meets our criteria (in this case its value being greater than 34999) then it is added into a temporary range object. Once the code has iterated over all the cells, the temporary range is returned to the caller. This range represents the set of cells the named range now refers to.
(Note: we're not coping with any runtime errors in this code ... which we should do in a proper version).
We can use the named range just like any other named range and Excel will call our UDF as and when it needs to. So, we could calculate the average of our range by using normal worksheet functions, such as:
=AVERAGE(Range1)
We can visually confirm the range that our UDF returned using the Name Manager. If we open up the Name Manager and select Range 1 and then click inside the 'Refers to:' textbox, Excel invokes our UDF and highlights the cells on the worksheet that our UDF returned.
Recalculating the Range when Data Changes
Excel knows that our dynamic range depends on $C$1:$C$100. This is because we pass $C$1:$C$100 to the UDF call in the named range definition.
This enables Excel to properly calculate our named range during worksheet calculations.
In our example, the cell $E$3 (which contains the formula =AVERAGE(Range1)) depends on Range 1 and Range 1 depends on $C$1:$C$100. Therefore, if $C$1:$C$100 changes, Excel knows that it needs to recalculate Range 1 in order to recalculate $E$3.
So, if we change the value of $C$1 from 40000 to 20000 Excel knows that it needs to recalculate $E$3 and, hence, invokes our UDF. Our UDF determines which cells Range 1 refers to and deems that $C$1 is no longer part of Range 1 (because its value is no longer > 34999); thus the value of $E$3 changes, as shown below.
We can also confirm that Range1 is comprised of different cells by inspecting Range1's address in the VBA Immediate window (this also illustrates that we can use the dynamic named range in VBA just like we can use a 'normal' named range).
A Note about Interacting with the Calc Tree
If our UDF didn't declare its source data range as a parameter then Excel would not be able to call it at the appropriate times during recalculation.
For example, if our UDF simply iterated through $C$1:$C$100 without declaring this range as a parameter then, when $C$1:$C$100 is changed, $E$3 wouldn't be recalculated.
This is because Excel would not know that $E$3 depends on $C$1:$C$100 because that dependency would be hidden inside the UDF.
There may circumstances when we don't care about this. But it is worth remembering that Excel needs to know about any worksheet dependencies our UDF has in order for our dynamic range to interact with the calculation tree properly.
Making More Use of Dynamic Ranges
Our GreaterThan UDF is about as basic as could be, but, it illustrates the technique. It doesn't, however, take much imagination to see how this technique could be used in more sophisticated ways.
For example, inside our UDF we could make use of external data, such as our company's databases, and use the logic and/or data stored in them to determine the contents of the dynamic range in Excel.
One example could be that we have a list of dates in our worksheet and we want to highlight the dates on which somebody downloaded a trial version of our product from our web site. This would make use of the same technique described here, except, in the loop in our UDF, we would make a call to the database to determine whether that cell should or should not be included in the range.
In other words, using UDFs in dynamic ranges enables us to incorporate external data and external components into our range definitions.
Another use is: search. We could write a fairly sophisticated custom, search feature that is built on top of dynamic ranges and UDFs.
Concerns About Execution Speed
I'm afraid I've left the bad news until the end...
Implementing dynamic ranges exactly as I have described here could hand you serious performance issues.
To illustrate this, let's change the definition of Range 1 from:
=GreaterThan('Example 1'! $C$1:$C$100, 34999)
to:
=GreaterThan('Example 1'! $C:$C, 34999).
Now, we are checking for cells greater than 34999 in the entire C column. In Excel 2007, that's 1,048,576 cells. On my PC (which is a 2.66 GHz dual core with 3GB RAM - not that dual core helps here anyway), calculating the range once takes 3 seconds. If we referenced Range 1 in only a handful of places in our workbook or VBA, our workbook would become fairly unusable fairly quickly.
It would be interesting to see if implementing our UDF in an XLL would improve the speed substantially - (something I'll look into).
Also, if our UDF did fancier things (like calling into an external database) then you can just imagine what would happen when we're iterating over 1,048,576 cells ....
So, it's worth stating that we should exercise some common sense and some judgement when using dynamic ranges. However, if we restrict our usage to suitably sized source ranges, dynamic ranges are a useful tool indeed (where suitably sized is something we each have to define for ourselves).
Summary
- Named range definitions can contain native and custom functions;
- A custom function used as a named range definition should return the range that it determines the named range represents;
- To interact properly with Excel's calculation tree, all worksheet dependencies (such as the source range) should be included as parameters to the custom function;
- Dynamic ranges can be used to build some fairly useful and sophisticated extensions;
- Speed when iterating over large ranges is not good and we should employ careful judgment when using dynamic ranges on potentially large source ranges;
You can download the workbook I used for this post in the list of attachments below.