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