Formulas as comments

[Guest]

I had a great add in for Excel 2000 & 2003 that I could click on a cell and
press CTRL+SHIFT+A and the formula from the cell would get put into the
comment for the cell, and the comment box would remain visible even when
another cell was highlighted.

Unfortunately the same add in does not work in 2007. As I have worksheets
with dozens of formulas it is not convenient to manually cut & paste the
formulas in.

I am looking for a macro, add in, etc that would allow me to do the
equivalent.

Thank you in advance!

 

[Guest]

If your goal is visible formulas, don't bother
Press CRTL + ~ (tilde) to display formulas

 

[Guest]

Tevuna:
CTRL+~ does not help. I need to take excerpt from my workbook and print them
out (including the comments) so that people can understand both the numbers
and the formulas that back them up. What I really need the formulas in the
comments fields.

I make extensive use of named fields so that the formulas make sense visually.

 

[Dave Peterson]

I'm surprised that your existing addin didn't work with xl2007. Are you sure
you enabled macros when you opened that addin?

But this may work ok for you:

Option Explicit
Sub PutFormulasInComments()
Dim myCell As Range
Dim myStartingRng As Range
Dim myRngToFix As Range
Dim lArea As Double

Set myStartingRng = ActiveSheet.UsedRange
'or
'Set myStartingRng = Selection

Set myRngToFix = Nothing
On Error Resume Next
Set myRngToFix = Intersect(myStartingRng, _
myStartingRng.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRngToFix Is Nothing Then
MsgBox "No formulas in Selection"
Exit Sub
End If

myRngToFix.ClearComments

For Each myCell In myRngToFix.Cells
myCell.AddComment Text:=GetFormula(myCell)
With myCell.Comment
.Visible = True 'I'd use False
.Shape.TextFrame.AutoSize = True
If .Shape.Width > 300 Then
lArea = .Shape.Width * .Shape.Height
.Shape.Width = 200
.Shape.Height = (lArea / 200) * 1.2
End If
End With
Next myCell

End Sub
Function GetFormula(Rng As Range)
Dim myFormula As String
GetFormula = ""
With Rng.Cells(1)
If .HasFormula Then
If Application.ReferenceStyle = xlA1 Then
myFormula = .Formula
Else
myFormula = .FormulaR1C1
End If
If .HasArray Then
GetFormula = "{=" & Mid(myFormula, 2, Len(myFormula)) & "}"
Else
GetFormula = myFormula
End If
End If
End With
End Function

There are two lines that you may want to review:

Set myStartingRng = ActiveSheet.UsedRange
'or
'Set myStartingRng = Selection

If you really want all the cells on the worksheet processed, then use the top
"Set" line. On the other hand, if you find that you like to select a particular
cell, then just process those cells, you could use the second formula.

In fact, if you select all the cells first, you could always use the second
line.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm