We’re working on collecting metrics for the upcoming Management Review, and I’m currently collecting bug information of the different project releases.

The data is exported from the database to Excel, and there I do most of the math. At some point, I was sure I was making mistakes, because the formulas became complicated, and if you know Excel formula’s they are not that readable.

So I said, that would be a great time to test the Excel template for VS2005. Which is actually nice, but when errors occur, the information is not that clear. Anyway, after experiencing some Excel integration, I coded the different calculations TDD style on a helper object.

After completing most of the calculations, we looked at the time – the remaining coding is Excel integration, which can be a bit painful. I decided to take a second chance with Excel formulas, only to break them down to manageable pieces (=Columns).

Which works, but with 7000 rows, its not testable – I can only check a few rows, and if it looks fine – that’s it. But I have a remote safety net. I translated all each C# function to a formula. After reviewing that the simple formulas are complete translation of the C# code I’m more relaxed. I have tests on the C# side to prove it.

And then I found a bug. A case I didn’t test for in C#. I did find through Excel, as one of the rows gave a strange result. This is lucky, because like I said, I can only check a few results. So I’ve added a test case in C#, and once the code is ready, I’ll translate that to Excel.

So conclusions from today:

• Tests should be named correctly and readable, it helped me a lot. Even 2 days later, I already forgot what I was thinking.
• Factor the code into small functions – it’s easy to translate…
• Safety nets come in all sort of shapes.
• TDD doesn’t catch bugs if you didn’t think of a test for them.

Eventually, I want to make it as automatic as possible, so I think the Excel VS is the way to go.