Many of my spreadsheets use Excel 2003 Commandbars for application control.
Having loaded these spreadsheets into Excel 2007, I see my commandbars presented on the Add-In tab of the Ribbon.

I’m a late starter with Excel 2007 ribbons, but I guess it’s time to move.
I’ve found that the various guides on the Internet tend to throw you in the deep end. I think this is a trend not specific to Excel. There is a real lack of guides out there for the developer who is just starting and knows nothing. Perhaps I’m just looking in all the wrong places?

My goal for the week was to, quite simply, create a button on a Ribbon that displays “Hello World” when clicked.

There are basically two parts to getting a button onto the ribbon.
1. Inject a special file into the xlsm, which you can use Custom UI Editor Tool for (look for the OfficeCustomUIEditorSetup.zip attachment).
2. Add some code into a VBA module.

Instructions as follows:
– Download and Install Custom UI Editor for Microsoft Office

From Excel:
– Create a new Excel Macro-Enabled Workbook, and save as Book1.xlsm
– Close the Workbook

From Custom UI Editor:
– Open Book1.xlsm
– From the Insert menu, select Office 2007 Custom UI Part
– From the Insert menu, select Sample XML > Custom Tab
– The Sample XML is actually tuned for Office 2010, so on the top line where it says the schema is 2009/07, change that to 2006/01.
– Save

Your screen should look as follows:

From Excel:
– Open Book1.xlsm (Do you notice the Custom Tab on the ribbon?)
– Alt-F11 to VBA
– Insert a Module, and insert this code:

From Excel:
– Click the Custom Tab, Happy Face button.
Hello World!

Some notes…
xlsx and xlsm files are really just zip files in disguise. Rename the file extension, and you can browse the contents.
The purpose of Custom UI Editor is to add a folder containing the XML file into the xlsm. The folder is customUI, and the file is customUI.xml.
You might feel more comfortable doing this by hand but I have a feeling there are better tools out there. Being a complete newbie at ribbons, I don’t know of any, but perhaps someone will add a link in the comments.

In New Zealand, we format our dates dd/mm/yy. However, the United States format as mm/dd/yy.
When I see a date by itself, like 12/01/09, I ask “is this the 12th of January or the 1st of December”?

When I started working with Oracle RDBMS, a habit I picked up was to format dates dd-mon-yyyy.
This avoided the dd/mm abiguity by supplying a 3 letter abbreviated month: 01-Dec-2009.

I borrowed the format for use in Excel, and I merrily formatted every date I could as dd-mmm-yyyy.

But, one day, I visited a user and saw this:

Shock! How dare Excel change my format to use slashes! It looks just plain weird.

It seems that before storing a date format, Excel does a quick check of the Date Separator in Control Panel Regional Settings (from VBA: Application.International(xlDateSeparator)) and performs character replacement.
So, while I was expecting Excel to store my custom date format as “d d dash m m m dash y y y y”, Excel instead stores “d d dateseparator m m m dateseparator y y y y”.
If that Workbook is then opened on a computer using a Slash as a Date Separator the weird date appears.

The trick, I’ve learned, is to use the escape character backslash to force the dash in: dd-mmm-yyyy

A “gotcha” is that the dd-mmm-yyyy presented as a re-usable item in the Custom Number format list is what is shown. It has no escape characters.
Because you can’t really re-use it without typing it out again, I’ve written a macro to apply the format to my selection.

A few years ago, I wrote a Formula Formatter add-in to present a long formula for easier reading. It does it through a process called Tokenizing, which is basically a process of putting the components of a formula into labelled boxes.
That add-in was compatible with Excel 2003 formulas, but Excel 2007 brought us extra formulas which meant my add-in was incomplete.

It was the first time I’d ever developed a tokenizer, so after a while I thought of better ways of doing it.
From my Oracle days, I recalled those diagrams in SQL manuals. Kind of like bubbles with lines leading in and leading out detailing the syntax of a statement. At the time I figured I could reuse this technique to document Excel Formulas. Of course, now that I’ve done a bit of reading, I’ve learned these are called Syntax Diagrams, and that’s exactly what they were intended for… visualising the syntax of formulas, or really, visualising BNF. BNF itself is a way of documenting a programming language’s grammar.

After some weeks of ripping apart formulas, I completed a BNF document describing Excel Formulas.
Probably the very next day, I stumbled across this document on Microsoft’s website: Excel Binary File Format (.xls) Structure Specification. It’s got BNF galore.
I felt like the guy who spent a weekend learning to program his VCR without a manual, only for the manual to turn up the next day.

So, with syntax in hand, I developed a tokenizer, which was quite a lot of fun.
It does Excel 2007 Tables, which was really my goal from the start. I also tidied up the bits around external named references.
It should work with International versions, but I use English only.
Ron de Bruin gave me some advice in this area (thanks Ron!), but still feel there might be problems with errors such as #VALUE!. You’ll find them defined once at the top of the module if you want to play.

Now that I’m finished, I’m sharing… You can download the code here (a zip file at 75 KB)
I’m not really interested in developing a user interface for formula formatter this time around. I’m happy to leave that to anyone else.

I wrote a proof of concept userform. In the image below, I’ve doubleclicked the IF function, and the whole IF, including it’s content, is highlighted.

I run on a wide screen 22? monitor (1680 x 1050). More than once I’ve delivered a prototype to the customer, and they’ve needed to scroll to find all the buttons, because their monitor was smaller than mine.

Here is a procedure for resizing the Excel Application to a specified dimension (measured in pixels).
Now I can develop on my screen, knowing what it will look like on the customers.

Simply change the constants cDesiredWidth and cDesiredHeight to the target screen resolution.

Note: It’s an approximate resize – it resizes to a few pixels wider than it should.

Nothing wrong with this sheet, right?

Until you get to the end…

The teeny-tiny vertical scrollbar handle is usually a give away. Hundreds of rows tacked onto the end of the list to give the illusion that the whole column has been formatted.

The trouble with formatting columns as per the above image is that it causes the “last used row” to be hundreds of rows further down than necessary. Click print and you waste a forest. It could also cause your workbook file size to bloat! In any case the vertical scrollbar’s usefulness takes a serious blow.

The answer is to format not individual cells, but the whole column.
– Select the worksheet column (or columns). The short-cut key is Ctrl+Space. In the above example, we’d highlight columns A to F.
– Right-click the selection, and choose Format Cells…
– Apply formatting the way you want.

If we did this exercise on the above example, you would notice that the column headers would also be formatted the same as the content, which is often not what we want. The trick is to format them last.

So the general order of formatting goes:
– Format the whole sheet
– Format the whole column
– Format just the column header (label)

Hopefully this diagram explains it.

This method allows your worksheet to grow while maintaining consistent formats for new rows.

It is possible to get a cell to display one thing, but store another.
Create a Custom Format by right-clicking the cell, select Format Cells…, then from the Number tab select Custom.
In the Type box use whatever text you want displayed, but be sure to enclose it in “double-quotes”.

I admit, this is a pretty dirty trick, but it may have legit uses.
Data Validation cell dropdowns also use this format. This makes it possible to select a text value from a list, but it will store the number instead!

There’s probably a limit to the number of custom formats. I’m not sure what that limit is, but I reckon it’s big, because in many of the spreadsheets I’ve worked on there are way, way too many.

I recently discovered how quick it is to create a simple Gantt chart in Excel.
The following example was created for Excel 2003. It should work the same for other versions.

Create a worksheet similar to the above table.
Note: the formula in column D2 is =C2-B2. You’ll need to set the Cell Format back to General, or column D will look like a date.

Select just the Activity and Start Date data (eg. A1:B5), then click the Chart Wizard.
Step 1 of the wizard:
Select the “Bar” Chart type, and the Stacked Bar sub-type, then click Next.
Step 2 of the wizard:
Click the Series tab, and Add another Series.
For Name, select the cell for the Duration Column Header (cell D1)
For Values, select the cells for for the Duration data (cells D2:D5)
Click Next
Steps 3 and 4 of the wizard can be skipped, though, on Step 3 you may want to hide the Legend.

The idea from here is to make the Start Date bar invisible, so only the Duration bar shows.
Double click on any one of the Bars for the Start Date data.
From the Patterns tab, set the Border and Area to None.

You may also notice the Activities are in reverse order.
Double click the Y axis (Category axis). A “Format Axis” window should appear.
From the Scale tab, tick the “Categories in reverse order” and “Value (Y) axis crosses at maximum category” are ticked.

You’re finished!

As an extra, you can force the chart to graph only between certain dates.
Double click the X axis (Value axis). A “Format Axis” window should appear.
From the Scale tab, override the Minimum and Maximum boxes with Dates (I didn’t know it could take dates, did you?!)

Sometimes I find myself copy-pasting between Excel and another application.
In this example, I have a table of three columns: First Name, Last Name, Birth Date.
My external application has 3 text boxes, one for each of those values.

I can’t just copy the 3 cells from Excel and paste them to my App, because they would all end up in the first text box!
But, by running VBA SendKeys with a specially crafted string, I can send keystrokes for tabbing to the 2nd and 3rd text boxes.

I also need to activate the SendKeys procedure only when my cursor is positioned correctly, or things could get messy.

My approach is to run a macro that sits there listening for F6 before activating SendKeys.
I’ve also included listening for the Esc key, just in case I change my mind.