Hi Mandy Meng and HarveyFrench, To clarify my previous post, the following line from HarveyFrench's code:

will not work unless line numbers have been added to every line of code. Rather than manually typing line numbers, which is way too tedious, you can use a tool to automatically add the line numbers. There are a few tools out there that can do this, I use one called CodeLiner. So, here is the code with line numbers, which will allow Erl() to work successfully:

To clarify my previous post, the following line from HarveyFrench's code:

will not work unless line numbers have been added to every line of code. Rather than manually typing line numbers, which is way too tedious, you can use a tool to automatically add the line numbers. There are a few tools out there that can do this, I use one called CodeLiner.

Here is the code with line numbers, which will allow Erl() to work successfully:

Regarding the Erl function - this can be used pretty effectively if you can toggle the line numbers on and off on-demand. There are tools out there that will toggle code line numbers e.g. codeliner

Hi Mandy Meng and HarveyFrench, To clarify my previous post, the following line from HarveyFrench's code:

RememberErrLine = Erl()

will not work unless line numbers have been added to every line of code. Rather than manually typing line numbers, which is way too tedious, you can use a tool to automatically add the line numbers. There are a few tools out there that can do this, I use one called CodeLiner. So, here is the code with line numbers, which will allow Erl() to work successfully:

Option Compare Database
Option Explicit

Dim RememberErrNumber As Long
Dim RememberErrDescription As String
Dim RememberErrSource As String
Dim RememberErrLine  As Integer

Private Sub RememberThenClearTheErrorObject()
10
11     On Error Resume Next
12
    ' For demo purposes
14     Debug.Print "ERROR RAISED"
15     Debug.Print Err.Number
16     Debug.Print Err.Description
17     Debug.Print Err.Source
18     Debug.Print " "
19
20
    ' This function has to be declared in the same scope as the variables it refers to
22     RememberErrNumber = Err.Number
23     RememberErrDescription = Err.Description
24     RememberErrSource = Err.Source
25     RememberErrLine = Erl()
26
    ' Note that the next line will reset the error object to 0, the variables above are used to remember the values
    ' so that the same error can be re-raised
29     Err.Clear
30
    ' Err.Clear  is used to clear the raised exception and set the err object to nothing (ie err.number to 0)
    ' If Err.Clear has not be used, then the next "On Error GoTo ALabel" that is used in this or the procedure that called it
    ' will actually NOT pass execution to the ALabel: label BUT the error is paseed to the procedure that called this procedure.
    ' Using Err.Clear (or "On Error GoTo -1 ")  gets around this and facilitates the whole TRY CATCH block scenario I am using there.
35
36
    ' For demo purposes
38     Debug.Print "ERROR RAISED is now 0 "
39     Debug.Print Err.Number
40     Debug.Print Err.Description
41     Debug.Print Err.Source
42     Debug.Print " "
43
    ' For demo purposes
45     Debug.Print "REMEMBERED AS"
46     Debug.Print RememberErrNumber
47     Debug.Print RememberErrDescription
48     Debug.Print RememberErrSource
49     Debug.Print " "
50
End Sub

Private Sub ClearRememberedErrorObjectValues()
54
    ' This function has to be declared in the same scope as the variables it refers to
56     RememberErrNumber = 0
57     RememberErrDescription = ""
58     RememberErrSource = ""
59     RememberErrLine = 0
60
End Sub




Sub ExampleOfTryCatchBlockInVBA()
67
68     On Error GoTo HandleError
69
70
    ' -----------------------------------------------------
    ' SubProcedure1 has the example of a multiple line TRY block with a block of code executed in the event of an error
73
74     SubProcedure1
75
76
77
78 Exit Sub
79 HandleError:
80
81     Select Case Err.Number
82         Case 0
            ' This shold never happen as this code is an error handler!
            ' However if it does still allow the Err.raise to execute below. (In this case Err.raise will fail
            ' and itself will raise an error "Invalid procedure call or argument" indicating that 0 cannot be used to raise and error!
86
87         Case 111111
            ' You might want to do special error handling for some predicted error numbers
            ' perhaps resulting in a exit sub with no error or
            ' perhaps using the Err.raise below
91
92          Case Else
            ' Just the Err.raise below is used for all other errors
94
95     End Select
96
    '
    ' I include the procedure ManageErrSource  as an exmple of how Err.Source can be used to maintain a call stack of procedure names
    ' and store the name of the procedure that FIRST raised the error.
    '
101     Err.Raise Err.Number _
            , ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
            , Err.Number & "-" & Err.Description
104
    ' Note the next line never gets excuted, but I like to have resume in the code for when I am debugging.
    ' (When a break is active, by moving the next executable line onto it, and using step over, it moves the exection point to the line that actually raised the error)
107     Resume
108
End Sub

Sub SubProcedure1()
112
    ' -----------------------------------------------------
    ' Example of a multiple line TRY block with a Case statement used to CATCH the error
115
    '
    ' It is sometimes better to NOT use this technique but to put the code in it's own procedure
    ' (ie I refer to the code below that is surrounded by the tag #OWNSUB) .
    ' However,sometimes using this technique makes code more readable or simpler!
    '
121
122     Dim i As Integer
123
' This line puts in place the defualt error handler found at the very foot of the procedure
125 On Error GoTo HandleError
126
127
    '
    ' Perhaps lots of statements and code here
    '
131
132
    ' First an example with comments
134
135
    ' -----------------------------------------------------
    ' TRY BLOCK START
138
        ' This next line causes execution to "jump" to the "catch" block in the event an error is detected.
140 On Error GoTo CatchBlock1_Start
141
        ' #OWNSUB
143
144         tsub_WillNotRaiseError_JustPrintsOk
145
146         If vbYes = MsgBox("1. Do you want to raise an error in the try block? - (PRESS CTRL+BREAK now then choose YES, try no later.)", vbYesNo) Then
147             i = 100 / 0
148         End If
149
        '
        ' Perhaps lots of statements and code here
        '
153
        ' #OWNSUB
155
    ' TRY BLOCK END
    ' -----------------------------------------------------
158
159
    ' -----------------------------------------------------
    ' CATCH BLOCK START
162 CatchBlock1_Start:
163
164     If Err.Number = 0 Then
165         On Error GoTo HandleError
        ' Re-instates the procedure's generic error handler
        ' This is also done later, but I think putting it here reduces the likelyhood of a coder accidentally removing it.
168
169     Else
170
        ' WARNING: BE VERY CAREFUL with any code that is written here as
        ' the "On Error GoTo CatchBlock1_Start" is still in effect and therefore any errors that get raised could goto this label
        ' and cause and infinite loop.
        ' NOTE that a replacement "On Error Goto" cannot be executed until Err.clear is used, otherwise the "On Error Goto"
        ' will itself raise and error.
        ' THEREFORE KEEP THE CODE HERE VERY SIMPLE!
        ' RememberThenClearTheErrorObject should be the only code executed and this called procedure must be tight!
178
        ' This saves the details of the error in variables so that the "On Error GoTo HandleError" can be used
        ' to determine how the next Err.Raise used below is handled (and also how any unexpected implicitly raised errors are handled)
181         RememberThenClearTheErrorObject
182
183         On Error GoTo HandleError   '#THISLINE#
184
185         If vbYes = MsgBox("2. Do you want to raise an error in the erro handler? - (PRESS CTRL+BREAK now then try both YES and NO )", vbYesNo) Then
186             i = 100 / 0
187         End If
188
189         Select Case RememberErrNumber
190             Case 0:  ' No Error, do Nothing
191
192             Case 2517
193                 Debug.Print "The coder has decided to just give a Warning: Procedure not found " & Err.Number & " - " & Err.Description
194                 ClearRememberedErrorObjectValues ' Not essential, but might save confusion if coding errors are made
195
196             Case Else
                ' An unexepected error or perhaps an (user) error that needs re-raising occurred and should to be re-raised
198
                ' NOTE this is giving an example of what woudl happen if the CatchBlock1_ErrorElse is not used below
200                 If vbYes = MsgBox("3. Do you want to raise an error in the ELSE error handler? CatchBlock1_ErrorElse *HAS NOT*  been used? - (PRESS CTRL+BREAK now then try both YES and NO )", vbYesNo) Then
201                     i = 100 / 0
202                 End If
203
204      On Error GoTo CatchBlock1_ErrorElse
205
206
                ' SOME COMPLEX ERROR HANDLING CODE - typically error logging, email, text file, messages etc..
                ' Because the error objects values have been stored in variables, you can use
                ' code here that might itself raise an error and CHANGE the values of the error object.
                ' You might want to surround the code with the commented out CatchBlock1_ErrorElse lines
                ' to ignore these errors and raise the remembered error.  (or if calling a error handling module
                ' just use on error resume next).
                ' Without the CatchBlock1_ErrorElse lines any error raised in this "complex code" will be handled by the
                ' active error handler which was set by the "On Error GoTo HandleError" tagged as '#THISLINE#" above.
215
216                 If vbYes = MsgBox("4. Do you want to raise an error in the ELSE error handler when CatchBlock1_ErrorElse   HAS  been used? - (PRESS CTRL+BREAK now then try both YES and NO )", vbYesNo) Then
217                     i = 100 / 0
218                 End If
219
220 CatchBlock1_ErrorElse:
221      On Error GoTo HandleError
                ' This line must be preceeded by an new "On error goto" for obvious reasons
223                 Err.Raise RememberErrNumber, RememberErrSource, RememberErrDescription
224
225         End Select
226
227         On Error GoTo HandleError
228
229     End If
    ' CATCH BLOCK END
    ' -----------------------------------------------------
232 On Error GoTo HandleError  ' Unnecessary but used to delimt the catch block
233
234
235
236
'
' lots of code here perhaps
'
240
241
242
243
    ' -----------------------------------------------------
    ' Example 2
    '
    ' In this example goto statements are used instead of the IF statement used in example 1
    ' and no explanitory comments are given (so you can see how simple it can look)
    '
250
    ' -----------------------------------------------------
    ' TRY BLOCK START
253
254 On Error GoTo CatchBlock2_Start
255
256         tsub_WillNotRaiseError_JustPrintsOk
257
258         If vbYes = MsgBox("Do you want to raise an error? - (PRESS CTRL+BREAK now then choose YES)", vbYesNo) Then
259             i = 100 / 0
260         End If
261
        '
        ' Perhaps lots of statements and code here
        '
265
    ' TRY BLOCK END
    ' -----------------------------------------------------
268
269
270 GoTo CatchBlock2_End:
271 CatchBlock2_Start:
272
273         RememberThenClearTheErrorObject
274
275         On Error GoTo HandleError
276
277         Select Case RememberErrNumber
278             Case 0:  ' No Error, do Nothing
279
280             Case 2517
281                 Debug.Print "The coder has decided to just give a Warning: Procedure not found " & Err.Number & " - " & Err.Description
282                 ClearRememberedErrorObjectValues ' Not essential, but might save confusion if coding errors are made
283
284             Case Else
                ' An unexepected error or perhaps an (user) error that needs re-raising occurred and should to be re-raised
                ' In this case the unexpecetd erro will be handled by teh code that called this procedure
                ' This line must be preceeded by an new "On error goto" for obvious reasons
288                 Err.Raise RememberErrNumber, RememberErrSource, RememberErrDescription
289
290         End Select
291
292         On Error GoTo HandleError
293
294     End If
295
296 CatchBlock2_End:
    ' CATCH BLOCK END
    ' -----------------------------------------------------
299 On Error GoTo HandleError  ' Unnecessary but used to delimt the catch block
300
301
302
303
'
' Here you could add lots of lines of vba statements that use the generic error handling that is after the HandleError: label
'
'
308
'
' You could of course, alway add more TRY CATCH blocks like the above
'
'
313
314
315
316 Exit Sub
317 HandleError:
318
319     Select Case Err.Number
320         Case 0
            ' This shold never happen as this code isan error handler!
            ' However if it does still allow the Err.raise to execute below. (In this case Err.raise will fail
            ' and itself will raise an error "Invalid procedure call or argument" indicating that 0 cannot be used to raise and error!
324
325         Case 111111
            ' You might watch to do special error handling for some predicted error numbers
            ' perhaps exit sub
            ' Perhaps using the Err.raise below
329     End Select
330
    ' ie Otherwise
    '
    ' Note that I use the Err.Source to maintain a call stack of procedure names
    '
335     Err.Raise Err.Number _
            , ManageErrSource("MyModuleName", Err.Source, Erl(), "tsub1_RaisesProcedureNotFoundError") _
            , Err.Number & "-" & Err.Description
338
    ' Note the next line never gets excuted, but I like to have resume in the code for when I am debugging.
    ' (By moving the next executable line onto it, and using step over, it moves the exection point to the line that actually raised the error)
341     Resume
342
End Sub



Sub tsub_WillNotRaiseError_JustPrintsOk()
348
349     Static i As Integer
350
351     i = i + 1
352
353     Debug.Print "OK " & i
354
End Sub



Public Function ManageErrSource(MyClassName As String, ErrSource As String, ErrLine As Integer, ProcedureName As String) As String
360
    ' This function would normally be in a global error handling module
362
    ' On Error GoTo err_ManageErrSource
364
365     Const cnstblnRecordCallStack  As Boolean = True
366
367     Select Case ErrSource
368
369         Case Application.VBE.ActiveVBProject.Name
370
            ' Err.Source is set to this value when a VB statement raises and error. eg In Access by defualt it is set to "Database"
372
373             ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
374
375         Case ""
376
            ' When writing code ouside of the error handling code, the coder can raise an error explicitly, often using a user error number.
            ' ie by using err.raise MyUserErrorNumber, "", "My Error descirption".
            ' The error raised by the coder will be handled by an error handler (typically at the foot of a procedure where it was raised), and
            ' it is this handler that calls the ManageErrSource function changing the Err.Source from "" to a meaningful value.
381
382             ManageErrSource = Application.VBE.ActiveVBProject.Name & " " & MyClassName & "." & ProcedureName & ":" & ErrLine
383
384         Case Else
385
            ' This code is executed when ManageErrSource has already been called.  The Err.Source will already have been set to hold the
            ' Details of where the error occurred.
            ' This option can be used to show the call stack, ie the names of the procdures that resulted in the prcedure with the error being called.
389
390             If cnstblnRecordCallStack Then
391
392                 If InStr(1, ErrSource, ";") = 0 Then
393                     ManageErrSource = ErrSource & ":: Called By: "
394                 End If
395                 ManageErrSource = ErrSource & ";" & ProcedureName & ":" & ErrLine
396
397             Else
398                 ManageErrSource = ErrSource
399
400             End If
401
402     End Select
403
404 Exit Function
405 err_ManageErrSource:
406     Err.Raise Err.Number, "MyModuleName.err_ManageErrSource", Err.Description
407     Resume
408
End Function