A number of objects may have extra properties or something where it might not exist for all instances. A good example might be finding Controlsource property on an Access.Control variable:
|
1 2 3 |
For Each ctl In Me.Controls Debug.Print ctl.ControlSource 'A potential error Next |
Not all controls have a ControlSource property and thus can fail to run. A common approach is to use OERN:
|
1 2 3 4 5 |
For Each ctl In Me.Controls On Error Resume Next Debug.Print ctl.ControlSource 'A potential error On Error GoTo 0 Next |
This will work OK but this can significantly clutter the code, since we have to check whether there was an error. A more realistic example might look like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
For Each ctl In Me.Controls Dim src as String On Error Resume Next src = ctl.ControlSource 'A potential error If Err.Number Then src = vbNullString End If On Error GoTo 0 If Len(src) Then 'Do something End If Next |
As you see, we’ve added a OERN block, and 2 If blocks just to handle the cases where controls might not have a ControlSource. We can do better by extracting this into a function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Public Function TryGetControlSource( _ Target As Access.Control, _ OutControlSource As String _ ) As Boolean On Error Resume Next OutControlSource = Target.ControlSource If Err.Number Then OutControlSource = vbNullString TryGetControlSource = False Else TryGetControlSource = True End If On Error GoTo 0 End Function |
Your code then become:
|
1 2 3 4 5 6 |
For Each ctl In Me.Controls Dim src As String If TryGetControlSource(ctl, src) Then 'Do something End If Next |
Which is much more readable and easier to understand. The access to the src is contained within the If TryGetControlSource(...) Then so it’s easy to understand it’s only meant to be used when it’s true.