Will have a look. As posted earlier, maybe download the add-in that
has this in it.

RBS

"MikeZz" wrote in message
...
Hi RB,
Thanks, this works really well.
The only strange thing is that when I run in the first time,
it errors when calling the Remove Indent procedure...
Says that VBC is not properly defined... although it clearly seems to be.

Then after I "End" and run the Compact sub again, it works like a charm.
Strange.

Thanks again!

"RB Smissaert" wrote:

This code will remove comments, indentations and blank lines. It will
need a
reference to the VBE extensiblity,
Microsoft Visual Basic for Applications Extensibility 5.x. You could put
it
in an add-in.

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


To scramble all Procedure names shouldn't be too difficult either.
Have a look at this website to learn about manipulating the VBE:
http://www.cpearson.com/excel/vbe.aspx


RBS





"MikeZz" wrote in message
...
Hi,
I have an excel VBA application that I'm considering to sell at some
point.

As a deterant to any unauthorized usage, I was wondering if it's
possible
through VBA to completely remove all comments and rename all the
routines
and
functions in all modules and forms with unrecogizable names so that the
code
would work yet be vertutally impossible to understand.

Thanks for any help!
MikeZz