We are in a refactor cycle, so it’s time to look at my mess and see what I can do to clean it up a bit.

I had to make a number of changes to the test in order to test multiple fields at once. It didn’t work with my existing function which changed a single field and saved the record.

So here’s the test function:

'@TestMethod("Verify Changes")
Private Sub WhenTwoTextFieldsChangeBeforeAndAfterValuesAreReturned()
    Dim testFormAuditor As FormAuditor
    Dim testCollection As New Collection
    Dim TestTextChange As New AuditFieldChange, TestComboChange As New AuditFieldChange
    With NewForm
        .TestText = "TextBeforeValue"
        .TestCombo = "ComboBeforeValue"
        .Dirty = False
        Set testFormAuditor = New FormAuditor
        .TestText = "TextAfterValue"
        .TestCombo = "ComboAfterValue"
        .Dirty = False
    End With
    Set testCollection = testFormAuditor.ListOfChanges
    Set TestTextChange = testCollection.Item(1).FieldChanges("TestText")
    Set TestComboChange = testCollection.Item(1).FieldChanges("TestCombo")
    Assert.IsTrue TestTextChange.OldValue = "TextBeforeValue" And TestTextChange.NewValue = "TextAfterValue" And TestComboChange.OldValue = "ComboBeforeValue" And TestComboChange.NewValue = "ComboAfterValue"
End Sub

I think there’s a lot to refactor here to get this into something that makes more sense and self-documents. It would be nice to have something that would take any number of array arguments, loop through them, update all the indicated fields, and save the form when complete.

In order to make that happen, I’m going to create a new test function using the ParamArray feature which will let me send as many parameters as I want to the function.

Here’s the documentation about it: Understanding parameter arrays (VBA) | Microsoft Learn

So here’s what I got:

'@TestMethod("Verify Changes")
Private Sub WhenTwoTextFieldsChangeBeforeAndAfterValuesAreReturned()
    Dim testFormAuditor As FormAuditor
    Dim testCollection As New Collection
    Dim TestTextChange As New AuditFieldChange, TestComboChange As New AuditFieldChange
    ChangeFields Array("TestText", "TextBeforeValue"), Array("TestCombo", "ComboBeforeValue")
    Set testFormAuditor = New FormAuditor
    ChangeFields Array("TestText", "TextAfterValue"), Array("TestCombo", "ComboAfterValue")
    Set testCollection = testFormAuditor.ListOfChanges
    Set TestTextChange = testCollection.Item(1).FieldChanges("TestText")
    Set TestComboChange = testCollection.Item(1).FieldChanges("TestCombo")
    Assert.IsTrue TestTextChange.OldValue = "TextBeforeValue" And TestTextChange.NewValue = "TextAfterValue" And TestComboChange.OldValue = "ComboBeforeValue" And TestComboChange.NewValue = "ComboAfterValue"
End Sub

Private Sub ChangeFields(ParamArray FieldChanges() As Variant)
    Dim arr As Variant
    For Each arr In FieldChanges
        NewForm(arr(0)) = arr(1)
    Next arr
    NewForm.Dirty = False
End Sub

And it still passes. So I added the sub “ChangeFields” with the ParamArray and I’m passing it an array of the field name and field value I want to set and I just pass in as many of those as I want. That cut out a number of lines from my code and I will be able to back port this for the single field changes as well. This first round of refactoring was successful! We’ll try another round tomorrow and see if we can make it even simpler next time.