Subscript out of range (Excel VBA)

Topic

New Reply

Hi,
Is there a way to determine which object is out of range other that just being informed by Error 9: Subscript out of range. I ask this as I may have collegues in other branches running a custom macro. Then if they run it with variables not in place and this particular error occurs, I would like to trap the error with a message saying, ” The workbook could not be found!” etc, other that the err. description of “Subscript out of range”. In cases it may be a workbook, and other cases it could be a worksheet etc…so I need to know if Subscript could be determined to a particular object ??

Tx

Reply | Quote

Replies

Not as far as I know. You’ll have to add specific error checking to the code.

Reply | Quote

Tx Hans.
Could you elaborate on specific error checking please. What did you have in mind here?

Reply | Quote

In the first place, try to prevent errors from occurring. For example, you can use the Dir function to check if a workbook with a specified name exists:

Dim strWorkbook As String
strWorkbook = “C:ExcelTest.xls”
If Dir(strWorkbook) = “” Then
MsgBox “There is no workbook ” & strWorkbook, vbExclamation
Exit Sub
End If

In the second place, instead of a generic On Error GoTo …, you can use specific labels for specific errors:

Dim wbk As Workbook
Dim wsh As Worksheet

On Error GoTo NoWorkbook
Set wbk = Workbooks(“Test.xls”)

On Error GoTo NoWorksheet
Set wsh = wbk.Worksheets(“Data”)

On Error GoTo OtherError
…

NoWorkbook:
MsgBox “The workbook ‘Test.xls’ is not open”, vbExclamation
Exit Sub

NoWorksheet:
MsgBox “The worksheet ‘Data’ doesn’t exist”, vbExclamation
Exit Sub

OtherError:
MsgBox Err.Description, vbExclamation
Exit Sub

Reply | Quote

Oh…I see what you mean now! I understand.

I interpreted “specific error checking” as some special method that you may have thinking about. Something like using the err.number to check for a object type…blah blah. I think I got myself into a tunnel vision of thought again and started complicting things! (I can’t believe I actually used this smiley…I still have a thing about it!!)

Thanx for your added elaboration.
Much obliged!

Reply | Quote

Hans,

Your example violates the mechanics of an error handler. Each error handler needs some variant of the Resume statement to be inside it.

An alternative to what you showed could be:

Dim sWhere as string
Dim wbk As Workbook
Dim wsh As Worksheet

On Error GoTo LocErr
sWhere=”NoWorkbook”
Set wbk = Workbooks(“Test.xls”)

sWhere= “NoWorksheet”
Set wsh = wbk.Worksheets(“Data”)

sWhere=”OtherError”
…
TidyUp:
Exit Sub
LocErr:
Select Case sWhere
Case “NoWorkbook”
MsgBox “The workbook ‘Test.xls’ is not open”, vbExclamation
‘You could do a resume next (or other label) here!
Case “NoWorksheet”
MsgBox “The worksheet ‘Data’ doesn’t exist”, vbExclamation
‘You could do a resume next (or other label) here!
Case Else
MsgBox “Unexpected Error:” & err.description
‘You could do a resume next (or other label) here!
End Select
Resume TidyUp

Reply | Quote

If you need to do some cleaning up, such as setting object variables to Nothing, I certainly agree. But if not, Exit Sub is equivalent to Resume TidyUp, because the only code in the TidyUp section is Exit Sub.

Reply | Quote

I might be mistaken…

I seem to recall that any error handler MUST use a Resume statement. A simple Exit sub doesn’t suffice because (especially in case this sub is called from another one) you will wreck any other error handling routines after an error has not been “resumed” properly. I could not reproduce this however, so I’m inclined to think you are correct.

Reply | Quote

If a procedure doesn’t have an error handler (any kind of On Error …), errors will “bubble up” to the procedure that called it (if any).

Once you have an error handler in a procedure, this makes error local to the procedure. Errors won’t bubble up any more to a calling procedure; the Err object is cleared when End Sub or Exit Sub is encountered. So it doesn’t really matter whether you have a Resume statement.

Of course, always including a Resume statement and an exit section has many advantages (consistency, maintainability, expandability). So there is much to be said for using it.

Reply | Quote