Compile error: ByRef argument type mismatch for vba isInArray macros

• Another option is using ByVal, then you don't need to change your main Sub.

Function IsInArray(ByVal stringToBeFound As String, arr As Variant) As Boolean
    IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function

How about something like this:

Dim str_part as String

    For Each part In curAr
        str_part = CStr(part)
        If Not isInArray(str_part, ar) Then ' part not in main list of parts, append it
            ThisWorkbook.Worksheets("B Averages").Range("A" & lastRowAverages + 1) = part
        End If

(1) Your type mismatch error doesn't come from the array, it comes from the first parameter. You pass part as argument, and that is of type Variant, while your routine expects a String.

A Variant is something different than a String. It can hold a string (in your case it does, as the Vartype returns 8 (= vbString), but it can hold everything else also. Now your routines expects the value to be passed by reference, which means that not the value is passed but the address where the value can be found. However, in your code, at that address there will be a variant.

The VBA compiler will throw an error because this cannot work.

You can do three different things to solve this:

• Cast the argument into a string - Tim Williams showed that in the comments: If Not isInArray(CStr(part), ar) Then will convert the content of the variant into a string, store this String (which is a copy of the string you have in the variant) at a certain point in memory and pass the address to the routine.

• Change the parameter from String to Variant:

  Public Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean

• Change the way the value is passed to ByVal (that's what taller describes in his answer). Now the VBA runtime will implicitly convert the content of the Variant (part) into a string and pass it to the function.

  Public Function IsInArray(ByVal stringToBeFound As String, arr As Variant) As Boolean

• Declaring part As String would solve that issue also, but unfortunately you can't do that because in that case it cannot be used in the For Each loop.

(2) The vartype of 8204 is a Sum of vbArray (8192) and vbVariant (12). So we have an array of variants - which makes perfectly sense as you read the content of Excel cells, and those cells can contain different types of data.

(3) When you fix the parameter problem as described, your first version of IsInArray will run into a runtime error 9 (subscript out of range). This is because when you read data from an Excel range into an array, the array always has 2 dimensions (even if you read only one row or one column). The only exception is when you read only a single cell, in that case you don't get an array but the single value. You could change that routine to

Public Function IsInArray(ByVal stringToBeFound As String, arr As Variant) As Boolean
    If Not IsArray(arr) Then
        IsInArray = (stringToBeFound = arr)
        Exit Function
    End If
    
    Dim arrVal As Variant
    For Each arrVal In arr
        If arrVal = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next arrVal
    IsInArray = False
End Function

(4) your second version of IsInArray will fail because Application.Match works only with one-dimensional arrays or with ranges that contain only one column.

The main reason you are having an issue? Because part is Variant, but stringToBeFound is String. As the error message says, the Type of the Parameter you are supplying does not match the Argument you are trying to fit it in. If you change your function to accept a Variant, or use IsInArray(Cstr(part), ar) instead, then it will stop giving that error.

(Note: this is a one-way issue. You can pass a String parameter to a Variant argument, but not a Variant parameter to a String variant. Because, a Variant parameter might end up being a Long or an Object or similar — it's like trying to put a large object in a small hole, versus trying to put a small object in a large hole)

That said: why limit yourself to just checking strings? You could swap out stringToBeFound As String for valueToBeFound as Variant without much issue.

Next: if lastRowAverages is ever 2, then ar will be a single value, and not an Array. Which will give a different error (but at Runtime, not when compiling)

Finally: your Arrays are 2D, because you created them from a Range. However, your first Function will only support 1D arrays, unless you change arr(i) to arr(i,1). Then again, you've already demonstrated an understanding of how to loop over arrays. As such, try this:

Public Function IsInArray(ByVal val As Variant, ByVal arr As Variant) As Boolean
    IsInArray = False
    If IsArray(arr) Then
        Dim element As Variant
        For Each element In arr
            If element = val Then
                IsInArray = True
                Exit Function
            End If
        Next element
    Else
        IsInArray = (val = arr)
    End If
End Function

Update List

Issues

• arr = rg.Value works only if the range contains more than one cell. Otherwise, arr is not an array but a Variant holding a single value. You could use the GetRange function to ensure it's an array.

• Your first function is looping through a 1D array while you're passing it a 2D one-based (single-column) array (if not a simple value). Replace arr(i) with arr(i, 1) and ensure it's an array.

Main

Sub UpdateListOfParts()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim dws As Worksheet: Set dws = wb.Sheets("B Averages")
    
    Dim drg As Range: Set drg = RefSingleColumnRangeEnd(dws.Range("A2"))
    
    If drg Is Nothing Then
        MsgBox "No data in column!", vbExclamation
        Exit Sub
    End If
    
    Dim dcell As Range: Set dcell = drg.Cells(drg.Cells.Count).Offset(1)
    Dim dData() As Variant: dData = GetRange(drg)
    
    Dim sws As Worksheet, srg As Range, sData() As Variant
    Dim mData As Variant, mrCount As Long
    
    For Each sws In wb.Worksheets ' skip charts
        If InStr(sws.Name, "B") And InStr(sws.Name, "Chart") Then
            Set srg = RefSingleColumnRangeEnd(sws.Range("A2"))
            If Not srg Is Nothing Then
                sData = GetRange(srg)
                mData = GetMisMatches(sData, dData)
                If Not IsEmpty(mData) Then
                    mrCount = UBound(mData, 1)
                    dcell.Resize(mrCount).Value = mData
                    Set dcell = dcell.Offset(mrCount)
                End If
            End If
        End If
    Next sws
    
    MsgBox "List of parts updated.", vbInformation

End Sub

Help

Function RefSingleColumnRangeEnd(topCell As Range) As Range
    If topCell Is Nothing Then Exit Function
    Dim ws As Worksheet: Set ws = topCell.Worksheet
    Dim RowsCount As Long
    With topCell
        RowsCount = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row - .Row + 1
        If RowsCount < 1 Then Exit Function
        Set RefSingleColumnRangeEnd = .Resize(RowsCount)
    End With
End Function

Function GetRange(rg As Range) As Variant
    If rg Is Nothing Then Exit Function
    If rg.Rows.Count * rg.Columns.Count = 1 Then
        Dim Data() As Variant: ReDim Data(1 To 1, 1 To 1)
        Data(1, 1) = rg.Value
        GetRange = Data
    Else
        GetRange = rg.Value
    End If
End Function

• The following will fail if there are blanks in the source array (sData)!

Function GetMisMatches(sData As Variant, dData As Variant) As Variant
    
    Dim drIndices As Variant: drIndices = Application.Match(sData, dData, 0)
    Dim MatchesCount As Long: MatchesCount = Application.Count(drIndices)
    Dim srCount As Long: srCount = UBound(sData, 1)
    
    Select Case MatchesCount
        Case 0: GetMisMatches = sData: Exit Function ' all mismatches
        Case srCount: Exit Function ' no mismatch
    End Select
    
    Dim mData() As Variant: ReDim mData(1 To srCount - MatchesCount, 1 To 1)
    
    Dim sr As Long, dr As Long
    
    For sr = 1 To srCount
        If IsError(drIndices(sr, 1)) Then
            dr = dr + 1
            mData(dr, 1) = sData(sr, 1)
        End If
    Next sr
    
    GetMisMatches = mData

End Function