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". :D Although if you were to make a dll out
of your code then it would be populated with error info. ;)