Excel VBA - industrialisation...Part Six "Error Handling"

A long standing complaint about Excel VBA has been the lack of error handling.  The error handling in other language is indeed more advanced - the Java "try-catch-finally", the similar C# "try-catch-finally" or the C++ "try-catch" are all more advanced.
Excel VBA instead uses a format like this:

Sub CodeWithError()
'Declarations

On Error Goto ErrHandler

'Code

Exit Sub

ErrHandler:
'Code to handle the error found in the code

End Sub

This works, but it's not easy to manage.  The use of "Goto" is generally deprecated and will catch every error that occurs.  This means that the ErrHandler code has to be able to handle every error that may occur in the code.  For a long piece of code with many possible errors it becomes burdensome to actually manage the code that does the work and then the code that handles errors.  It's a platform limitation.

But there is a way to try and track down errors that's poorly documented by Microsoft but which is very helpful. That's the Erl property.

Look at the F2 object browser in the VBA development environment

Now, right click in the box entitled "Members of 'Information'" and then in the context menu select "Show Hidden Members"

Now we can see Erl...

To demonstrate a use-case for this hidden and badly documented feature you can follow these steps:

1. Download the code from github
2. Create a new Excel workbook.
3. Save the new workbook as an xlsm format workbook
4. Enter the VBA editor
5. Import the module you just downloaded from github
6. Execute the "TestNumbers" method
7. Examine the debug window content
8. Execute the "TestNumbers2" method
9. Examine the debug window content

In the code the following line appears in "TestNumbers":
lngBrokenAnswer = clngNumerator / clngDenominator

And with an added line number the same code appears in "TestNumbers2"
6   lngBrokenAnswer = clngNumerator / clngDenominator
Since clngNumerator  = 100 and clngDenominator = 0 we will clearly see a division by zero error raised.

Look in the debug window after executing the steps listed above and you will see this:

TestNumbers: Testing error handling
[TestNumbers]Number=11
Description=Division by zero
TestNumbers2: Testing error handling
[TestNumbers2]Number=11
Description=Division by zero
LineOfCode=6

The message "LineOfCode=6" clearly tells us where to look for the error.

Combine this with a method for the spreadsheet encountering the error to "phone home" and report to IT that there has been a problem and you can see the power of this form of error handling.

See also
Excel industrialisation...Part One "Code to write code"    
Excel industrialisation...Part Two "Export VBA code to file system"
Excel industrialisation...Part Three "What is Excel Industrialisation?"
Excel industrialisation...Part Four "Enumerate Addin references"
Excel industrialisation...Part Five "Integration with Git"
Excel industrialisation...Part Six "Error Handling"
Excel industrialisation...Part Seven "Central Error Reporting"
Excel industrialisation...Part Eight "Enumerating RTD Servers"
Excel industrialisation...Part Nine "Enumerating DDE Servers"  
Excel industrialisation...Part Ten "Enumerating COMAddins"