Adding Comments to a Formula

[Cyberdude]

I just saw an interesting tip for adding comments to the end of a formula. In VBA when I want to add a comment to a statement line, I just preface the comment with an apostrophe ( ‘ ) like:

Range(“A1”).Select ‘This is a comment

To do something similar in a formula, the author says to add a sequence like:

+N(“Comment”)

The comment will be displayed in the formula line at the top of the screen, but will not be seen in the cell where the formula is written. Klutsy, but perhaps useful for documentation sometimes.

Note that the “N” is the “Value” function as used here. It attempts to convert the text comment into a number, but fails without generating an error message. Actually N generates a zero when it fails. It will always fail when trying to convert a text value.

Will this technique cause any kind of problem?

[Oorang]

Where in Outlook are you entering a formula that is not in VBA?

[Cyberdude]

I'm confused. I've never used Outlook. In fact I don't even know what it's used for.

[Oorang]

I'm confused too:) You posted this in "Outlook Help". Perhaps you meant to post it in the Excel section? I moved it over for you.

[p45cal]

No, as long as you bear in mind that it produces a zero value, so just make sure that it's not in a place in the formula where a zero would materially affect the result of the fomula.

[mdmackillop]

Not a bad idea sid. Here's a wee sub to add a comment to an existing formula

Sub AddComment()
Dim txt As String
txt = "+N(" & Chr(34) & InputBox("Enter formula comment") & Chr(34) & ")"
ActiveCell.Formula = ActiveCell.Formula & txt
End Sub

[Bob Phillips]

This technique works fine in a numeric formula such as

=SUM(A1:A7)+N("Sum of amounts")

but fails miserably with text output such as

=A2&" should be paid on: "&TEXT(B2,"mm/dd/yyyy")&N("due date of payment")

but this can also be handled by outputting that as text also

=A2&" should be paid on: "&TEXT(B2,"mm/dd/yyyy")&TEXT(N("due date of payment"),"")

[Oorang]

Hi CD,
My only concern about this approach is that every remark represents a function call. This will add to the calculation time when the workbook calculates, as well as adding to the complexity of the formula itself. If I saw someone doing this at work, I would want to know why they didn't just use the already built in comment feature? (You can also make your formula's easier to understand by using named ranges.) It's a clever trick, but perhaps a little too clever.

[mikerickson]

How about
=CHOOSE(1, desiredFormula, "any comment")