Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

Save A Formula As A Comment by LoNeRaVeR
Posted: 17 Dec 02

Any Microsoft Excel 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 [Ctrl+C] and then paste over the same range with the values of the cells in that range[ALT,E,S,V].

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 Excel VBA 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 PERSONAL.XLS workbook and assign a shortcut-key to it.

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:
   .Comment.Text Text:=sAuthor & sCR & sDate & _
     "  " & sTime & sCR2 & sFormula
   .Comment.Visible = False
 End With
Exit Sub
 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
  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.




Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close