Selected Answer
Hi John,
I use Excel 365 and what you are pointing at looks like a bug to me. I was able to set or change the description only before the first use of a UDF. Therefore I investigated what constitutes a "first use".
A UDF's status doesn't seem to change with saving or on recalculating the sheet. But when its name is changed in the code things start to happen.
- I created a UDF and set the description.
- I tried to change the description but it didn't change.
- I renamed the UDF. The new name was never used before.
- The renamed UDF inherited the changed description that was previously apparently rejected (not the previous description that didn't want to change).
- After renaming I couldn't change the description although the UDF had never been used under its new name.
This kind of "ghosting" appears to be a feature of this property. While I was playing with my new trial UDF your CalcByColor quietly accepted the new description I had given it unsuccessfully a while ago. Of course, I had clicked and calculated and saved many times in the interim. I can't tell which of these actions or which combination of them triggered the change of mind. My guess is that Excel has a routine of updating its memory and the update occurred because of nothing I did. That doesn't mean that there couldn't be an easy way to prompt the update but I only found a complicated one.
In conclusion,
- Set the description before you first use your UDF.
- To set or change a description after first use, modify the description and ignore that it doesn't change. Rename the UDF and the modified description will appear for the new name. Change back to the original name which will now have the new description. (You may have to save in between.)
- Or ignore that the modification isn't shown immediately, don't use the UDF for half an hour, and hope that the update will be the reward for your patience.
Here is a formatted copy of your UDF, renamed SumByColor. Besides featuring the better logic it has the added advantage in that it actually works :-)
Function SumByColor(NumRange As Range, _
SampleCell As Range, _
Optional ByVal FillOrFont As Variant, _
Optional ByVal SumOrCount As Variant) As Double
' 135
' return the Sum (or Count) of cells in NumRange where
' the Fill (or Font) colour equals that in SampleCell
' ==========================================================
' FillOrFont can be 1, "1" or "Font"
' if it omitted or has any other value "Fill" is presumed
' SumOrCount can be 1, "1" or "Count"
' if it omitted or has any other value "Sum" is presumed
' ==========================================================
Dim Fun As Double ' function return value
Dim Font As Boolean ' True if "Font" is specified
Dim Count As Boolean ' True if "Count" is specified
Dim Col As Long ' Fill or Font color
Dim Cell As Range ' loop object
If Not IsMissing(SumOrCount) Then _
Count = (Val(SumOrCount) = 1) Or _
(StrComp(SumOrCount, "count", vbTextCompare) = 0)
If Not IsMissing(FillOrFont) Then _
Font = (Val(FillOrFont) = 1) Or _
(StrComp(FillOrFont, "font", vbTextCompare) = 0)
With SampleCell
Col = IIf(Font, .Font.Color, .Interior.Color)
End With
For Each Cell In NumRange
With Cell
If Font Then
If .Font.Color = Col Then
Fun = Fun + IIf(Count, 1, Val(.Value))
End If
Else
If .Interior.Color = Col Then
Fun = Fun + IIf(Count, 1, Val(.Value))
End If
End If
End With
Next Cell
SumByColor = Fun
End Function
While on the subject of Excel's internal memory, Excel will remember the capitalisation of SumByColor. It will show you "SumByColor" (capitalisation taken from the code), which you can select, or you can even type "SumByColor", being very careful to capitalise 3 leading characters, but Excel will stubbornly return "sumbycolor". It works. So, why bother? If you do want to bother, declare a range by the name of "SumByColor". All existging UDF calls will change to assume this capitalisation and Excel will remember your preference for all future uses even after you delete the named range.