CODEWITHSUNDEEP
vbaexcelerror-handlingskip
Wizhi
Wizhi

Reputation: 6549

Skip code if no error occurs by using an "if statement" in VBA

I need some input, since I'm quite new to VBA.

I have a part of a code that is looking for a file in three different path location. The code tries path 1, if error, it will continue to the next path (that's perfect)

My problem is, if the file path is working for code "test 2" and "test 3", It will always run until last one (i.e. "test 3") instead of jumping to next part of code. If the location path works for example for test 1 or test 2 location, I don't need to run the following lines after. How can I make my code skip that part?

'Test 1 location path
   On Error GoTo Err1:
     sFldr = "Path1"
Err1:
     Resume Next

'Test 2 location path
     On Error GoTo Err2:
     sFldr = "Path2"
Err2:
     Resume Next

'Test 3 location path
   On Error GoTo Err3:
     sFldr = "Path3"
Err3:
     Resume Next

'next part of big code
more code here

Upvotes: 0

Views: 7559

Answers (3)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

One Procedure = One Error Handler.

Simple as that.

Make sure the error-handling subroutine only ever runs in an error state.

I can't really give you a more concrete answer than that, because the code you're showing us does literally nothing; assigning a string literal to a string variable is never going to raise an error, ...and a workflow that jumps around up and down from one line to another in the "normal" execution path isn't sane - you need to restructure things. I'd love to help, but I've no idea what your code does.

To put it shortly, you should have small procedures that look like this:

Private Sub DoSomething()
    On Error GoTo CleanFail

    'procedure code here

CleanExit:
    'cleanup code here
    Exit Sub

CleanFail:
    'error-handling code here
    Resume CleanExit
End Sub

Upvotes: 3

Comintern
Comintern

Reputation: 22195

If I understand correctly, you need to progressively try values for (in this contrived example) sFldr, and the operation might error. Instead of using On Error Goto, just test the Err object for an error directly:

On Error Resume Next
sFldr = "Path1"
If Err.Number <> 0 Then
    Err.Clear
    sFldr = "Path2"
    If Err.Number <> 0 Then
        Err.Clear
        sFldr = "Path3"
    End If
End If
On Error GoTo 0 'Or resume your normal error handling.
'next part of big code

Call it like this:

If GetFldrValue = vbNullString Then
    'whatever you need to do if all 3 fail.
End If
'next part of big code

Another option is to extract the whole thing into its own function (which might not be a bad idea - the comment "next part of big code" indicates that the routine might be doing too much). If you do that, you can just turn error handling off entirely and return the first valid value found:

Function GetFolderValue() As String
    On Error Resume Next
    GetFolderValue = "Path1"
    If Err.Number <> 0 Then Exit Function
    GetFolderValue = "Path2"
    If Err.Number <> 0 Then Exit Function
    GetFolderValue = "Path3"
End Function

Upvotes: 3

Y C
Y C

Reputation: 85

Option 1: Wrap in Function It would be best to wrap this in a function that is responsible for retrieving the value of sFldr.

Option 2: GoTo Statement - not recommended Perhaps add a GoTo if the value of sFldr is not null

'Test 1 location path
   On Error GoTo Err1:
     sFldr = "Path1"
     If(sFldr <> "") Then Goto ContinueFunc
Err1:
     Resume Next

'Test 2 location path
     On Error GoTo Err2:
     sFldr = "Path2"
     If(sFldr <> "") Then Goto ContinueFunc

Upvotes: 1

Related Questions