Displaying Excel Formulas Within Worksheet Cell Comments

Aug 31st 2016
Share this content
Spreadsheets and graphs on a desk
xfgiro/istock

Several years ago I developed a proprietary tool that enables me to instantly display Excel formulas in a formatted cell comment. I’ve gotten asked about this concept often enough that in this article I’ll explain how you can create a “lite” version of my tool.

We’ll start the process by way of Excel’s Macro Recorder so that the macro can be placed in your Personal Macro Workbook. This macro requires programming code that cannot be recorded, so we’ll only turn the macro recorder on and off to create an initial shell for the programming code that you’ll copy and paste later on in this article.

  1. Choose Excel’s View menu.
  2. Click the arrow beneath the Macros command.
  3. Choose Record Macro.
  4. Assign a name to the macro you wish to create, such as FormulaComment. Macro names cannot contain spaces or punctuation; although underscores are permitted.
  5. Choose Personal Macro Workbook from the Store In list.
  6. Click the Record button.
  7. Click the Stop Recording button that appears on Excel’s Status bar. The look of this button varies by Excel version, but it’s always a square adjacent to the word Ready.

Formula Macro 1

Figure 1: Macros created within the Personal Macro Workbook become available to all Excel workbooks.

This gives us the beginning of our macro, within which you’ll be able to copy and paste some programming code. We must first unhide the Personal Macro Workbook so that you can access the underlying macro, as shown in Figure 2.

  1. Choose Excel’s View menu.
  2. Click Unhide.
  3. Click OK.

Formula Macro 2

Figure 2: You must unhide the Personal Macro Workbook before you can edit a macro stored within it.

A workbook with the name Personal.xlsb will appear onscreen. This workbook has a couple of special characteristics:

  • Typically worksheets within this workbook remains blank.
  • The workbook launches automatically when you open Excel.
  • Macros that you place within this workbook are available to all open workbooks in Excel. This means you’ll be able to use the FormulaComment macro in any other Excel workbook.
  • Unless you’re familiar with Excel’s Visual Basic Editor, you must first unhide this workbook to make changes to the underlying macro.

The next step is to access the programming code generated by the Macro Recorder, as shown in Figure 3:

  1. Choose Excel’s View menu.
  2. Click the Macros command.
  3. Click once on FormulaComment.
  4. Click Edit.
  5. At this point Excel’s Visual Basic Editor will appear on screen, along with the beginnings of a subroutine.
  6. Activate Microsoft Excel again, and then click the Hide command on Excel’s View menu to hide your PersonalMacro Workbook again.
  7. Activate the Visual Basic Editor again.

Formula Macro 3

Figure 3: Excel’s Visual Basic Editor is a programming interface for creating and editing macros within Excel.

As shown in Figure 4 you’ll add the following lines of code to your macro, between the Sub and End Sub lines. In this case Sub is short for subroutine, which is another name for a macro in Excel. You can copy the text below to the clipboard and then paste it into the subroutine.

    Dim rng As Range

    On Error Resume Next

    For Each rng In Selection

        strFormula = rng.Formula

        If strFormula <> "" Then

            rng.ClearComments

            rng.AddComment

            rng.Comment.Text strFormula

            rng.Comment.Shape.TextFrame.Characters.Font.Size=14

            rng.Comment.Shape.TextFrame.Characters.Bold=True

            rng.Comment.Visible = True

        End If

    Next

    On Error GoTo 0

Formula Macro 4

Figure 4: The completed macro that will store cell formulas within comments.

Now let’s break down the lines of code so that you can understand the concept:

  • Dim rng as Range creates a placeholder called rng that will represent an individual worksheet cell that you’ve selected.
  • On Error Resume Next instructs our macro to skip over any lines of code that trigger an error. The macro can only execute cleanly if you select one or more worksheet cells, but at times you may inadvertently launch the macro while a chart or other object is selected.
  • For each rng in Selection sets up a loop, where the process we establish will be carried out in each cell that is selected onscreen.
  • strFormula = rng.Formula captures the formula from a given cell and stores it in a variable called strFormula. We’re capturing the formula so that we can place it in a cell comment later in the routine.
  • If strFormula <> "" then allows us to have the macro only act on non-blank worksheet cells.
  • rng.ClearComments instructs Excel to remove any existing cell comments.
  • rng.AddComment instructs Excel to add a comment to the current cell.
  • rng.Comment.Text strFormula instructs Excel to make the text of our comment be the formula that we captured earlier in the routine.
  • rng.Comment.Shape.TextFrame.Characters.Font.Size=14 sets the font size of the comment text to 14. This line is optional, so you can delete it entirely, or change 14 to a number of your choice.
  • rng.Comment.Shape.TextFrame.Characters.Font.Bold=True makes the text within the comment be bold. You can remove this line or change True to False if you do not wish to have the formula be bolded.
  • rng.Comment.Visible = True is an optional step that instructs Excel to display the comment onscreen. You can remove this line or change True to False if instead you only want to see the cell comment when you hover over a cell with your mouse.
  • End If closes the decision-making line that we created with If earlier in the routine.
  • Next instructs Excel to go to the next cell in the selection if you chose more than one cell.
  • On Error GoTo 0 turns off our error handling that we set earlier in the routine. That’s a zero at the end. It’s a cryptic line of code, but crucial in macros to ensure that you intentionally skip over errors in specific sections of your programming code.

At this point save the changes you’ve made to the Personal Macro Workbook, and then exit Excel’s Visual Basic Editor as shown in Figure 5.

  1. Choose File.
  2. Choose Save personal.xlsb.
  3. Choose File.
  4. Choose Close and Return to Microsoft Excel.

The final steps assign a keyboard shortcut for your macro:

  1. Choose Excel’s View menu.
  2. Click the Macros command.
  3. Click once on the name of your macro.
  4. Click the Options button.
  5. Assign a shortcut key, such as Ctrl-Shift-C.
  6. Click OK to close the Macro Options dialog box.
  7. Click Cancel to close the Macro dialog box.

Be mindful when assigning shortcut keys to macros. In this case if you assigned Ctrl-C, (thinking C for comment) your macro will then supersede the Ctrl-C keyboard shortcut for copying. Using the Shift key as part of your keyboard shortcut ensures that you don’t inadvertently step on any built-in Windows keyboard shortcuts that use the Ctrl key and a letter.

Formula Macro 5

Figure 5: The shortcut key you assign for your macro will override any built-in Windows keyboard shortcuts.

You can now click on any cell that contains a formula and press Ctrl-Shift-C (or the shortcut that you assigned). As shown in Figure 6 the formula should appear within a cell comment. You may need to manually resize the comment to display the formula on a single line. It’s possible to create a self-resizing macro, but this gets well beyond the scope of introducing the concept of macros in Excel.

Going forward you can toggle cell comments on or off by using the Show All Comments command on Excel’s Review menu, or right-click on an individual cell and choose Show Comment or Hide Comment from the context menu.

Formula Macro 6

Figure 6: Use the assigned shortcut key to display the formula within a cell comment.

Replies (3)

Please login or register to join the discussion.

avatar
By ccurran
Mar 8th 2017 17:32

Thanks for this. The only question is regarding the 5 lines of code displayed after "On Error GoTo 0" in Figure 4. These are not discussed in the article and are not necessary for the macro to work. Can you explain these 5 lines of code? Maybe they are from your "full" version of the macro?

Thanks (0)
avatar
By monica123
Apr 17th 2017 10:31

The accounting advisor David ringstorm tells Computer Science Assignment Help guide in a proper way about excel file through this accounting web site.

Thanks (0)
avatar
By shirley001
Aug 23rd 2017 22:04

Excel sheet is very useful for making any work update sheet because they're user easily use column and raw relate to his/her work even most of the employ any strategy on it so they easily use mention that work with date by which another person understand freely. And those students study in college or university, so they have to use it for managing their time excel and PPT for making presenting and teachers provide final assignment to those students who desire to get a life experience degree accredited by the us department of education in their desire faculty by which to grow in their desire industry without any problem.

Thanks (0)