CODEWITHSUNDEEP
vbaexcel
Dan White
Dan White

Reputation: 631

How to Use Error Handling to Skip Loop Iterations

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

Upvotes: 1

Views: 9362

Answers (2)

Brandon Barney
Brandon Barney

Reputation: 2392

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.

Upvotes: 2

Brian M Stafford
Brian M Stafford

Reputation: 8868

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

Upvotes: 4

Related Questions