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

You probably mean to use `Assert.SequenceEquals` to compare the contents of both arrays, right? — Mathieu Guindon, Apr 25 '18 at 13:49
@MathieuGuindon I have changed is to your suggestion, but now I get the following error `unable to cast object of type 'system.int64' to type 'system.array'` — Alfa Bravo, Apr 25 '18 at 13:56
Please don't be loose with the data type. If you look at the `VarPtr`'s autocompletion or in the object browser (with hidden member shown), you will see that it returns a `LongPtr` and you should use that. `LongLong` works because you are on 64-bit Office but the same code would fail on 32-bit Office because it should be `Long` there. Only `LongPtr` can change size to accommodate, and thus is the correct data type to use w/ `VarPtr` (or anything else that exposes a raw pointer rather than just an ordinary integer) — this, Apr 25 '18 at 13:58
Gah, re-reading the article (I wrote it ...lol), I see what you're trying to do - in order to get a definitive answer we'll need to see at least the signature for `wsService.WriteAllData` (i.e. how the `Variant` parameter is being passed) - `VarPtr` *should* return the same value if the variant arrays are passed `ByRef`, but not if they're passed `ByVal`. Signature should be something like `Private Sub IWorksheetService_WriteAllData(ByRef data As Variant)`. — Mathieu Guindon, Apr 25 '18 at 14:00
@MathieuGuindon Hi, on your example you already had it as `Private Sub IWorksheetService_WriteAllData(ByRef data As Variant)`, and that is the way I had it from the beginning, but it shows two different pointers, and I have your code verbatim, that is also why I have the `AreEqual` and not `SequenceEquals` — Alfa Bravo, Apr 25 '18 at 14:09
Hmm, that whole `VarPtr` stuff might have been a stretch / me trying to figure out something to test for in a short example that involves dependencies and stubs. I'll update the article to a simpler test that asserts the two arrays contain the same values, with `SequenceEquals`. I can assure you that the code did work on my 32-bit Excel when I wrote it.. I shouldn't have involved pointers there, it needlessly complicates things. That said, *how to test whether a method is working with a specific array* is a very valid question nonetheless. — Mathieu Guindon, Apr 25 '18 at 14:18
I will refresh your article every day to check for the updated version :p — Alfa Bravo, Apr 25 '18 at 14:29

score 5 · Accepted Answer · answered Apr 25 '18 at 15:05

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.

And now everything is green ... :) – Alfa Bravo Apr 25 '18 at 15:14 — Alfa Bravo, Apr 25 '18 at 15:14

How to unit test VBA code? - Two different pointers

1 Answers1