Thanks Nick!
I actually did something similar to that: I used MZ Tools and created a
code temlate with logging directions. After that I bound a shortcut key
to "Add code template" and manually went though every procedure....
This is effectively the same editing the Error Handler.
However, for the sake of "academic" interest I still tried to
accomplish the same automatically, half way successful-
For that purpose i have created 2 procedures and 1 global variable:
1. A global variable
LogEverything is either true or false and can be changed at any point
of time to indicate if I want to log anything as the code executes or
not
2. A Function WriteLog(TextToLog As String) which stores TextToLog
string in a predefined log file:
Public Function WriteLog(TextToLog As String)
'---------------------------------------------------------------------------------------
'
' Procedure : WriteLog
' DateTime : 18-10-06 09:42
' Organisation: XXX
' Author : XXX
' Purpose :
'
'---------------------------------------------------------------------------------------
On Error GoTo WriteLog_Error
Dim Fname As String
Dim LogFileName As String
'Log file name
LogFileName = "log.txt"
'Log file path
Fname = ThisWorkbook.Path & "\" & LogFileName
'Writing text to log file
Open Fname For Append As #1
Write #1, TextToLog
Close #1
On Error GoTo 0
Exit Function
'Errors handled here
WriteLog_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
function WriteLog of Module max"
End Function
3. A sub AddLogging which actually does the job:
Sub AddLogging()
'---------------------------------------------------------------------------------------
'
' Procedure : AddLogging
' DateTime : 18-10-06 10:04
' Organisation: XXX
' Author : XXX
' Purpose :
'
'---------------------------------------------------------------------------------------
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim ProcName As String
Dim ProcType As Long
Dim CodePosition As Long
Dim CodeToInsert As String
Const ModuleName = "Sample_Module"
On Error GoTo AddLogging_Error
Set VBCodeMod =
ThisWorkbook.VBProject.VBComponents(ModuleName).CodeModule
With VBCodeMod
'A position in the procedure where the code will be inserted
StartLine = .CountOfDeclarationLines + 1
'Looping through all procedures in a module
Do Until StartLine >= .CountOfLines
ProcName = .ProcOfLine(StartLine, ProcType)
CodePosition = .ProcStartLine(ProcName, ProcType) +
..ProcCountLines(ProcName, ProcType) - 1
'The code to be inserted before the end of every procedure
CodeToInsert = _
Chr(39) & "Logging code inserted here automatically on " & Date
& " " & Time & Chr(13) _
& "If LogEverything = True Then" & Chr(13) _
& "call WriteLog(" _
& Chr(34) _
& "Module: " & ModuleName _
& " - > Procedure: " & ProcName _
& Chr(34) & ")" & Chr(13) _
& "End If" & Chr(13)
'Insert the above code
.InsertLines CodePosition, CodeToInsert
'Move to the next procedure
StartLine = StartLine + _
.ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), ProcType)
Loop
End With
On Error GoTo 0
Exit Sub
'Errors handled here
AddLogging_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure AddLogging of Module max"
End Sub
As a result if you originally had a sub like that:
Public Sub SampleSub()
MsgBox ("Hi!")
End Sub
After the AddLogging is executed, it looks like that:
Public Sub SampleSub()
MsgBox ("Hi!")
'Logging code inserted here automatically on 18/10/2006 14:01:07
If LogEverything = True Then
Call WriteLog("Module: Sample_Module - > Procedure: SampleSub")
End If
End Sub
Now, everything is fine but for the one point which I still don't
know how to tackle:
If a target procedure has some error handling in the end, as almost any
should, it will spoil everything.
Alternatively I could insert the code in the beginning of any
procedure, beginning given by "ProcBodyLine" property. However if
the procedure declaration is something like that:
Public Sub Ini(ByVal strDir As String, _
ByVal strMonthEnd As String, _
Optional ByVal bRCMWithFX As Boolean = False, _
Optional ByVal bRCMWithTruncatedRepaySched As Boolean = True, _
Optional ByVal bRCMWithGroupStructure As Boolean = False _
)
I again have troubles since my code would split the sub declaration.
And I don't know how to identify the line with the last ")"...
