A couple of days ago I floated the concept of a dedicated annual “Still Broken” week in which we can talk very honestly about the things in this otherwise great application 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 our gracious host…that would be Microsoft…to actually remedy anything we were bitching about the previous year. And just as importantly, to 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.

100% of the comments of that post that directly mentioned this concept were generally supportive (thanks, Doug). So I thought “There’s no week like the present”.

And so, without further ado, here’s a bit of Excel that I was playing with today that strikes me as being not quite finished: the Add-ins options.

Why is it that if I save a brand new Add-in to the Add-ins folder:
 

 
 
…it doesn’t automatically show up in this window, under the Inactive Application Add-ins section?
 

 
 
…and yet if I click the Go button next to Manage Excel Add-ins, I see that Excel obviously knows about it: Edit: And even though I parked it in the right car-park building, it still won’t show up until I politely point Excel to the exact spot I parked it in:
 

 

 

 
 
Given it was correctly stored in the Add-ins folder, why didn’t you just let me automatically populate it into the ‘Inactive Application Add-ins’ part of the Excel Options/Add-ins pane like this from the get go:
 

 
 
…and then let me decide to activate it just by double clicking on that listing?

Currently, selecting an Add-in from that Excel Options dialog and double clicking does absolutely nothing. What’s the point in letting me even select it…it’s not like I can actually do anything with it. All you’re doing is giving me vain hope.
 

 
 
Furthermore, jumping back a few steps now, whenever I push OK from that Add-ins dialog box:
 

 
 
…why do you automatically assume I’m done with the Excel Options dialog box I came from, and close it? Is it inconceivable that I might want to manage more than one Add-in? Or that my intention was to manage a COM add-in, but I forgot to change that default Excel Add-ins option in the dropdown, and want some simple way to go back and choose the other option? Sheesh.

Please Microsoft…don’t make me click. Here’s a thought: hire someone to the newly created position of “Manager, Click Reduction”, and empower them to champion basic design changes that let users use your interface much more efficiently. I’m available.

In fact, I think you need to take an honest look at what you expect users to do in order for them to leverage off of Add-ins. I think the current system presents too much of a barrier to non-technical people who would otherwise be able to make much more out of your fine application. I’ve just spent quite a few days writing the chapter in my book that explains how users can leverage off of Add-ins without having to have one jolt of knowledge of VBA. Unfortunately, my conclusion is that while they don’t have to be VBA experts, they do have to be “Install an Add-in” experts, because the process is not as simple and genius as it could be.

Okay, that’s kick-started the week. Who’s up next. Anyone? Anyone?

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?

Whenever I look at the default Table Style that Excel spits out:
 

 
 
…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:
 

 
 

…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:
 

 
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:
 

 
 

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:
 

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:
 

 

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

Let’s ask Microsoft where our beautifew new Custom Table Style is.
 

 
 
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…

 
NOT!

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:
 

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:
 

 

 
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)

 

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

 

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

 

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

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.

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:

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:

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

…it tells you there are none:

…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.

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)
 

 
 
Excel will warn you that the formula will be converted to static values. Click No.
 

 
 
Beeeeep.
 

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

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:
 

 
 

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

 
 

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?

Since Excel 2013, Microsoft has given us a FORMULATEXT function, which if you point at a cell will do just that. Here’s a naughty snapshot of FORMULATEXT playing with itself in the corner:
 

 
 
I thought I’d have a go at writing a FormulaArray function to complement it, because when I’m building up a complicated formula that uses lots of array manipulation, then I like to document how all the different arrays within it fit together. (I was going to say “come together” there, but after that crack about FORMULATEXT playing with itself, I thought better of it. But now that I’ve said crack, I’m gonna throw caution to the wind and say wind too.)

Currently I document my formula beasts by either either array-enter a sub-part in the sheet with some notes, like this:
 

 
 
…which shows how my ExtractNumber formula works, or I enter the desired formula in one cell with a ShowFormula to the left and a hand-rolled hard-coded array to the right, like in this table where I’m documenting a few ways to dynamically generate consecutive integers:
 

 
 
I get that ResultArray manually, by clicking in the formula bar, pushing F9, copying the resulting evaluated array, then pasting it in another cell. Tedious. Especially when I later make a change to that sub-part, because then I get to do those steps all over.

So I started to roll my own FormulaArray function. I got a bit bogged down in the joining bit, but after about an hour of Googling, I rediscovered Nigel Heffernan’s code for joining two dimensional arrays. Which is very concerning, because I discovered it like just 10 days ago, and even wrote an extensive blog post on it right here. Senility is obviously setting in. If I start saying the same thing over and over like my mother does, just shoot me. If I start saying the same thing over and over like my mother does, just shoot me.

Anyways, Nigel’s function needs a 2D array. You can create an array from formula text by using VBA’s Evaluate method. If the formula returns a Row vector or a 2D vector, then Evaluate nicely turns it into a 2D vector. But here’s the rub: if the formula returns a Column vector, then Evaluate only gives us a 1D vector, which ain’t gonna wash with Nigel’s function:
 

 
 
So what we need to do is TRANSPOSE any formulas that would return Column vectors, because chucking a TRANSPOSE into the mix has the desired effect:
 

 
 
Note that I’m using the square brackets [ ] shortcut for Evaluate. I could just have easily done it like this:
 

 
 

Okay, so we know that if our formula string returns a Column vector, we’ve got to transpose it. But how can we tell that ahead of time? I can’t think of a way. So I just do this:


Function FormulaArray(Target As Range) As String
Dim strInput As String
Dim var2 As Variant
Dim lb As Long

strInput = Mid$(Target.Formula, 2)
var2 = ActiveSheet.Evaluate(strInput)
On Error Resume Next
lb = LBound(var2, 2)
If Err.Number <> 0 Then
var2 = Application.Transpose(ActiveSheet.Evaluate(strInput))
FormulaArray = Join2d(var2, ",", ";")
Else:
FormulaArray = Join2d(var2, ";", ",")
End If

End Function

So I evaluate the formula as if it’s a Row vector, then check if I’ve got 2 dimensions as a result. If not, it must have been a column vector, in which case I transpose it, then reevaluate it. Shame about the double evaluation, but I can’t think of a foolproof way to do it differenty, other than perhaps array entering the formula into a 2D range on the worksheet and looking at where the #N/A! errors fall.

Anyway, it seems to work just fine:
 

 
 

..unless you happen to be using Structured Table References, and your arguments happen to use the @ table notation to point at something on the same row:
 

 
 
…or unless you happen to have a formula with the INDIRECT function in it:
 

 
 
With the ThisTableRow thing, I guess I can just replace the @[SomeColumn] bit with the actual address, but I can’t think of easy ways around the INDIRECT thing. Anyone got any ideas?

Edit: Thinking about this some more, all I need to do is substitute the INDIRECT(SomeExpression) with whatever gets returned by RANGE(SomeExpression).value

Sample workbook:

FormulaArray_20141125

There’s a handy post over at Charles Williams’ site that talks about some other quirks of Evaluate that’s worth checking out:
https://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/

So after doing some incredibly complicated formula challenges and ninja-grade VBA, I thought I’d turn my hand to something simple: finally changing the default template that loads when Excel starts or when I create a new workbook or worksheet so that gridlines are turned off. Because if there’s one thing I hate about Excel, it’s those gridlines: they make everything look like it was done in Excel. And if there’s one thing I do as soon as I open a new file, it’s turn those gridlines off. Tables and PivotTables give me all the borders I need, thank you very much.

Boy, what a battle. I have saved my changed book as an XLTM to just about everywhere I can think of.

• I’ve tried saving it to C:\Users\Samsung\AppData\Roaming\Microsoft\Excel\XLSTART but when I start Excel, I have gridlines.
• I’ve tried creating a new file at C:\xlStart with the template in it, and told Excel via Options>Advanced to open files in that folder but when I start Excel, I have gridlines:

  

• I’ve saved it to C:\Users\Samsung\Documents\Custom Office Templates but when I start Excel, I have gridlines

I’ve gone from feeling like I’ve mastered to Excel, to feeling like I’m a complete idiot. Anyone care to tell me that I’m not?

How the heck the average user is supposed to know how to do this stuff is beyond me. Why isn’t there simply a button on the ribbon or backstage that says:
Give all future workbooks the settings of this one.

I’m using Excel 2013 365. But I’m thinking of doing a complete 180.