I overheard two people talking, one of whom was showing his intellectual prowess using that puzzle whose answer is always ‘four’. I don’t remember the specifics of the game, but you count the letters of an integer, do some basic math, and you always end up with ‘four’ because the word ‘four’ has that many letters in it. I get how it works, but I thought there’s surely another number with that property. Off to VBA to find out.
There’s not. But let me back up a bit. I searched the vast DDoE archives to find my Numbers-to-Words code. I’ve already written this code right? Apparently not. A broader search showed plenty of hits and, I’m sure, code, but I didn’t click any of the links. I’ve been hankering to write some test-first code and this seemed like a good candidate. I didn’t want to read anybody else’s code so it wouldn’t influence my test-first experience.
I’m going to document my test-first methodology in a series of posts. This is not test driven development (TDD), but borrows some of the principles from that. Basically, I write a test, watch it fail, then write enough code to make it pass. I refactor the code as necessary. If you are interested in TDD in Excel and VBA, search for xlUnit on Mike Woodhouse’s blog.
On to test 1! The idea is to write a function that returns a string that represents the English words of the argument. The procedure header should look like this:
|
1 |
Function NumbersToWords(ByVal dNumbers As Double) As String |
First, write a test.
|
1 2 3 4 5 6 7 8 |
Sub TEST_Singles() Debug.Assert NumbersToWords(0) = "zero" Debug.Assert NumbersToWords(1) = "one" Debug.Assert NumbersToWords(7) = "seven" Debug.Assert NumbersToWords(19) = "nineteen" End Sub |
I know that once I hit 20 a pattern will start emerging, so I’m going to start by testing up to 19. Now I have to write enough code to make this pass. That’s seems pretty easy.
|
1 2 3 4 5 6 7 8 9 |
Function NumbersToWords(ByVal dNumbers As Double) As String Dim vaSingles As Variant vaSingles = Split("zero,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,thirteen,fourteen,fifteen,sixteen,seventeen,eighteen,nineteen", ",") NumbersToWords = vaSingles(dNumbers) End Function |
When I run that test, it passes. I put all the words for zero to 19 in an array, then read the array. What could be easier? For my tests, I choose the two edge cases (0 and 19) and a couple in the middle. It’s not very rigorous, but what the heck. One of the benefits of a test-first approach is that you’re biting off a bigger problem in smaller chunks. No single code revision is particularly difficult, but when put together can be complex. The refactoring part will be the interesting part. I assume it will be apparent when it’s time to refactor (as it is in examples I’ve seen) but I’ve never done it before, so we’ll see.
Next time, I’ll test some ‘tens’.
Posting code? Use <pre> tags for VBA and <code> tags for inline.