VBA Function gives #VALUE! error when used as Formula in Excel, whereas works fine in debugging mode

Question

I have written following Excel VBA code for Function to get the count of cells with Green fill in a Range.

It gives #VALUE! error when I use this Function as Formula, e.g., =GreenCount(E3:E48), in Excel.

Function GreenCount(rcell As Range) As Long

    Dim myCell As Range
    GreenCount = 0
   
    For Each myCell In rcell

       If myCell.DisplayFormat.Interior.Color = RGB(0, 255, 0) Then
           GreenCount = GreenCount + 1
       End If
        
    Next myCell

End Function

Surprisingly, desired result is seen in debugging mode when the Function is called through below Sub.

Sub Find_Count()

Dim rcell As Range
    
Set rcell = Sheet2.Range("E3:E48")
Sheet2.Range("E49").Value = GreenCount(rcell)

End Sub

Any help to sort out the error would be highly appreciated.

Edit: Cells in the Range are conditionally formatted.

Answer 1

You can apply a workaround by calling your function indirectly, using the Evaluate method.

Function EGreenCount(rcell As Range) As Long
    EGreenCount = rcell.Parent.Evaluate("GreenCount(" & rcell.Address & ")")
End Function