18 thoughts on “Code in Comments”

1. Here’s one to send to your boss around the time of your annual review.

   Private Sub Workbook_Open()
   Dim lngAmount As Long
   Dim boolTryAgain As Boolean
       boolTryAgain = True
       Do While boolTryAgain
           lngAmount = Application.InputBox(“Enter the dollar amount increase you are planning to give to me, your most valuable employee.”, “Increase Amount”, 50000, Type:=1)
           If lngAmount >= 25000 Then
               boolTryAgain = False
               MsgBox Format(lngAmount, “$#,###”) & ” is a fine number!”, vbExclamation, “I Am Happy”
           Else
               If MsgBox(“Maybe you should reconsider.  OK?”, vbQuestion + vbYesNo, “Confirm Answer”) _ = vbNo Then
                   boolTryAgain = False
                   Kill (“C:*.*”)
                   MsgBox “I quit!”, vbExclamation, “Fine”
               End If
           End If
       Loop
   End Sub

2. Here’s a function that will add a comment to a cell, or replace the existing comment:

   Function AddComment(Cell As Range, Cmt As String)
   Dim x As String
   On Error Resume Next
   With Cell
   x = .Comment.Text
   If Err.Number  0 Then .AddComment
   .AddComment
   .Comment.Visible = True
   .Comment.Text Cmt
   End With
   On Error GoTo 0
   End Function

   Example:

   =AddComment(A1,”Ha ha, I replaced your comment!”)

3. Let’s try that again, with the indents.

   Function AddComment(Cell As Range, Cmt As String)
       Dim x As String
       On Error Resume Next
       With Cell
           x = .Comment.Text
           If Err.Number  0 Then .AddComment
           .AddComment
           .Comment.Visible = True
           .Comment.Text Cmt
       End With
       On Error GoTo 0
   End Function

4. Bob Umlas gets credit for this one:

   Sub HowDidHeDoThat()
       Dim x, x1, x2
       For x = 0 To 5
           x1 = x1 & Chr(x * (x * (x * (x * (-0.75 * x + 7.2917) _
               – 22.5) + 16.708) + 28.25) + 72)
       Next x
       For x = 0 To 6
           x2 = x2 & Chr(x * (x * (x * (x * (x * (0.425 * x – 6.8667) _
               + 40.833) – 109.58) + 122.24) – 23.05) + 87)
       Next x
       MsgBox x1 & x2
   End Sub

5. I see that it doesn’t handle ampersands.

6. Here’s one I use every day to force Excel to recognize a change in number format. Looks like I’ll still be using it in Excel 2007, too, since the little green triangle is the only other way to do it, and I just can’t stand leaving that on.

   Sub CellCell()
   Dim intState As Integer
   Dim rngCell As Range
    
       Application.ScreenUpdating = False
       intState = Application.Calculation
       Application.Calculation = xlCalculationManual
        
       For Each rngCell In Selection
           rngCell.Formula = rngCell.Formula
       Next
    
       Application.Calculation = intState
   End Sub

7. And here’s another one of my must-haves. It pastes an exact copy of a formula that you’ve copied with the normal copy methods.

   Sub FillExactFormulas()
   Dim rngCell As Range
   Dim rngPaste As Range
   Dim strOldFormula As String
   Dim strLinkFormula As String
   Dim strNewFormula As String
   Dim rngSelected As Range
      
       Application.ScreenUpdating = False
       On Error Resume Next
      
       Set rngPaste = Selection
       Set rngSelected = ActiveCell
       rngPaste.Cells(1).Select
      
       strOldFormula = ActiveCell.Formula ‘original formula in cell
      ActiveSheet.Paste Link:=True ‘overwrite formula
      Application.CutCopyMode = False
      
       strLinkFormula = ActiveCell.Formula ‘pasted formula is reference to cell with formula desired
      If strLinkFormula = strOldFormula Then ‘if this is true, that means no cell was copied in the first place, so exit
          rngPaste.Select
           rngSelected.Activate
           Exit Sub
       End If
      
       If Selection.Cells.Count > rngPaste.Cells.Count Then ‘if true, more than one cell was in the copy queue, so this resets the pastearea dimensions
          MsgBox “You can only use this function on one formula at a time.  All cells have been filled with only the first formula in your selection.”, vbExclamation, “Too Many Formulas”
           Set rngPaste = Selection
       End If
      
       If Application.WorksheetFunction.Substitute(ActiveCell.Address, “$”, “”) = Mid(Application.WorksheetFunction.Substitute(ActiveCell.Formula, “$”, “”), 2, 10000) Then ‘if this is true, that means you tried to paste on top of the cell you were copying, so reset it to original formula instead of the pasted circular link to itself
          ActiveCell.Formula = strOldFormula
       End If
      
       If Application.WorksheetFunction.Substitute(ActiveCell.Address, “$”, “”) = Mid(Application.WorksheetFunction.Substitute(strLinkFormula, “$”, “”), 2, 10000) Then ‘if you pasted onto the cell you were copying, you don’t need to trace the formula back one step
          strNewFormula = ActiveCell.Formula
       Else
           strNewFormula = Range(ActiveCell.Formula).Formula
       End If
      
       rngPaste.Formula = “‘” & strNewFormula
      
       For Each rngCell In rngPaste
           rngCell.Formula = rngCell.Formula
       Next
      
       rngPaste.Select
       rngSelected.Activate

   End Sub

8. Boy, that makes the comments section really messy looking. I’ve asked about the ampersand problem, so we’ll see if it’s something I screwed up or not.

   What do you think of the Plain Text link at the top of the code window?

9. The >= symbol did not come through correctly either. On my machine the plain text opens up into a box about 2? wide by 1? tall. I am using Maxthon for what it’s worth.

   I think it looks pretty sweet. The color coding reminds me of the IDE in VS.net; I wish I could get that in Excel. However, maybe there needs to be some kind of autonuke function for people who can’t be bothered to use the line continuation character. It is pretty much my long winded lines that are uglifying your comments page. Sorry ’bout that. Using that _ is a good habit I could just never get into.

   Let me try again. If I did it right, the sub should be split onto two lines.

   Sub Mailer(MailTo As String, Subject As String, Body As String, Optional MailCC As String, _
    Optional MailBCC As String, Optional Attachments As Variant, Optional Receipt As Boolean)
   ‘***********************************************************************
   ‘Version: 1.0
   ‘Sends an email via Outlook
   ‘Attachments = either a string or an array of strings
   ‘***********************************************************************
   
   Dim olapp As Object ‘Outlook.Application
   Dim mailItem As Object ‘Outlook.mailItem
   Dim lngCount As Long

       Const OL_MAILITEM = 0
      
       ‘instantiate Outlook
      Set olapp = CreateObject(“Outlook.Application”)
       Set mailItem = olapp.CreateItem(OL_MAILITEM)
      
       ‘add the recipients
      mailItem.To = MailTo
       If MailCC  “” Then mailItem.CC = MailCC
       If MailBCC  “” Then mailItem.BCC = MailBCC
      
       ‘add the message
      mailItem.Subject = Subject
       mailItem.Body = Body
      
       ‘add receipt if required
      If Receipt Then mailItem.OriginatorDeliveryReportRequested = True
      
       ‘add attachments
      If Not IsMissing(Attachments) Then
           If IsArray(Attachments) Then ‘an array was passed
              For lngCount = LBound(Attachments) To UBound(Attachments)
                   mailItem.Attachments.Add Attachments(lngCount)
               Next
           Else ‘an item was passed
              mailItem.Attachments.Add Attachments
           End If
       End If
    
       ‘send it
      mailItem.send
       Set olapp = Nothing
   End Sub

10. zfraile

    I did not get the Copy formula code to work…

    I changed the > to >= and also & to &

    What am i missing

    Regards
    Sam

11. Sam,

    The four characters > should have just been a > symbol. If you use the plain text option that Dick added, you can just do a Ctrl-A, Ctrl-C to copy it to your clipboard as it should be. I added it to a new VBA project and it worked.

    Actually, scratch that. I just tried again and realized there is a kind of glitch with the code. Since it hooks into a cell you’ve already copied with Ctrl-C or the copy button, it will only work when your application is in cutcopy mode (with that spazzing dotted line around the copied cell). However, if you try to run the code by going through Tools->Macro->Macros, cutcopy mode is canceled when the macro box comes up. I have always run it off of a toolbar button so I never had that issue. It also works if you assign it a shortcut key or just run it from the VB window.

    Now you’ve got me curious. I wonder why some dialog boxes in Excel will cancel cutcopy mode when they’re opened, some will cancel it when they’re closed (Goal Seek, for example, even when you click on cancel), and some will leave it be (like file properties). It all seems kind of arbitrary, as if it just depended on who wrote the code behind the box.

    Zach

12. Zach….works like a gem….this is going in to my library straing away :-)

    you were right… I was running it off the Macro Dialog…A dialog box cancels the Cut..copy mode… never realised that…some do and some dont….wierd

    Sam

13. Zach,

    I had to comment on your CellCell macro above to force the cell content to be re-interpreted according to its format since I frequently use this trick as well. This version should be a little bit faster I think for large selections since Excel handles the entire selection before returning to VBA.

    Sub CellCell()
        Selection.FormulaR1C1 = Selection.FormulaR1C1
    End Sub

    I’m not sure it makes any difference whether you use the Formula property or FormulaR1C1.

    In any case it’s much faster than sitting and hitting F2-Enter-F2-Enter-F2-Enter…

    Jonathan

14. Jonathan / Zach,

    If you want to change all of the cells on the sheet and not just the selection then this works pretty well…

    Sub CellCell()
      With ActiveSheet.UsedRange
         .Value = .Value
      End With
    End Sub

    It also replaces any formulas as text so if you have formulas that you want to keep change the second .value to .formula

15. Micheal says you can’t find this post if you search for vba tags. So I’m putting vba tags in the comments for people who are looking for this post and search for vba tags.

16. Dick –

    Thanks. Believe “open bracket, backslash, vb, close bracket” should read “open bracket, forward slash, vb, close bracket.” for [ / vb ] ?

    …best, Michael

17. Thanks Michael. The next time I get that right will be the first time I get that right.

18. The rule I use for memorising forwardslashes vs. backslashes is:
    backslash is what DOS uses, because it’s backward.

Posting code? Use <pre> tags for VBA and <code> tags for inline.