I fire up Excel and start with a blank
- 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
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
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)
- I switch back to my spreadsheet and manually recalculate the
spreadsheet by pressing F9. It
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.
The code now looks like this:
- 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
- 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.
Public Function i2r(i As Integer) As String
Application.Volatile ' don't forget to add this!
i2r = "I"
- So I pop back to Excel.
Do a manual “F9” recalc and hey-presto! my first test has passed.
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.