VBA run-time error 9: subscript out of range

zeno

Board Regular
Joined
Feb 16, 2012
Messages
71
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:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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).
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,192
Messages
6,170,645
Members
452,344
Latest member
LarryRSch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top