Self Extending UDFs – Part 1

If you’re an advanced Excel user then I’m sure that at some point you’ve become frustrated with the clumsiness of array formulas. I’m talking about the situations where you have to fiddle around with your worksheet in an attempt to resize an array formula so the number of cells it belongs to (the array range) matches the size of the array it returns. If you make the array range too small then you risk not displaying an important piece of information; make it too large then you get unsightly #N/A errors for the elements which are out of range. To cap it off you have to put up with that galling ‘You cannot change part of an array’ error which is particularly prevalent you’re trying to shrink the formula. Ugh.

Of course, practice and various tools can make this a bit easier, but wouldn’t it be nice if the formulas just knew how big or small they should be? Darn right, it would. To that end, I’ve created a VBA add-in called RADExcelFormulaExtensions.xlam which has a couple of worksheet functions, rxlEXTEND() and rxlRESIZE(), which provide two different ways of letting the formula manage itself. Curious? Windows user? Read on.

First, a disclaimer. There are a huge number of limitations when you’re using these two functions, so I’m not for one minute suggesting that they should become the de facto method for handling multi-cell array formulas. They’re not ‘best practice’. If you have an industrial, complex workbook then you shouldn’t use them in it. The space for these functions is as a useful, day-to-day utility to make a quick bit of analysis easier to stomach. If you have UDFs which return arrays from databases/services then you’re going to love these puppies.

So How Do I Get My Hands On The Add-In?

The add-in can be downloaded from here but it will only work for Windows users with Excel 2007 or later. It should work in 64-bit Excel but I haven’t tested it so if you have any problems then please let me know.

I’m assuming you’re an advanced Excel user so you already know how to load an add-in into your Excel session. If you’ve done it correctly, you should see a small section titled RAD Excel Formula Extensions on the Formulas tab of the ribbon which looks something like this:

More on what those options do later. For now, all you need to know is that, once you’ve loaded the add-in, you’re ready to rock and roll.

There is so much I could write about on this add-in that this post would end up being the size of a book. Well, a small book. For now, I’m just going to cover how to use these functions and exactly what it is they do from an end-user standpoint. I’ll let your feedback drive what I write about in follow-up posts and, as and when time allows, I also plan to add more functions to the add-in.

rxlRESIZE()

The rxlRESIZE() function can be used in a formula so that it automatically resizes to fit the array it returns. It was initially based on the Resize() function written by Govert van Drimmelen for his fabulous Excel-DNA product, with a few customisations to help control its behaviour on the worksheet.  Enough chat. Let’s see it in action.

Watch what happens when I enter this simple formula:

=C1:C10

Not a lot. It just returns the first value in the referenced range. Now watch what happens when I wrap the same formula with the rxlRESIZE() function:

=rxlRESIZE(C1:C10,-1,1)

The rxlRESIZE() function works out that the formula needs to be 10 rows x 1 column and resizes the array formula to fit those dimensions.

If I insert a cell in the referenced range, the formula resizes itself again:

Pretty cool, eh? Let’s do a more complicated one.

In the next example, I’ve written a VBA UDF called LISTUNIQUE() which lists the unique values (values which occur only once) within a given range. The problem with listing unique values is you never know how many there could be. If the source range is ten cells then there could be no unique values or there could be ten unique values. It could change too if the values in the cells are changed. Because there could be up to ten unique values, I have to enter the array formula across ten cells to make sure that all the unique values are displayed. The problem is that if there aren’t ten unique values then I get #N/A errors:

Let’s rxlRESIZE() it instead:

Since my LISTUNIQUE() UDF can check multiple columns but always returns values in a single column, instead of using -1 (see syntax section below) for the maximum number of rows, I could use:

=rxlRESIZE(LISTUNIQUE(C1:C10),ROWS(C1:C10)*COLUMNS(C1:C10),1)

If the size of C1:C10 is changed then the maximum number of columns will change accordingly.

I haven’t included the source code for the LISTUNIQUE() function because it’s not part of the add-in, but there are plenty of examples out there if you want to give this particular scenario a try yourself.

rxlRESIZE() Syntax

=rxlRESIZE(ResizeValues,[MaxRows],[MaxCols])

• ResizeValues is a Variant and can be a literal, an array of literals or a Range. It can also be a set of functions which return any of these types.
• [MaxRows] is optional. It is a Long which indicates the maximum number of rows the formula can be resized to. The default value is 0. If a value less than 0 is used then the formula will resize to as many rows as is required to show all the row values in ResizeValues. If a value of 0 or 1 is used then the maximum number of rows will be 1.
• [MaxCols] is optional. It is a Long which indicates the maximum number of columns the formula can be resized to. The default value is 0. If a value less than 0 is used then the formula will resize to as many columns as is required to show all the column values in ResizeValues. If a value of 0 or 1 is used then the maximum number of columns will be 1.

If MaxRows and MaxCols are both either 0 or 1 then no resizing will occur. I have set this as the default to stop the user from accidentally clearing/overwriting data in the worksheet. The numbers provided in these arguments have a base value of 1 so if you specify 10 and 10 then it will resize to no larger than a 10×10 matrix.

rxlRESIZE() Limitations and Features

• Excel 2007 or later
• Windows operating system
• Function must be outermost one in the formula
• If worksheet contents are protected then the relevant cells must not be locked
• Should be avoided with slow formulae because the act of resizing causes the formula to calculate twice
• Treat it like an array formula: avoid using whole row or column references whenever possible
• Should not be used with automation. The formulas will not recalculate while other VBA code is running. Setting Application.EnableEvents to False in your automation code will cause the functions to return an error if you have the short-circuit calculation loops option ticked (see Ribbon section)
• Should be used with caution on worksheets with Change event handlers
• No allowance has been made for merged cells
• Cannot be used with volatile functions (including in precedent cells)
• If a volatile formula sits on the same worksheet then that formula will calculate twice
• If you alter the formula then you need to use CTRL+SHIFT+ENTER to complete your changes
• Destructive: it will overwrite existing data in the worksheet except for pivot tables and tables

rxlEXTEND()

The rxlEXTEND() function takes an array result within a single-cell formula and automatically populates that array in the cells surrounding the formula. This is a very unorthodox approach which breaks Excel’s calculation tree.

Here’s the simple =C1:C10 formula used in the first rxlRESIZE() example, except wrapped with rxlEXTEND() instead:

=rxlEXTEND(C1:C10,-1,1)

Scary stuff. The formula in E1 is managing the data in the cells underneath it.

For the second example, instead of using my LISTUNIQUE() function, I’m going to have a go with the LISTDISTINCTS() function which comes with the latest version of FastExcel. In terms of output, it’s similar to my LISTUNIQUE() function except that it returns a list of the different values from the range rather than a list of unique values.

 

I believe that the LISTDISTINCTS() function only looks at a single column and returns values in a single column so, in this case, to avoid using -1 in MaxRows – which will wipe out every cell under C1 (see syntax section) – a better option would be to use the ROWS() function to work out the maximum number of rows rxlEXTEND() needs to touch:

=rxlEXTEND(LISTDISTINCTS(C1:C10),ROWS(C1:C10),1)

rxlEXTEND() Syntax

=rxlEXTEND(ExtendValues,[MaxRows],[MaxCols])

• ExtendValues is a Variant and can be a literal, an array of literals or a Range. It can also be a set of functions which return any of these types.
• [MaxRows] is optional. It is a Long which indicates the maximum number of rows below the formula which can be written to. The default value is 0. If a value less than 0 is used then as many rows as are required will be written to in order to show all the row values in ExtendValues. If a value of 0 or 1 is used then the maximum number of rows will be 1.
• [MaxCols] is optional. It is a Long which indicates the maximum number of columns the formula can be resized to. The default value is 0. If a value less than 0 is used then the formula will resize to as many columns as is required to show all the column values in ExtendValues. If a value of 0 or 1 is used then the maximum number of columns will be 1.

If MaxRows and MaxCols are both either 0 or 1 then no resizing will occur. I have set this as the default to stop the user from accidentally clearing/overwriting data in the worksheet. The numbers provided in these arguments have a base value of 1 so if you specify 10 and 10 then it will resize to no larger than a 10×10 matrix.

It’s important to understand that there is a fundamental difference between rxlEXTEND() and rxlRESIZE(). rxlEXTEND() has no memory of which cells it has populated around the formula. When it recalculates it uses the MaxRows and MaxCols arguments to decide the size of the range it needs to clear.

WARNING: If you use =rxlEXTEND(array, -1 , -1) then it will clear EVERY cell below and to its right.

 rxlEXTEND() Limitations and Features

• Excel 2007 or later
• Windows operating system
• Function must be outermost one in the formula
• If worksheet contents are protected then the relevant cells must not be locked
• Treat it like an array formula: avoid using whole row or column references whenever possible. The existing code can be optimised and is on my to-do list
• You must manually clean up residue values when reducing the row/column limits. This is because it uses the MaxRows and MaxCols to determine which cells it needs to clear. If you reduce them in the formula then it won’t clear data in the wings of the old range. The same is true if you delete or clear the cell containing the formula.
• Should not be used with automation. The formulas will not recalculate while other VBA code is running. Setting Application.EnableEvents to False in your automation code will cause the functions to return an error if you have the short-circuit calculation loops option ticked (see Ribbon section)
• Should be used with caution on worksheets with Change event handlers
• No allowance has been made for merged cells
• Cannot be used with volatile functions (including in precedent cells)
• If a volatile formula sits on the same worksheet then that formula will calculate twice
• Should only be called from a single cell formula
• Destructive: it will overwrite existing data in the worksheet except for pivot tables and tables

The Ribbon

The Formulas tab on the ribbon offers three formula extensions options:

1. Short Circuit Calc Loops is a setting that I recommend you keep ticked. This option means that the rxlRESIZE() and rxlEXTEND() functions will detect if you’ve created an infinite calculation loop and will short-circuit. Calculation loops could be created through using volatile functions, circular references, and so on. This setting requires Application.EnableEvents to be set to True for the functions to work.
2. Reset Used Range is a setting which means that the rxlRESIZE() and rxlEXTEND() functions will reset the used range on a sheet when they calculate. If you have a large sheet with lots of data then this could really slow the extension functions down so you should generally leave this option unchecked. However, if you have a sheet where you’ve recently cleared a lot of data then Excel might be confused and still think that the sheet has a large used range which will slow the resize functions down: ticking this option will fix that.
3. Show Recent Errors will show a modeless userform with any recent errors which couldn’t be returned to the worksheet. The extension functions will return errors to the worksheet whenever possible but, because of their nature, sometimes they can’t. If they can’t return an error to the worksheet then they put it on the userform for you to see.

Finally…

Last, but not least, a thank you to my alpha testers:  Rory Archibald, Zack Barresse, Jon Von Der Heyden and Andy Pope. I contacted these guys a while ago and asked them for feedback on some pretty rough-looking, proof of concept code. They were a great help and spent quite a bit of time digging around for problems and giving me suggestions.