Failing to use an Excel array formula with a custom VB function

I'm trying to use Excel array formulae for summing up the number of cells in a columnar range (an array if you will), which have a background color.

For checking whether a cell has a background color, I use this function:

Function FillIndicator(color As Range) As Integer
If color.Interior.ColorIndex = -4142 Then
    FillIndicator = 0
Else
    FillIndicator = 1
End If
End Function

which works.

If I were to eschew array formulae, and my range were, say, C5:C7, I would need to write something like:

=FillIndicator(C5) + FillIndicator(C6) + FillIndicator(C7)

Now, my naive attempt at using an array formula here was writing:

=SUM(FillIndicator(C5:C7))

and pressing <kbd>Ctrl</kbd>+<kbd>Shift</kbd>+<kbd>Enter</kbd>. But that just gets me a #VALUE error.

What am I doing wrong? And - can I make this work?

Note: I'm using Excel 365 version 2208. I think.

I think this is what you need to count cells or ranges of specific columns.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

Function SumCellsByColor(colorRange As Range, targetColor As Long) As Long
    Dim cell As Range
    Dim count As Long
    
    count = 0
    
    For Each cell In colorRange
        If cell.Interior.color = targetColor Then
            count = count + 1
        End If
    Next cell
    
    SumCellsByColor = count
End Function


Sub ExampleUsage()
    Dim rng As Range
    Dim count As Long
    
    ' Define the range of cells in which you want to count
    Set rng = Range("A18:C22")
    
    ' Call the SumCellsByColor function and pass the range and target color.
    count = SumCellsByColor(rng, RGB(255, 0, 0)) ' For example, the color red
    
    ' Print the result
    MsgBox "Number of cells with red color: " & count
End Sub

<!-- end snippet -->

This answer is the same as @RicardinhoL answer with three small differences: 1) use an Optional color parameter with default value that you use in question 2) check if range is nothing else an error occurs 3) use as counter the name of the function, since it works like a variable itself.

Must to return Long because there are many-many cells in excel!

Public Function CountIfColor(rngClr As Range, Optional thisColor As Long = -4142) As Long
   Dim r As Range
   If Not rngClr Is Nothing Then
      For Each r In rngClr
         If r.Interior.ColorIndex = thisColor Then CountIfColor = CountIfColor + 1
      Next
   End If
End Function

To have the UDF return an array, you merely need to set the array equal to the function in the return line.

For example, given your apparent desire to return a boolean array corresponding to whether or not the cells in a range are colored:

Option Explicit

Function FillIndicator(color As Range)
    Dim v  'variable to loop through array
    Dim al As Object 'collect the test results
    
Set al = CreateObject("System.Collections.ArrayList")

'run the test on each cell
For Each v In color
    al.Add v.Interior.ColorIndex <> -4142
Next v

'Set the Function equal to an array
FillIndicator = al.toarray

End Function

Note the penultimate line where FillIndicator is set equal to the array.

The formula will return an array of TRUE/FALSE depending on the Interior.ColorIndex property.

You can then treat this the same as any other array function.