In Self Extending UDFs – Part 1 I introduced a couple of VBA UDFs,
rxlEXTEND(), which will correctly display an array of values without the need for the user to manually resize the formula. They achieve this by either automatically resizing the formula to fit the values or by keeping the formula in a single cell and editing the cells around it.
A good question cropped up in the comments on whether the UDFs should be allowed to do this, which is what I will write about today.
The short answer to this question is yes, in this particular case I think they should, because it can be extremely useful and convenient. For example, in my world, I have to deal with upstream system APIs a lot. These APIs are often made available as compiled worksheet functions where you pass in some arguments and then get an array back. The return array can dramatically vary in size depending on the arguments and can be very large and cumbersome. It’s not just VBA developers that have to use these upstream APIs: consider business users who just want to do some light analysis but don’t want to write any code. In this sort of situation, these functions – wrapped up in a plug and play add-in – serve a unique and practical purpose. They must, however, be used responsibly.
As a side note, Google certainly considered self-extending formulas to be a good idea when they put together Google Docs Spreadsheets. Have you seen its
UNIQUE() function? Or perhaps its
Whilst I don’t think Google Docs Spreadsheets is a patch on MS Excel, that is pretty cool. This add-in lets you do exactly that sort of thing in MS Excel, as demonstrated in the previous post. Until the time comes where this functionality comes as a built-in feature in MS Excel, it’s the best that I can offer.
But how do the formula extensions work?
A golden rule when you’re working with UDFs in MS Excel is that they can only return values to the caller range (the range which contains the formula). UDFs cannot change the MS Excel environment. This rule makes a lot of sense: just imagine the havoc which could be caused if you recalculate a worksheet and its formulas start changing your Excel settings. Yep, it would be a total nightmare.
Microsoft thought of that and wisely decided to enforce some restrictions by disabling parts of Excel’s object model when the caller is a formula. For example, you could write a VBA function which merges some cells together: if you run that function by pressing F5 then (assuming it is written correctly) it will work absolutely fine but, if you try to use it in a formula, it won’t. There are less obvious examples: a fairly common question on the forums is why
Range.SpecialCells() ‘doesn’t work’ in some situations – and it’s for this very reason. There are some exceptions though. Up to and including Excel 2000, the
Range.Find() method was disabled in UDFs. A lot of people wanted to use it so Microsoft enabled it in the Excel 2002 release. I’m glad they did, because GetLastUsedRow() is one of my favourite functions!
I had to work around these enforced restrictions to let
rxlEXTEND() do their magic. They edit the contents of cells which means that they modify the MS Excel environment. Consequently, I had to spend a lot of time trying to make them stable and addressing potential pitfalls such as implied circular referencing and volatility. If you’re wondering what I mean by ‘implied circular referencing’, hopefully this will explain it:
MS Excel doesn’t realise that there’s a circular reference between the formulas in
C1 because there isn’t a formula in
C3. Whilst you’d never use
rxlEXTEND() in this particular way, I didn’t want it to be able to send a workbook into an infinite calculation loop, so I had to find a way of short-circuiting it and returning an error to the user.
Generally speaking it is a bad idea to let UDFs modify the MS Excel environment, which means that I do not intend to explain on here exactly how I did it. I even considered locking the VBA project to prevent someone with idle curiosity abusing it but, since I like to share, I’ve left it open – at least for now.