VBA - Put the currently running module/sub name when running code in it

[Ecniv]

Hi,

A colleague (who is even more lazy than me) wants to write a generic Error handler for his db/excel code.

Only he is so lazy he doesn't want to hard code if possible.

Example Code:

Public sub Blah()
    On error goto ErrH:

'---- other code

ErrH:
    ErrorHandlerSub <thissub>

End Sub

Public Sub ErrorHandlerSub(byval strSubThatErrored as string)
   debug.print "Error : " & err.number & " - " & err.description
   debug.print "Errored in : " & strSubThatErrored
End Sub

I haven't come across it, and I personally would hardcode it. But it was just a thought if there was a way to retrieve the sub name etc whilst running.

[salvelinus]

I saw here a while back that for Excel you can use Application.Caller. Doesn't work in Access, though.

[RobDog888]

You may want to check out .LastDLLError and .Source of the Err object, depending on how your project is structured.

[JustinLabenne]

How about a central error handling method?

In a standard module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
'         CENTRAL ERROR HANDLING SYSTEM:            '
'   Log all errors to a log file which tracks the   '
'   logged in user, the code module the error came  '
'   from, the procedure, and the date and time      '
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
Option Explicit
 
'Name of our error log
Private Const msAPP_ERRLOG As String = "\Error.log"
 
 
Public Function bCentralErrorHandler(ByVal sModule As String, ByVal sSub As String) As Boolean
    Dim sFullSource As String
    Dim sDescription As String
    Dim iFileNum As Integer
    Dim sPath As String
    
    sPath = ThisWorkbook.Path
    
'   Make sure there's no errors in the Error Handling system
    On Error Resume Next
    iFileNum = FreeFile()
    
'   Make sure the log path is valid
    If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
 
'   Get the file the error came from, the module, and a procedure description
    sFullSource = "Module: " & sModule & "  Procedure: " & sSub & " "
    
    Select Case Err.Number
        Case Is <> 0
            sDescription = Err.Description
        Case Else
            sDescription = "Minor Error"
    End Select
                      
'   Log the error
    Open sPath & msAPP_ERRLOG For Append As #iFileNum
    Print #iFileNum, Format$(Now(), "mm/dd/yy hh:mm AM/PM")
    Print #iFileNum, "******************************"
    Print #iFileNum, Application.UserName
    Print #iFileNum, sFullSource
    Print #iFileNum, sDescription
    Print #iFileNum, "******************************"
    Print #iFileNum, " "
    Close #iFileNum
    
    Err.Clear
'   Reset the error handler
    bCentralErrorHandler = False
End Function

Then just add this to every error handler necessary:

ErrHandle:
    If bCentralErrorHandler("Module Name", "Sub's Name") Then
    End If
End Sub

I hard code each module name in a constant string at the top of each module,
and then use a constant within each procedure which contains the procedures name.

This logs all errors to a log file stored in the same path as the Excel file.
Hope it helps out with some ideas...

[Ecniv]

Justin:
Yeah thats what I would have done, but you then need to 'hardcode' where it is coming from.

The Thread is to see if there is a way of identifying the sub as it is running, not hard coding it.

I am guessing that there isn't a way, or that it is very tricky if there is.

RogDogg:
Thanks, I will check and see if it is useful, but in normal errors I do not think that the err.lastDllError is set :/

[RobDog888]

Correct, Vince thats why I CMA with the "depending how your program is structured". Although if you were to make a dll out
of your code then it would be populated with error info.