Using new 'spill range' formulas in Excel VBA

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

excel

Formulas and Functions

Macros and VBA

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

11 Replies

JKPieterse

Silver Contributor

Jan 10, 2023

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

Weaver1967

Copper Contributor

Jan 10, 2023

JKPieterse

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

JKPieterse
Silver Contributor
Jan 10, 2023
Get rid of the quotes, simply write it as if it were a regular Excel formula

Forum Discussion

Using new 'spill range' formulas in Excel VBA

11 Replies

Resources