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

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

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…

Excellent
 
NOT!

How is the average user going to manage this, eh?
 
Suck
 
 
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)
  2.  

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

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

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

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:

 
SomeFormula
 
 
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.
 
Continue
 
 
Beeeeep.
 
Restarting

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?

Evaluate(Evaluate)

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:
 

FormulaText
 
 
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:
 
Documentation 1
 
 
…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:
 
Documentation 2
 
 
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:
 
Row vs Column
 
 
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:
 
Row vs Column2
 
 
Note that I’m using the square brackets [ ] shortcut for Evaluate. I could just have easily done it like this:
 
Row vs Column3
 
 

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

..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:
 
Result_SameTable
 
 
…or unless you happen to have a formula with the INDIRECT function in it:
 
Result_INDIRECT2
 
 
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/