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.

:dunno :dunno :dunno :dunno

[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