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.

are you looking to read the contents of a comments from a Cell ? let's say in Cell A1 you have a comment inside, do you want to read it ? — Shai Rado, Nov 15 '16 at 15:23
The Ruberduck team might have something like this in their code: http://chat.stackexchange.com/rooms/14929/vba-rubberducking — Chrismas007, Nov 15 '16 at 15:28
Thanks all for the great input, 3 of the answers are really top! :) Could not select all of them, but at least +1 to all. — Vityata, Nov 15 '16 at 16:47

Mathieu Guindon · Answer 1 · 2016-11-26T16:05:07.763

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!

score 4 · Answer 2 · answered Nov 15 '16 at 15:36

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.

Thanks for the note :) I ran it in module first! :) – Vityata Nov 15 '16 at 16:42 — Vityata, Nov 15 '16 at 16:42

score 4 · Accepted Answer · answered Nov 15 '16 at 15:36

4

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

answered Nov 15 '16 at 15:36

gizlmo

1,882
1
14
14

Thanks for the code, it works quite ok, as expected! :) – Vityata Nov 15 '16 at 16:43
It was tough to select an answer, when there are 3 as good as yours, but I have selected yours, because it does not use external library. (There should have been a criteria). – Vityata Nov 15 '16 at 16:46
1

@Vityata it *does*. It just doesn't mention it. The `VBProject`, `VBComponents`, `CodeModule` types and the `CountOfLines` method are all defined in the library everybody else is saying you need to include. – Mathieu Guindon Nov 15 '16 at 16:51
When I paste your code and give debug>compile, I get a missing library. (as you mention in the notes) With the code from @gizlmo it is not the case. Probably this is because of the early binding you did or something else on my PC, but it worked. Anyhow the three of them are really superb. :) – Vityata Nov 15 '16 at 16:59

score 1 · Answer 4 · answered Nov 15 '16 at 15:39

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

that is correct, as I have written, initial code for further improvements :) — Kazimierz Jawor, Nov 15 '16 at 18:45

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

4 Answers4

Linked