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.

## 4 thoughts on “Beginning VBA: Advanced Functions”

1. Paul Mayfield says:

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?

2. Dick says:

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.

3. Swami Rajendra says:

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

4. Nice information,Thank you very much.

Posting code? Use <pre> tags for VBA and <code> tags for inline.