How do I add a description to a User-Defined Function in Excel 2016?

0

I've written a trial UDF to which I'd like to add a description to aid the user.

I've followed the tutorial Add Description Text to a UDF in Excel several times without success...

I can seemingly add a brief desciption for the UDF under the Module via its Properties and close the Object Browser/ save the file and close Excel as instructed in the tutorial. Trouble is that when I re-open the file, no description appears via Fx when the function is part-typed in the formula box. The tutorial's sample file however works, it does display a "Fx" description for its UDF.

Here's my file (it has two sample calculations in green cells and a verbose desciption).

What am I doing wrong please?

Answer
Discuss

Discussion

In the Project Browser the description is displayed at the bottom of the screen when you select the UDF. After updating it you can see that the change wasn't accepted immediately. No need to close and save, and closing & saving will not alter the outcome.
Variatus (rep: 4864) Dec 7, '20 at 10:10 pm
Thanks Variatus, I see that now (and my close and save operations were actions suggested by the tutorial I mentioned).
John_Ru (rep: 1917) Dec 8, '20 at 4:24 am
Add to Discussion

Answers

0
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,

  1. Set the description before you first use your UDF.
  2. 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.)
  3. 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.

Discuss

Discussion

Thanks for a very thorough investigation Variatus!

It's an odd behaviour by Excel, as you say. When I renamed my original macro, it inhereitted the description it has previously failed to take. Initially the workbook "fx" description didn't appeared (s you said, but see below).

As an aside to that main issue and on the modification to the logic of my UDF, your code is neater but your logic doesn't match my thinking, sorry. I called the UDF CalcbyColor (since the count option isn't an arithmetic sum but still a calculation) and I ordered my arguments so a formula made some sense (when read in the absence of any fx description) e.g. =CalcByColour(A1:A6,"SumLike",A1,"Font") was meant to imply the range is summed for cells like A1's font (colour) or fill (but not both). In my example, A1 (=1) has black text and so do cells containing 3,5 and 6. It returns the sum of 15 but if I use yours,  =SumByColor(A1:A6,A1,,), it gives 6 (i.e. 1 and 5 from A5 which is also both black text and yellow fill).

Not to worry (or correct) but that's why I wanted a UDF descrition, to add that clarity.

Thanks again, I just reopened both my revised workbook (and one based on your UDF) after a while and the fx descriptions appear when fx is opened. Cheers!
John_Ru (rep: 1917) Dec 8, '20 at 4:40 am
The argument SumOrCount takes the arguments of "Sum" or "Count" in addition to 0 or 1 and can be omitted with no need for a comma. FillOrFont works the same way. Your function doesn't look at the font colour at all, unless a query is "hidden" in the IFs and I overlooked it, and it examines the ColorIndex where mine looks at the Interior.Color. These are minor points, easy to modify in the structure I provided. I felt that the nested IFs in your function design makes it hard to maintain or modify.
I failed to understand the second calculation by looking at your code and still don't understand it now. What else can there be but to sum up or count occurrences? However, I didn't take issue with your choice of "Calc". I just wanted another name so that both UDFs could survive in the same workbook.
Variatus (rep: 4864) Dec 8, '20 at 5:35 am
Thanks Variatus. Fair point about renaming the sub and the nested IFs!

That's were my reference to text colour was hidden (since I'd lazily not added line breaks- now added); see the bold bits in the extract below: 
If SumOrCount = "SumLike" And _
    (TextOrFill = "Fill" And jCell.Interior.ColorIndex = jCol) _
    Or (TextOrFill = "Font" And jCell.Font.Color = jText) Then


Not doing more on this at present, since I haven't heard back on my last discussion points under the recent fourum question Calculating income and expense 
John_Ru (rep: 1917) Dec 8, '20 at 2:57 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login