CODEWITHSUNDEEP
excelvba
Repairer
Repairer

Reputation: 135

Determine if all lines were faulty or some were correct--error handling in excel vba

I have a structure like as below, for error handling.

    On error resume next
    Statement1
    Statement2
    Statement3
    On error goto 0

I need to determine if all of the 3 statements were faulty or at least one of them was correct. Thanks in advance for any help.

Upvotes: 1

Views: 61

Answers (2)

Brian M Stafford
Brian M Stafford

Reputation: 8868

As with any problem, there are many ways it can be solved. Sticking with the error handling idea, you could structure the code like this:

Private Sub Test()
   On Error Resume Next
   
   Dim atLeastOne As Integer
   
   Err.Clear
   Debug.Print 1 / 0
   If Err.Number = 0 Then atLeastOne = atLeastOne + 1

   Err.Clear
   Debug.Print 1 / 1
   If Err.Number = 0 Then atLeastOne = atLeastOne + 1

   Err.Clear
   Debug.Print 1 / 0
   If Err.Number = 0 Then atLeastOne = atLeastOne + 1
   
   MsgBox atLeastOne
End Sub

Upvotes: 2

TerrifiedJelly
TerrifiedJelly

Reputation: 135

You can make a bypass section (Error Handling) so that when it errors, it'll action that specific chunk of code and then continue where you were.

Sub Test
dim E1 as boolean
dim E2 as boolean
dim E3 as boolean

[any other code here]

On Error goto Err1Fail
Statement1
ResumeErr1Fail:
On Error Goto Err2Fail
Statement2
ResumeErr2Fail:
On Error Goto Err3Fail
Statement3
ResumeErr3Fail:
On Error Goto 0

[rest of code]

Exit Sub

'Error Handling Section
Err1Fail:
E1 = TRUE
resume ResumeErr1Fail
exit sub

Err2Fail:
E2 = TRUE
resume ResumeErr2Fail
exit sub

Err3Fail:
E3 = TRUE
resume ResumeErr3Fail
exit sub

End Sub

In the [rest of the code] section, you can then evaluate E1, E2 and E3 to do whatever you need to with them.

Upvotes: 3

Related Questions