D
Dennis Tucker
Is there any tool available to automatically strip only the comments from a VBA project?
Dennis
Dennis
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Mike H
D
Dennis Tucker
Mike,
Thanks.
Dennis
Thanks.
Dennis
Mike H said:
R
Rick Rothstein
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)
Is there any tool available to automatically strip only the comments from a VBA project?
Dennis
--
Rick (MVP - Excel)
Is there any tool available to automatically strip only the comments from a VBA project?
Dennis
H
Howard31
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
Is there any tool available to automatically strip only the comments from a
VBA project?
Dennis
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
Is there any tool available to automatically strip only the comments from a
VBA project?
Dennis
H
Howard31
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
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
R
RB Smissaert
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
Is there any tool available to automatically strip only the comments from a
VBA project?
Dennis
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
Is there any tool available to automatically strip only the comments from a
VBA project?
Dennis