April 28, 2007

Macro Comments

Something I'm experimenting with the moment is writing code to work with the Visual Basic Editor. Writing code to write code if you know what I mean.

Anyway, this is something I made to add comments to all macros within a project (workbook). As far as I can see (very quick testing, mind you), it seems to work. Before you run the code, you will need you will need to set a reference to the Visual Basic Project - make sure the project you are working on is selected and go to Tools, References and check Microsoft Visual Basic for Applications Extensibility on the VBE, (not the Tools Menu within Excel itself, the Visual Basic Editor!) Yes, I copied and pasted this part from my Marlett Checkboxes post :-)

Also, for Excel versions 2000 and above, you may need to check “Trust access to Visual Basic Project” from the Tools menu, Macros, Security, Trusted Sources. For Excel 2007, check “Trust access to the VBA project object model” on the Developer tab, Macro Security, Macro Settings, Developer Macro Settings. (If the Developer tab is not visible, you must push the round Office button at the top left corner, then Excel Options, Popular, Show Developer tab in the Ribbon)

Okay, here is the code,
Sub AddMacroComments()
    Dim sComment As String
    Dim Cntr As Long
    Dim NumLines As Long
    Dim oVBComp As VBComponent

    Application.Visible = False

    sComment = InputBox("Please enter your generic macro comment.", _
                        "Add Macro Comments")

    If sComment = "" Then GoTo Cleanup

    sComment = "'" & sComment

    For Each oVBComp In ActiveWorkbook.VBProject.VBComponents
        With oVBComp.CodeModule
            NumLines = .CountOfLines * 2
            For Cntr = 1 To NumLines
                If InStr(.Lines(Cntr, 1), "Sub ") > 0 _
                   Or InStr(.Lines(Cntr, 1), "Function ") > 0 Then
                    If InStr(.Lines(Cntr, 1), "InStr(.Lines(Cntr, 1)") = 0 Then
                        .ReplaceLine Cntr, (.Lines(Cntr, 1) & Chr(13) & sComment)
                    End If
                End If
            Next Cntr
        End With
    Next oVBComp

Cleanup:
    Application.Visible = True
End Sub

And this code should delete them,
Sub DeleteMacroComments()
    Dim Cntr As Long
    Dim NumLines As Long
    Dim oVBComp As VBComponent

    Application.Visible = False

    For Each oVBComp In ActiveWorkbook.VBProject.VBComponents
        With oVBComp.CodeModule
            NumLines = .CountOfLines * 2
            For Cntr = 1 To NumLines
                If InStr(.Lines(Cntr, 1), "Sub ") > 0 _
                   Or InStr(.Lines(Cntr, 1), "Function ") > 0 Then
                    If Left(.Lines(Cntr + 1, 1), 1) = "'" Then
                        .DeleteLines Cntr + 1
                    End If
                End If
            Next Cntr
        End With
    Next oVBComp

    Application.Visible = True
End Sub

I'm actually in the process of making an add-in so you can add or delete multiple line comments and will upload it when (if) I get it finished ;-)

Blog Comments Gone Bye Bye

Unfortunately I have had to spend a lot of time getting rid of comment spam recently, yesterday I deleted about 2,000 of them. Being a free blog site, I guess I can't complain but the anti-spam options offered are not up to the task. Therefore I am taking the drastic step of not accepting any comments or trackbacks at all. I might revert back every now and then to see if something has been added to get rid of this spam, until then please send an email. My apologies in advance for the inconvenience.  

Posted by andrewe at 18:29