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: .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.