2
votes

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?

1
Remove Application.Volatile because your function is not volatile. Make all arguments ByVal. Replace WorksheetFunction.SUM(rCell, vResult) with vResult + rCell.Value, unless you are using SUM to silently ignore non-numeric values.GSerg
@GSerg Thanks for the tips. If I remove Application.Volatile the function does not calculate unless I force it. Your WorksheetFunction... appears to have made some impact. Can you explain your ByVal comment? Thank youredditor
I have to force recalculation even when Application.Volatile (True) is included except when last parameter is TRUE. Then, either way recalculates.Brian
@redditor With Volatile 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 with Volatile. ByVal should be used whenever you don't need ByRef, and you don't, so use ByVal.GSerg

1 Answers

0
votes

Try it:

Function ColorFunction2(rColor As Range, rRange As Range, _
                        Optional pSUM As Boolean) As Variant
Dim rCell As Range
Dim lCol As Long
Dim vResult As Variant, vTmp As Variant ' for intermediate calcs, _
                                          for clear vision of logic, _
                                          for not doubling code
    Application.Volatile (True)

    lCol = rColor.Interior.ColorIndex

    For Each rCell In rRange.Cells ' expicit .Cells
        If rCell.Interior.ColorIndex = lCol Then
            Select Case pSUM ' SUM is reserved word
                Case True:  vTmp = rCell.Value ' out of sheet functions calling
                Case False: vTmp = 1
        ' change True-False sequence according to real probability they occures
            End Select
        End If
        vResult = vResult + vTmp
    Next ' rCell ' No need

    ColorFunction2 = vResult
End Function

Added expicit "As Variant"