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.


SqlServer: get rowcounts for wildcards


SqlServer/PowerShell: simple test of backup and restore

The SQLServer package has taken over from SQLPS. Basic backup and restore examples on my GitHub, and the data I used to create the tables (just create the databases by hand for now).

And the screenshots as I got the hang of it…


Node.js: The [assert] package

I was tempted to write something about Assert with examples… but no point, as this person/persons has/have done a way better job than I would/could (Ed: enough).

And, in fact, this is part of a very generous GitBook:

[Later…] in fact I gave the book credit for giving more comprehensive coverage of Node.js that I assumed on first glance. This is the sum total of the book’s coverage:

Regardless, thanks to Nelson/Nelsonic for at least giving us this.


Pester and PowerShell: TDD

There is obviously nothing special about Pester that encourages you down the TDD route. But assuming you start out with a test structure pattern that works for you, then with some trivial cosmetics, I find it is pretty good fun to do.

An example of some failing tests:

And following a fix to the code under test…

This is my overarching test runner:

For contriving a TDD approach out of this, you could:

clone my repository from GitHub here

From this location, delete the section below, so that you have a test but no SUT for it

When you execute RunAllTests.ps1, you see this:

You can then create nothing but the function name…

and execute RunAllTests.ps1 again…

, and eventually you get to a point when you have fully working code…

… and all tests passing:

Jasmine: also has a CDN version

Such that you could stick this in a template, and not bother about downloading the artefacts:


June 8 2016 However, there was a bit of a fatal flaw: Jasmine having a CDN is fine… but the Angular Mocks library is harder to find as a CDN artefact. This is the one I found and currently works:

The whole thing…

And how that appears when all the tests fail…

The page as text:

<!DOCTYPE html>
<html lang="en">

        <meta charset="utf-8">
        <title>Jasmine as a UI</title>

        <!-- ensure that all the Jasmine files come before all the Angular files... -->
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jasmine/2.4.1/jasmine.css"></link>
        <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jasmine/2.4.1/jasmine.js"></script>
        <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jasmine/2.4.1/jasmine-html.js"></script>
        <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jasmine/2.4.1/boot.js"></script>

        <!-- ensure that you include the core Angular file... -->
        <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.5.1/angular.js"></script>

        <!-- before you reference the Angular mocks file -->
        <script src="https://cdnjs.cloudflare.com/ajax/libs/angular.js/1.5.1/angular-mocks.js"></script>

        <!-- include source files here... -->
        <script src="NamesApp.js"></script>

        <!-- include spec files here... -->
        <script src="NamesApp.spec.js"></script>