VBA: TDD…ish

Test Driven Development (TDD) focuses on What you want to achieve before thinking about How. That focus means you define and prepare your (automated) tests before writing a line of production code. Ideally, the code under test, the to-be production code, does not even exist at the point you execute your first test. The principle can be applied to pretty much any language, including VBA – why not? Personally I like it a lot – amongst other things I find it satisfying to have a rigorous, up-front, Definition of Done in the form of a set of tests. Indeed, as you develop your tests, you will often find that the functionality requested by the Product Owner has ambiguities that she had not foreseen, leading to more functional rigour. Sure, it is a leap to suggest that a VBA project could be the subject of a Sprint,  but again, why not, especially in a hobby situation where you are everything from Product Owner to Sprint Master.

Code under test. Test code.

First I need a trivial test framework, for this scope, that is basically some utilities that I can re-use across projects. Quite often, I find a need to have a Sleep function available, and some expected/actual results formatting geared to my preferences is handy:



Having done that, I can then think about the problem I want to solve. Examples I find are always easier than definitions. If I have a “word” (e.g. “Curiosity”), then I want to know how many times it occurs in a “line” (a set of words delimited by a given character), e.g. in “Curiosity:Curiosity;Beagle”, then it occurs 1 time if the delimiter is “:”, and zero times if the delimiter is “;”. (you can disagree… but that’s the joy of functionality… you CAN disagree 🙂 )

So I’ll call my function “CountOfTimesOneWordOccursInALine”… and I’ll call my test TestCountOfTimesOneWordOccursInALine. You will see I have more than 1 test in that er test. Discuss. Or not.

What we have thus far then is a function name:


and a test name:


Write the first test to establish a pattern – first time round it won’t shouldn’t even compile if we are close to TDD, as the vba under test doesn’t exist:


Next time, if we think we have coded it correctly, we deliberately fail it, e.g. by putting in a mad expected result:


… with a predictable outcome:


Then we change the 42 to a zero… and get an OK:



And then write the rest of the tests making sure each one breaks before we allow it to pass:


And sharing the code under test:


In the above, the reference to [Dim uniqueSet As New Scripting.Dictionary] has to be set in Tools.


VBA for Word

Honestly, I have a genuine use-case for this. Yes I feel guilty writing VBA.. but one “I wonder if you can do x?”… (yes), led to a “I wonder if you can do y?” (yes). It would be nice if you could test this stuff confidently – sure I could trawl the final document to make sure it had 23 carriage returns (hm, maybe that’s the way) when before it had 2. At least that’s making a nod towards testing. I mean automated testing, of course.

Gist. Some text you can use for testing.

May 2018: Given a selection, remove all carriage returns (In fact I won’t repaste, but more typically the replacement should be a ” “, not a “”).



Microsoft Word: vba

This is predictably the same principle as Excel and other Office components.

So again I have this file…



… that I want to reduce to this file…



Few things you have to do first:

  • Enable the developer tab (see below)
  • When you write your vba, you have to save the host doc as a docm, not  a docx

Macro that achieves the functional stuff (not the config, to be clear): (gist)

This lacks any parameterisation – can you even do that? Dunno. Another day.


I note BTW that this is case-insensitive – check “Uniqueid”.


Might also be useful: https://msdn.microsoft.com/en-us/vba/vba-word



Excel: VBA

I needed to do stuff with strings in Excel, specifically, to get the initials from a full name (first name, last name, optional middle name), to make that the basis of an (non-unique) identifier. I had not previously written VBA (sure, there are other ways), so this was new, and dare I say, quite amusing.

This is an example of the input (B2 down) and the output (C2 down):

First point is how did I get the Column B data? I grabbed it from the Adventureworks example database (look elsewhere in the blog for that), and then ran this SQL over it:

 ;with names as 
 (select p.BusinessEntityID, p.FirstName + ' ' + ISNULL(p.MiddleName,'') + ' ' + p.LastName as FullName 
 from [AdventureWorks2016].[Person].[Person] p)
 select * from names
 order by BusinessEntityID

, which returns you this, for a very useful 3,000 rows or so of test/dummy data (btw):

Here is the code for the function that gets the initials from the full name:

But when I first opened  Excel for Office 365, I could see no way of editing vba. I happened to know I needed the Developer tab to be visible, and it is not visible, by default. The steps to enable the Developer tab are:




Once you have ticked the Developer check box, the Developer tab will appear, and you can then start editing (you will be prompted to enable macros, which you must accept):


Gist for the SQL, Gist for the VBA.

So going back to the first screenshot, the way you turn [Ken J Sánchez] into [KJS] is type [GetInitials(B2)] into the cell C2, then copy down. Now obviously I could have done this in a 1-liner, but that would have been ugly and hard to test. A bonus of this way is that you get a step-in debugger.

A further trivial example. Gist.


July 2018

Adding a small bit to that…(gist)