Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subscript out of range (Excel VBA)

    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
    Regards,
    Rudi

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Subscript out of range (Excel VBA)

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

  4. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subscript out of range (Excel VBA)

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

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Subscript out of range (Excel VBA)

    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

  6. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subscript out of range (Excel VBA)

    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! <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> (I can't believe I actually used this smiley...I still have a thing about it!!)

    Thanx for your added elaboration.
    Much obliged!
    Regards,
    Rudi

  7. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subscript out of range (Excel VBA)

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Subscript out of range (Excel VBA)

    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.

  9. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subscript out of range (Excel VBA)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Subscript out of range (Excel VBA)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •