Is there any tool available to automatically strip only the comments from a VBA project?

Dennis

Hi,

I've used this in the past

http://www.vbaexpress.com/kb/getarticle.php?kb_id=266

Mike

"Dennis Tucker" wrote:

Is there any tool available to automatically strip only the comments from a VBA project?

Dennis

Mike,

Thanks.

Dennis

"Mike H" wrote in message
...
Hi,

I've used this in the past

http://www.vbaexpress.com/kb/getarticle.php?kb_id=266

Mike

"Dennis Tucker" wrote:

Is there any tool available to automatically strip only the comments from
a VBA project?

Dennis

The following method is aggressive as it will remove things that looked like comments inside of text constants within your code. If you can live with that, just call up the Replace dialog box with the VB editor (Edit/Replace from the menu bar or Ctrl+H from the keyboard), put '* (that is an apostrophe followed by an asterisk) in the "Find What" field, leave the Replace field empty, select the "Current Project" OptionButton from the Search frame and put a checkmark in the "Use Pattern Matching" CheckBox, then click the "Replace All" CommandButton... that should remove all comments (both inline and stand alone ones) that start with an apostrophe (it will not do comments that start with the Rem keyword).

--
Rick (MVP - Excel)


"Dennis Tucker" wrote in message ...
Is there any tool available to automatically strip only the comments from a VBA project?

Dennis

Try the following code, it will work only on comments which are not on the
same line as code

Sub ClearAllComments()
Dim App As Application, i As Integer, x As Integer, LastLine As Integer
Set App = Application
Dim ThisMod As VBComponent

Set ThisMod = ThisWorkbook.VBProject.VBComponents("MyModuleName" )

LastLine = ThisMod.CodeModule.CountOfLines

For i = 1 To LastLine
For x = 1 To Len(ThisMod.CodeModule.Lines(i, 1))
If Mid(ThisMod.CodeModule.Lines(i, 1), x, 1) < "" Then
If Mid(ThisMod.CodeModule.Lines(i, 1), x, 1) = "'" Then
ThisMod.CodeModule.DeleteLines i, 1
Exit For
End If

End If
Next x
Next i
End Sub

Hope this helps

--
A. Ch. Eirinberg
"Dennis Tucker" wrote in message
...
Is there any tool available to automatically strip only the comments from a
VBA project?

Dennis

Sorry my previous code wouldn't work, copy and paste the following, I made a
few changes
Dont put this Sub in the same code module from which you want to get rid of
comments.
---------------------------------
Sub ClearAllComments()
Dim TheMod As VBComponent, i As Integer, x As Integer, LastLine As
Integer

Set TheMod = ThisWorkbook.VBProject.VBComponents("MyModuleName" )

LastLine = TheMod.CodeModule.CountOfLines

For i = LastLine To 1 Step -1
For x = 1 To Len(TheMod.CodeModule.Lines(i, 1))
If Mid(TheMod.CodeModule.Lines(i, 1), x, 1) < " " Then
If Mid(TheMod.CodeModule.Lines(i, 1), x, 1) = "'" Then
TheMod.CodeModule.DeleteLines i, 1
Exit For
Else
Exit For
End If
End If
Next x
Next i
End Sub


--
A. Ch. Eirinberg
"Howard31" wrote in message
...
Try the following code, it will work only on comments which are not on the
same line as code

Sub ClearAllComments()
Dim App As Application, i As Integer, x As Integer, LastLine As Integer
Set App = Application
Dim ThisMod As VBComponent

Set ThisMod = ThisWorkbook.VBProject.VBComponents("MyModuleName" )

LastLine = ThisMod.CodeModule.CountOfLines

For i = 1 To LastLine
For x = 1 To Len(ThisMod.CodeModule.Lines(i, 1))
If Mid(ThisMod.CodeModule.Lines(i, 1), x, 1) < "" Then
If Mid(ThisMod.CodeModule.Lines(i, 1), x, 1) = "'" Then
ThisMod.CodeModule.DeleteLines i, 1
Exit For
End If

End If
Next x
Next i
End Sub

Hope this helps

--
A. Ch. Eirinberg
"Dennis Tucker" wrote in message
...
Is there any tool available to automatically strip only the comments from
a VBA project?

Dennis

This code will give you all options to remove formatting from the code.
Run the first Sub.

Option Explicit
Private VBProjToClean As VBProject
Private strFileToClean As String

Sub CompactVBEProject()

Dim VBC As VBComponent
Dim WB As Workbook
Dim VBProj As VBProject
Dim strFile As String
Dim msgResult As VbMsgBoxResult

For Each VBProj In Application.VBE.VBProjects
On Error Resume Next
Select Case MsgBox("COMPACT THIS FILE ?", _
vbYesNoCancel + vbDefaultButton2, _
VBProj.Filename)
Case vbYes
Set VBProjToClean = VBProj
strFileToClean = VBProj.Filename
Exit For
Case vbNo
Case vbCancel
Exit Sub
End Select
Next

If VBProjToClean Is Nothing Then
Exit Sub
End If

Select Case MsgBox("DO ALL 3 COMPACTING OPTIONS ?" & _
vbCrLf & vbCrLf & _
"REMOVE COMMENTS" & _
vbCrLf & _
"REMOVE INDENTATIONS" & _
vbCrLf & _
"REMOVE BLANK LINES", _
vbYesNoCancel + vbDefaultButton1, _
strFile)
Case vbYes
VBE_Remove_Comments
VBE_Remove_Indents
VBE_Remove_BlankLines
Case vbNo
Select Case MsgBox("REMOVE COMMENTS FROM THIS FILE ?", _
vbYesNoCancel + vbDefaultButton2, strFile)
Case vbYes
VBE_Remove_Comments
Case vbNo
Case vbCancel
Exit Sub
End Select
Select Case MsgBox("REMOVE INDENTATIONS FROM THIS FILE ?", _
vbYesNoCancel + vbDefaultButton2, strFile)
Case vbYes
VBE_Remove_Indents
Case vbNo
Case vbCancel
Exit Sub
End Select
Select Case MsgBox("REMOVE BLANK LINES FROM THIS FILE ?", _
vbYesNoCancel + vbDefaultButton2, strFile)
Case vbYes
VBE_Remove_BlankLines
Case vbNo
Case vbCancel
Exit Sub
End Select
Case vbCancel
Exit Sub
End Select

End Sub

Sub VBE_Remove_Comments()

Dim VBC As VBComponent
Dim i As Long
Dim j As Long
Dim str As String
Dim blnStringMode As Boolean
Dim blnLineContinue As Boolean
Dim lCount As Long

With Application
.Cursor = xlWait
.StatusBar = " Removing comments, please wait ..."
End With

For Each VBC In VBProjToClean.VBComponents

blnStringMode = False
i = 1
With VBC.CodeModule

Do Until i .CountOfLines
If Not .ProcOfLine(i, vbext_pk_Proc) = "VBE_Remove_Comments" Then
str = .Lines(i, 1)
blnLineContinue = (Right(str, 2) = " _")

For j = 1 To Len(str)
Select Case Mid(str, j, 1)
Case """": blnStringMode = Not blnStringMode
Case "'"
If Not blnStringMode Then
str = RTrim(Mid(str, 1, j - 1))
If LTrim(str) = "" Then
.DeleteLines i
i = i - 1
lCount = lCount + 1
Else
.ReplaceLine i, str
lCount = lCount + 1
End If

Do While blnLineContinue
blnLineContinue = _
(Right(.Lines(i + 1, 1), 2) = " _")
.DeleteLines i + 1
lCount = lCount + 1
Loop

Exit For
End If
End Select
Next

End If
i = i + 1
Loop
End With
Next

With Application
.Cursor = xlDefault
.StatusBar = False
End With

MsgBox lCount & " COMMENT LINES REMOVED", , strFileToClean

End Sub

Sub VBE_Remove_Indents()

Dim VBC As VBComponent
Dim i As Long
Dim j As Long
Dim lCount As Long

With Application
.Cursor = xlWait
.StatusBar = " Removing indentations and trailing spaces, please
wait ..."
End With

For Each VBC In VBProjToClean.VBComponents
With VBC.CodeModule
For i = 1 To .CountOfLines
If Not .ProcOfLine(i, vbext_pk_Proc) = "VBE_Remove_Indents" Then
If Left(.Lines(i, 1), 1) = Chr(32) Then
.ReplaceLine i, _
Replace(.Lines(i, 1), _
.Lines(i, 1), _
Trim$(.Lines(i, 1)), , , _
vbTextCompare)
lCount = lCount + 1
End If
End If
Next
End With
Next

With Application
.Cursor = xlDefault
.StatusBar = False
End With

MsgBox "REMOVED INDENTATIONS FROM " & lCount & " LINES", ,
strFileToClean

End Sub

Sub VBE_Remove_BlankLines()

Dim VBC As VBComponent
Dim i As Long
Dim j As Long
Dim lCount As Long

With Application
.Cursor = xlWait
.StatusBar = " Removing blank lines, please wait ..."
End With

For Each VBC In VBProjToClean.VBComponents
With VBC.CodeModule
For i = .CountOfLines To 1 Step -1
If Not .ProcOfLine(i, vbext_pk_Proc) = "VBE_Remove_BlankLines" Then
If Len(Trim(.Lines(i, 1))) = 0 Then
.DeleteLines i
lCount = lCount + 1
End If
End If
Next
End With
Next

With Application
.Cursor = xlDefault
.StatusBar = False
End With

MsgBox "REMOVED " & lCount & " BLANK LINES", , strFileToClean

End Sub


RBS



"Dennis Tucker" wrote in message
...
Is there any tool available to automatically strip only the comments from a
VBA project?

Dennis