Code in Comments

You can now use code in comments, thanks to the new version of iG:Syntax Hiliter. If you want to hilite the code in your comment, surround it with special tags. The special tags are [ vb ] and [ / vb ]. If you exclude the spaces, and you should, it reads: open bracket, vb, close bracket and open bracket, backslash, vb, close bracket.

If, in your code, you have any blank lines, the font will change and it will look silly. I recommend that you put a single space in any blank line so that the formatting doesn’t change. I’ve asked the author of the plug-in why it does this and he says it’s because of my css. I wrote my css, but I still don’t know enough about it to be able to figure out why this misbehaves. If you’re a css expert and want to help me, let me know.

Enjoy your new found abilities. Feel free to experiment like crazy in a comment to this post.

Posted in Uncategorized

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

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

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

  9. zfraile

    I did not get the Copy formula code to work…

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

    What am i missing

    Regards
    Sam

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

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

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

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

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

  15. Dick –

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

    …best, Michael


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

Leave a Reply

Your email address will not be published. Required fields are marked *