Comments Off

[Paleo]

Is there a way to run a script that takes all the comments in my excel vba modules off?

I mean both. The comments alone in a row as well as the comments in the same row as code.

[Jacob Hilderbrand]

Do you want to delete the comments or just remove the ' ?

[Paleo]

Not just remove the ', because this would lead to an error as the comment would become part of the code. I want to delete all the comments.

[Jacob Hilderbrand]

Ok, try this macro:

Option Explicit
 
 Sub Macro1()
Dim n               As Long
 Dim i               As Long
 Dim j               As Long
 Dim k               As Long
 Dim LineText        As String
 Dim ExitString      As String
ExitString = "Ignore Comments In This Module"
For i = 1 To ActiveWorkbook.VBProject.VBComponents.Count
         With ActiveWorkbook.VBProject.VBComponents(i).CodeModule
             For j = 1 To .CountOfLines
                 LineText = Trim(.Lines(j, 1))
                 If LineText = "ExitString = " & _
                     """" & "Ignore Comments In This Module" & """" Then
                     Exit For
                 End If
                 n = InStr(1, LineText, "'")
                 Select Case n
                     Case Is = 0
                         'No Comment
                     Case Is = 1
                         .ReplaceLine j, ""
                     Case Is > 1
                     .ReplaceLine j, Left(LineText, n - 1)
                 End Select
             Next j
         End With
     Next i
End Sub

[Paleo]

Gee man, you ARE great.



I will test it too and let you know.

[Jacob Hilderbrand]

Sounds good. Let me know if there are any problems.

Take Care

[Jacob Hilderbrand]

Here is a variation if you want to completely delete a line if it is all comments instead of leaving a blank line.

Option Explicit
  
 Sub Macro1()
Dim n               As Long
     Dim i               As Long
     Dim j               As Long
     Dim k               As Long
     Dim LineText        As String
     Dim ExitString      As String
For i = 1 To ActiveWorkbook.VBProject.VBComponents.Count
         With ActiveWorkbook.VBProject.VBComponents(i).CodeModule
             For j = .CountOfLines To 1 Step -1
                 LineText = Trim(.Lines(j, 1))
                 If LineText = "ExitString = " & _
                 """" & "Ignore Comments In This Module" & """" Then
                 Exit For
             End If
             n = InStr(1, LineText, "'")
             Select Case n
             Case Is = 0
             Case Is = 1
                 .DeleteLines j, 1
             Case Is > 1
                 .ReplaceLine j, Left(LineText, n - 1)
             End Select
         Next j
     End With
 Next i
ExitString = "Ignore Comments In This Module"
End Sub

[Paleo]

Gee DRJ,

are you reading my mind?? I was trying to do that, .

Many thanks again.

[Jacob Hilderbrand]

Yeah I figured that you would probably want to actually delete the lines.

Take Care