ExcelBanter - Logging what procedures are executed

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).Co deModule
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 _
& " - Procedu " & 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 - Procedu 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 ")"...

NickHK wrote:
To make it some what easier, you could edit the Error handler text in MZ
Tools "Add Error Handler", to reflect your Logging code instead.
You still have go to each routine, but may be quicker.

www.mztools.com

NickHK

"MaxS" wrote in message
oups.com...
Thanks Tushar,

I guess I will have to write some code myself which would atomatically
add some debugging information to each procedure, indicating procedure
name and module....

Tushar Mehta wrote:
AFAIK, there's no XL/VBA native mechanism to do what you want. Maybe,
someone (don't ask me for a reference since I don't have one) has a
profiler
that you can use. Of course, if you find one it might be a priced item.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article .com,
says...
Hi all,

the following problem:

I need to understand a relatively large chunk of VBA code written by
someone else. The code cotains a bunch of modules, class modules and
around 300 procedures.
The comments are poor in many places.

Question:
Is there a way to log/debug.print all the procedures which are
executed
and their order without manually adding my own code to each procedure.
Is it possible to raise an event which would debug.print procedure
name
every time a new one is called?

Thanks in advance!

MaxS