Throwing and Catching Erros in VBA?

July 26, 2022

I am familiar with java programing and throwing and catching errors and I am unsure if this is a functionality within excel VBA. I would like to do the following catching but unsure it is still not working

 If Workbooks("Name").Worksheets("Sheet1") Is Nothing Then
    Err.Raise vbObjectError + 9, , "Destination Spreadsheet not Open. Please Open"
    End If
    Set wsDest = Workbooks("Name").Worksheets("Sheet1")

However when I create this code I still run into the same "Subscript out of range error". If anyone has any advice or tips please let me know.

>Solution :

You need to use an on error goto statement to handle this situation. A workbook and a worksheet are both contained in a collection, so any attempt to access a non-existent element will throw a subscript out of range error before you get to your err.raise.

     Dim wsdest As Worksheet
    On Error GoTo desterr
    Set wsdest = Workbooks("Name").Worksheets("Sheet1")
    On Error GoTo 0
    Exit Sub
desterr:
    Err.Raise vbObjectError + 9, , "Destination Spreadsheet not Open. Please Open"
    
End Sub

If you’re not a fan of that you could also try and loop through each workbook and worksheet to find what you are looking for, thought that would be a bit less efficient.