Getting #Value! Error on worksheet, but VBA test sub returns what it should

1
votes

I have tried to use several posts I found in regards to using DisplayFormat within a UDF that loops. I am trying to count conditionally formatted cells. I can return the correct count in a test sub, but get a #VALUE! error in my worksheet.

I have tried many things, but the one I thought was most relevant was from a response by Tim Williams:

Getting cell Interior Color Fails when range passed from Worksheet function

However, the EVALUATE method would cause "Error 2015" values and I was unable to get around that problem, I saw this issue while using the test sub routine.

WORKING ANSWER thanks to @BigBen:

Public Function DFColor(addr)
DFColor = Range(addr).DisplayFormat.Interior.ColorIndex
End Function

Function COUNTBYCOLOUR(CountRange As Range)
Dim countColour As Integer
Dim rCell As Range
countColour = 3
For Each rCell In CountRange
   If countColour = (rCell.Parent.Evaluate("DFColor(""" & rCell.Address & """)"))Then
    COUNTBYCOLOUR = COUNTBYCOLOUR + 1
    End If
Next rCell
End Function

Previous attempt Main Function: 

Function COUNTBYCOLOUR(CountRange As Range)
'make workbook recalculate on cell changes
Application.Volatile
'Declaring variables
Dim countColour As Integer
Dim rCell As Range
'Setting search colour (red)
countColour = 3
'Looping through the defined range
For Each rCell In CountRange
Dim Y As Integer
Y = evalCell(rCell)
'Getting the count of matching colored cells
If Y = countColour Then
        COUNTBYCOLOUR = COUNTBYCOLOUR + 1
End If
Next rCell
End Function

'USE TO TEST UDF, Place cursor before Sub test() and press f8 to step through and debug
'Sub test()
 'MsgBox COUNTBYCOLOUR(Range("C4:C11"))
'End Sub

Previous attempt Secondary function to try and separate DisplayFormat from UDF: 

'get cell colour outside of COUNTBYCOLOUR UDF so it evaluates DisplayFormat on worksheet.
Public Function evalCell(currentCell)
    evalCell = currentCell.DisplayFormat.Interior.ColorIndex
End Function

When I run the sub routine at the bottom of my main function, I get the correct count (only trying to count red cells, no need to have a colour reference cell). When I use COUNTBYCOLOUR in my worksheet, I get the #VALUE! error. Hopefully some more experienced eyes can help me. Thank you.

2
excelvba
Where do you use Evaluate - as that is the key workaround as noted in the linked question? – BigBen
Can you share how you've used Evaluate then? – BigBen
Yes Ben, Thanks for your help. Unfortunately I had overwritten, but it was along the lines I added in post above. – Stephen Dawson
That version works for me. What is your data like and how are you calling the UDF? – BigBen
Small note about "USE TO TEST UDF, Place cursor before Sub test() and press f8 to step through and debug": a better way would be to hit Ctrl+G to bring up the immediate pane, and type test and ENTER. – Mathieu Guindon

2 Answers

0
votes

Working Version:

Public Function DFColor(addr)
DFColor = Range(addr).DisplayFormat.Interior.ColorIndex
End Function

Function COUNTBYCOLOUR(CountRange As Range)
Dim countColour As Integer
Dim rCell As Range
countColour = 3
For Each rCell In CountRange
If countColour = (rCell.Parent.Evaluate("DFColor(""" & rCell.Address & """)"))Then
    COUNTBYCOLOUR = COUNTBYCOLOUR + 1
    End If
Next rCell
End Function
-1
votes

I think your code is an overkill sorry. Following simplified form of your code works well for me:

Function COUNTBYCOLOUR(CountRange As Range)

Application.Volatile
Dim rCell As Range

For Each rCell In CountRange 'Getting the count of matching colored cells
    If rCell.Interior.ColorIndex = 3 Then 'search colour (red) is 3
        COUNTBYCOLOUR = COUNTBYCOLOUR + 1
    End If
Next rCell

End Function