1

The below code is supposed to check if there is "Book1.xlsx" workbook currently open. If it's not then it is supposed to open it.

When it checks for the open workbook and cannot find it, the program should throw a "subscript out of range" error. To handle this I added "On error resume next" however it still pops up on the screen.

Do you know why this is not handled by the error trapping routine (the error should have been ignored and the code execution continued)?

Sub TestBook()
    Dim strBookName As String, filepath As String, TestBook As Workbook, usr As String
    usr = Environ("username")
    filepath = "C:\Users\" & usr & "\Desktop\book1.xlsx"
    strBookName = "book1.xlsx"

    On Error Resume Next
    Set TestBook = Workbooks(strBookName)
    On Error GoTo 0

    If TestBook Is Nothing Then
        Set TestBook = Workbooks.Open(FileName:=filepath)
    Else
        MsgBox strBookName & " is open", vbOKOnly + vbInformation
    End If
End Sub
GSerg
  • 76,472
  • 17
  • 159
  • 346
Bart
  • 11
  • 1
  • 2
    What [error trapping mode](https://stackoverflow.com/q/12687105/11683) do you have? – GSerg Sep 15 '21 at 14:14
  • You probably should NOT be using On Error Resume Next. Anyway, if you insist on using it, you can put it at the top, right after the Sub(). – ASH Sep 15 '21 at 14:33
  • 2
    @ASH - no, OP is using it appropriately and your suggestion to put it at the top is not a good idea. – BigBen Sep 15 '21 at 14:34
  • My guess is that the workbook is not open and it does not exist in the folder. As such the error is happening on the Open line. – Scott Craner Sep 15 '21 at 15:14
  • 1
    @ScottCraner Then it wouldn't be "subscript out of range". – GSerg Sep 15 '21 at 16:17
  • @GSerg no trapping routine as such, probably worded it incorrectly. When the below line is executed: `On Error Resume Next` `Set TestBook = Workbooks(strBookName) ` it should check the open workbooks collection and if cannot find the file (it's not open) then it should evaluate `TestBook = Nothing` but not throw the error as it has the `On Error Resume Next` preceding. I have also tried other variations like ` On Error GoTo ErrHandler ` and had my error handler seperately but this does not work throwing an error. Any sugestions? – Bart Sep 16 '21 at 06:31
  • @Bart I didn't ask about a trapping routine, I asked about the trapping mode. Please click the link. – GSerg Sep 16 '21 at 07:02
  • @GSerg I did have the first one selected: Break on All Errors. I have just read through the link you shared and now understand what the problem was. Simple change to the other mode fixed the issue but it's good to know about these modes when you're trying to debug error handlers. Many thanks Gserg! – Bart Sep 16 '21 at 07:24

0 Answers0