Excel vba Variant array lookup

Question

I have two (non empty) arrays (of variants) with numbers. I would like to list all the data that is in the first array and is not in the second array.

Dim existingWorkerIDs() As Variant
Dim newWorkerIDs() As Variant

  For Each temp In newWorkerIDs

        If existingWorkerIDs.contains(temp) Then
            ...do sth...
        End If

   Next temp

Is it possible?

Answer 1

Easily doable by abusing MATCH.

The first procedure is just a test for verification, and also an example of how things have to be declared (and conversely, what declarations you would have to change if you need other variable types, etc).

Sub testCaller()
    Dim testArr1() As Variant ' <~~ Variable type must match
    Dim testArr2() As Variant '     the variable required in the 
    Dim testArr3() As Variant '     actual procedure
    Dim testArr4() As Variant


    testArr1 = Array("abc", "abc", "def", "abc", "asdf", "bcd")
    testArr2 = Array("abc", "asdf")
    Call listUniqueArrayContents(testArr1(), testArr2())

    testArr3 = Array(1, 2, 3, 4, 5)
    testArr4 = Array(1, 2)
    Call listUniqueArrayContents(testArr3(), testArr4())
End Sub

Sub listUniqueArrayContents(arr() As Variant, arrCompare() As Variant)
    Dim uniqueValues() As Variant
    Dim mIndex As Variant
    Dim j As Integer

    j = 0

    For i = 0 To UBound(arr())
        ' Reset the placeholder for our MATCH values
        mIndex = Null

        ' Disable errors, otherwise you get popups every time there's a unique value
        On Error Resume Next

        ' Call MATCH function
        mIndex = Application.WorksheetFunction.match(arr(i), arrCompare(), 0)

        ' Restore normal error handling
        On Error GoTo 0

        If mIndex < 1 Or IsNull(mIndex) Then
            ' If match variable is Null, it means the value was unique
            ' So we'll write that value to a separate array to keep track of it
            If j = 0 Then ReDim Preserve uniqueValues(0 To 0)
            If j <> 0 Then ReDim Preserve uniqueValues(UBound(uniqueValues()) + 1)
            uniqueValues(UBound(uniqueValues)) = arr(i)
            j = j + 1
        End If
    Next i

    Debug.Print "--Unique values:--"
    For k = LBound(uniqueValues()) To UBound(uniqueValues())
        Debug.Print uniqueValues(k)
    Next k
    Debug.Print "--End--"
End Sub

Which, for the test examples, gives you the expected:

--Unique values:--
def
bcd
--End--
--Unique values:--
3
4
5
--End--

Alternatively, you can change this into a Function and have it return the array of unique values.

Change this:
Sub listUniqueArrayContents(arr() As Variant, arrCompare() As Variant)

to this:
Function listUniqueArrayContents(arr() As Variant, arrCompare() As Variant) As Variant

and replace the last-most For-loop with listUniqueArrayContents = uniqueValues()