I created a module to automatically add ERL lines to exsisting code. Any thoughts?

[redbull]

Hey everyone,


So I've been debugging other peoples code and some of these modules are over 2000 lines of code. With zero existing error handling, and my general inexperience with the desired process... I decided to ERL these large blocks of unruly code. I have a Error handling function that gets called on any error and generates an email to me, telling me what module failed, what ERL it was on, and other variables that deliver me a somewhat kind of clue as to the problem.


So here is the code, if anyone has any use for it Take it... If there are any glaringly obvious improvements let me know.

Sub CreateERLlines()
'Created by Redbull on 03/10/2016 - Posted to Accessforums.net
'This little utiliy sub will read textfile and add ERL numbers to each line of active code. Saving it into a new txtfile.
'I created this because I am debugging some programs with poor error handling that are over 2000 lines of code.
'''
' To use:
'        Highlight the module or code blocks that you want to ERL, then outdent the code until there are no leading spaces.
'        Save the file locally, and "Set F =" needs to be updated to this location and file name.
'        Set F2 as the new file name to be created, it probably could be saved as a .bas file, but txt is tested and proven.
'        Run the code, open the newly created text file, copy/paste where you want it.
'Variables to be set by user:
'        F, this is the txt file holding the outdented code without ERL lines.
'        F2, This is going to be the newly created file WITH the ERL lines.
'        Special cases, the If vba.left statement in the loop might have to be adjusted to prevent ERL lining lines you do not
'        want.
        Dim fso As Scripting.FileSystemObject
        Dim F As File
        Dim F2 As TextStream
        Dim ts As TextStream
        Dim rptText As String
    Set fso = New Scripting.FileSystemObject
    Set F = fso.GetFile("C:\FolderPath\ModuletoERL.txt")  ' Location and name of saved text file to be numbered.
    Set F2 = fso.CreateTextFile("C:\FolderPath\ModuletoERLFinished.txt") ' Name an location of newly created txt file with ERL numbers.
    
    Set ts = F.OpenAsTextStream(1, -2)
    
s = ""
ErlCount = 1
LoopCount = 1
On Error GoTo Handler:
Do
rptText = ts.ReadLine ' Advance to next row.
  'This IF statement can be modified to add/remove filter criteria to NOT get ERL lines
  If VBA.Left(rptText, 1) = "'" Or VBA.Left(rptText, 3) = "Set" Or VBA.Left(rptText, 3) = "Dim" Or VBA.Left(rptText, 1) = "" Or VBA.Left(rptText, 3) = "Sub" Or VBA.Left(rptText, 3) = "Call" Or VBA.Left(rptText, 1) = "(" Or VBA.Left(rptText, 1) = " "Then
       
    s = s & rptText
    s = s & Chr(13) & Chr(10)
    LoopCount = LoopCount + 1
    
    Else  ' This should take place on lines of code you DO want the ERL lines.
    
    s = s & ErlCount & " " & rptText
    s = s & Chr(13) & Chr(10)
    LoopCount = LoopCount + 1
    ErlCount = ErlCount + 1
    
  End If
           If LoopCount >= 100 Then  ' Every 100 lines of code Write the textstream to the new .txt file.
            F2.Write s
            s = ""
            LoopCount = 1
 End If
  
Loop
F2.Close ' Just incase end of textstream doesn't cause error flag.
F2.Write s
MsgBox " At the end of the file, thank you for using the Error Line Generating Tool!", , "www.accessforums.net"
Handler:
  If Err.Number = "62" Then ' End of textstream
    MsgBox " At the end of the file, thank you for using the Error Line Generating Tool!", , "www.accessforums.net"
    F2.Write s
    F2.Close
  End If
    
End Sub

[orange]

Good stuff.

IIRC there is/was a free utility MZTools for VBA (32 bit) that had line number and oodles of other functionality.

I found this on last weekend while helping someone.
https://sites.google.com/site/msacce...rserrorhandler

And that author also provided code to copy a failing module line number if, then report the error with line number.
here https://sites.google.com/site/msacce...errorlineinvba

I use an older version of MZTools for VBA and find it is a great tool.

I also recommend SmartIndenter which is free and will indent your vba code consistently.

I was recently looking at possible code to read modules and insert code to
-have a flag to show debug.print lines
-add line to debug.print "entered procedurename " & now()
-add line to debug.print "exited procedurename " & now()

This was to allow user to see the program flow --helpful for debugging.
The flag was to allow turn the debugging off when moving to production/operation.

[redbull]

Interesting.

Ill post my error handling code tomorrow, it also includes the ERL line.

I really want to use that smartindexer but I know I won't be able to get it cleared for install here. I might just wing it in the ERL sub, shouldn't be hard really... just a matter of setting the amount of spaces I want after the ERL entry in the string....

Yea, another thing I'm not programming in access... I'm using the VBA editor in a system called reflections. I would give a substantial amount to have any database tools at my disposal lol.