How to skip runtime error 9 vba?

Question

I make a macro to open many files and do some operations like copy and paste in final file. But I want when there is no file to skip the piece of code connected with this file:

'create variables'
FinalFile = "order.xls" 
Obj1 = "order-obj1.xls"
Obj1Range = "E11"
......
Windows(Obj1).Activate
Range(MyRange).Select
Selection.Copy
Windows(FinalFile).Activate
Range(Obj1Range).Select
ActiveSheet.Paste
Windows(Obj1).Activate
ActiveWindow.Close

Windows(Obj2).Activate
Range(MyRange).Select
Selection.Copy
Windows(FinalFile).Activate
Range(Obj2Range).Select
ActiveSheet.Paste
Windows(Obj2).Activate
ActiveWindow.Close

If I can't open some file I recieve run time error 9. So my question is how to skip the code for Obj1 and proceed to Obj2? I hope you can understand me...

Recommending the use of `On Error Resume Next` is a bad advice in general. Refrain from using OERN, unless you are very fond of surprises in your applications. — Pradeep Kumar, Jul 31 '14 at 08:01
I would argue whether is a bad advice or not @PradeepKumar. It really depends on the situation... when I see code using .Select .Activate etc I think the easiest way to help someone is to adjust the solution to their level of understanding the programming language (or rather recorded macro). [Maybe you find this discussion interesting](http://meta.stackoverflow.com/questions/261402/am-i-overkilling-questions-with-solutions-that-are-too-complex-for-the-op-to-und) — , Jul 31 '14 at 08:04
Irrespective of what that discussion topic contains, I won't ever recommend giving a bad (but working) solution to anyone without explaining to them what risk they face as a result of implementing that solution. Tailor made solutions that solve the specific problem are acceptable, but something as common as like use of OERN etc., I won't ever recommend. It would have been better if you explained how they would mess up their program if they start putting OERN on every next run-time error they encounter in their application. — Pradeep Kumar, Jul 31 '14 at 10:51
@PradeepKumar As per Me How's comment, OERN is a reasonable approach depending on the situation, and what you are testing for (for example whether a sheet exists). — brettdj, Aug 04 '14 at 02:05
WHOA! Question about OERN comes later. First is whether one should use this method! Please avoid the use of `.Select/.Activate`!!! And then one can use OERN to check if the sheet/workbook exists or not. But then use OERN with OEGT0. — Siddharth Rout, Aug 04 '14 at 10:31
http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179 — Siddharth Rout, Aug 04 '14 at 10:33
Use of OERN is more about "Error hiding" rather than "Error handling". While there could be genuine cases for use of OERN, you should avoid it wherever possible using defensive programming. Use of OERN will never make it into the best practices books for obvious reasons, and should not be recommended on public forums (except in special cases). In the worst case I would recommend proper error handling using the `On Error GoTo x` and `Resume` or `Resume Next` from the error handler block after examining the error, rather than a blind `On Error Resume Next`. — Pradeep Kumar, Aug 04 '14 at 10:55
@PradeepKumar: I am an offensive programmer myself but there are moments when I use OERN as defensive programming as I have to write less code and it is much faster :) See the answer that I posted below — Siddharth Rout, Aug 04 '14 at 10:57
@PradeepKumar: I also feel that Mehow wanted to say the same thing as what i mentioned in the first comment but seems like he didn't finish his sentence :) — Siddharth Rout, Aug 04 '14 at 11:06

score 2 · Accepted Answer · edited Jul 31 '14 at 10:35

2

Use the commmand Dir() to check whether the file exists.

e.g.

If Dir(Obj1) <> "" Then
    Windows(Obj1).Activate
    Range(MyRange).Select
    Selection.Copy
    Windows(FinalFile).Activate
    Range(Obj1Range).Select
    ActiveSheet.Paste
    Windows(Obj1).Activate
    ActiveWindow.Close
End If

Also, you probably want to put this code into a function so as not to repeat it, but that is another question.

edited Jul 31 '14 at 10:35

Pradeep Kumar

6,836
4
21
47

answered Jul 31 '14 at 07:55

Mark Nash

184
10

1

Good way of looking ahead instead of running in the error. – peter_the_oak Jul 31 '14 at 08:07
Thanks for advice..but when I start it with your code I recieve run-time error 13. Type mismatch... – KDimitrof Jul 31 '14 at 08:18
"I found it.. it should be `If Dir(Obj1) <> "" Then` In this way I ignored the new error. Thx a lot" – KDimitrof Jul 31 '14 at 09:17

Siddharth Rout · Answer 2 · 2014-08-04T11:10:52.417

UNTESTED

Here is how I would do it. Without using .SELECT/.ACTIVATE

Dim destwb As Workbook

Sub Sample()
    Dim FinalFile As String
    Dim Obj1 As String, Obj2 As String
    Dim MyRange As String, Obj1Range As String, Obj1Rang2 As String
    Dim wb As Workbook

    '~~> Change as applicable
    FinalFile = "order.xls"
    Obj1 = "order-obj1.xls"
    Obj2 = "order-obj2.xls"
    Obj1Range = "E11"
    Obj2Range = "E12"
    MyRange = "A1"

    Set destwb = Workbooks(FinalFile)

    On Error Resume Next
    Set wb = Workbooks(Obj1)
    On Error GoTo 0

    If Not wb Is Nothing Then
        CopyRange wb, MyRange, Obj1Range
        DoEvents
        Set wb = Nothing
    End If

    On Error Resume Next
    Set wb = Workbooks(Obj2)
    On Error GoTo 0

    If Not wb Is Nothing Then
        CopyRange wb, MyRange, Obj2Range
        DoEvents
        Set wb = Nothing
    End If
End Sub

Sub CopyRange(w As Workbook, r1 As String, r2 As String)
    On Error GoTo Whoa

    Dim ws As Worksheet, rng As Range

    Set ws = w.Sheets(1)
    Set rng = ws.Range(r1)

    r1.Copy destwb.Sheets("Sheet1").Range(r2)

    DoEvents

    wb.Close savechanges:=False

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

+1 and a question out of curiosity: do we need to clear the error after the first OERN part? Or the second OERN clear it? (I would check it myself but I can't at the moment) — Ioannis, Aug 04 '14 at 11:10
@loannis: Yes the 2nd OERN will clear it but by default, I ensure that I include OEGT0. This ensures that if I don't have a 2nd OERN, I don't have to worry about it :) — Siddharth Rout, Aug 04 '14 at 11:13

How to skip runtime error 9 vba?

2 Answers2