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

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:

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.

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

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

How about a central error handling method?

In a standard module:

VB Code:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
'         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:

VB Code:

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...

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 :/

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