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)
End If
Next cell
SumReds = dTempSum
End Function
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…)
Application.Volatile True
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.
Cool! However, I noticed that the function doesn’t recalculate when you change the background of an additional cell to red, or change a red background to another color. Is there a way to do this?
I tried copying this formula into my VBA and now I get an error message that says “‘Microsoft Visual Basic’ File Not Found” every time I open up Excel. Anything I can do to fix this?
Raph,
No, that’s the way Excel is designed. Changing the format of a cell doesn’t trigger a recalculation, which seems logical. So you have to either put
Application.Volatile True
at the top of the function and it will recalculate itself each time any cell changes, or force a recalculation using
Control Shift F9
Russ: That function doesn’t use any files, so I doubt that it’s the culprit. Did you make any other changes? Generally, for startup errors I do the following:
1. Run the Workbook_Open of Personal.xls and any other workbooks that open automatically. Do this with the workbooks already open and see which one gives you an error, if any.
2. Write down all the add-ins that are installed. Uncheck them one-by-one and restart excel until you don’t get an error. The last one you unchecked has the error.
3. Reregister excel by going to Start Run and typing “excel.exe /regserver” (no qutoes)
Excellent site!
thank you, but what if i want Excel to look at the contents of one cell, and use another cell’s value to calculate. That is, total values based on their definition?
I created the following Macro that causes the selected and some cases the whole sheet to recalculate upon pressing Ctrl+Shift+C
There must be a control in Excel to accomplish the same.
Sub Macro3()
‘
‘ Macro3 Macro
‘ Macro recorded 7/9/2007 by B2HPOHLO
‘
‘ Keyboard Shortcut: Ctrl+Shift+C
‘
ActiveCell.Select
ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1
End Sub
Harold,
A range object has a Calculate method, so you should be able to just do something like:
If TypeName(Selection) = “Range” Then Selection.Calculate
End Sub
Matt
thank
I am having trouble entering a range arguement for my functions.
The following Function should read the cell designated by the range x hwever it does not work. Any thoughts?
ReadCell(x As Range)
ReadCell = Cells(x).Value
End Function
Jeffrey: Since x is already a range, you’d use
ReadCell = x.Value
“Jeffrey: Since x is already a range, you’d use
ReadCell = x.Value”
Or just declare it as a double (or whatever it is) rather than a range:
If x is a single cell:
ReadCell(x As Double)
ReadCell = x
End Function
Or if x is a multicell range, declare it as a Variant:
ReadCell(x As Variant)
‘ This line converts x from a range object into an array, it speeds things up hugely!
x = x.value
‘ Then read whichever value you want from the array
ReadCell = x(1,1)
End Function
“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.”
I do that all the time. Like this:
Function RtoP(Coords As Variant, Optional Origin As Variant, Optional Result As Long) As Variant
Dim …
If TypeName(Coords) = “Range” Then
Coords = Coords.Value
End If
….
So if the function is called from a worksheet, and Coords is a range, it gets converted into an array. If it is called from a sub it will usually be an array already, but if it isn’t it gets converted.