VBA Code Cleaner

Question

I "inherited" some very large Excel files with a lot of VBA code.

This code needs a lot of cleaning up. There are many line breaks that are confusing, indentations that are just wrong, and so on.

What I've written so far is:

Sub Frank_Code_Cleaner()

'VBA Code Clean up

    Dim i As Long

    Dim strCodeLine As String

    Dim strModule As String

    Dim Wb As Workbook

    Dim strErrorLine As Long

    Dim VBComp As VBComponent

'    Dim VBComp As Object

'    Dim VBComp As VBIDE.VBComponent 

    Dim strVBCompName As String

    Dim s As String

    Dim n As Integer

    n = FreeFile()

    Open "C:\test.txt" For Output As #n

    'Print #n, Now()

    's = "Hello, world!"

    'Debug.Print s ' write to immediate

    'Print #n, s ' write to file

     Set Wb = ActiveWorkbook

     strVBCompName = "Module2"   '#########Edit to required module name#########

     Set VBComp = Wb.VBProject.VBComponents(strVBCompName)

     With VBComp.CodeModule

         For i = 1 To .CountOfLines

             If Trim(.Lines(i, 1)) = vbCrLf Or Trim(.Lines(i, 1)) = Chr(10) Or Trim(.Lines(i, 1)) = "" Then

             ElseIf Left(Trim(.Lines(i, 1)), 4) = "Sub " Or Left(Trim(.Lines(i, 1)), 11) = "Public Sub " Or Left(Trim(.Lines(i, 1)), 12) = "Private Sub " _

              Or Left(Trim(.Lines(i, 1)), 9) = "Function " Or Left(Trim(.Lines(i, 1)), 16) = "Public Function " Or Left(Trim(.Lines(i, 1)), 17) = "Private Function " Then

                Print #n, Trim(.Lines(i, 1))  ' write to file

             ElseIf Left(Trim(.Lines(i, 1)), 4) = "Dim " Then

                If Left(Trim(.Lines(i, 1)), 4) = "Dim " And Not Left(Trim(.Lines(i + 1, 1)), 4) = "Dim " Then

                    Print #n, "    " & Trim(.Lines(i, 1)) & Chr(10)  ' write to file

                Else

                    Print #n, "    " & Trim(.Lines(i, 1)) ' write to file

                End If

             ElseIf Trim(.Lines(i, 1)) = "End Sub" Or Trim(.Lines(i, 1)) = "Option Explicit" Or Trim(.Lines(i, 1)) = "End Function" Then

                Print #n, Trim(.Lines(i, 1)) & Chr(10) ' write to file

             ElseIf Not Left(.Lines(i, 1), 4) = "    " And Not Left(.Lines(i, 1), 1) = "'" Then

                Print #n, "    " & Trim(.Lines(i, 1)) ' write to file

             Else

                Print #n, .Lines(i, 1) ' write to file

             End If

         Next i

     End With

    Close #n

End Sub

Does anybody have something better or can help me to create a macro to complete the cleanup?

I persume that the ideal situation is that for every module or form the code gets written to a seperate text file and then import it again?

Thx

Frank

***Post moved by the moderator to the appropriate forum category.***

Answer 1

Rob Bovey has written a routine for cleaning VBA code.

Free download from here. . . . .

http://www.appspro.com/Utilities/Utilities.htm

Gord

Answer 2

Anybody have sugestions to make this work a little better?

A problem I have is that exe files and zip files are blocked by our firewall and can not install anything on my computer, so the only solution I see is to write this myself.

Pleeeaase can some body help?

Answer 3

I do not have the code.

Gord

Answer 4

Rob Bovey has written a routine for cleaning VBA code.

Free download from here. . . . .

http://www.appspro.com/Utilities/Utilities.htm

Gord

Our company firewall is blocking this exe file from http://www.appspro.com/Utilities/CodeCleaner.htm.

Do you perhaps have the code itself?

Frank