In Your First Function you wrote your first function. In this post, we modify that function to be more flexible and look some more advanced techniques.
In the first function, we summed all the cells that had a red background. In this function we want the user to determine which color(s) to sum.
Function SumColors(rSumRng As Range, ParamArray aColorIndex() As Variant) As Double
Dim cell As Range
Dim i As Long
Dim dTempSum As Double
For Each cell In rSumRng.Cells
For i = LBound(aColorIndex) To UBound(aColorIndex)
If cell.Interior.ColorIndex = aColorIndex(i) Then
dTempSum = Application.Sum(dTempSum, cell.Value)
End If
Next i
Next cell
SumColors = dTempSum
End Function
This function has two arguments, rSumRange and aColorIndex. rSumRange is the range that contains the cells that you want to sum. aColorIndex is a little different beast. I’m giving you a challenging example because I know you’re ready for it. The ParamArray keyword means that this particular argument can actually be up to 29 arguments. The standard SUM function uses a ParamArray argument. You can enter one cell
=SUM(A1)
a range of cells
=SUM(A1:A10)
or just about any combination of stuff you want
=SUM(A1:A10,B12,14,MyNamedRange,"Harry")
up to 30 arguments. In our example, we want to be able to sum any combination of colors, e.g. just red, red and green, red, green and blue. Using a ParamArray gives us lots of flexibility. ParamArray arguments must be an array and they must be of the data type Variant. The parentheses () after the argument name tell us that it’s an array.
To calculate the sum, it loops through the cells in rSumRng. For every cell, it loops through each element in the array of variants, aColorIndex. If the cell’s interior matches one of those supplied color indeces, the value of the cell is added to the temporary variable (dTempSum) which later is set equal to the name of the function. Remember that setting the function name equal to something is how the function returns a value. Here’s what it looks like in action:
The downside to this function is that the user has to know the colorindex of the colors they want to sum. We can add still more flexibility by allowing the user to specify a colorindex, the name of a color, or a range that has the same color as the color they want to sum.
Function SumColors2(rSumRng As Range, ParamArray aColorIndex() As Variant) As Double
Dim cell As Range
Dim i As Long
Dim dTempSum As Double
Dim lColorString As Long
For Each cell In rSumRng.Cells
For i = LBound(aColorIndex) To UBound(aColorIndex)
Select Case TypeName(aColorIndex(i))
Case "Double"
If cell.Interior.ColorIndex = aColorIndex(i) Then
dTempSum = Application.Sum(dTempSum, cell.Value)
End If
Case "Range"
If cell.Interior.Color = aColorIndex(i).Interior.Color Then
dTempSum = Application.Sum(dTempSum, cell.Value)
End If
Case "String"
Select Case UCase(aColorIndex(i))
Case "BLACK"
lColorString = vbBlack
Case "BLUE"
lColorString = vbBlue
Case "CYAN"
lColorString = vbCyan
Case "GREEN"
lColorString = vbGreen
Case "MAGENTA"
lColorString = vbMagenta
Case "RED"
lColorString = vbRed
Case "WHITE"
lColorString = vbWhite
Case "YELLOW"
lColorString = vbYellow
End Select
If cell.Interior.Color = lColorString Then
dTempSum = Application.Sum(dTempSum, cell.Value)
End If
End Select
Next i
Next cell
SumColors2 = dTempSum
End Function
The structure is the same as the other function. The difference is that inside the interior loop, it uses the TypeName function to determine which kind argument the user has supplied.
If the type of data input is a Double, we assume they are supplying a ColorIndex and compare it to each cells Interior.ColorIndex. If the data type is Range, it assumes that we want to match the color of the supplied range. If the data type is a String, it assumes that the user has supplied the name of a color. In that case another Select Case statement is used to assign a built-in color variable and compare that to the Color property.
I noticed that this function does not update when you change cell colors in the input range. I thought the solution would be to add an Application.Volatile statement, but change a cell’s interior color doesn’t trigger a “change” such that Excel will recalculate. Any ideas?
Paul: Volatile doesn’t work because changing the format doesn’t force a sheet recalc. Volatile only means that function recalcs with the sheet regardless of dependencies. I know of no way around this problem.
Dick,
There are two partial solutions:
1. is very silly: Cut the formulas and Paste them
2. Better one: To recolour use Format Painter. This makes calculation automatic.
Real fully automated solution:
Thinking..
Swami Rajendra
Nice information,Thank you very much.