Excel VBA Macro Programming

Overview

When you have a complex spreadsheet, you often need to check formulas to make sure that the results are correct, and this may involve looking through a number of cells where they are chained together. The only way in Excel to view a formula is to click the cell that contains it. This can be time consuming and frustrating.

An easier way is to copy the formula into the comment for that cell. That way, you only need to sweep the cursor across that cell, and the formula will pop up in a comments box. You can see it instantly and without having to transfer your cell cursor position to that cell.

The only problem is that there could be many formulas in the spreadsheet ‚ what happens if any are changed? Trying to check them manually would be a nightmare.

The following code adds any formulas into the comments box for a user selection. If there is a comment already there, it preserves it, and if there is already a formula there, it updates it. The pipe () symbol ( Shift-\ ) is used to separate existing comments from the formula.

Sub note() For Each window In Windows For Each Worksheet In window.SelectedSheets For Each cell In Application.Selection addr = Worksheet.Name & "!" & cell.Address temp = "" On Error Resume Next temp = Range(addr).Comment.Text If InStr(temp, "") Then temp = Mid(temp, InStr(temp, "") + 1) End If Range(addr).ClearComments If Range(addr).HasFormula = True Then Range(addr).AddComment (cell.Formula & "" & temp) Else If temp <> "" Then Range(addr).AddComment temp End If Next cell Next worksheet Next window End Sub

The code cycles through each window in the Windows collection and through each worksheet in the selected sheets to find out which worksheets have been selected. Each cell in the user selection on that sheet is then worked through. The variable addr holds the worksheet name and the cell address, concatenated with the ! character.

A variable called temp is used to build the note string, which is set to Null initially. It is then loaded with the text of any existing comment using the comment.text property. If there is no comment, then an error can occur, which is why this is preceded by On Error Resume Next .

A search is then made within the temp string using the Instr function to see if there is a symbol. If found, it means that the routine has already been run for that cell and that it already contains a formula. If you already use the symbol for something else in comments, you may wish to alter the preceding code to use a different symbol, such as a backslash (\).

If there is already a formula in the comment, you need to remove it. Do this by setting the temp string to all characters after the symbol so that the current formula for that cell can be added in.

The next step is to clear the comments from that cell since you have the comments in the temp string.

The code then checks whether the cell has a formula by checking the HasFormula property. If it does, it adds in a new comment by concatenating the formula, then the symbol to denote the end of the formula, and then the original comment if it was there. Bear in mind that if there was no comment previously, temp was initially set to Null for each loop so no comment would show. If there was no formula, then the old comment is added back using

If temp <> "" Then Range(addr).AddComment temp

If it is a null string (because there was no previous comment), then no comment is actually set because of the If statement at the start of this line.

Make a selection on a spreadsheet by dragging the cursor over a range of cells and then run the code. The result should look like Figure 22-1.

Figure 22-1: Example of a formula shown in a comments box

Try changing the formula and rerunning the code. The formula part of the comment will change, but the original comment will stay the same.