none
UDF as criteria for advanced filter within macro error RRS feed

  • Question

  • If I use a UDF to generate criteria for Advanced Filter, and run the advanced filter using VBA, a 1004 error is generated within the UDF.

    If the Advanced Filter is called from Excel, the Filter functions as expected.

    Why the difference?

    (And yes, I know there are other methods that can be used. I am trying to understand the difference between calling the Advanced Filter from Excel vs VBA when using this UDF as a criteria).

    I am filtering to return the entire row, if any item in the row has a red font (RGB 255,0,0). The UDF is within the code below.

    In the screenshot below, the criteria formula are:

    A2: =IsRed(A8) B3: =IsRed(B8) C4: =IsRed(C8)

    The screenshot shows the Advanced Filter functioning as designed when called from Excel

    But when the code below is run, after the column headers are copied to E1:G1, the code stops within the UDF with the above error message. At the time R.Address = A8

    I also tried recording code when I did the Advanced Filter from Excel, and then executing that recorded code instead of that below. This resulted in the same error.

    Option Explicit
    Sub marine()
        Dim rTable As Range
        Dim rCriteria As Range
        Dim rDestination As Range
    
    Set rTable = Range("a7").CurrentRegion
    Set rCriteria = Range("a1:c4")
    Set rDestination = Range("E1")
    
    rDestination.Resize(columnsize:=3).EntireColumn.Clear
    
    With rCriteria
        .Rows(1).ClearContents
        .Cells(2, 1).Formula = "=IsRed(A8)"
        .Cells(3, 2).Formula = "=IsRed(B8)"
        .Cells(4, 3).Formula = "=IsRed(C8)"
    End With
    
        rTable.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
            rCriteria, CopyToRange:=rDestination, Unique:=False
    End Sub
    '-------------------------------------
    Function IsRed(R As Range) As Boolean
    'Runtime error '1004' on next line when above macro is run
        IsRed = (R.Font.Color = RGB(255, 0, 0))
    End Function


    Ron

    Tuesday, September 12, 2017 10:30 AM

All replies

  • UDF's do not work in conjunction with all worksheet functionality. Some examples of incompatibility are Find, Autofilter,  DisplayFormat (as used with Conditional Formatting) and I believe this is also the case with Advanced Filter.

    I suggest an additional column D and in cell D8 enter the formula =OR(IsRed(A8),IsRed(B8),IsRed(C8))and copy down. Now for the Criteria use a criteria column header and in the cell below enter True.

    If you set up your table of data as follows then the VBA code below will filter it. Note that it is necessary to copy the results returned by the UDF and PastSpecial -> Values so that the UDF does not get called during the Advanced Filter operation. Also Application Volatile is required so that the UDF will respond to calculate to update the formulas.

    Note that no criteria is entered. All of that is done with the VBA code.

    The following VBA code to set up the filters and run Advanced Filter.

    Sub marine()
        Dim rTable As Range
        Dim rCriteria As Range
        Dim rDestination As Range
        Dim strFormula As String
        Dim c As Long
       
        With ActiveSheet
            .Columns("D:I").ClearContents   'Clear the Criteria column and filtered output
            .Cells(1, "D") = "ColD"         'Add a column header for criteria
            .Cells(2, "D") = True           'Enter a value for the criteria
        End With
       
        Set rTable = Range("a10").CurrentRegion
       
        With rTable
            'Add a column to the table range for the formulas
            Set rTable = .Resize(.Rows.Count, .Columns.Count + 1)
        End With
       
        Set rCriteria = Range("D1:D2")
        Set rDestination = Range("F1")
       
        With rTable
            .Cells(1, .Columns.Count) = Range("D1").Value   'Must be same column header as Criteria Column header
           
            'Build the string for the Formula
            strFormula = "=OR("
            For c = 1 To .Columns.Count - 1
                strFormula = strFormula & "IsRed(" & .Cells(2, c).Address(0, 0) & "),"
            Next c
            strFormula = Left(strFormula, Len(strFormula) - 1) & ")"    'Remove last comma and add last bracket
           
            .Cells(2, .Columns.Count).Formula = strFormula  'Insert the formula
           
            'Copy the formula down
            .Cells(2, .Columns.Count).AutoFill _
                Destination:=Range(.Cells(2, .Columns.Count), _
                .Cells(.Rows.Count, .Columns.Count)), _
                Type:=xlFillDefault
           
            Calculate   'Force update of UDF data
           
            'Copy the formulas and PasteSpecial Values to remove the UDF formulas.
            Range(.Cells(2, .Columns.Count), .Cells(.Rows.Count, .Columns.Count)).Copy
            Range(.Cells(2, .Columns.Count), .Cells(.Rows.Count, .Columns.Count)).PasteSpecial Paste:=xlPasteValues
        End With
       
        rTable.AdvancedFilter Action:=xlFilterCopy, _
                                CriteriaRange:=rCriteria, _
                                CopyToRange:=rDestination, _
                                Unique:=False
       
    End Sub


    Function IsRed(R As Range) As Boolean
        Application.Volatile    'Formulas do not respond to Calculate command without this line
        IsRed = (R.Font.Color = RGB(255, 0, 0))
    End Function


    Regards, OssieMac

    Thursday, September 14, 2017 4:44 AM
  • UDF's do not work in conjunction with all worksheet functionality. Some examples of incompatibility are Find, Autofilter,  DisplayFormat (as used with Conditional Formatting) and I believe this is also the case with Advanced Filter.

    Thank you.  But as I demonstrated, my UDF worked perfectly OK with the AutoFilter when called from the worksheet.

    I am trying to understand why the same UDF does not work when the same AutoFilter is called from VBA.  Are you aware of any MS documentation explaining this discrepancy?  I have searched and not found any.


    Ron

    Thursday, September 14, 2017 11:13 AM
  • But as I demonstrated, my UDF worked perfectly OK with the AutoFilter when called from the worksheet.

    I am trying to understand why the same UDF does not work when the same AutoFilter is called from VBA.  Are you aware of any MS documentation explaining this discrepancy?  I have searched and not found any.

    Hi again Ron,

    Firstly the example is Advanced Filter; not AutoFilter. Two different functionalities.

    I tested the example and accept that it works from the interactive mode but will not run from the code. I was somewhat surprised that it worked at all because with Advanced Filter because the Criteria is supposed to have Column headers on the Criteria that match the column headers on the data to be filtered. However, if the column headers are included on the Criteria then it does not work even in the interactive mode.

    I provided some examples where I know there are incompatibilities with UDF's and worksheet functionality and that I believe it is probably the case with Advanced Filter but I don't have any documentary evidence that it applies to Advanced Filter. (Purely speculation).

    Have you found any documentation on the method you are using to filter by Font Color using Advanced Filter? I have not been able to find anything.

    test


    Regards, OssieMac

    Friday, September 15, 2017 6:20 AM
  • Firstly the example is Advanced Filter; not AutoFilter.

    My mistyping. Sorry about any confusion. 

    I tested the example and accept that it works from the interactive mode but will not run from the code. I was somewhat surprised that it worked at all because with Advanced Filter because the Criteria is supposed to have Column headers on the Criteria that match the column headers on the data to be filtered. 

    Have you found any documentation on the method you are using to filter by Font Color using Advanced Filter? I have not been able to find anything.

    Not sure what you mean by my method.  If you mean using criteria without column headers, that is explained in HELP for the Advanced Filter in the section titled "Creating criteria by using a formula"

    That documentation for Advanced Filter states that, when using formulas for the criteria, one should either OMIT the column header, or use a column header that does NOT match any of the existing column headers. I know of no documentation that either includes or excludes using a UDF as the formula.


    Ron

    Friday, September 15, 2017 10:37 AM