It’s nice when answering a question reveals an idea you had not noticed before, in this case, the last of the three demo macros I did
a few posts up, Sub MaybeSomethingLikeYasserWants() , revealed for me a slightly more compact way to do something….
While it’s fresh in my mind, I thought it would be a useful contribution to re explain that a bit better for future reference….
first, to refresh what we often see already...
A very typical way seen of doing error handling in VBA
A very common way to handle errors for macros of that form is to have error handling code section, often towards the bottom. That’s OK, nice and well organised. Just as example, something like this following macro might be the more typical way seen currently, and it is very similar to my second demo macro of the three demo macros I did
a few posts up
In this and the next example I changes the demo data slightly so that I loop through an array with this data in it ,
arrNumbers(1 To 5) = {1, 0, "a", 0, 1 }
, and I try to divide 1 by each element r from the array. So I will have error with
arrNumbers(2) and
arrNumbers(3) and
arrNumbers(4) because
1/1 - OK
1/0 – Error, divide by zero
1/"a" – Error , type mismatch
1/0 – Error, divide by zero
1/1 - OK
So this is like a common way to error handle that sort of thing:
Code: Select all
Sub ACommonErrorHandling() '
Dim arrNumbers(1 To 5) As String, Cnt As Long, Nmber As Double
Let arrNumbers(1) = 1: Let arrNumbers(2) = 0: Let arrNumbers(3) = "a": Let arrNumbers(4) = 0: Let arrNumbers(5) = 1
For Cnt = 1 To 5
'
'' Other stuff not expected to error
'
On Error GoTo Bed ' Get ready for stuff we might expect to error
Let Nmber = 1 / arrNumbers(Cnt)
On Error GoTo 0 ' Finished with custon error handling, so best go back to normal default error handling
'
'' Other stuff not expected to error
'
Next Cnt
Exit Sub
Bed: ' Here I am in the parallel dark net world in the glorius State of Exception where my coding is cloned like a zombie that walks around ignoring all error handlers
Debug.Print Err.Description & " at count " & Cnt
Resume Next ' This both brings me back to the real world and takes me to the line after where the error occured
End Sub
So that is perfectly OK,
But here is a slightly more compact form. A couple of examples, almost identical.
Code: Select all
Sub MaybeSomethingLikeYasserWants2() ' https://eileenslounge.com/viewtopic.php?p=305638#p305638
Dim arrNumbers(1 To 5) As String, Cnt As Long, Nmber As Long
Let arrNumbers(1) = 1: Let arrNumbers(2) = 0: Let arrNumbers(3) = "a": Let arrNumbers(4) = 0: Let arrNumbers(5) = 1
For Cnt = 1 To 5
'
'' Other stuff not expected to error
'
On Error GoTo Bed ' Get ready for stuff we might expect to error
Let Nmber = 1 / arrNumbers(Cnt)
Bed: If Err Then: Debug.Print Err.Description & " at count " & Cnt: On Error GoTo -1
On Error GoTo 0 ' Finished with custom error handling, so best go back to normal default error handling
'
'' Other stuff not expected to error
'
Next Cnt
End Sub
Sub MaybeSomethingLikeYasserWants3()
Dim arrNumbers(1 To 5) As String, Cnt As Long, Nmber As Long
Let arrNumbers(1) = 1: Let arrNumbers(2) = 0: Let arrNumbers(3) = "a": Let arrNumbers(4) = 0: Let arrNumbers(5) = 1
For Cnt = 1 To 5
'
'' Other stuff not expected to error
'
On Error Resume Next ' Get ready for stuff we might expect to error
Let Nmber = 1 / arrNumbers(Cnt)
If Err Then: Debug.Print Err.Description & " at count " & Cnt: On Error GoTo -1
On Error GoTo 0 ' Finished with custom error handling, so best go back to normal default error handling
'
'' Other stuff not expected to error
'
Next Cnt
End Sub
Just for future reference a more detailed explanation of what is happening in the last two macros
These next code bits are just demo-ing the usual good practice of not using the custom error handling unless we think we may need it.
So we only start it before the code line that we think ,may error,
'
'' Other stuff not expected to error
'
On Error ……………. ' Get ready for stuff we might expect to error
, and then also, we typically get rid of it after, going back to the default error handling using the
On Error GoTo 0 thing,
On Error GoTo 0 ' Finished with custom error handling, so best go back to normal default error handling
'
'' Other stuff not expected to error
'
So that is nothing new
Now the new compact stuff:
_... in the next post…._