Wrong data type in WorksheetFunction.Filter

Just encountered this problem myself and wanted to post my workaround.

We need to return an array of True/False to the worksheet function. To do this I created a Function that takes a 2D array, the column wanted and the value to compare. It then returns a 2d single column array of the necessary True/False.

Function myeval(arr() As Variant, clm As Long, vl As Variant) As Variant()
    Dim temp() As Variant
    ReDim temp(1 To UBound(arr, 1), 1 To 1)
    
    Dim i As Long
    For i = 1 To UBound(arr, 1)
        temp(i, 1) = arr(i, clm) = vl
    Next i
    
    myeval = temp
End Function

So in this particular case it would be called:

Function MonstersInLevel(level As Integer) As Variant
    MonstersInLevel = Application.WorksheetFunction.Filter(Range("LevelMonsters").Columns(2), myeval(Range("LevelMonsters").Value, 1, level),"""")
End Function

Avoid type mismatch in Worksheetfunction via VBA

Keeping in mind that the 2nd argument reflects a dynamic matrix condition based entirely on ►worksheet logic (returning an array of 0 or 1 cell values /False or True]) it seems that you have

• to execute an evaluation at least within this argument and
• declare the function type (explicitly or implicitly) as Variant

Function MonstersInLevel(level As Integer) As Variant
    ''   Failing assignment:
    '    MonstersInLevel = Application.WorksheetFunction.Filter(Range("LevelMonsters").Columns(2), _
    '        Range("LevelMonsters").Columns(1) = level _
    '        )
    MonstersInLevel = Application.WorksheetFunction.Filter( _
                      Range("LevelMonsters").Columns(2), _
                      Evaluate(Range("LevelMonsters").Columns(1).Address & "=" & level) _
                      )
End Function

...or to evaluate the complete function

Function MonstersInLevel(level As Integer) As Variant
    Dim expr As String
    expr = "=Filter(" & _
           Range("LevelMonsters").Columns(2).Address & "," & _
           Range("LevelMonsters").Columns(1).Address & "=" & level & _
           ")"
    'Debug.Print expr
    MonstersInLevel = Evaluate(expr)
End Function

Example call writing results to any target

Dim v
v = MonstersInLevel(2)
Sheet1.Range("D2").Resize(UBound(v), UBound(v, 2)) = v

Of course it would be possible as well to write .Formula2 expressions programmatically, even splitting into spill range references.

Addendum ........... //as of Jan 10th

Backwards compatible workaround via VBA.Filter()

"If you guys know any other VBA function that would be more appropriate than Application.WorksheetFunction.Filter I'd be ok."

In order to provide also a backwards compatible alternative, I demonstrate the following approach using the classic (VBA.)Filter() function (see section [3]) based upon prior matching results (see [1]).

Note that Application.Match() comparing two (!) array inputs delivers a whole array of possible findings (instead of a single result as most frequently executed). Non findings are identified by IsError() values of -1; adding +1 results in a set of zeros and ones. Section [2] enters corresponding data for positive findings. Eventually non-findings (i.e. 0or zero) are removed by a tricky negative filtering.

Function getLevels()

Function getLevels(rng As Range, ByVal level As Long)
'Site: https://stackoverflow.com/questions/65630126/how-to-remove-only-the-duplicate-row-instead-of-removing-all-the-rows-that-follo
    '[0] get datafield array
    Dim v, v2
    v = Application.Index(rng.Value2, 0, 1)     ' 1st column
    v2 = Application.Index(rng.Value2, 0, 2)        ' 2nd column
    '[1] check data (with Match comparing 2 arrays :-)
    Dim results
    results = Application.Transpose(Application.Match(v, Array(level), 0))
    '[2] rebuild with False/True entries
    Dim i As Long
    For i = 1 To UBound(results)
        results(i) = IsError(results(i)) + 1        ' 0 or 1-values
        If results(i) Then results(i) = v2(i, 1)    ' get current value if true
    Next i
    '[3] remove zeros (negative filtering)
    results = Filter(results, "0", False)
    '[4] return results as vertical 1-based array
    getLevels = Application.Transpose(results)
End Function

Example call

    Const LVL = 2       ' define level
    With Sheet1                                     ' change to project's sheet Code(Name)
        'define data range (assuming columns A:B)
        Dim rng As Range
        Set rng = .UsedRange.Resize(, 2)
        'function call getLevels()
        Dim levels
        levels = getLevels(rng, level:=LVL)
        'write to target
        .Columns("I:I").Clear
        .Range("I2").Resize(UBound(levels), 1) = levels
    End With

Solution without any supporting VBA function:

Function MonstersInLevel(level As Integer) As Variant
    With Application.WorksheetFunction
        MonstersInLevel = .Filter(Range("LevelMonsters").Columns(2), _
        .IfError(.XLookup(Range("LevelMonsters").Columns(1), level, True), False))
    End With
End Function

XLookup returns an array of #N/A or True. IfError replaces errors with False. Finally, the Filter function receives an array of booleans as the second parameter.

EDIT

Removed the IfError function thanks to @ScottCraner:

Function MonstersInLevel(level As Integer) As Variant
    With Application.WorksheetFunction
        MonstersInLevel = .Filter(Range("LevelMonsters").Columns(2), _
        .XLookup(Range("LevelMonsters").Columns(1), level, True, False))
    End With
End Function

I couldn't resolve your question but as I did some testing on the subject trying to do so, I thought I'd share my findings:

Based on this Microsoft community post, or at least the answers there, it seems you will need to loop through the output in one way or another...

That question seems to want to achieve the same as what you are wanting to do (I think?).

On the other hand, I have never used the WorksheetFunction.Filter method, and the closest I could get it to working was like so:

Here is my sample data - RangeOne is Column A and RangeTwo is Column B. I have used the =FILTER() function in cell C1 evaluating the input in D1 for reference of expected results. Naturally this function is working as expected! The VBA routine is outputting to Columns E, F and G.

Sub TestFilterFunction()
    Dim TestArray As Variant
    
    Range("E1:E3") = Application.Filter(Range("RangeTwo"), Range("RangeOne"), Range("D1"))
    Range("F1:F3") = Application.Filter(Range("RangeTwo"), Range("RangeOne") = Range("D1")) 'Runtime Error 13
    Range("G1:G3") = Application.Filter(Range("RangeTwo"), Range("RangeOne"))
    
    TestArray = Application.Filter(Range("RangeTwo"), Range("RangeOne"), Range("D1"))
    TestArray = Application.Filter(Range("RangeTwo"), Range("RangeOne") = Range("D1"))      'Runtime Error 13
    TestArray = Application.Filter(Range("RangeTwo"), Range("RangeOne"))

    Range("H1:H3") = Application.Filter(Range("RangeTwo", "RangeOne"), Range("RangeOne"), Range("D1"))
    TestArray = Application.Filter(Range("A1:B9"), Range("RangeOne"), "2")

End Sub

Column E returned the first 3 values from RangeTwo. Column F has not been populated - This is because that line threw the Runtime error 13 - Type Mismatch Column G returned the first 3 values from RangeTwo. Column H returned the first 3 values from "A1:B9" (both ranges together) - specifically the first 3 values of column A.

I thought this was odd so I threw in an array to assign the values to rather than directly to the worksheet;

The first TestArray line and the third TestArray line both populated the array with the entire RangeTwo values;

I realised with the syntax of the first and third attempt at the WorksheetFunction.Filter, the entire range is returned (that being the first argument - Arg1 - range), but when trying to include the = Range("D1") , it returns the Type Mismatch error.

The final TestArray attempt being the same syntax as the Column H test, returned both columns in a 2D array (now TestArray(1 To 9, 1 To 2)).

I should note I couldn't find any documentation at all on WorksheetFunction.Filter so I'm assuming it does follow the same syntax as the Excel Sheet Function has.

If I find anything more on this topic I'll come back and edit it in, but for now it's looking like perhaps a solution using either loops or Index/Match functions also will need to happen to have the data returned in VBA.

I thought about perhaps writing the sheet formula to a cell and then grab that into an array or something but Excel inserts @ into it now which only returns a single cell result, i.e.

Range("J1").Formula = "=FILTER(B1:B9, A1:A9 = D1)"

Would return in J1:

=@FILTER(B1:B9, A1:A9 = D1)

Which with our sample data, would only return 2 in J1 as opposed to the expected/desired 2, 3 and 4 in J1:J3.

I can't work out a way to remove the @ as it is applied when the function is written to the cell unfortunately, but hopefully any of the above helps someone find a solution.

Just some comments to help you out.

If you are using the new FILTER() function from either a worksheet cell or within some VBA code, the first argument should be a range and the second argument should a a Boolean array. (if you don't enter something that can evaluate to a Boolean array, VBA may complain the the data type is wrong)

You would be best served (in VBA) if you:

1. explicitly declared a 2 dimensional, column-compatible, Boolean array
2. filled the array
3. used the array in the function call

Here is a super simple example. Say we want to filter the data from A1 to A6 to remove blanks. We could pick a cell and enter:

=FILTER(A1:A6,A1:A6<>"")

Looks like:

Now we want to perform the same activities with a VBA sub and put the result in a block starting with B9. The code:

Sub SingleColumn()
    Dim r As Range, wf As WorksheetFunction, i As Long
    Dim arr, s As String, dq As String, boo, rc As Long
    
    Set wf = Application.WorksheetFunction
    Set r = Range("A1:A6")
    rc = r.Rows.Count
    ReDim boo(1 To r.Rows.Count, 1 To 1) As Boolean
    
    i = 1
    For Each rr In r
        If rr.Value = "" Then
            boo(i, 1) = False
        Else
            boo(i, 1) = True
        End If
        i = i + 1
    Next rr

    arr = wf.Filter(r, boo)
    
    MsgBox LBound(arr, 1) & "-" & UBound(arr, 1) & vbCrLf & LBound(arr, 2) & "-" & UBound(arr, 2)
    
    Range("B9").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Result:

On Excel version 15.0 (2013), I don't see Application.WorksheetFunction.Filter (tried with Show Hidden Members):

So maybe this is a newer function in later versions ?

My top Google search directs me to this question ;)

So, my answer is to avoid the function primarily from the point of view of backwards compatibility.

Alternate code options presented below returning e.g. a Range and a Variant.

Input:

Code:

Option Explicit

Sub Test()
    Dim rngInput As Range
    Dim rngFiltered As Range
    Dim varFiltered As Variant
    Dim varItem As Variant
    
    Set rngInput = ThisWorkbook.Worksheets("Sheet1").Range("A2:B10")
    
    ' as range
    Debug.Print "' Output as Range"
    Set rngFiltered = MonstersInLevel_AsRange(rngInput, 2, 1, 2)
    Debug.Print "' " & rngFiltered.Address ' expect B5, B6, B8
    Debug.Print "' ---------------"
    
    ' as variant
    Debug.Print "' Output as Variant"
    varFiltered = MonstersInLevel_AsVariant(rngInput, 2, 1, 2)
    For Each varItem In varFiltered
        Debug.Print "' " & varItem ' expect 3, 4, 5
    Next varItem
    Debug.Print "' ---------------"

End Sub

Function MonstersInLevel_AsRange(rngToFilter As Range, _
    ByVal lngLevel As Long, _
    ByVal lngColIxToFilter As Long, _
    ByVal lngColIxForValue As Long) As Range
    
    Dim rngResult As Range
    Dim lngRowIndex As Long
    Dim lngResultIndex As Long
    
    Set rngResult = Nothing
    For lngRowIndex = 1 To rngToFilter.Rows.Count
        If rngToFilter.Cells(lngRowIndex, lngColIxToFilter) = lngLevel Then
            If rngResult Is Nothing Then
                Set rngResult = rngToFilter.Cells(lngRowIndex, lngColIxForValue)
            Else
                Set rngResult = Union(rngResult, rngToFilter.Cells(lngRowIndex, lngColIxForValue))
            End If
        End If
    Next lngRowIndex
       
    Set MonstersInLevel_AsRange = rngResult
    
End Function

Function MonstersInLevel_AsVariant(rngToFilter As Range, _
    ByVal lngLevel As Long, _
    ByVal lngColIxToFilter As Long, _
    ByVal lngColIxForValue As Long) As Variant
    
    Dim varResult As Variant
    Dim lngRowIndex As Long
    Dim lngResultIndex As Long
    
    lngResultIndex = 0
    ReDim varResult(0)
    For lngRowIndex = 1 To rngToFilter.Rows.Count
        If rngToFilter.Cells(lngRowIndex, lngColIxToFilter) = lngLevel Then
            lngResultIndex = lngResultIndex + 1
            ReDim Preserve varResult(1 To lngResultIndex)
            varResult(lngResultIndex) = rngToFilter.Cells(lngRowIndex, lngColIxForValue)
        End If
    Next lngRowIndex
    
    MonstersInLevel_AsVariant = varResult
    
End Function

Test output:

' Output as Range
' $B$5:$B$6,$B$8
' ---------------
' Output as Variant
' 3
' 5
' 4
' ---------------

Based on Christian Buses answer (https://stackoverflow.com/a/65671334/16578424) I wrote a generic function to use the FILTER-function.

It returns a one-dimensional array with the filtered values.

Public Function getFILTERValuesFromRange(rgResult As Range, rgFilter As Range, varValue As Variant) As Variant

If rgResult.Columns.count > 1 Or rgFilter.Columns.count > 1 Then
    Err.Raise vbObjectError + 512, , "Only ranges with one column are allowed."
ElseIf rgResult.Rows.count <> rgFilter.Rows.count Then
    Err.Raise vbObjectError + 512, , "Both ranges have to be of the same size."
End If

Dim arr1 As Variant
With Application.WorksheetFunction
    arr1 = .filter(rgResult, .XLookup(rgFilter, varValue, True, False))
End With
getFILTERValuesFromRange = getOneDimensionalArrayFromRangeArray(arr1)
End Function

Private Function getOneDimensionalArrayFromRangeArray(arr1 As Variant) As Variant
Dim arr2 As Variant
ReDim arr2(LBound(arr1, 1) To UBound(arr1, 1))

Dim i As Long
For i = 1 To UBound(arr1, 1)
    arr2(i) = arr1(i, 1)
Next
getOneDimensionalArrayFromRangeArray = arr2
End Function