I have a custom function that is used to count or sum how many colored cells I have. This is the function:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Application.Volatile (True)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function
It is invoked by inputting the following function:
=colorfunction($A$1,A2:C2,FALSE)
or
=colorfunction($A$1,A2:C2,TRUE)
Where:
- A1 = Cell with background color to be checked
- A2:C2 = Range to be counted
- FALSE/TRUE = Count or Sum
My question is: Is there anything I can do to the VBA to speed up this function?
I have tried to experiment with the WorksheetFunction
part but was not able to find a syntax that worked. Any other ideas?
Application.Volatile
because your function is not volatile. Make all argumentsByVal
. ReplaceWorksheetFunction.SUM(rCell, vResult)
withvResult + rCell.Value
, unless you are usingSUM
to silently ignore non-numeric values. – GSergApplication.Volatile
the function does not calculate unless I force it. YourWorksheetFunction...
appears to have made some impact. Can you explain yourByVal
comment? Thank you – redditorApplication.Volatile (True)
is included except when last parameter isTRUE
. Then, either way recalculates. – BrianVolatile
the function recalculates on each sheet change anywhere in Excel, as opposed to only when there is a change in its arguments. However a color change is not a part of this, your function will not reevaluate when you change a cell's color, even withVolatile
.ByVal
should be used whenever you don't needByRef
, and you don't, so useByVal
. – GSerg