Can I get the text of the comments in the VBA code

Question

Lets say I have the following:

Public Sub Information()
    'TEST
End Sub

Is there a way to get "TEST" as a result? Somehow through VBA?

E.g. - In PHP there is a good way to take the comments. Any ideas here?

Edit: There should be a way, because tools like MZ-Tools are able to provide the comments when they generate the documentation.

Answer 1

After some tests, I got to this solution:

simply pass the name of the code-module to the function and it will print all comment lines. Inline comments won't work(you have to change the condition)

Function findComments(moduleName As String)

Dim varLines() As String
Dim tmp As Variant

With ThisWorkbook.VBProject.VBComponents(moduleName).CodeModule

    'split the lines of code into string array
    varLines = Split(.lines(1, .CountOfLines), vbCrLf)
End With

'loop through lines in code
For Each tmp In varLines

    'if line starts with '
    If Trim(tmp) Like "'*" Then

        'print comment line
        Debug.Print Trim(tmp)
    End If
Next tmp
End Function

Answer 2

You could try with reading line by line of code in your module. Here is just idea returning first comment for further improvements:

Sub callIt()

    Debug.Print GetComment("Module1")

End Sub
Function GetComment(moduleName As String)
    Dim i As Integer
    With ThisWorkbook.VBProject.VBComponents(moduleName).CodeModule
        For i = 1 To .CountOfLines

            If Left(Trim(.Lines(i, 1)), 1) = "'" Then
                'here we have comments
                'return the first one
                GetComment = .Lines(i, 1)
                Exit Function
            End If
        Next i
    End With


End Function

Important! in Reference window add one to 'Microsoft Visual Basic for Applications Extensibility'.

Answer 3

You can use Microsoft Visual Basic for Applications Extensibility to examine code at runtime:

'Requires reference to Microsoft Visual Basic for Applications Extensibility 
'and trusted access to VBA project object model.
Public Sub Information()
    'TEST
End Sub

Public Sub Example()
    Dim module As CodeModule
    Set module = Application.VBE.ActiveVBProject.VBComponents(Me.CodeName).CodeModule

    Dim code As String
    code = module.lines(module.ProcStartLine("Information", vbext_pk_Proc), _
                        module.ProcCountLines("Information", vbext_pk_Proc))
    Dim lines() As String
    lines = Split(code, vbCrLf)
    Dim line As Variant
    For Each line In lines
        If Left$(Trim$(line), 1) = "'" Then
            Debug.Print "Found comment: " & line
        End If
    Next
End Sub

Note that the above example assumes that it's running in a Worksheet or Workbook code module (hence Me when locating the CodeModule). The best method for locating the correct module will depend on where you want to locate the procedure.

Answer 4

You need to parse the code yourself, using the VBA Extensibility library (aka "VBIDE API"). Add a reference to the Microsoft Visual Basic for Applications Extentibility 5.3 type library, and then you can access types such as CodePane and VBComponent:

Sub FindComments()

    Dim component As VBComponent
    For Each component In Application.VBE.ActiveVBProject.VBComponents
        Dim contents As String
        contents = component.CodeModule.Lines(1, component.CodeModule.CountOfLines)
        '"contents" now contains a string with the entire module's code.

        Debug.Print ParseComments(contents) 'todo

    Next

End Sub

Once you have a module's contents, you need to implement logic to find comments... and that can be tricky - here's some sample code to play with:

Sub Test()
    Dim foo 'this is comment 1

    'this _
       is _
            comment 2

    Debug.Print "This 'is not a comment'!"

    '..and here's comment 3

    REM oh and guess what, a REM instruction is also a comment!
    Debug.Print foo : REM can show up at the end of a line, given an instruction separator
End Sub

So you need to iterate the lines, track whether the comment is continuing on the next line / continued from the previous line, skip string literals, etc.

Have fun!