Close a Workbook if opened

Question

I would like an error handler to handle closing an Excel workbook that is not open.

I tried below code.

If Workbooks("Combo.xlsx").IsOpen Then 
     Workbooks("Combo.xlsx").Close SaveChanges:=False 
Else: Resume Next 
MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical 
End If 

This gives me an error message:

Run time Error: 9 Subscript Out of Range.

Answer 1

I had the same problem that the On Error Resume Next didn't seem to work for this. My settings are only to break on unhandled errors so I can't understand why it keeps breaking when trying to close.

Anyway, I made this simple workaround subroutine to call when closing the workbook:

Private Sub closeWorkbookIfOpen(wb As Workbook)
    On Error GoTo NotOpen

    wb.Close

NotOpen:
End Sub

Not sure why the Resume Next doesn't work, but this seems to do the trick!

Answer 2

All you really need is

On Error Resume Next
Workbooks("Combo.xlsx").Close SaveChanges:=False
On Error Goto 0

You can ignore the error if there is no workbook open with that name.