I like Colin Legg’s cool code over at Self Extending UDFs – Part 1 and Self Extending UDFs – Part 2. And I keenly await Self Extending UDFs – Part 3 (subtitled Return of the User-i or something similar)
If you haven’t seen those articles, go check ’em out…they show you how you can use a UDF to change something on the worksheet. Particularly handy where you have array formulas that you can’t be arsed resizing. (And before y’all start yelling Nooooooooooo… at me, I’m going to yell back preemptively: As Colin points out, if it’s good enough for Google Sheets to offer this kind of thing via their UNIQUE and SORT functions, it’s good enough for Microsoft Excel too. Microsoft ALREADY lets the user overwrite data accidentally anyhow, with things like the Advanced Filter, etc. What’s so sacred about UDFs?)
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? And so I wonder if Colin’s code can 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 here sometime back 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, too? Let me remind you that the average programmer programs Excel with Formulas, and may not know their VBA 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?
Which reminds me of Oz Du Soleil’s latest post Google Has Gone Mad, which is a great post. In fact, I love all of Oz’s posts as much as I like his hat collection and his cool surname. (Mine is just “Weird” without the “d”). So should you, so go and subscribe to his blog now if you’ve never heard of the man. And check him and the team out at Excel.tv.
Oz makes the point that it’ll take a much longer time for an Excel user to need to resort to VBA compared to an “enlightened” Google Sheets user who’ll need to be good at JavaScript to do anything remotely as interesting as you can do in Excel. And in typically beautiful turn of phrase, he puts it like this:
Let’s present this at the most extreme. Pick one:
Pay the nanny state [Microsoft] or
Live in the open wilderness [Google Sheets]
Neither is bad, but you need to have a sober assessment of what you’ll need to live in the wilderness before you freeze to death under an unvalidated spreadsheet.
Man, I wish this guy was writing my book instead of me.
But while I agree with that, I think it’s our duty to constantly remind the Nanny State that there is still a LOT of unfulfilled potential in this here ‘virtual country’ that we all share. Yes, we voted for them with our wallets…but largely because the other guy’s policies looked far worse for the economy. In fact, sometimes we get irked because we see a lot of fluffy stuff that looks like it’s more focused on attracting votes than improving outcomes, and meanwhile some old irritants are now very old indeed. Some are over a decade old now.
That’s actually a key point of the book I’m supposed to be writing right now instead of this blog post. It’s called Excel for Superheroes and Evil Geniuses: An irreverent guide to getting Microsoft Excel to do your dirty work. A superhero is someone who uses the right bit of the application to do something as efficiently as it can be done out of the box. So they happily live in the Nanny State’s protection, and thanks to their advanced knowledge, they live well. Whereas an Evil Genius is someone who has a powerful arsenal of ‘borrowed’ technology at their disposal in the form of some killer User Interface tweaks, User Defined Functions, and weapons-grade Add-ins. They might not actually understand one single line of VBA code, but that doesn’t stop them from using incredibly powerful point-and-click Macros to pimp Excel so that it runs faster, meaner, and leaner than even a Superhero could make it run.
Of course, the Evil Geniuses of my book only need code because the Excel UI holds even Superheros back due to some poor choices in UI design. For instance, out of the box, you can’t filter a PivotTable on an external list automatically. You don’t have a viable dynamic concatenate function. You can’t natively deselect something in your selection, without wiping your entire selection. You can’t see long references in the Go To box because it ain’t wide enough, and it doesn’t let you resize it. And many, many, many more things that to me seem like easy-to-improve no-brainers.
I guess we’ve got to keep voting for the Nanny State, but that doesn’t mean we can’t tell them that we shouldn’t have to become Evil Geniuses just to fix suboptimal stuff in their “country”. And we should definitely point out to the Nanny State that we like – no, love – the look of some of the ‘policies’ of the other party. Even if the other party is not a credible threat, come election time (or 365 subscription renewal time, rather). Even if on balance, deep down inside we really love the Nanny State and would never renounce our citizenship.
Despite the fact that they don’t seem to listen, I think we simply have to keep loudly demonstrating on these virtual streets about things that should be a fundamental right to every citizen that lives here. Can’t filter a PivotTable on an external list natively? Tell the Nanny State. Still don’t have a viable concatenate function after all these years? Tell the Nanny State. No way to natively deselect something in your selection, without wiping your entire selection? Tell the Nanny State. Can’t see long references in the Go To box because it ain’t wide enough? Tell the Nanny State. And keep telling them. Don’t let up. Even if you’re just shouting it into the cyber wind, as I am here.
Of course, the Nanny State has to balance what they believe is best ‘on balance’ for the entire country against what they will think will keep them in power. And of course, they’re always going to be doing some stuff that’s more focused on attracting marginal voters in swing states than you and I in the beltway. Which means they’re probably unlikely to prioritize what I consider to be some simple no-brainers that we’ve been asking for for ages. Which also means we have to use a UI which is good enough so that users don’t absolutely have to learn VBA to do stuff, but in many cases is FAR from optimal. Far from perfect.
Shouting into the cyber wind by yourself can seem pretty pointless though. Which is why I’ve had an idea. We’ve had VLOOKUP week, and we’ve had some other similar week that Chandoo kicked off recently (but the theme of which eludes me right now). How about a dedicated annual “Still Broken” week in which ALL of us that can talk very honestly about the things that we think are still broken, plus review our list from the same time last year to see if any action has been taken by the Nanny State to actually remedy anything we were bitching about back then. And then give credit where credit is due…because let’s face it…it’s not trivial to make changes to something that 750 million odd users are using at the time.
Anyways…about that week. Here’s my ideas for names:
- “The week of tough love.”
- “Vote with your bleat” (Very fitting, given I live in New Zealand with lots of fluffy white things and Hobbits. Sometimes I confuse the two. Often after drinking)
- I guess “Vote with your sheet” is fitting too.
What say you?
Jeff, This is also an alternative:
http://optionexplicitvba.blogspot.com/2011/04/rollover-b8-ov1.html
David
Interesting concept Jeff. I’ve added a quick prototype to a special version of FormulaDesk. Please note that this is a test version of FormulaDesk – don’t try to use the Profiler in this version as I’m half-way through a few enhancements! Download and install FormulaDesk.
Click the download button here: http://www.formuladesk.com
Excel will now have a UDF called RunMacroWhenValueChanges. It will run the specified macro with the specified parameters when the value of the target cell changes. ie: changing the value of the target cell causes the macro to run.
The parameters are:
1) Target cell
2) Macro name
3) Macro’s first argument [optional – only use if macro needs this parameter]
4) Macro’s second argument [optional – only use if macro needs this parameter]
5) Macro’s third argument [optional – only use if macro needs this parameter]
eg: =RunMacroWhenValueChanges(A1, “MyMacro”, “111”, “Red”, D5)
Is this useful? Any suggestions for enhancements?
David: Thanks, I’d forgotten about that. Useful in some situations, but not so great for my hypothetical example of filtering a Pivot based on a list.
Gareth: Cool! Maybe you should make it display a TimeStamp instead of the value of the target cell. I note it will quite happily fire macros in the PMW if I use it like so:
=RunMacroWhenValueChanges(C1,”Personal.xlsb!test”)
I guess the only issue is that if we’re designing templates for other users, we can’t guarantee that they will 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.
Thanks Jeff. Timestamp – good idea. If there’s any interest, I’ll also enable more than three arguments to be specified for the macro ;-)
I agree, it would be cool to do this using just VBA, but I’m not aware of any way to accomplish this with VBA. I think you need to use the Excel C API.
Thinking about this some more, I guess that if the intent is to give non coders some better inroads into the world of automation – i.e. let them trigger helpful UI-enhancing code until the day that MS makes the helpful UI-enhancing code redundent by providing better dynamic options in tools that currently ain’t – then it doesn’t matter whether the triggering happens via a UDF, or via a wizard that helps a non technical user write an event handler. But the reason I mention UDFs is that non VBA people already do some pretty serious programming via formulas, so if formulas are what they are comfortable with, then let them use formulas to trigger automation, I say. But maybe until the day that MS agree with me (heh), a wizard approach is the better way to go…because it writes code that is distributed with the workbook, and doesn’t rely on an addin being installed on a file recipient’s machine.
But my point with this out-loud thought is that I reckon there’s a whole bunch of people who can do sophisticated programming using formulas, and are heavyweights in things like PivotTable-based or Advanced filter-based solutions, but are locked out from having Excel run these automatically when prudent (i.e. when the inputs change) purely because they don’t have the VBA chops nor understanding of object models to set up requisite event handers.
Take the advanced filter…you don’t need to use VBA to do some real cool filtering. But you do need VBA to automate it so that it re-runs if a parameter changes. Why should it be that way? Ditto with my point about PivotTables…why should you have to know VBA in order to “plug them in” to a spreadsheet solution. Why does MS insist that a human have to actually click the ‘Go’ button on these type of things. Isn’t that what computers are for? Doesn’t smart programming dictate that you give people easy options to remove all those tedious, manual steps?
Isn’t this a massive blind-spot in MS’s world view? Isn’t it time they stepped back, looked at how users use the product, and then admit “Man, these guys and gals have to do an awful lot of clicking. We probably should do something about that…”
Clicks cost money. As a lazy I.T. worker I
ll work really hard to find ways to automate routine, and not so routine, processes.The call logging system I have to endure daily requires numerous clicks for stuff that should be one-click wonders. By design. From day one.
As for things that are broke in Excel, I still have a copy of O'Reillys "Excel '97 Annoyances".
Your posting discusses adding innovative new features, but also those decade old irritants.
Why do people climb Everest (or Aoraki)? Because its there. Why should MS fix the broken bits? Because they can. Because striving for perfection, even if they never achieve it, demonstrates that they care about the quality of their software, and therefore us. I suspect they have a well funded department whose only role is to strategise methodologies for convincing the customer base that they are cared about.
Don`t tell me. Show me.
Yeah, those extra clicks that they’re not programming out probably account for quite a few millions of dollars worldwide in lost productivity. Several reasons why they don’t fix these things quickly include:
That said, many of the things that need to be fixed shouldn’t have been broke in the first place. Often, there is just dumb UI design. As evidenced by my previous rants at http://dailydoseofexcel.com/archives/2014/01/24/yes-please-vs-wtf/ and http://dailydoseofexcel.com/archives/2014/01/24/always-vs-never/ and http://dailydoseofexcel.com/archives/2013/11/05/excel-isnt-fully-cooked/
I just can’t believe that someone isn’t picking dumb UI design practices up, before they hit the market.
If I could say one sentence to someone at MS that could listen, it would be this:
Please don’t make me click.
Great post Jeff.
I’m all in favour of a “still broken” week.
My suggested name is “Excel, Still Broken Week”.
Hope you like it :)
Maybe we should modify that to “Excel, still a bit broken” Week.
Please note: I’ve created a new version of FormulaDesk and added the UDF above (RunMacroWhenValueChanges) to it. I have therefore deleted the link above (the above link will no longer work), as the normal download link for FormulaDesk should be used. Click the download button here: http://www.formuladesk.com
[Edit: I’ve changed the link above to mirror this one. Jeff out]
On a side note, I’ve also added a ‘Trim Bloated Workbook’ menu button which lists and optionally deletes unused blank columns and rows, as well as unused custom styles from the workbook.
Gareth, where’s the “Trim Bloated Application” button? ;-)
Jeff, thanks for pointing that out! I uploaded a fixed version: http://www.formuladesk.com
Good work, Gareth and good post, Jeff.
The M_RunMacro module of My Excel Toolbox includes several UDFs that trigger macros. They use the Windows API functions SetTimer and KillTimer to start a Sub procedure after the UDF has returned. (Application.OnTime will not work in a UDF.) They also use a System.Collections.Queue from mscorlib.dll to keep track of delayed procedures.
Here is a simplified example of a UDF that sets the cell style of a target range (which can’t be done directly with custom or conditional formatting):
If this UDF is used (w/o error) in a text formula, it returns an empty string (“”); in a numeric or boolean formula it returns zero (0) or False. For example,
=IF(E3>50,”WARNING”&SetStyle(“BAD”,E3),”OK”&SetStyle(“good”,E3))
See My Excel Toolbox for the complete version of SetStyle and similar UDFs.
I forgot the VBA not-equal operator does not post properly here. There are two lines in my previous comment that need to be corrected:
This line
If Err 0 Then SetStyle = CVErr(xlErrValue): Exit Functionshould be
If Not (Err = 0) Then SetStyle = CVErr(xlErrValue): Exit FunctionThis line
If TimerID 0 Then KillTimer 0, TimerIDshould be
If Not (TimerID = 0) Then KillTimer 0, TimerIDSorry about that.