Yesterday you wrote your first macro. I’m so proud. The next step is to write a function. Functions (also called User Defined Functions of UDFs) are procedures just like Sub procedures. There is a key differece, however. Functions are designed to return a value. That’s it, just return a value. That means that they can’t modify the user interface like a Sub can.
They can’t, for instance, write a value to a cell EXCEPT for the cell in which they’re entered.
Functions are handy for a number of reasons. You can use them in your Sub procedures to separate out a tricky piece of code. You can also use them in worksheet cells just like our old friends SUM and VLOOKUP. That makes them pretty powerful. As far as I know, I’ve never written a function that I’ve used in a cell AND called from a Sub procedure. It’s usually one or the other.
Let’s keep it relatively simple to start. Here’s a function that will sum all the numbers in a range whose background color is red.
Function SumReds(sSumRng As Range) As Double
Dim cell As Range
Dim dTempSum As Double
For Each cell In sSumRng.Cells
If cell.Interior.Color = vbRed Then
dTempSum = Application.Sum(dTempSum, cell.Value)
SumReds = dTempSum
The first line of the function starts with Function and the last line is always End Function. The function name follows the Function keyword and in this example it’s SumReds. After that, the argument list – stuff we want to tell the function. Here, we’re passing in a range – presumably the range that contains cells that we want to sum. Every argument has a data type (this one is Range) and the function itself has a data type. The function’s data type says what kind of value it will return.
Next we declare some variables to use in our function. The variable cell will be used to loop through all the cells in our inputted range. dTempSum will temporarily hold our work until we’re ready to return a value. I like to use a temporary variable in this manner. Some people use the function name as their working variable. We’ll see why they can do that in a minute.
The meat of the function loops through all the cells and compares the Interior.Color to vbRed (that’s a built-in constant so you don’t have to remember the Hex or RGB for red). If the interior color is red, it adds the cell’s value to the temporary variable.
I use the worksheet function SUM (via Application.Sum()) because I want my function to have the same functionality as the normal SUM function. I want it to ignore text, for instance. If I just use
dTempSum = dTempSum + cell.Value
I’d have to include all kinds of tests to make sure the cell’s value was summable.
Now the important part. To have your function return a value, you MUST set the function’s name equal to something. As I said before, I use dTempSum to do the work and when the work is done, I set the function’s name equal to dTempSum.
This is what SumReds looks like in the wild
You just wrote your first function. Have a drink.
Tomorrow: Objects, Properties, and Methods
Update: Raph commented that the function doesn’t recalc when you change cell colors. He’s right, it doesn’t. You can add this as the first line of the function (after Function SumReds…)
but that still isn’t perfect. Using Volatile causes the function to recalc whenever the sheet is recalculated – even if none of the arguments have changed. That’s fine, but changing the background color doesn’t cause the sheet to recalculate, so it won’t cause the function to recalculate either. In short, I don’t know how to easily make this function recalc when colors change. Maybe someone who does know can post a comment. Thanks for the comment Raph, it’s an important point.