In Self Extending UDFs – Part 1 I introduced a couple of VBA UDFs, rxlRESIZE() and 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 SORT() function?
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 rxlRESIZE() and 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 A3 and 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.
RAD Excel 
Nice post Colin. While I don’t use Google Spreadsheets I can really appreciate what they’ve done with some of their functions. I really, really wish MS would add this type of functionality to Excel, as the current workarounds for things like SORT/UNIQUE are cumbersome, especially when it comes to sharing files, or working with a customer’s file(s).
The UDF’s handle ranges very elegantly IMHO, and I think you did a great job of making them robust and calculation friendly. Well done. :)
LikeLike
I like this, Colin. What I’d like to see next along these lines is something similar to this concept where you can have a UDF called RunMacro(Target, Macro, [Condition]) that would let non VBA programmers run point-and-click macros and functions when something on the sheet changes.
Sure, you and I would just set up an event handler. But I think ‘normal’ users should be able to set up event handlers too. Why not via a UDF?
Could your project be amended to do this? (I haven’t delved into your code yet)
Here’s a hypothetical situation where I think such a function would be useful: filtering PivotTables based on an external range. I wrote a function over at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ who’s arguments are a range where a PivotField is, and a range where a list of filter terms is. It looks something like this:
Private Function FilterPivot(rngPivotField As Range, rngFilterItems As Range) As Boolean
Imagine if you could call that right from a UDF in the worksheet, so that a user could dynamically filter a Pivot – or an entire PIvot-based dashboard – simply by pasting new data into the rngFilterItems. Much handier than having to manually click a slicer.
Sure, you can write an event-handler to do this. IF you know how to write an event handler. Why not give this type of functionality to the average programmer. The average programmer programs Excel with Formulas, and may not know their For from their Next. But that doesn’t mean they shouldn’t be able to trigger well-thought-out macros directly from the sheet, surely?
LikeLike
Pingback: Daily Dose of Excel » Blog Archive » Why not let users trigger macros from a UDF?
Hi Jeff,
Yes this can definitely be done with a few code changes – absolutely no problem at all.
Let’s throw a few ideas around.
Over at DDOE you mention an alternative: providing a wizard to allow non VBA users a way of plugging the macro into an event handler. I think that’s a good way of going about it, but let’s dig into the UDF approach a bit more. 3 options:
(1) Provide a generic “run whatever macro you want at your own risk” UDF
When I built the formula extensions add-in I spent a lot of time making sure the RESIZE and EXTEND functions were as stable as possible: I trap circular references, overwriting tables, pivot tables, etc etc. Some of it really wasn’t easy to do. If we allow a user to call any old macro they want from a UDF then we give them the ultimate flexibility but they really could cause a lot of havoc! The upside for me is this is a quick and easy one to implement.
Pros
–Flexible
–Quick to implement
Cons
–User can destroy Excel
(2) Provide a set of pertinent, built-in “action” UDFs
An alternative might be to build out the formula extensions add-in with a set of properly written UDFs which can perform predefined actions such as manipulating/refreshing worksheet-embedded objects such as pivot tables, listobjects, autofilters and advanced filters. This is a lot more work for me and locking it down to a specific set obviously wouldn’t give the same flexibility, but it would make things controlled and stable and the user wouldn’t have to source a “clean” VBA macro from somewhere to paste into their project. I’ve been planning to add more UDFs to the add-in anyway so I could just add these ones to my ever growing to-do list. Honestly, I’m not sure when I would find the time to do it.
Pros
–Stable
–User can still be 100% free of macro awareness
Cons
–Less flexible
–More work
(3) Do both of the above
Hmmm…
LikeLike
Thanks for your reply, Colin.
My pal and fellow kiwi Gareth Hayter today read my post and quickly whipped up a RunMacro type UDF in his beta product FormulaDesk. (FD is a pretty cool and fast formula parser, among other things, and I’ve been helping out with some testing and concepts over the last wee while).
That looks like it works, but the only issue is the usual one: what if another user doesn’t have FormulaDesk installed?
It would be cool to be able to do this with native VBA, and so distribute the code with the actual workbook it operates on. Would that be possible with your approach, or would a user always need to install it as an add-in? (Sorry, I still haven’t delved into your code yet…could probably answer this myself if I did.)
I think that perhaps that Wizard idea might be the way to go, as it overcomes the sharing issue.
LikeLike
Hi Jeff,
For the add-in UDF approach:
The user’s own macro would be in their workbook but the “RunMacro” UDF to trigger their macro is contained in the add-in which means the add-in would need to be installed for every user. The workaround would be to inject the “RunMacro” code from the add-in into the user workbook. It’s possible but IMO not worth it and defeats the point of having a single code base add-in in the first place. So, short answer, I’ll just say no.
For the wizard/event handler approach:
The wizard could be built into the formula extensions add-in and then, because it builds the event handler code into the workbook, the add-in would only need to be installed on the originator’s machine. Personally, I’m not worried about the sharing issue but, that aside, this is a much better option on many levels since it conforms to the model MS intended us to use.
There are a couple of drawbacks to using an event handler though.
Firstly, suppose you trap the worksheet’s change() event. This gets fired every time a cell on the sheet is changed – quite a bit of overhead if you’re only interested in whether or not one specific cell is changed. It could get called hundreds of times without a positive hit. The UDF() would be cell specific so there isn’t that overhead – but it would require calculations on automatic if the user wants an immediate call.
Secondly, if, for some reason, Application.EnableEvents is set to False then the event handler won’t get called.
LikeLike
Good points, Colin. I always wondered about how much overhead is involved in using Event Handlers, but haven’t got round to looking into this further. That said, if you were going to the trouble of putting an event handler in a workbook, you’d have a decent expectation that it will be worth the hit.
LikeLike