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.