Throwing and Catching Erros in VBA?-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

If you try to access an workbooks that don't exist, this error will raise. You can use this method to check all kind of collection.

Public Function CollectionItemObjectAvalible(p_Collection As Object, p_key As String)
   Dim l_val
   On Error Resume Next
   Set l_val = p_Collection(p_key)
   CollectionItemObjectAvalible = Err.Number = 0   'nur wenn oben der Zugriff keine Fehler erzeugt hat, ist das Objekt vorhanden
End Function

If CollectionItemObjectAvalible(Workbooks, "Name") Then
   Err.Raise vbObjectError   1, "My Project", "Cannot find 'Name' in workbooks."
End If