Ignore Comments in code

[PhilOfWalton]

I am trying to parse code to extract various words using

Module.CodeModule.Lines(LineNo, 1)
 

this for example gives me something like

ElseIf InStr(OriginalMsg, "PLP_MessageBox(" & StrSearchTerm) > 0 Then       ' Look for PLP_MessageBox(PLP_Msg)
 

I am trying to find a method of ignoring everything after the "'" comment identifier:- Chr$(39)

So the obvious thing is to search backwards for a "'" Chr$(39) and ignore everything after that.
That fails for a comment like

' Can't find the table
 

If there is no comment, working backwards will fail on

DLookup('MyWord', 'MyTable')
 

The only guaranteed success I have had is if a comment is on a line of it's own.

If Left(Trim(.CodeModule.Lines(LineNo, 1)),1) = Chr$(39) then ...
End If
 

Now the VBA Editor recognises comments, as on my editor the font turns green

Has anyone any ideas how this can be achieved?

Thanks

Phil

[ADezii]

If your Comments are consistently defined as

' <space>Comment

namely

' Can't find the Table
Chr(39) & Chr(32) & "Comment"

then it should be a simple matter to eliminate Lines that start with

' <space>

[PhilOfWalton]

To explain further, I an writing a Db to translate other people's Dbs into foreign languages. It is going surprisingly well, but I have no control over their code. 99% of their DB will remain unchanged. The major exception is, if they want their Msgboxes translating into a foreign language, but this simply means changing
"MsgBox" to "PLP_MessageBox", which is a simple find & replace job.

So going back to your suggestion, I have no control over their code so can't look for the Chr$(39) space that you are suggesting.

This would fail anyway with
' Comment1 ' Comment2 (bit similar to my "Can't" example in the original post

Don't think it's going to be easy, but as I said, the VBA editor gets it right

Thanks

Phil

[Rabbit]

Read a line, split on single quote.

If number of elements in the returned array is even, then that means there's an unpaired single quote. The last element will be the comment.

You might have to do something similar to handle double quotes as well.

You don't need to look backwards, you need to look forward and find the start of possible strings until the string closes or you get to the end of the line.

[PhilOfWalton]

Thanks for trying, but that doesn't work if my comment is

    ' That idea does't work
 

In other words if there is a odd number of quotes within the comment, any pairing method will fail.

Phil

[Rabbit]

That works too, because it's an unpaired single quote. You're not looking for pairs, you're excluding pairs.

Start by excluding all double quote pairs. Then exclude single quote pairs. What's left are any unpaired single quotes. In the example above, there are no pairs to exclude, all that's left is the unpaired comment.

Edit: oh, I see what you mean, I didn't see the other quote the first time.

[Rabbit]

So the solution is a similar methodology, just modified to account for possible single quotes and double quotes within the comment itself.

Basically, I use a recursive function to strip out paired double quotes starting from the beginning of the string until the first occurrence of a single quote outside of double quotes.

The Function

Function CommentFinder(strInput As String) As String
    Dim strLength As Integer, firstSingleLoc As Integer, firstDoubleLoc As Integer
 
    strLength = Len(strInput)
    firstSingleLoc = InStr(1, strInput, "'")
    firstDoubleLoc = InStr(1, strInput, """")
 
    If firstSingleLoc = 0 Then ' No comment found
        CommentFinder = ""
    ElseIf firstSingleLoc < firstDoubleLoc Or firstDoubleLoc = 0 Then ' Comment found, first double quote after comment or no double quote
        CommentFinder = Mid(strInput, firstSingleLoc, strLength)
    Else ' Possible comment found, double quote found, find paired double quote and recurse
        Dim pairedDoubleLoc As Integer
        pairedDoubleLoc = InStr(firstDoubleLoc + 1, strInput, """")
        CommentFinder = CommentFinder(Mid(strInput, pairedDoubleLoc + 1, strLength))
    End If
End Function

The Test

Sub CommentFinderTest()
    Dim testStr As String
 
    testStr = "test string with 'comment at end"
    Debug.Print ""
    Debug.Print testStr
    Debug.Print CommentFinder(testStr)
 
    testStr = "test ""string"" with 'double quotes before comment at end"
    Debug.Print ""
    Debug.Print testStr
    Debug.Print CommentFinder(testStr)
 
    testStr = "test ""st'ring"" with 'double quotes and single quote before comment at end"
    Debug.Print ""
    Debug.Print testStr
    Debug.Print CommentFinder(testStr)
 
    testStr = "test ""st'ring"" with double quotes and single quote and no comment"
    Debug.Print ""
    Debug.Print testStr
    Debug.Print CommentFinder(testStr)
 
    testStr = "test ""st'ring"" with 'double quotes"" and"" single' quote' before and after comment"
    Debug.Print ""
    Debug.Print testStr
    Debug.Print CommentFinder(testStr)
 
    testStr = "test """"""st'''r""""ing"" with 'escaped double quote sequences"
    Debug.Print ""
    Debug.Print testStr
    Debug.Print CommentFinder(testStr)
End Sub

[PhilOfWalton]

Thanks for the new code.

I am trying to test it out on

MsgBox "Can't find it"        ' No My Field
 

But having little success. That is an actual line from some code, so I want to strip out the comment so that I can examine the code.

If your method works, I just replace the comment with "", then trim what remains

Thanks

Phil

[Rabbit]

Just tested it, and yes, it works on that line of code

[PhilOfWalton]

Firstly a big Thank you - seems to be spot on

As a matter if interest I have used the following to create a text file with all the code and the comments highlighted.
It might be of use to semeone

Function GetVBEDeatils()
    On Error GoTo Error_Handler
    Dim vbProj                As VBIDE.VBProject
    Dim vbComp                As VBIDE.VBComponent
    Dim vbMod                 As VBIDE.CodeModule
    Dim pk                    As VBIDE.vbext_ProcKind
    Dim sProcName             As String
    Dim strFile               As String
    Dim iCounter              As Long
    Dim FileNumber            As Integer
    Dim bFileClosed           As Boolean
    Const vbNormalFocus = 1
    Dim StrLine As String
    Dim StrComment As String
    Dim j As Long
 
    'Where do you want the text file created
    strFile = "E:\Phil Data\Access\Mdb 2010\Mike\Mike\ModulesTest.txt"
    If Len(Dir(strFile)) > 0 Then Kill strFile
    FileNumber = FreeFile                           'Get unused file number.
    Open strFile For Append As #FileNumber          'Create file name.
      ' Print the Db header
    Print #FileNumber, "Database: " & Application.CurrentProject.Name
    Print #FileNumber, "Database Path: " & Application.CurrentProject.Path
    Print #FileNumber, String(80, "*")
    Print #FileNumber, String(80, "*")
    Print #FileNumber, ""
 
    For Each vbProj In Application.VBE.VBProjects   'Loop through each project
        Print #FileNumber, "VBA Project Name: " & vbProj.Name
 
        For Each vbComp In vbProj.VBComponents      'Loop through each module
            Set vbMod = vbComp.CodeModule
          ' Modole Name
            Print #FileNumber, "   " & vbComp.Name & " :: " & _
                               vbMod.CountOfLines & " total lines"
            Print #FileNumber, "   " & String(80, "*")
            iCounter = 1
            Do While iCounter < vbMod.CountOfLines  'Loop through each procedure
                sProcName = vbMod.ProcOfLine(iCounter, pk)
                If sProcName <> "" Then
                    Print #FileNumber, ""
                    Print #FileNumber, "      " & sProcName & " :: " & _
                                       vbMod.ProcCountLines(sProcName, pk) & " lines"
                  For j = 1 To vbMod.CountOfLines
                      StrLine = vbComp.CodeModule.Lines(j, 1)
                      If Trim(StrLine) > "" Then                         ' Blank line
                          Print #FileNumber, "          Line: " & StrLine
                          StrComment = CommentFinder(StrLine)
                          If StrComment > "" Then
                              Print #FileNumber, "          ** COMMENT: " & StrComment
                          End If
                      End If
                  Next j
 
                    iCounter = iCounter + vbMod.ProcCountLines(sProcName, pk)
                Else
                    iCounter = iCounter + 1
                End If
            Loop
            Print #FileNumber, ""
        Next vbComp
    Next vbProj
 
    Close #FileNumber                               'Close file.
    bFileClosed = True
 
    'Open the generated text file
    Shell "cmd /c """ & strFile & """", vbNormalFocus
    'In Access you could also use the following line instead of the previous one.
    'Application.FollowHyperlink strFile
 
Error_Handler_Exit:
    On Error Resume Next
    If bFileClosed = False Then Close #FileNumber   'Close file.
    If Not vbMod Is Nothing Then Set vbMod = Nothing
    If Not vbComp Is Nothing Then Set vbComp = Nothing
    If Not vbProj Is Nothing Then Set vbProj = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetVBEDeatils" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function
 

Phil

[Rabbit]

Glad to be of help