The Daily Dose

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. Particulary 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:

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.

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.

So we should still demand things from the Nanny State that we think should be a fundamental right of anyone living 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?

Dilbert readers – Please visit Dilbert.com to read this feature. Due to changes with our feeds, we are now making this RSS feed a link to Dilbert.com.