Handling erros for Application.Workbooks without using Error handlers

Question

I am trying to assign an opened workbook to an workbook object. If that workbook is not already opened, then it throws an error. I am handling the errors using an Error Handler.

This works for me:

On Error GoTo OpenWorkbookError

Set Uwk = Application.Workbooks(WbkName)

OpenWorkbookError:
If Err <> 0 Then
  Err.Clear
  MsgBox ("Please Open the Required Workbook")
  Exit Sub
End If

But is there a way to avoid using the error handlers in this situation.

What's wrong with google? https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open — SJR, Jul 11 '18 at 12:08
@SJR Saw those exact answers. But the functions declared use the error handlers. — Student of the Digital World, Jul 11 '18 at 12:09
Then I think it's safe to say that for all intents and purposes this is one of those occasions where impossible to avoid. — SJR, Jul 11 '18 at 12:10
I wouldn't say it's impossible - switching through all opened workbooks and matching the names with the required one, should do it for me without the error handlers. But it consumes time, that's why I didn't opt for that yet. — Student of the Digital World, Jul 11 '18 at 12:12
You've answered your own question then! Strictly speaking this isn't an SO question as your code is working. — SJR, Jul 11 '18 at 12:15

Darren Bartrup-Cook · Accepted Answer · 2018-07-11T12:22:49.000

One way to do it is check each workbook name, but what's wrong with the functions in the link that @SJR gave?

In your provided code you've kept the error handler within the main body of code - it should appear between the Exit Sub and End Sub at the end of the procedure.

Something like this would work without an error handler, but it's slower as it needs to check each workbook:

Sub Test()

    Dim Uwk As Workbook
    Dim WbkName As String

    WbkName = "PERSONAL.XLSB"

    For Each Uwk In Workbooks
        If Uwk.Name = WbkName Then
            Exit For
        End If
    Next Uwk

    If Not Uwk Is Nothing Then
        MsgBox Uwk.Name & " found!"
    Else
        MsgBox "Not found."
    End If

End Sub

Your version of the code should have the error handler outside the main body:

Sub Test1()

    Dim WbkName As String
    Dim UWk As Workbook

    WbkName = "PERSONAL1.XLSB"

    On Error GoTo OpenWorkbookError

    Set UWk = Workbooks(WbkName)

TidyExit:
    'Close anything that needs closing and Exit.

Exit Sub  '<< End of main body of procedure.

OpenWorkbookError:
    Select Case Err.Number
        Case 9 'Subscript out of range.
            MsgBox "Please open the required workbook."
            Resume TidyExit
        Case Else
            MsgBox "Error " & Err.Number & vbCr & _
                Err.Description, vbOKOnly + vbCritical
            Resume TidyExit
    End Select

End Sub  '<< End of procedure.

Handling erros for Application.Workbooks without using Error handlers

1 Answers1