Adding my first test and setting up my testing environment

I fire up Excel and start with a blank spreadsheet.

  • I type column headings in row 1 (“Integer” in Column A and “Roman” in column B).

  • I add my first row of test data in row 2, using the test case that 1=i.

My spreadsheet looks like this:

  • Then I add another heading into cell C1 called “=i2r()” and I use in cell C2 I type the formula ‘=i2r(A2)’. This is the function that I’m going to build, test-case by test-case.

But, since the function i2r() doesn’t exist yet, Excel gives an error. This is the same as a compile error in, say, Java.

I need to add a new function called i2r() before it will work.

  • So I chose Tools/Macros/Visual Basic Editor which, unsurprisingly, opened up the Visual Basic editor. I could also have pressed ALT-F11. I then hunt around the menus and find INSERT/PROCEDURE which sounded handy – I presume that this is how I insert a new function (not just a procedure).

  • Bad news: it’s greyed out.

  • But, just underneath it is INSERT/MODULE which I bravely click and it gives me a new module. I check under the INSERT menu and PROCEDURE is now enabled. So I click on it and add in a new function (not procedure) – naming it i2r().

Then I add in an integer parameter to the function and (lazily) call it i.

Public Function i2r(i As Integer)

End Function

  • I switch back to my spreadsheet and manually recalculate the spreadsheet by pressing F9. It shows me:

My first test is now failing! This is progress. But, why is i2r returning 0 ? I look at the code and realise the VBA default must be to return 0.

  • I switch to VBA, take a peek in the help files to learn how VBA returns parameters and then change the function so that it returns the string “I”.

  • I also add in “Application.Vaolatile” to the top of the function which forces Excel to recalculate spreadsheet cells which use VBA code that has changed (it doesn’t do this automatically otherwise). You won’t believe how well this little trick is hidden in the Excel help files.

The code now looks like this:

Public Function i2r(i As Integer) As String

Application.Volatile ' don't forget to add this!

i2r = "I"

End Function

  • So I pop back to Excel. Do a manual “F9” recalc and hey-presto! my first test has passed.

Phewww! Since I’m writing up my steps as I go this has taken me a while, but in normal circumstances I reckon it would take 1-2 minutes, tops.

Right, now let's add some Visual Feedback - I want to know when my tests are passing and failing.