Possible to use user defined functions within array formula?

[fender242]

I created the following user defined function:

Function FontColor(r As Range) As Integer
FontColor = r.Font.ColorIndex
End Function

Now I need to count only the rows that have "cat" in column A as well as red font in column B:

A	B
dog	8
cat	9
shoe	6
cat	9
shoe	42
dog	8
cat	7

<tbody>
</tbody>

The formula I'm using is this:

{=COUNT(IF(A:A="cat",IF(fontcolor(B:B)=3,B:B)))}

This tells excel to count the rows in column B, only when the left column is "cat" and the right column has a red font. But it's not working. I get the answer "0". However if I replace the user defined formula with a "built-in" one like this:

{=COUNT(IF(A:A="cat",IF(SQRT(B:B)=3,B:B)))}

The formula works well, I get the answer "2". This tells me that somehow user-defined functions don't work within array formulas, OR they need to be modified to work within array formulas.

Any suggestions, or comments?

 

[StephenCrump]

Your UDF needs to allow for each cell in the range, e.g.

Function FontColor(rng As Range) As Variant
    
    Dim lColor() As Long
    Dim r As Long, c As Long
    
    ReDim lColor(1 To rng.Rows.Count, 1 To rng.Columns.Count)
    
    For r = 1 To UBound(lColor, 1)
        For c = 1 To UBound(lColor, 2)
            lColor(r, c) = rng(r, c).Font.ColorIndex
        Next c
    Next r
    
    FontColor = lColor
    
End Function

Then, for example:

{=SUM((A1:A10="cat")*(FontColor(B1:B10)=3))} will return 2 as required.

 

[StephenCrump]

You may have noticed that I dodged using whole column references in the previous example:

1. Because generally they are not good practice, and also

2. Because I couldn't get them to work (using Excel 2013).

I've since fiddled around and discovered that there's a limit of 65,536 on the number of rows the UDF can return, i.e. equal to the maximum number of rows in Excel 2003.

I've encountered a similar issue before, e.g. try the following code in Excel 2007 or later:

Dim lWorksOK As Long, lTypeMismatchError As Long

lWorksOK = Application.Count([Row(1:65536)])
lTypeMismatchError = Application.Count([Row(1:65537)])

It appears that Excel sometimes still carries forward the old row limit of 65,536 when checking upper bounds. To me, that's a bug, albeit minor.

 

[fender242]

Thanks for that! That was very helpful. I tried to apply your code to conditionally formatted cells using Interior.ColorIndex, but I guess different code is needed for that (available on the CPearson site). I was referencing the whole column because I'm using a very large number of rows, however I took your advice, and limited the # of rows referenced to within a few hundred of what I'm using (to make room for additions later), and it seems to be calculating faster. Cheers!

 

[StephenCrump]

Thanks for the feedback!

... limited the # of rows referenced to within a few hundred of what I'm using (to make room for additions later)

You might want to check out dynamic range referencing. There are plenty of useful posts on the Forum.

The risk otherwise is that months or years down the track, someone discovers that their spreadsheet has been producing incorrect results, because sums are calculated using a hard-coded limit of 10,000 rows (say).

If, for example, you want to pick up column A, you know that the data starts in A10, and that all data fields will be non-blank, you can define your range:

Dim rng As Range

Set rng = Range("A10", Range("A10").End(xlDown))