How to unit test VBA code? - Two different pointers

Question

I am working on this excellent tutorial, but in the end my first test is not passing, due to the fact that I can clearly see I am creating two different arrays (and pointers), and trying to compare them with one another.

Now the tutorial from what I can see leaves out a few lines of code that I have added, which is also where I see the problem, but without those lines the code does not even run of coarse.

All my other test methods are the same as the example, except for this method that I created the following lines - else nothing happens when you run the test.

Public Sub Run(ByVal dataService As IDataService, ByVal wsService As IWorksheetService)
Dim data As Variant                   'Added this line
data = dataService.GetSomeTable       'Added this line
Call wsService.WriteAllData(data)     'Added this line
End Sub

And here is where I can see the code going south...

'@TestMethod
Public Sub WorksheetServiceWorksOffDataFromDataService()
    'Arrange
    Dim dataServiceStub As MyDataServiceStub
    Set dataServiceStub = New MyDataServiceStub
    Dim expected As LongLong
    expected = VarPtr(dataServiceStub.GetSomeTable)  'expected creates an Array

Dim wsServiceStub As MyWorksheetServiceStub
Set wsServiceStub = New MyWorksheetServiceStub

'Act
With New MyTestableMacro
    .Run dataServiceStub, wsServiceStub 'here we create a second array
End With

Dim actual As LongLong
actual = wsServiceStub.WrittenArrayPointer 'here we point to the address of the second array

'Assert
Assert.AreEqual expected, actual  ' this test fails cause it points to two different addresses
End Sub

I had to change the type from Long as in the tutorial for the array pointers to LongLong due to the fact that the number on 64 bit is too long for Long. LongPtr also worked

Answer 1

VarPtr is what's artificially complicating that test, introducing frail and flawed pointer logic that doesn't need to be there.

---

Change your stub data service to return some non-empty data - literally anything will do:

Option Explicit
Implements IDataService
'@Folder "Services.Stubs"

Private Function IDataService_GetSomeTable() As Variant
    IDataService_GetSomeTable = GetSomeTable
End Function

Public Function GetSomeTable() As Variant
    Dim result(1 To 50, 1 To 10) As Variant
    result(1, 1) = "test"
    GetSomeTable = result
End Function

Now change the stub worksheet service to keep a copy of the actual data (rather than just a LongPtr):

Option Explicit
Implements IWorksheetService
'@Folder "Services.Stubs"

Private Type TStub
    WasWritten As Boolean
    WrittenData As Variant
End Type
Private this As TStub

Private Sub IWorksheetService_WriteAllData(ByRef data As Variant)
    this.WasWritten = True
    this.WrittenData = data
End Sub

Public Property Get DataWasWritten() As Boolean
    DataWasWritten = this.WasWritten
End Property

Public Property Get WrittenData() As Variant
    WrittenData = this.WrittenData
End Property

Now change the test to assert that IDataService.GetSomeTable is returning the same data that IWorksheetService.WriteAllData works with - you can do that using Assert.SequenceEquals, which compares all elements of two arrays and fails if anything is different:

'@TestMethod
Public Sub WorksheetServiceWorksOffDataFromDataService()
    'Arrange
    Dim dataServiceStub As StubDataService
    Set dataServiceStub = New StubDataService
    Dim expected As Variant
    expected = dataServiceStub.GetSomeTable

    Dim wsServiceStub As StubWorksheetService
    Set wsServiceStub = New StubWorksheetService

    'Act
    With New Macro
        .Run dataServiceStub, wsServiceStub
    End With

    Dim actual As Variant
    actual = wsServiceStub.WrittenData

    'Assert
    Assert.SequenceEquals expected, actual
End Sub

This makes the test much simpler, and it passes:

---

I will be updating the article with this simpler test later today.