How to Use Error Handling to Skip Loop Iterations

Question

I have a for loop where near the top I expect to throw an error trying to set a variable equal to a workbook. The error is returned because the workbook may or may not exist. The for loop loops over many workbooks and any number of them may or may not exist. the loop looks something like this

for i = 1 to x
     'get ready for to open the workbook

     set = myworkbook = workbooks.open("path\myworkbook" & date)

     'do a bunch of stuff after I get the workbook

next i

this works all fine and dandy, but if the workbook doesn't exist, then I get an error. Now I have tried a handful of error handling techniques. Ideally what happens is that if the workbook isn't found, it skips right down to the bottom of the loop and just goes into the next i.

I have used goto statements to just jump to the bottom of the line, but that only works once and on the second pass through when the workbook doesn't exist, that throws an error. I have tried err.Clear after the the goto line so that the new error can be caught. I have tried goto -1 to clear the error but that doesn't work either. I have tried a variety of resume next statements but if the error doesn't occur because the workbooks are found, then that throws an error which I can't seem to handle. I check the error number before it starts the error handling routine wrapping the variable setting line to make sure that it shows 0 but it still throws the error.

There seem to be a lot of methods to solve this problem and I think I've tried them all. This isn't a unique problem I'm certain and I just need a push in the right direction. Thanks for any help you guys might provide.

Just so you don't think I haven't tried whatever solution you think of first, here are some other questions I've read:

For Loop, how to skip iterations

Difference between 'on error goto 0' and 'on error goto -1' -- VBA

On Error Goto 0 not resetting error trapping

Access VBA: Is it possible to reset error handling

Continue For loop

Error handling only works once

Why not use For each ws in myworkbook.Worksheets see SO Excel documentation topic — Siyon DP
– Siyon DP, Commented Jul 26, 2017 at 13:50
These are separate workbooks, not worksheets within a workbook. They all have a unique file name and path that is differentiated by the date. Notice the file name includes the date. — Dan White
– Dan White, Commented Jul 26, 2017 at 15:29

Brian M Stafford · Accepted Answer · 2017-07-26 13:51:14Z

4

Why not something like this:

Public Sub SkipLoop()
   On Error Resume Next

   Dim i As Integer
   Dim wb As Workbook

   For i = 1 To 100
      Err.Clear
      Set wb = Workbooks.Open("some file")

      If Err.Number = 0 Then
         'do work on success
      End If
   Next
End Sub

answered Jul 26, 2017 at 13:51

Brian M Stafford

8,9432 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan White Over a year ago

This worked for the first pass through, but not the second. If there were two successive errors, then the second error would be raised but not the first. I used message boxes to make sure that Err.Clear had removed error 1004 but the error was still thrown.

Brian M Stafford Over a year ago

@Dan I'm not seeing this behavior with the above code.

Brandon Barney · Accepted Answer · 2017-07-26 14:01:50Z

2

No need to use error handling for something like this. Just use smart logic to your advantage:

Dim MyWorkbook as Workbook
for i = 1 to x
     'get ready for to open the workbook

     On Error Resume Next
     set myworkbook = workbooks.open("path\myworkbook" & date)
     On Error GoTo 0

    If Not MyWorkbook Is Nothing Then
        'do a bunch of stuff after I get the workbook
    End if
next i

What this does is checks for 'Nothingness' of the object variable. If the object isnt set (wasnt found) the code within the If block wont run.

edited Jul 26, 2017 at 14:01

answered Jul 26, 2017 at 13:53

Brandon Barney

2,3921 gold badge11 silver badges18 bronze badges

4 Comments

Brian M Stafford Over a year ago

You need to use error handling because if the file is not found an error is thrown.

Brandon Barney Over a year ago

I could've sworn it would return Nothing if the filepath isnt found, but wrapped it in an error skip just in case (since the error will be handled by the logic check).\

Dan White Over a year ago

I actually tried this as well, and I would get errors on the MyWorkbook Is Nothing check.

Brandon Barney Over a year ago

If MyWorkbook is declared properly there should be no way of getting an error.

Collectives™ on Stack Overflow

How to Use Error Handling to Skip Loop Iterations

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related