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" <(E-Mail Removed)> wrote in message
news:ue%(E-Mail Removed)...
Is there any tool available to automatically strip only the comments from a
VBA project?
Dennis
Similar Threads
