Todo.txt TDD Part 1

Earlier, I wrote a post inviting you to try your hand at test-first development. This post is the first in a series of how I did it. In the previous post, I had all the tests written, but here I’m starting from scratch and writing the tests as I go. Well, I’m not starting from scratch in that the classes are already set up. If you want to see what the classes look like, download the workbook from the previous post or the one at the bottom of this post.

First, create the property in CTodo that will parse the string. There’s nothing in it, but we’ll get to that shortly.

Write a test. This test will determine if the todo item is complete. Per the spec, the first thing in the string is an “x” if it’s complete

Now write the simplest code to make the test pass. I probably could have written simpler code than this, but don’t get too hung up on that. Just write simple code and don’t try to solve the next test – only this test.

When I split the string on a space, the Complete property is set to whether the first element is “x”. The test runs successfully. Next, write a test for incomplete todos.

Oh goodness, that test already runs successfully. There’s no “x”, so Complete is set to False. Next, write a test for a completed todo with a priority. Per the spec, the first element after the optional “x” is a capital letter in parentheses.

This test fails on Debug.Assert clsTodo.Priority = "A", so it’s time to write the simplest code to make it pass.

The Priority property is set to the second character of the second element. The test passes. Did we break anything? Let’s see.

Nope, everything passes so far. Time for the next test. Check the priority for an incomplete todo.

It fails, so let’s write some code

If my fist element is an “x”, get the second element, otherwise get the first element. Pretty simple and the test passes. Every test I write, I add to the TEST_All() procedure to make sure I don’t break any prior tests. The next part of the spec is an optional completion date. Let’s start with a completed todo with no priority and a completion date.

My new test passes, but I get an error in one of my old ones. Plus this code is getting pretty ugly. When your code is ugly or repetitive, it’s time to refactor. Instead of a bunch of nested If’s, I’ll just move a pointer down the line.

I use lNext to keep track of where I am in the array. If the first element is an “x”, I advance the pointer. Then I check vaSplit(lNext) rather than a specific element number. All my tests pass.

In the next installment, I keep writing tests, writing code, and refactoring.

The below workbook has all the tests and the completed Raw property. It also has a userform, but it’s not complete.

You can download


  1. todo-txt-tdd-part-1/
  2. todo-txt-tdd-part-2/
  3. todo-txt-tdd-part-3/

Test First Todo.txt

In my ongoing struggle to find a todo list app I like, I took a look at Todo.txt. I ended up going back to GoodTodo, but I was intrigued by the text based system. I wanted to build something in Excel to be an interface to Todo.txt and I used a test-first methodology to parse the file. You may remember my first foray into test-first development when I converted numbers into words.

Later this week, I’ll post how I wrote the parser using tests. If you’ve ever wanted to try to write code using test-first, here’s your chance. Download the workbook below. I’ve setup all the classes and the tests. I even wrote enough code to make the first test pass. If you’re interested in this exercise, follow these steps:

  1. Download, unzip, and open the workbook
  2. Go to the MTest module and run the TEST_All sub and see that it passes
  3. Uncomment each link in TEST_All one at a time
  4. Run TEST_All to see that it doesn’t pass. If it passes, uncomment the next test
  5. If it doesn’t pass, go to the Raw property in the CTodo module and write just enough code to get the test to pass
  6. When your code looks cumbersome or you see a pattern emerge, refactor Raw
  7. Repeat until all the lines in TEST_All are uncommented

Check back later this week to see what I came up with.

You can download

Ribbon customUI Text Editor

Over at my site, yoursumbuddy, I’ve published an Excel addin for creating, editing and validating the XML that makes up Excel Ribbons. I developed it because I can’t install executables at work and want an addin that I can just run from Excel. It works on ribbons in workbooks and addins and runs in Excel 2010 and later.

Along the way I learned about XPATH, SAX, and DOM as they relate to XML and VBA. I’ve already forgotten what those things are and how they work, but they were important for a tool that downloads customUI/customUI14 XML, validates multiple errors, creates and/or modifies the .rel entries, and then uploads it all back into the zip file that is an Excel workbook. Whew. Should you care to learn more, my unpassword-protected code is available for your reading pleasure. If you poke around you’ll see that my code is built on the work of Jan Karel Pieterse, Ken Puls, Ron deBruin and even keepitcool. Thanks to all of them!

Here’s a couple of pictures of the addin form:

form with tips

form with highlighted error

To learn more and download the addin, just visit the yoursumbuddy Ribbon customUI XML Editor page.

Seven steps to (almost) protected Calculated Columns

Earlier I complained about how you can’t lock down Calculated Columns in Tables, while still allowing users to insert new rows. Dick gave me half a hack in the comments of that post, and I’ve come up with the other half to give you an almost-foolproof way to protect those precious Calculated Columns from your almost fools.

Step 1: Unprotect the entire rows that your Table occupies.

Step 2: Park a shape over the Calculated Column. Leave it’s protection as the default ‘Locked’. This will act as a shield once the sheet is protected.

Step 3: Format the shape so that it has no border, and a white fill that is 99% transparent. (If it’s 100% transparent, then Excel will let users click right through it, to the cells behind).

Step 4: Add some DV that stops users from changing the formula in the column, should they navigate around the shape shield via using the arrow keys. Dick’s formula below ensures that someone will have to spend an awful long time typing before they are allowed to overwrite the existing formula.

Step 5: Add a pop-up input message that politely warns them not to mess with your calculated column, should they manage to reach it using the arrow keys. This is still needed, because while they can’t change the formula, they can still clear it using the Delete key.

Step 6: Protect your worksheet in a manner that allows row insertions/deletions.

Step 7: Go vote for Zach Barresse’s UserVoice request for Microsoft to fix Tables so that you don’t have to use 6 hacks to work around their “feature”.

This works a treat: Try as they might, they can’t select the unprotected cells in the Calculated Column by clicking on them. If they click in the bordering cells and use the arrow keys to navigate behind the shield, they get warned off politely:

…and yet they can still insert new rows:

…and as you can see, the unprotected formula in the calculated column copies down just fine.

Stack Overflow Milestone

Look how special I am.

This is it. From what I can tell, 25,000 is the last milestone that can be achieved. So I’m at the top of the mountain.

I haven’t had as much time to answer questions on SO lately, but I still find answers on there all the time. I love the way the site is organized and the outstanding UI. I wish I could get the comments box on this site to behave just like their answer boxes. And by “wish”, I mean I want it to be that way without any effort on my part.

I did happen to answer a question the other day about the “Loop without Do” compiler error. As usual, properly indenting your code avoids these errors. I guess people didn’t like the question as it got a few downvotes. I’m not so critical. I think people tend to forget their early struggles as they become more proficient with something. I remember when I first starting learning Ruby, I didn’t know enough to formulate a non-stupid question. It’s not knowing what you don’t know that can be the most frustrating. And a little throw away tip like indenting can be really helpful.

I’m really thankful there are sites like SO (and NNTP back in the day) that give me an avenue to help people. I get so much more out of it than I give that I feel guilty (not really).

Identifying PivotTable Collisions

So you’ve got a file with dozens of PivotTables in it. One day you hit Refresh All, only to see this complaint:

…or perhaps this variant:

What’s happened is that the size of the PivotTable has increased, and the PivotTable is now trying to occupy space where another Pivot or Excel Table already lives. So you know what has gone wrong. But you have no idea where. You’re either going to need to eyeball each and every bit of each and every worksheet (including the hidden ones), or you’re going to have to run a bit of code. Something like this:

…which does this:

A (refactored) Christmas Carol

Our story begins on a cold and bleak desktop. Excel, an aged application , ignores an invitation to Christmas dinner from his nephew UserVoice. Excel turns away desperate pleas from 222 voters who seek an upgrade to Tables in order to provide data integrity and usability for their poor users. Excel only grudgingly allows his overworked, underpaid Tables to be used while a sheet is protected, and even then, only to conform to the bare minimum of the existing Sheet Protection functionality.

At home that night, Excel is visited by Lotus 1-2-3’s ghost, who wanders the Earth, entwined by heavy patents and dependency chains forged during a lifetime of greed and selfishness. Lotus 1-2-3 tells Excel that it has one chance to avoid the same fate: Excel will be visited by three spirits and he must listen to them or be cursed to carry dependency chains of his own, much longer than Lotus 1-2-3’s chains.

The first of the spirits, The Ghost of Microsoft Past, takes Excel to scenes of Excel’s boyhood and youth, reminding him of a time when he was more innocent. The boyhood scenes portray Excel’s lonely childhood, his relationship with his beloved sister Word, and an office party hosted by his first employer, Mr. Gates, who treated Excel like a son. They also portray Excel’s neglected fiancée Access, who ends their relationship after she realizes that Excel will never love her as much as he loves unstructured data.

The second spirit, the Ghost of Microsoft Present, takes Excel to a joy-filled help forum of people gathering the makings of a PowerQuery, and then on to a celebration of business intelligence in a data-miner’s PowerPivot data model. They then visit Acme Inc, where we meet Jolly Jeff, an otherwise happy analyst who’s Table-driven macro-free Excel templates are seriously unprotected. The spirit informs Excel that Jolly Jeff’s carefully constructed templates will soon be corrupted by ignorance and laziness unless the course of events changes. Before disappearing, the spirit shows Excel two hideous, emaciated columns of data named Calculated_Column and Freetext. He tells Excel to beware the former above all and mocks Excel’s complete lack of concern for their unprotected state.

The third spirit, the Ghost of Microsoft Yet to Come, shows Excel a business office in the future. The ghost shows him scenes involving the death of a disliked application. The application’s funeral will only be attended by local businessmen if donuts (of the charting variety) are provided. When Excel asks the ghost to show anyone who feels any emotion over the application’s death, the ghost can only show him the pleasure of Stephen Few. The ghost then shows Excel the application’s neglected grave:

Excel: 1985 – 365.
Here lies one who frivolously cavorted with the entire office while not using adequate protection.
Recalculate AND Die.

Sobbing, Excel pledges to the ghost that he will change his Table Protection to avoid this outcome. Excel awakens the next day a changed program. Excel spends the day with Mr Gates’ family and anonymously sends a large update to Tables to the Weir home for Christmas dinner. From then on Excel began to treat everyone who uses Tables in a protected workbook with kindness, generosity and compassion, embodying the spirit of User Experience.