UDF as criteria for advanced filter within macro error

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

---

Answer 1

This may not serve as an answer but since I can't comment here goes:

When calculating or running the filter from Excel and looking at the locals window, with a breakpoint on the 'IsRed =' line, most of the Expressions are defined, including all from CurrentArray to FormatConditions.

When running the macro, the breakpoint is reached three times as the functions are set, assuming automatic calculation. First time through, CurrentArray, DirectPrecedents, Precedents are not defined, when they are there in a regular function call. Second time, DisplayFormat is not defined. Third, DisplayFormat. Edit: these return FALSE as expected but the Range reference is different.

Next time as the advanced filter is applied 'Unable to get the xxxx ' seems to be the main Value, but a4 does come through as Formula and FormulaLocal. 1004 Error.

My point: the IsRed function and Range reference seems to behave differently when calculation is happening as part of the macro running. Hopefully this provides some impetus for further investigation.