1. Gareth Leachman
  2. vba unittest framework

Overview

HTTPS SSH

vba Unit testing Framework

Objective

To create a simple NUnit style unit testing framework for vba that can run from the command line or inline from the VBA IDE. My project differentiates itself from other similar frameworks by providing extensibility.

I am also working on a series of short demo youtube clips to make it more accessable.

Any feedback you have would be welcome.

Benefits

Class based development encouraged.

Examples provided

  • I've included a bridge dealing example. This includes the vba bridge dealing spreadsheet, as well as the vba unit testing project for the spreadsheet.

Setup youtube demos

License

Requirements

To setup a test book

1) Create a new project. reference the unittestFramework.xla and your system under test project.


2) Create a class for a set of tests implementing ITestAble. or type templates.TestClass in immediate pane

 'simpleTestClass
 Implements ITestAble
 Option Explicit

 Public t As ITestRunner
 Private Sub ITestAble_setTestRunner(testRunner As unittestFramework.ITestRunner)
     Set t = testRunner
 End Sub
 Private Sub ITestAble_setUp()
 End Sub
 Private Sub ITestAble_tearDown()
 End Sub

3) Add your tests into the class

 Public Sub aFailingTest()
     t.AssertTrue False
 End Sub

3) From the immediate pain type

 test
 or
 tester(defaults.reportToDebug).run

This code is from the bridgeDealTests.xls example provided

 ...
 Private Sub ITestAble_setUp()
     Set sut = mFactory.get_Card(Suits.hearts, Values.Ace)
 End Sub
 ...
 'SUT Template End

 Public Sub shouldHaveAValidHasValue()

     Call sut.setSuit(Clubs)
     Call sut.SetValue(Values.Two)
     t.AssertEqual CStr(sut.getHashValue), Split(FiftyTwoPrimes, ";")(0), "Two of Diamonds has is wrong hash (" & sut.getHashValue & ")  " & Split(FiftyTwoPrimes, ";")(0)

     Call sut.setSuit(Diamonds)
     Call sut.SetValue(Values.Two)
     t.AssertEqual CStr(sut.getHashValue), Split(FiftyTwoPrimes, ";")(13), "Two of Diamonds has is wrong hash (" & sut.getHashValue & ")  " & Split(FiftyTwoPrimes, ";")(13)

 End Sub

 Public Sub shouldReportTheCorrectName()

     Set sut = mFactory.get_Card(Suits.Clubs, Values.Five)
     t.AssertEqual sut.toString(), "Five of Club", "The card should be a 5 of clubs"

 End Sub

Command line

To run the example on the downloads page from the command line

 excel /e /r BridgeDeals.xls BridgeDealsTests.xls /out:c:\temp\report.txt /quit
 /out:<path>        : to output a text file using the default fileReporter.
 /quit              : to quit after the tests have run.

make sure you have the files in the correct order with the Test project last.

Specifics

  • Any public method in a class that implement ITestable will be considered a test.
  • A test will not be run if it is a public method with a prefix "xx"

Features still to be added

  • add another project for an excel BuildTool
  • create a .net implementation
  • implement cruisecontrol build and release

Other vba unittest frameworks

Creating test data

Tools:

Disclaimer

The techniques applied here are well documented in many places. I make no claim to ingenuity.