Why not let users trigger macros from a UDF?

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?

Custom Table Styles

Whenever I look at the default Table Style that Excel spits out:
Default Table
…I think: Nice structure. Pity about the finish. That formatting is a bit eyestrain-inducing , if not migraine-producing. I’m going to have to sand that sucker back to the wood, and repaint it.

So I look through the default styles for something that I can use in the spreadsheet that I’ll later be sending Stephen Few:
Default Styles

…but there are very few that are Few-worthy, let alone sponge-worthy. This one is getting close:
…but that huge contrast in the header row between pitch-black fill and white writing is really Tufte on my eyes, not to mention that dark grid makes this data look like it comes from Excel. Stephen won’t like that at all.

So I create my own style:
Custom Table
Ahh, that’s better…it lets the data – and our eyes – breathe a little easier. It uses pretty minimal formatting so that the data is front-and-centre, rather than the table itself.

In fact, I’m going to assign a custom name to my beautifew new custom style, befitting of it’s ability to help me get one step up the ladder of visual enlightenment:

And now I’ll save little Stairway so that it’s the default Table style used whenever I create any Table ever again:
Set Table as default

And with that done, now I can dappily go to a new workbook, and – while happily humming Bohemian Rhapsody – create a new table using my beautifew new Custom Table Style:
New Table

What the? Why am I NOT in that list? And why doesn’t that list have my beautifew new Custom Table Style applied to it? Ah well, can’t be good at Excel and lucky in lust, I guess. And anyways, at least I saved that Custom Table Style to the Table Styles gallery earlier. Let’s just apply my style manually from there:

No stairway denied

That’s right, Wayne. No Stairway. Denied, indeed.

Let’s ask Microsoft where our beautifew new Custom Table Style is.
Have you seen this Style2
Well, er…um…you see, the thing is…when you save a Table Style, it only gets saved in the particular workbook you’re working in.

What? Really? You went to all the effort to allow users to create new Table Styles, but you didn’t give them a way to reuse those anywhere else?

It turns out, the only way we can make our beautifew new Custom Table Style permanently stick around is by:

  1. Copying a Table that uses our new Table Style into a new blank workbbook
  2. Setting that Table Style as the Default Table Stlye, like I did earlier
  3. Deleting that Table
  4. Saving the workbook as an Excel Template in the Startup folder, so that Excel will use this workbook – and our beautifew new Custom Table Style – as a template whenever we create a new document.

Well that is just…


How is the average user going to manage this, eh?
That’s right, Wayne…it’s pretty tricky. Here’s a couple of tips that might help.

Firstly, before you save that template, make things (slightly) easier on yourself and find your Startup folder location by typing ?application.StartupPath in the immediate window of the VBE:
Startup Path

You can then copy that path, so that later on you can paste it into the Save As dialog box.

And there’s a few things you need to note about the Save As dialog box:
Save as 2


  1. You want to change the name of the workbook from Book1 to just Book. (Excel will add the 1 or 2 or whatever automatically when it opens a copy of the template)

  3. You might as well go all-out and save it as an Excel Macro Enabled Template, so that you never again get this pesky message:
    No Macros

  5. You want to paste that Startup folder location in after you’ve selected that Excel Macro Enabled Template option from the Save as type dropdown, not before. Why? Because otherwise Excel inexplicably overwrites your previous directory choice as soon as you choose to save a file as a Template with this location:
    Wrong place

Well, hopefully they’ll make this easier for us in Excel 2016.

Converting an Excel Range to HTML the Hard Way

Every time I write a RangeToHTML function, it’s different. I don’t re-use my old functions because the HTML elements that I care about change from project to project. I could make a generic RangeToHTML function that attempts to capture every possible cell property, but I don’t. I don’t want a bunch of code in my project that doesn’t do anything. I figure out which cell properties matter to the project and code those.

In this example, I not only did not want fidelity with the spreadsheet, I was using bold and italics to trigger completely different HTML elements. But usually I’m trying to make the cells look like they do in the spreadsheet for those characteristics that I’ve deemed important. Below is another example where I’m converting a range to HTML to put into an Outlook email. The things that are important to me are bold, italics, font size, cell alignment, merged cells, and bottom borders. That’s a lot of stuff, but it’s not every formatting element that could be applied to a cell.

Here’s a breakdown of code:

  1. It’s a bit arbitrary, but I’m pulling the font size from the last cell in the range. For my data, I know that the header may have a different font size, but there is no footer. Whatever the last cell in the range is, that’s my default font size.
  2. I create two styles in the header: one for the default td element and one for the “bb” class (bottom border). The font name is pulled from the first cell of the range (because I know there’s o change in font family within the range. The font size I get from above. My Tag function is nested here so that my styles are in a ‘style’ tag and then the whole thing is wrapped in a ‘head’ tag.
  3. Inside the loop, I fill the aCells array with each cell. Before I go to the next row, I Join that array into an element of the aRows array. Later I’ll be Joining that array into a big string.
  4. If the cell is empty, I need a non-breaking space in my td tags. If the cell has more than one line, I insert the br HTML tag to replicate that.
  5. At this point, I’m just checking out the cell properties and converting them to HTML. These two lines wrap the value in ‘strong’ or ’em’ if the cell is bold or italic, respectively.
  6. I got the default font size up in step 1. If this cells font size is different than the default, then I set it explicitly. I’d considered trying to make everything a relative font size, but ultimately it was a pain and unnecessary.
  7. There are three cell properties that will turn into attributes in the td tag. The first is the cell alignment. I have left, right, and center cells and set the align property using the AlignmentAttr function shown below.
  8. Next, I look for merged cells and set the COLSPAN and ROWSPAN attributes accordingly. Yes, I hate merged cells too, but sometimes they’re necessary.
  9. The I look for a bottom border, which I implement in a css class. I don’t look for every border because I only care about bottom borders.
  10. Finally, I make the string by Joining my Attr array. If I’m in the first cell of a merged area (which also is true if there is no merge area), then I make the string. If I’m not in the first cell, I don’t do anything because I’ve already done it back when I was in the first cell.

To wrap it all I up, I tag and join everything into one glorious string. The Tag function looks like this:

The AlignmentAttr function from #7 above. I put this in its own function to keep the code a little cleaner, not because it does anything special.

Go To Special Blanks no longer my Go To guy…

So I’ve always used Excel’s Go To Special and VBA’s SpecialCells method to select things like formulas, constants, blanks etc from large ranges because I was under the impression that this was efficient. Is is, unless you’re using it to find blanks, in which case it’s a dog.

Try this: Select column A:A, and use Ctrl + Enter to enter say the number 1 into the whole column. Now, delete one of the cells so there’s a blank, push F5 to bring up Goto Special, select the Blanks option, click OK, and go put the kettle on.

Goto Special Blanks

It took about 54 seconds on my PC. Admittedly my PC has been running slow of late, but that’s ridiculous.

Now try the Constants option:
Goto Special Constants

Just over a second.

And in case you think the number of blanks (1) vs the number of constants(1048574) is the culprit, you’re wrong. This takes just as long:
Goto Special Blanks2

Ironically – perhaps moronically – if you use the Go To Special>Blanks option on a range outside of the used range:
Goto Special Blanks3

…it tells you there are none:
No Cells were found

…which is about as helpful as that “Was this information helpful?” message.

So from now on, instead of Go To Special > Blanks I’ll be using Chip Pearson’s FindAll function. You?

I’m using Excel 365 on Windows 8. Anyone NOT get the same behavior on different flavors? Googling vba specialcells xlCellTypeBlanks slow brings up heaps of hits. Quickly.

Quick Access Toolbar Usage Update

Ever since the great QAT Usage Survey of last year, I’ve had five controls on my QAT. Up from zero. Here’s how they’ve fared.

  1. Borders: Used zero times. I have my two most common border situations (single underline and grand total) available elsewhere. I guess I don’t muck around with borders as much as I thought.
  2. Text to Columns: Used zero times. I use TTC all the time; sometimes one of my own invention, but equally the built-in kind. The built-in kind is good when I have a lot of rows or if I’m tyring to convert a bunch of numbers into text. But I use Alt+A+E without even thinking, so this button has been lonely.
  3. Connections: Used exclusively. Well, kind of. I always start navigating the Ribbon before I remember I have a QAT at all. Then I back out of the Ribbon and Alt+3 to show the external data connections. I think the last couple times I’ve remembered so it’s possible I’ve turned a corner.
  4. Switch Windows: Used zero times. I Ctrl+Tab to switch windows and simply haven’t needed this.
  5. Table Name: Used exclusively. It didn’t take too long to wean myself from Alt+JT+A to Alt+5. This has been the biggest success.

The QAT has awesome Alt+n shortcuts that I need to use. I just don’t know what to put on there. I use Borders and Switch Windows far less than I thought. I need to find some that I’ll actually use.

First bug of the year

And it’s a funny one. Save all your work, then make up a simple datasource that has a formula in the header:

Now, turn the Macro Recorder on, and while it’s recording, turn that datasource into an Excel Table. (I use the Ctrl T shortcut for that)
Create Table
Excel will warn you that the formula will be converted to static values. Click No.

Happens for me using Office 365. Pretty obscure, granted.

What Is the Most Awesome Microsoft Product

As if you didn’t know.

As the people talked, we started to get a sense for why they thought Excel is cool – Excel enables people to be rockstars at their jobs and get recognition from their peers. Excel makes people feel powerful. Excel is a work tool that people *love* to use.

You can also read the rest of the answers. Lots of OneNote and Kinnect. Even a Visio and a Minesweeper.

Evaluate Mid

In my last post, I created an array from formula text by using VBA’s Evaluate method, in order to roll my own FormulaArray function that displays the array returned by a formula, for documentation purposes.

In the course of this, I’ve discovered something a bit weird about how this method evaluates the arrays returned by a MID function.
Let’s use this snippet:

Sub EvaluateThis()
Dim var As Variant
var = ActiveSheet.Evaluate(ActiveCell.Formula)
End Sub

First, let’s look at how it handles an array generated by the COLUMN() function:
Evaluate COLUMN array2

Now watch what happens when we use that array to split apart a string using the MID() function:
Evaluate MID array2

So if you push F9, you get an array, but if you use the Evaluate method you don’t…you just get the first letter. Is this weird, or am I missing the point?