Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Save A Formula As A Comment

Excel How To

Save A Formula As A Comment

by  LoNeRaVeR  Posted    (Edited  )
Any [color blue]Microsoft Excel[/color] user with large ranges of formulae has experienced the slow response of calculations each time a change is made in a worksheet. Sure you could turn the calculation off, but what if you still want some of the formulae to calculate? One solution is to highlight and copy the range you don't want to calculate [color green]
Code: 
[Ctrl+C]
[/color] and then paste over the same range with the values of the cells in that range[color green]
Code: 
[ALT,E,S,V]
[/color].

What do you do if you want to keep a copy of your formula? Wouldn't it be nice if you could save your formula in a comment so you could replace all your formulae with values, but not lose the formula you spent so much time creating? Wouldn't it be even better if you could then copy the formula back out of the comment into the cell?

Some time ago I wrote some [color blue]Excel VBA[/color] code that not only saves the formula into a comment, but provides the option to copy the formula back to the cell, or clear the comment altogether. For ranges of similar formulae I always create the comment with the formula in the first cell and then replace all the similar formulae in that range with their values. This way I can not only see where the data came from, but I can also easily replace the formula within the range should the need arise.

Feel free to copy my code and use it. I suggest that you place it in your [color blue]PERSONAL.XLS[/color] workbook and assign a shortcut-key to it.


Code: 
Sub CommentFormula()
' v1.0 - 2001.06.12 - LoNeRaVeR
 Dim bEqual    As Boolean
 Dim iEqual    As Integer
 Dim iLen      As Integer
 Dim iLoc      As Integer
 Dim sAuthor   As String
 Dim sComment  As String
 Dim sCR       As String
 Dim sCR2      As String
 Dim sDate     As String
 Dim sFormula  As String
 Dim sResponse As String
 Dim sTime     As String
  
 bEqual = False
 sAuthor = Application.UserName
 sCR = Chr(10)
 sCR2 = sCR & sCR
 sDate = Format(Date, "yyyy.mm.dd")
 sTime = Format(Time, "hh:mm")
 sFormula = ActiveCell.Formula
 iLen = Len(sFormula)
 For iEqual = 1 To iLen
  If Mid(sFormula, iEqual, 1) = "=" Then bEqual = True
 Next iEqual
 If bEqual = False Then sFormula = ActiveCell.Value
 With ActiveCell
  On Error GoTo ErrorHandler:
   .AddComment
   .Comment.Text Text:=sAuthor & sCR & sDate & _
     "  " & sTime & sCR2 & sFormula
   .Comment.Visible = False
 End With
Exit Sub
ErrorHandler:
 sResponse = MsgBox(Prompt:="Copy Formula from Comment to Cell?", _
  Buttons:=vbYesNo, Title:="Comment Formula")
 If sResponse = vbYes Then
  sComment = ActiveCell.Comment.Text
  iLen = Len(sComment)
  If bEqual = False Then
   iLoc = Application.WorksheetFunction.Find(sCR2, sComment, 1)
   sFormula = Right(sComment, iLen - iLoc - 1)
   ActiveCell.Value = sFormula
   Resume Next
  End If
  iLoc = Application.WorksheetFunction.Find("=", sComment, 1)
  sFormula = Right(sComment, iLen - iLoc + 1)
  ActiveCell.Formula = sFormula
 End If
 If sResponse = vbNo Then
  sResponse = MsgBox(Prompt:="Overwrite Comment with Cell Formula?", _
   Buttons:=vbYesNo, Title:="Comment Formula")
  If sResponse = vbYes And bEqual = False Then
   sFormula = ActiveCell.Value
   ActiveCell.Comment.Delete
   ActiveCell.AddComment
  ElseIf sResponse = vbNo Then
   sResponse = MsgBox(Prompt:="             Erase Comment?", _
    Buttons:=vbYesNo, Title:="Comment Formula")
   If sResponse = vbYes Then ActiveCell.Comment.Delete
   Exit Sub
  End If
 End If
 Resume Next
End Sub


I sincerely hope this shortcut helps you as much as it helps me.

Regards,

[color purple]LoNeRaVeR[/color]

[img http://www.LoNeRaVeR.com/images/LoNeRaVeR.gif]
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top