VBA: Way to read autofilter criteria w/o runtime errors?

[rlv01]

I'm working on a routine to preserve the state of the user's autofilter picks for a given range of cells so they can later be restored. However, attempting to read filter criteria under certain circumstances produces runtime errors. Some questions:

1. For filters using only one criteria (criteria1), attempting to read criteria2 produces a runtime error. Is there a way to test for the existence of criteria2 without throwing an error, or am I going to be reduced to something like "On Error Resume Next"?

2. Setting a filter for background color produces a runtime error when trying to read the critera1 value for that filter. But if you set a font color filter, there are no issues reading the criteria1 value. Why?

Sub AutoFilterTest()
Dim WS As Worksheet, I As Long, F As Filter

    Set WS = ActiveSheet

    'Clear old data
    WS.AutoFilterMode = False
    With WS.Cells(1, 1)
        .EntireColumn.Clear
        .Value = "Data"
    End With

    'Put some Data in Column1 to filter
    For I = 2 To 100
        If I < 100 Then
            WS.Cells(I, 1).Value = "Red"
            WS.Cells(I, 1).Interior.Color = RGB(255, 0, 0)
            WS.Cells(I, 1).Font.Color = RGB(255, 255, 255)
        End If
        If I < 75 Then
            WS.Cells(I, 1).Value = "Green"
            WS.Cells(I, 1).Interior.Color = RGB(0, 255, 0)
            WS.Cells(I, 1).Font.Color = RGB(0, 0, 0)
        End If
        If I < 50 Then
            WS.Cells(I, 1).Value = "Yellow"
            WS.Cells(I, 1).Interior.Color = RGB(255, 255, 0)
            WS.Cells(I, 1).Font.Color = RGB(0, 0, 0)
        End If
        If I < 25 Then
            WS.Cells(I, 1).Value = "Blue"
            WS.Cells(I, 1).Interior.Color = RGB(0, 0, 255)
            WS.Cells(I, 1).Font.Color = RGB(255, 255, 255)
        End If
    Next I


    'Test getting existing filter properties
    ''''''''''''''''''''''
    I = 1

    'Set an autofilter
    'Filter on cell contents
    WS.UsedRange.AutoFilter Field:=1, Criteria1:="Green", Operator:=xlOr, Criteria2:="Blue"

    'Inspect the autofilter properties
    With WS.AutoFilter.Filters(1)
        If .On Then
            Debug.Print "State       : Filter " & I & " is ON"
            Debug.Print "Criteria1   : " & .Criteria1
            Debug.Print "Criteria2   : " & .Criteria2    '<- no error
            Debug.Print "Operator    : " & .Operator
        Else
            Debug.Print "State       : Filter " & I & " is OFF"
        End If
    End With
    Debug.Print "---"


    ''''''''''''''''''''''
    I = 2

    'Set an autofilter
    'Filter on cell contents
    WS.UsedRange.AutoFilter Field:=1, Criteria1:="Yellow"

    'Inspect the autofilter properties
    With WS.AutoFilter.Filters(1)
        'in place filter definitions
        If .On Then
            Debug.Print "State       : Filter " & I & " is ON"
            Debug.Print "Criteria1   : " & .Criteria1
            Debug.Print "Criteria2   : " & .Criteria2    'this line will produce a runtime error (1004)
            Debug.Print "Operator    : " & .Operator
        Else
            Debug.Print "State       : Filter " & I & " is OFF"
        End If
    End With
    Debug.Print "---"

    ''''''''''''''''''''''
    I = 3

    'Set an autofilter
    'Filter on cell color
    WS.UsedRange.AutoFilter Field:=1, Criteria1:=RGB(0, 0, 255), Operator:=xlFilterCellColor

    'Inspect the autofilter properties
    With WS.AutoFilter.Filters(1)
        'in place filter definitions
        If .On Then
            Debug.Print "State       : Filter " & I & " is ON"
            Debug.Print "Criteria1   : " & .Criteria1    'this line will produce a runtime error (438)
            Debug.Print "Operator    : " & .Operator
        Else
            Debug.Print "State       : Filter " & I & " is OFF"
        End If
    End With
    Debug.Print "---"

    ''''''''''''''''''''''
    I = 4

    'Set an autofilter
    'Filter on font color
    WS.UsedRange.AutoFilter Field:=1, Criteria1:=RGB(255, 255, 255), Operator:=xlFilterFontColor

    'Inspect the autofilter properties
    With WS.AutoFilter.Filters(1)
        'in place filter definitions
        If .On Then
            Debug.Print "State       : Filter " & I & " is ON"
            Debug.Print "Criteria1   : " & .Criteria1    '<- no error
            Debug.Print "Operator    : " & .Operator
        Else
            Debug.Print "State       : Filter " & I & " is OFF"
        End If
    End With
    Debug.Print "---"

    WS.ShowAllData
End Sub

 

[John_w]

I think you'll need to use On Error Resume Next to handle the Test 2 error:

    With WS.AutoFilter.Filters(1)
        'in place filter definitions
        If .On Then
            Debug.Print "State       : Filter " & I & " is ON"
            Debug.Print "Criteria1   : " & .Criteria1
            On Error Resume Next
            Debug.Print "Criteria2   : " & .Criteria2    'this line will produce a runtime error (1004)
            If Err.Number <> 0 Then
                Debug.Print "Criteria2   : UNDEFINED"
            End If
            On Error GoTo 0
            Debug.Print "Operator    : " & .Operator
        Else
            Debug.Print "State       : Filter " & I & " is OFF"
        End If
    End With

For Test 3, if you look at Criteria1 in the Locals window you'll see that it is an Interior type and the Criteria1:=RGB() is the Interior.Color property. Therefore we can use the TypeName function to test the data type and read the Criteria1 as Interior.Color:

    With WS.AutoFilter.Filters(1)
        'in place filter definitions
        If .On Then
            Debug.Print "State       : Filter " & I & " is ON"
            'Debug.Print "Criteria1   : " & .Criteria1    'this line will produce a runtime error (438)
            If TypeName(.Criteria1) = "Interior" Then
                Debug.Print "Criteria1   : " & .Criteria1.Color
            Else
                Debug.Print "Criteria1   : " & .Criteria1
            End If
            Debug.Print "Operator    : " & .Operator
        Else
            Debug.Print "State       : Filter " & I & " is OFF"
        End If
    End With

I had a go at saving and restoring AutoFilter settings in this thread:

Check status of current filter mode

I need to understand the syntax and code to determine what filters are currently applied to a spreadsheet so that I can return the filters to the sheet at the end of my macro. I currently have a macro I created to copy and insert a row of data identical to the row your cursor is on. The first...

www.mrexcel.com

However, I think the code will need updating to handle cell and font colour filters (xlFilterCellColor and xlFilterFontColor).

 

[rlv01]

Wow. A thread from 2010. I'll take some time to digest it. I always feel like I'm using a brick to crack walnuts when I have to use 'On Error Resume Next', but I think you are right that there is no way around that here. Thanks for the suggestion to use TypeName. I think that will help me to sort out the different data types used by the different filter operators .