CODEWITHSUNDEEP
vbaexcel
Jain
Jain

Reputation: 999

Putting Labels and Error Handler in the right place

I am using an error handler in Vba and want to use in Error Handler in my code as suggested by many experts over here.

Sub test()

For i =1 to 100

On Error GoTo ErrHand:
Filename=Dir()
Folder=
Workbooks.open(Folder & Filename)

Label1:
Code   
Code 
Close the file

ErrHand:
Application.GetOpenFilename()
GoTo Label1

Next i

End Sub

I am finding difficulties in running the code in a normal way. I try to pen a file and if it fails, I call Error Handler and throw a prompt to select a file and then I close the file and do the same thing for next files. One difficulty I am facing is that the opened file never closes. Thanks in advance.

Upvotes: 0

Views: 83

Answers (1)

FreeMan
FreeMan

Reputation: 5687

In addition to Jeeped's excellent suggestion:

Sub test()

On Error GoTo ErrHand:
For i =1 to 100
  Filename=Dir()
  set Folder= Workbooks.open(Folder & Filename)
  'Label1:
  Code   
  Code 
  folder.Close  'based on using 'folder', above
next

exit sub 'if you don't do this, code execution will go right into your error handler

ErrHand:
if err.Number = <something>
  Application.GetOpenFilename()
  'GoTo Label1
  Resume Next 'this is a simpler version, and doesn't require a label and another GoTo
              'skips the line of code that errored, continues with the next line
elseif err.number = <something else>
   '...
   Resume 'allows you to retry the same statement after making some changes
endif

'Next i

End Sub

Upvotes: 2

Related Questions