Using new 'spill range' formulas in Excel VBA

[Weaver1967]

I thought it would be useful to use the 'filter' function from Excel in an Excel VBA script.  I had a table where I needed the results from one column, dependent on the value of the second column, meaning I couldn't use the VBA filter function.

So I figured you should be able to access the FILTER() function via 'worksheetfunction' but I got errors (see below) which made me realise that I don't know what data type the function passes back to VBA or even if it'll work.  I tried a number of ways of referring to the columns required, including as elements of a listObject but I think the real problem is either understanding what data type the function is trying to return or whether or not these new 'spill' formulas even work in VBA.  In the end I solved the problem with a sledgehammer (i.e. looping through all the rows in the table and building an array from all the matching data) but I'd still like to know if it's possible to make the "worksheetfunction" approach work.  

Update: I'm thinking the problem here is with the 'include' parameter, since I just tried using SORT() with the worksheetfunction and that worked.

for each f in worksheetfunction.filter(range("table1[column1]"),range("table1[column2]")="X")
    'do stuff
next f

[#2]

@Weaver1967 I must admit that I have no idea!

This, however, seems to work:

    Dim v As Variant
    For Each v In [FILTER(Table1[column1],Table1[Column2]="x")]
        MsgBox v
    Next

[#3]

@Jan Karel Pieterse 

I couldn't get this very Pythonesque syntax to work, type mismatch error

    Dim v As Variant

    With Me.Controls(dropName)
        .Clear
        For Each v In [Filter(causes["ListValue"],causes["ListName"]=listName)]
            .AddItem v
        Next v

    End With

I'm using this instead, which works but looks amateur as heck!

    Dim LO As ListObject
    Set LO = Sheets("Pick Lists").ListObjects("Causes")
    With Me.Controls(dropName)
        .Clear
        For r = 1 To LO.ListRows.Count
            If LO.Range.Cells(r + 1, 1).Value = listName Then
                .AddItem LO.Range.Cells(r + 1, 2)
            End If
        Next r
    End With

[#4]

Get rid of the quotes, simply write it as if it were a regular Excel formula

[#5]

so how is that even working?

[#6]

PS how would you achieve a result if the table and/or field names were dynamic?

[#7]

Private Sub testfilter()
    Dim v As Variant, str As String
    str = "Shut Down Reason"
    For Each v In [filter(causes[listValue],Causes[ListName]=str)]
        Debug.Print v
    Next v
End Sub

Doesn't seem to work if you have to use a variable as the condition.

[#8]

The square brackets are shorthand for "Evaluate". So you could write the same formula as a string variable and use Evaluate on the string variable:
str = "[filter(causes[listValue],Causes[ListName]=str)]"
For Each v In EValuate(str)

[#9]

That is achingly cool. Never knew that was a thing. Thank you.

[#10]

Tried this:

Private Sub testfilter()
    Dim v As Variant, StrCondition As String, strEval As String
    StrCondition = "Shut Down Reason"
    strEval = "[filter(causes[listValue],Causes[ListName]=""" & StrCondition & """)]"
    For Each v In Evaluate(strEval)
        Debug.Print v
    Next v
End Sub

Still getting type mismatch.

[#11 · Accepted solution]

I think you need to get rid of the square brackets in the string, the Evaluate function effectively replaces them

[#12]

That cracked it.