PDA

View Full Version : [SOLVED] Comments Off



Paleo
01-06-2005, 04:56 PM
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
01-06-2005, 05:02 PM
Do you want to delete the comments or just remove the ' ?

Paleo
01-06-2005, 05:06 PM
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
01-06-2005, 05:49 PM
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
01-06-2005, 06:01 PM
Gee man, you ARE great.



I will test it too and let you know.

Jacob Hilderbrand
01-06-2005, 06:41 PM
Sounds good. Let me know if there are any problems.

Take Care

Jacob Hilderbrand
01-06-2005, 06:46 PM
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
01-07-2005, 05:00 AM
Gee DRJ,

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

Many thanks again.

Jacob Hilderbrand
01-07-2005, 05:28 AM
Yeah I figured that you would probably want to actually delete the lines.

Take Care