Excel's Dynamic Array function "Filter" in VBA

Question

I have used Excel's new dynamic array functions like Sort in VBA but have been unable to use the "Filter" function with Application.WorksheetFunction in VBA.

It gives a "Type Mismatch" or a similar error.

I am trying to extract information from a Table (ListObject) through a VBA sub.
A simple example could be a Table having Stock "Symbol", "Date" and "Close price" as 3 columns and a filter could be applied on the first column such as Table[Symbol] = "AAPL".

It will replace the bit lengthy AutoFilter in the VBA sub and could extract the information directly to an Array with the command like:

Array = Application.Worksheetfunction.Filter (Array, [include] .....)

The macro recorder results in something like:

ActiveCell.Formula2R1C1 = "=FILTER(NewPages,NewPages[Symbol]=""AAPL"","""")"

where NewPages is the name of the table.

I am unable to replicate it in VBA with results in an array.

taller · Accepted Answer · 2023-07-13 19:14:34Z

1

You could set formula with Formula2R1C1.

If you don't want to show result on worksheet, the easiest solution is to use Evaluate.

Sub Demo2()
    With ActiveSheet.ListObjects(1).Range
        arr = Application.Evaluate("=FILTER(" & .Address & "," & .Columns(1).Address & "=E2,"""")")
        Debug.Print UBound(arr), UBound(arr, 2)
    End With
End Sub

You definitely can use Application.WorksheetFunction.Filter in VBA. The tricky part is the 2nd parameter.

Sub Demo3()
    With ActiveSheet.ListObjects(1)
        arr = Application.WorksheetFunction.Filter(.Range, _
            Evaluate(.Range.Columns(1).Address & "=E2"), "")
        Debug.Print UBound(arr), UBound(arr, 2)
    End With
End Sub

The second parameter returns an array, which requires the use of the Evaluate function when calling it in VBA.

edited Jul 13, 2023 at 19:14

answered Jul 13, 2023 at 2:25

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ghulam Sabri Over a year ago

Thanks for your response but I want to assign the results directly to an array instead of occupying a range on worksheet instead ... This in any case will be a spilled range that's why an array will be easy to handle it ...

taller Over a year ago

@GhulamSabri Please try to get the result with Evaluate. I have updated the code.

Ghulam Sabri Over a year ago

Many thanks for your response. Could you elaborate that why we need to use "Evaluate" in the 2nd argument and why not in the first argument? This makes it very tricky to use the Filter function in VBA. Alternatively, I have figured out that AutoFilter works very well but it does not work on dynamic arrays directly, instead we need to use it on ranges/tables.

taller Over a year ago

The second parameter returns an array, which requires the use of the Evaluate function when calling it in VBA. It's helpful for understanding it with "Evaluate Formula"

Ghulam Sabri Over a year ago

Can we have multiple Filter criteria in this VBA formula? In Excel we can have multiple criteria as (Criteria1)*(Criteria2) etc. but I am not being able to do it in this tricky VBA substitution. For ex., in this case first criteria could be "=E2" and second could be "=E3" while E3 has a date value to filter on ? Thanks

Collectives™ on Stack Overflow

Excel's Dynamic Array function "Filter" in VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related