I followed this guide to create the following custom function which counts the number of cells of a certain colour in my spreadsheet:
Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
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 works fine and gives me the expected result if I click the green tick in the formula bar but won't update automatically after I colour another cell. How do I set it to automatically update the count every time I manually colour a cell elsewhere on the spreadsheet?
Extra info: I'm using Excel on the Mac and I've checked in settings and automatically calculate is turned on.
Best Answer
Excel Recalculates UDFs Only When Their Input Changes
You've created a User-Defined Function (UDF). Excel only runs the code of a UDF when the cell(s) that serve as input to the function change.
For example, suppose I have this UDF:
And in cell A1 on my worksheet I reference the cell with the formula:
During normal use of my workbook, the code of my UDF will only be called if there's a change to the contents of cell A2 or another cell referenced by a formula in A2.
Solutions
You can work around this behavior in several ways:
Include a volatile function in one of the cells referenced by your UDF. Do this by modifying the definition of your VBA function to accept an additional parameter:
Then edit the cell referencing your UDF to pass the result of a volatile function such as
Now()
to the UDF:The net result will be that Excel will deem the cell containing the reference to your UDF as needing to be recaculated every time the worksheet is changed because it references a volatile function.
Edit the cell containing the UDF. Simply entering the cell then pressing Enter without making changes should be sufficient to trigger an update.
Create macro button that runs the following line of code:
Place the following code in your Workbook's Open event:
This causes Excel to always recalculate all formulas, regardless of whether it believes recalculation is required. This setting remains in effect until Excel is restarted.
Why Doesn't Excel Always Recalculate My UDF?
When Excel performs an automatic recalculation, it does not recalculate every single formula in the workbook. Instead it only updates those cells which contain formulas that refer to the most recently modified cell. This method avoids the much lengthier process of needlessly performing many calculations across the entire workbook only to come up with the same result for the vast majority of them.
With a UDF, the only references in the workbook of which Excel's calculation engine is aware are those identified in the function's inputs. Excel cannot examine the VBA code within the UDF and identify other cells that may influence the function's output. So while the function's author may construct the function to return different results based on any number of changes made in the workbook, the only cells Excel knows will change the function's result are the declared inputs. Therefore, changes to these cells are the only ones Excel pays attention to when deciding whether the UDF needs to be recalculated.