Skip to content
Add testing and TDD to VBA on Windows and Mac
Branch: master
Clone or download
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
src v2.0.0-beta.3 Aug 29, 2018
tests v2.0.0-beta.3 Aug 29, 2018
.gitattributes
.gitignore Update .gitignore Dec 31, 2016
LICENSE
README.md
vba-block.toml Add vba-block Aug 28, 2018
vba-test-blank.xlsm v2.0.0-beta.3 Aug 29, 2018

README.md

vba-test

vba-test (formerly Excel-TDD and VBA-TDD) adds testing to VBA on Windows and Mac.

Donate

Example

Function AddTests() As TestSuite
  Set AddTests = New TestSuite
  AddTests.Description = "Add"

  ' Report results to the Immediate Window
  ' (ctrl + g or View > Immediate Window)
  Dim Reporter As New ImmediateReporter
  Reporter.ListenTo AddTests

  With AddTests.Test("should add two numbers")
    .IsEqual Add(2, 2), 4
    .IsEqual Add(3, -1), 2
    .IsEqual Add(-1, -2), -3
  End With

  With AddTests.Test("should add any number of numbers")
    .IsEqual Add(1, 2, 3), 6
    .IsEqual Add(1, 2, 3, 4), 10
  End With
End Function

Public Function Add(ParamArray Values() As Variant) As Double
  Dim i As Integer
  Add = 0
  
  For i = LBound(Values) To UBound(Values)
    Add = Add + Values(i)
  Next i
End Function

' Immediate Window:
'
' === Add ===
' + should add two numbers
' + should add any number of numbers
' = PASS (2 of 2 passed) =

For details of the process of reaching this example, see the TDD Example

Advanced Example

For an advanced example of what is possible with vba-test, check out the tests for VBA-Web

Getting Started

  1. Download the latest release (v2.0.0-beta.3)
  2. Add src/TestSuite.cls, src/TestCase.cls, add src/ImmediateReporter.cls to your project
  3. If you're starting from scratch with Excel, you can use vba-test-blank.xlsm

If you're updating from Excel-TDD v1, follow these upgrade details.

TestSuite

A test suite groups tests together, runs test hooks for actions that should be run before and after tests, and is responsible for passing test results to reporters.

' Create a new test suite
Dim Suite As New TestSuite
Suite.Description = "Module Name"

' Create a new test
Dim Test As TestCase
Set Test = Suite.Test("Test Name")
Test.IsEqual ' ...

' or create and use test using With
With Suite.Test("Test Name")
  .IsEqual '...
End With

TestSuite API

  • Description
  • Test(Name) As TestCase
  • Event BeforeEach(Test)
  • Event Result(Test)
  • Event AfterEach(Test)

TestCase

A test case uses assertions to test a specific part of your application.

With Suite.Test("specific part of your application")
  .IsEqual A, B, "(optional message, e.g. result should be 12)"
  .NotEqual B, C

  .IsOk C > B
  .NotOk B > C

  .IsUndefined ' Checks Nothing, Empty, Missing, or Null
  .NotUndefined

  .Includes Array(1, 2, 3), 2
  .NotIncludes Array(1, 2, 3), 4
  .IsApproximate 1.001, 1.002, 2
  .NotApproximate 1.001, 1.009, 3
  
  On Error Resume Next
  
  Err.Raise vbObjectError + 1, Description:="Uh oh."
  .IsError Description:="Uh oh."
  
  Err.Clear
  .NotError

  .Pass
  .Fail "e.g. should not have gotten here" 
  .Plan 4 ' Should only be 4 assertions, more or less fails
  .Skip ' skip this test
End With

With Suite.Test("complex things")
  .IsEqual _
    ThisWorkbook.Sheets("Hidden").Visible, _
    XlSheetVisibility.xlSheetVisible
  .IsEqual _
    ThisWorkbook.Sheets("Main").Cells(1, 1).Interior.Color, _
    RGB(255, 0, 0)
End With

In addition to these basic assertions, custom assertions can be made by passing the TestCase to an assertion function

Sub ToBeWithin(Test As TestCase, Value As Variant, Min As Variant, Max As Variant)
  Dim Message As String
  Message = "Expected " & Value & " to be within " & Min & " and " & Max

  Test.IsOk Value >= Min, Message
  Test.IsOk Value <= Max, Message
End Sub

With Suite.Test("...")
  ToBeWithin(.Self, Value, 0, 100)
End With

TestCase API

  • Test.Name
  • Test.Self - Reference to test case (useful inside of With)
  • Test.Context - Dictionary holding test context (useful for BeforeEach/AfterEach)
  • Test.IsEqual(A, B, [Message])
  • Test.NotEqual(A, B, [Message])
  • Test.IsOk(Value, [Message])
  • Test.NotOk(Value, [Message])
  • Test.IsUndefined(Value, [Message])
  • Test.NotUndefined(Value, [Message])
  • Test.Includes(Values, Value, [Message]) - Check if value is included in array or Collection
  • Test.NotIncludes(Values, Value, [Message])
  • Test.IsApproximate(A, B, SignificantFigures, [Message]) - Check if two values are close to each other (useful for Double values)
  • Test.NotApproximate(A, B, SignificantFigures, [Message])
  • Test.IsError([Number], [Source], [Description], [Message]) - Check if Err contains an error
  • Test.NotError([Message])
  • Test.Pass() - Explicitly pass the test
  • Test.Fail([Message]) - Explicitly fail the test
  • Test.Plan(Count) - For tests with loops and branches, it is important to catch if any assertions are skipped or extra
  • Test.Skip() - Notify suite to skip this test

ImmediateReporter

With your tests defined, the easiest way to display the test results is with ImmediateReporter. This outputs results to the Immediate Window (ctrl+g or View > Immediate Window) and is useful for running your tests without leaving the VBA editor.

Public Function Suite As TestSuite
  Set Suite = New TestSuite
  Suite.Description = "..."

  ' Create reporter and attach it to these specs
  Dim Reporter As New ImmediateReporter
  Reporter.ListenTo Suite

  ' -> Reporter will now output results as they are generated
End Function

Context / Lifecycle Hooks

TestSuite includes events for setup and teardown before tests and a Context object for passing values into tests that are properly torn down between tests.

' Class TestFixture
Private WithEvents pSuite As TestSuite

Public Sub ListenTo(Suite As TestSuite)
  Set pSuite = Suite
End Sub

Private Sub pSuite_BeforeEach(Test As TestCase)
  Test.Context.Add "fixture", New Collection
End Sub

Private Sub pSuite_AfterEach(Test As TestCase)
  ' Context is cleared automatically,
  ' but can manually cleanup here
End Sub

' Elsewhere

Dim Suite As New TestSuite

Dim Fixture As New TestFixture
Fixture.ListenTo Suite

With Suite.Test("...")
  .Context("fixture").Add "..."
End With
You can’t perform that action at this time.