Return the Module Location & Sub Procedure Name on Error

[RyanH]

How can I return the Module Location and Sub Procedure of an error when an
error occurs?

Public Sub ProcedureName()

On Error GoTo ErrorHandler

' my code here
Exit Sub

'********************************

ErrorHandler:
Call ErrorHandler(ModuleName, ProcedureName, Err.Number, Err.Description)

End Sub

Public Sub ErrorHandler(ModuleName As String, ProcedureName As String,
ErrNumber As Integer, Description As String)

' show a msgbox with error information

End Sub

 

[Chip Pearson]

Unfortunately, there is no way to return the name of the currently
executing procedure or module. One way is to insert a constant in each
procedure that contains the name of the procedure and a constant
containign the name of the module. I have code at
http://www.cpearson.com/Excel/InsertProcedureNames.aspx that automates
this process and simplifies it to running one procedure for each
module.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[RyanH]

Thanks for the reply. I believe that will do what I need.

As a secondary question, what if in the procedure I expect an error how do I
get the code to ignore calling the error handler? For example,

Public Sub TEST()

On Error GoTo ErrorHandler

' I have additional code here

' delete all blank rows from cutting, if no blank rows error occurs
On Error Resume Next
.Range("A3:A" &
lngGlobalLastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

' I have additional code here

Exit Sub

' *********************
ErrorHandler:
Call ErrorHandler(ModuleName, SubName)

End Sub

 

[Jim Thomlinson]

I use this tool from MZ Tools to insert the procedure name and the module
name with the click of a button in the VBE.

http://www.mztools.com/index.aspx

My error handler uses roughly the same info as you are describing.

As for the second question you want to change

On Error GoTo 0 'default error treatment
to
On Error GoTo ErrorHandler 'your error handler...

 

[RyanH]

So VBA will make On Error Resume Next first priority over On Error GoTo
ErrorHandler in this example?

Sub Test

 

[Chip Pearson]

You can switch the error handling between modes freely as your code
exectutes. Typically, you would use

On Error Goto Label

most of the time, and then switch to

On Error Resume Next

for small blocks of code where an error is anticipated and recovery
can be made. Once that small block of code finishes, you can change
error handling back. For example,

Sub AAA()
On Error GoTo ErrHander:
''
' some code
''
On Error Resume Next
''
' code where an error is anticipated
' and recovery is possible.
''
On Error GoTo ErrHandler
''
' restore ErrHandler as the
' error handler.
''
Exit Sub
ErrHandler:
MsgBox "Error: " & Err.Description
End Sub

I have some notes about error handling at
http://www.cpearson.com/Excel/ErrorHandling.htm


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jim Thomlinson]

Your error handler is whatever you specified last. You can switch it as often
as you want during execution.

 

[Chip Pearson]

Within a procedure, no error handler has any "priority". The error
handling mode is simply the most recently executed On Error statement.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)