Results 1 to 5 of 5

VBA run-time error 9: subscript out of range

This is a discussion on VBA run-time error 9: subscript out of range within the Excel Questions forums, part of the Question Forums category; Hello, I have been looking on internet for a solution to this common error message, but have not found it. ...

  1. #1
    Board Regular
    Join Date
    Feb 2012
    Posts
    71

    Default VBA run-time error 9: subscript out of range

    Hello,

    I have been looking on internet for a solution to this common error message, but have not found it.

    I open a file, and in the code activate that file before closing it. I give you the code below with the line where I get the error message.

    Code:
                Select Case nStage
                    Case 2
                        nFileCheck = nFileCheck + 1
                        Workbooks(NameFileToOpen & FileExtention).Activate 'run-time error 9: subscript out of range
                        ActiveWorkbook.Close
    So in the line where the specific file is activated, I receive this error message.
    A few clarifications:
    NameFileToOpen: this correctly identifies the specific file name
    FileExtention: in the earlier 2003 Excel version, I only specified ".xls" but with my recent upgrade to 2007 Excel version, I now specify this as ".xls*" so that the various Excel extensions can be taken into account. I have also tested with ".xls?" and ".xls" but I receive the same error.
    I did not receive this error message in 2003 version, only in 2007 version.

    Thank you for your advice.
    Last edited by zeno; May 31st, 2012 at 05:25 PM.

  2. #2
    Board Regular
    Join Date
    Apr 2011
    Location
    California
    Posts
    331

    Question Re: VBA run-time error 9: subscript out of range

    I normally receive this error message in my code when it's looking for a particular worksheet or workbook to interact with that isn't present. My guess is that it's looking to activate a workbook that doesn't exist (may be something to do with how you've defined the workbook name).

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Posts
    71

    Default Re: VBA run-time error 9: subscript out of range

    In the beginning, I thought that too, so I verified the name definitions, which are correct. To my understanding, with the "*" in ".xls*" it should be able to identify the open file. That file is open. And in 2003 version this worked?
    Last edited by zeno; May 31st, 2012 at 05:34 PM.

  4. #4
    Board Regular
    Join Date
    Apr 2011
    Location
    California
    Posts
    331

    Default Re: VBA run-time error 9: subscript out of range

    Quote Originally Posted by zeno View Post
    In the beginning, I thought that too, so I verified the name definitions, which are correct. To my understanding, with the "*" in ".xls*" it should be able to identify the open file. That file is open. And in 2003 version this worked.
    my next assumption would be issues with interoperability. Seeing as my native systems are 2007+ i'm not extremely familiar with working in the 2003 environment. Was this procedure originally written for excel 2003?

  5. #5
    Board Regular
    Join Date
    Feb 2012
    Posts
    71

    Default Re: VBA run-time error 9: subscript out of range

    It does indeed work for 2003, but to my understanding it should also work for 2007 version. I thought that by adding "*" it could take into account the 4 letter extension of version 2007?

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com