I’ve decided to use an array instead of a collection. I have to loop over either to search for the element, the array will be small, and it is native to the VBA language and I don’t have to create a new object. Also, I am just planning on using the array in the configuration and don’t plan on having it change size at all.

Here is the function we will work with today:

Private Function IsControlTypeTracked(CType As AcControlType) As Boolean
    Dim retVal As Boolean
    Select Case CType
        Case acComboBox, acTextBox, acCheckBox, acOptionGroup: retVal = True
        Case Else: retVal = False
    End Select
    IsControlTypeTracked = retVal
End Function

I will start with just creating the array like this:

    Dim arrTrackedControlTypes As Variant
    arrTrackedControlTypes = Array(acComboBox, acTextBox, acCheckBox, acOptionGroup)

Then I need to loop over it to see whether the passed CType variable is in the array. If it’s found, we can also exit the loop. Here is the new function:

Private Function IsControlTypeTracked(CType As AcControlType) As Boolean
    Dim retVal As Boolean: retVal = False
    Dim arrTrackedControlTypes As Variant
    Dim itm As Variant
    arrTrackedControlTypes = Array(acComboBox, acTextBox, acCheckBox, acOptionGroup)
    For Each itm In arrTrackedControlTypes
        If itm = CType Then retVal = True
        If retVal = True Then Exit For
    Next itm
    IsControlTypeTracked = retVal
End Function

I’m also going to make the arrTrackedControlTypes a variant in the scope of the main class so it can be accessed by the programmer who will be using this class and will be able to set their own types that they want to track.

To the top of the class I’m adding a variable declaration:

Private arrTrackedControlTypes As Variant

I am currently using the Class_Initialize Sub to initialize other parameters, so I will also initialize this variable there with the defaults I have so far:

Private Sub Class_Initialize()
    Set FormToAudit = Forms("TestForm")
    FormToAudit.BeforeUpdate = "[Event Procedure]"
    arrTrackedControlTypes = Array(acComboBox, acTextBox, acCheckBox, acOptionGroup)
End Sub

And now I can update the IsControlTypeTracked Function to simplify it and use the new class level array:

Private Function IsControlTypeTracked(CType As AcControlType) As Boolean
    Dim retVal As Boolean: retVal = False
    Dim itm As Variant
    For Each itm In arrTrackedControlTypes
        If itm = CType Then retVal = True
        If retVal = True Then Exit For
    Next itm
    IsControlTypeTracked = retVal
End Function

And finally I added a new Public Property Let statement to set the array to something else if the programmer using this class would like to.

Public Property Let ArrayOfTrackedControlTypes(Val As Variant)
    arrTrackedControlTypes = Val
End Property

And let’s see if it compiles. Remember, this was all done under the umbrella of refactoring. I did not write any tests, but probably should have since I’m adding a new public property. I should add tests to make sure it works as expected. It does compile. So now I will add a test next time to make sure it’s working as expected.