none
subscript out of range (Excel 2007 vs Excel 2003)

    Question

  • When executing the following code:

     

    Option Explicit On

    Sub LoadDataFile()

       '

       ' Open a file

       '

       On Error GoTo ErrHandler

       Dim sFileName As String

       ' Open File

       sFileName =Application.GetOpenFilename("Excel Files (*.xls), *.xls," & _

       "All Files (*.*), *.*" _

       , , "Select Excel Data File to Open", "Open", False)

       ' Test filename

       If sFileName <> "False" Then

    MsgBox(Err.Description) 'inserted for testing

          Workbooks.Open(Filename:=sFileName)

    MsgBox(Err.Description) ' inserted for testing

       ' go to subroutine to do more work

       End If

     

       ErrHandler:

          If Err.Number <> 0 Then

             MsgBox("An error occured:" & vbCrLf & vbCrLf & Err.Description, _

    vbInformation, "LoadDataFile" & ": Err #" & CStr(Err.Number))

          End If

    End Sub

     

    I receive the "subscript out of range" error in the messagebox.  The funny thing, is that the error event is not raised (it does not goto the error handler), only add the err number (9) in the error stack. I am running this code in an Excel 2007 (compatibility mode on) file, opening an Excel 2007 (compatibility mode on) file.  The error also occurs when the two files are true 2007 (default version mode).  This error does not occur for Excel 2003 to Excel 2003 files.

     

    I added the file folder as a "trusted location" that the two files (the Excel file containing the code and the one that is the Data file) reside in, but the error still occurs.  I can't believe it is a variable declaration or invalid object issue, as I am pulling the file name right from the GetOpenFileName message box.  I was suspecting that it was a difference in Office 2007 vs. Office 2003, but did not see anything on this specific topic.

     

    Do anyone else have the replicate this problem (and how can it be fixed?)

     

    Thanks.

    Saturday, May 10, 2008 12:31 AM

All replies

  •  mfroman wrote:
    I receive the "subscript out of range" error in the messagebox.

    You have several msgboxes. Which is the one that returns the error? It would also be useful to know what the value of sFileName is at that point. Try changing the text to:

    MsgBox("1: Err = " & Err.Description & vbCrLf & "sFileName = " & sFileName)

    MsgBox("2: Err = " & Err.Description & vbCrLf & "sFileName = " & sFileName)

    etc

    Or else temporarily commenting out the On Error line so that you get the regular VBA error message and can Debug to see what line is failing and hover over the variables to see what their values are. 

     

     mfroman wrote:
    This error does not occur for Excel 2003 to Excel 2003 files.

    Do you mean the code works fine IN EXCEL 2007 when you choose an Excel 2003 file, or do mean the code runs fine IN EXCEL 2003? Assuming the former, with the combinations you explained we still haven't determined whether the deciding factor is the format of the macro book or the format of the book being opened. Could you try the code in a 2007 book, choosing a 2003 book in the GetOpenFilename dialog, and vice versa?

     

    You should also change the following, but presumably this not the problem because you'd know if was only failing when you Cancelled the dialog box.

     mfroman wrote:
    If sFileName <> "False" Then

    False should not be in quotes in this line. The point of the line is to determine whether something was returned by the GetOpenFilename dialog box. If that was Cancelled then it returns False (False is a Keyword so should not be in quotes, although I think False = "False" for comparisons). That's how it behaves in Excel 2003, anyway. It's possible that it behaves differently in Excel 2007 (I have no experience with that, according to the Object Model Cancel does still return False but maybe False <> "False" in Excel 2007). 

    An alternative way to deal with it (which I expect should work in any version) is to change that line to check for the length of the variable as well. Try replacing that line with this:

    If sFileName <> False And len(sFileName) > 7 Then

    Note that (in Excel 2003) False returns a length of 5 (as if it was text) if the variable is declared as String or Variant, but the shortest valid filename that can be returned will have 8 characters ("C:\a.xls").

     

     

    Saturday, May 10, 2008 5:32 AM
  • Cringing Dragon,

    Thanks for your responses.  To answer:

     

    1)  "Which is the one that returns the error?" (regarding the messages boxes):  The SECOND message box is the one that returns the error string "subscript out of range" but the VBA code does not treat it like an error -- it only returns the requested text of [err.description] because that was the next line of code to execute.  If Excel actually would have "known about" the error, code execution should have been redirected to the error handler.  So, when this code executes the folloing happens:

    a) I am prompted for the file to "open" -- based on [GetOpenFilename code].  I select a file.

    b) a blank (no body text) messagebox is displayed, as requested for testing -- based on [msgbox code]

    c) the file requested in (a) opens -- based on [workbooks.open code]

    d) a message box with the "Subscript out of range" text is displayed, as requested for testing --  based on [msgbox code]

    e) a message box with the "An error occurred:  Subscript out of range" text is displayed, as per the error handler--  based on [msgbox code].  PLEASE NOTE:  the only reason this section of code executed was due to the fact it fell through to here -- not because Excel's error event redirected program flow to here.  As the err.number value was "9", this code in the ErrHandler section was executed.

     

     

    2) I should have been more specific.  For purposes of the following, the workbook that this code is in will be called Workbook "A" and the requested workbook to open will be called Workbook "B":  Here are the Excel version scenarios:

    a) Open up "A" in Excel 2003.  Run macro and request workbook "B" to open.  VBA code executes, without error (the two messageboxes in the main body return blank text (as expected), and both workbooks are open.

    b) Open up "A" in Excel 2007.  Run macro and request workbook "B" to open.  VBA code executes with the situation described in part 1) above (that is, a blank message box is presented, workbook "B" is opened, a messagebox with the "subscript out of range" text is presented, the messagebox in the errorhandler is presented with the "subscript out of range" message, and both workbooks are open.

    As for your query on the format of the workbooks, for purposes of this problem, the two workbooks are the most "basic" types:  Workbook "A" is blank except for the code above and Workbook "B" is blank.  They have been saved in Excel 2007 as Excel 2007 types and as Excel 2003 (compatibility mode) types.  They have been saved in Excel 2003 as Excel 2003.  The ONLY way this code runs without error is when workbook "A" is opened up in Excel 2003 and run.  Excel 2007 must treat the code differently, but I'm not sure how.

     

    3) Thanks for the tip. The [GetOpenFilename] method returns a variant, so I'm assuming since I have declared the "sFileName" variable prior to the call, Excel implicitly converts the variant return type to a string data type, hence there is no issue on the [If sFileName <> "False"] logical comparison.  I changed the variable declaration to Variant and removed the quoutes (if you just remove the quotes as you suggested, you will receive a "data type mismatch error"), but the results are the same.

     

    To recap, the error does not seem to be with obtaining the filename from the [GetOpenFilename] method, but rather in the opening of the filename in Excel 2007 with the [Workbooks.Open] method.  And while this line of code does now throw and error, an error number is bumped up on the stack.

     

    Is the [Open] method incorrect in 2007?

     

    Michael

     

    Monday, May 12, 2008 4:31 PM
  • Here is the same situation (in its most simplistic state) that I have been trying to describe above. 

     

    If you have an Excel file located on the c:\ drive named "C:\test.xls" (the file can be blank).

     

    In Excel 2007, when this code executes:

     

    Sub LoadDataFile()
       Workbooks.Open ("C:\test.xls")
       MsgBox (Err.Description)
    End Sub

     

    it displays a message box "Subscript out of range", but

     

    In Excel 2003, when the same code executes, it displays a message box with a blank string "". 

     

     

    As mentioned, the Excel 2007 "error" is not trappable -- Excel internally stores an error flag without raising an event.  But I am at a loss as to why this err.number / err.description is generated.  I was able to reproduce this on other machines. 

     

    The same thing will happen if you change the code to open a text file (called "c:\test.txt") when this text file is located on the c:\ drive.

     

    Does anyone else confirm that this is (or not) a problem for them?

     

    Michael

    Tuesday, May 13, 2008 1:44 AM
  • If the workbook does open, then the Workbooks.Open line is working, and as far as this VBA code is concerned it has not encountered an error. The error code that is returned by the message box must be generated by something else that takes place when the workbook is opened. You get the same sort of thing when you run code that uses an external application or library, say making a sql call via ADO. If the sql has errors, the VBA code that called it doesn't necessarily realise that there was a problem (as far as it's concerned it completed the line of code that sent the sql). The external app can still return error codes to the calling VBA, although they will be error codes that are meaningful to the external app or library, not VBA error codes (so the error number 9 that you're getting might not mean "Subscript out of range"). 

    Aside - you've said the second workbook is empty - I assume that means it has no VBA code in it either. If it has code in it, particularly if that code is in any Event that is being triggered by the book being opened, then it could be that code that's generating the error.

     

    If the file does not open correctly then please let us know what isn't right about it.

     

    If the file opens correctly, then I think you can ignore that error. I'm guessing it's something to do with the way Excel 2007 opens workbooks (maybe the equivalent of a dialog box saying "file converted from previous version" - who knows, I'm guessing). It may well be a bug with Excel 2007 that it returns an error code when nothing went wrong. 

     

    You should have an Exit Sub before your ErrHandler: line anyway, to stop it from ending up in the error handler if nothing went wrong (which is what it looks like here). That will stop the original message box from appearing.

    Tuesday, May 13, 2008 10:05 AM
  • Thank you for your help.  The Exit Sub is a great recommendation that should have been in my code. 

     

    However, I can't be more clear on the fact that I think this is a bug in Excel 2007.  In response to your comment "The error code that is returned by the message box must be generated by something else that takes place when the workbook is opened" the answer is no.  If you put a breakpoint on the "Workbook.Open" code, and type "Print Err.Description" (or "print err.number") in the immediate window, nothing will be displayed.  It is only after the workbook open call is made that the err.number (and err.description) stacks are populated (but again, no Error event is raised).

     

    The first workbook in this example is a completely blank workbook, except the two lines of code [behind Sheet1] as shown in the second example.  This code will open a workbook (line 1) and will display err.description (line 2).  The second workbook is also completely blank (no data, no code).  Confirming, your assumption on the empty workbook "I assume that means it has no VBA code in it either. If it has code in it, particularly if that code is in any Event that is being triggered by the book being opened, then it could be that code that's generating the error" is true.  There is NO code, so there should be no other Event triggered.

     

    I concur with your suspicion that "I'm guessing it's something to do with the way Excel 2007 opens workbooks (maybe the equivalent of a dialog box saying "file converted from previous version" - who knows, I'm guessing)" which is why I went ahead and tested the opening of a blank text file.  Granted, I am still using the [Workbook.Open] method, but his should eliminate the "file conversion" speculation.

     

    So inclusion of the [Exit Sub] will correctly prevent the execution of the ErrHandler (small picture), but can you confirm that "It may well be a bug with Excel 2007 that it returns an error code when nothing went wrong" (big picture).  Are you (or anyone else) seeing this "error" occur?

     

    Thanks again Cringing Dragon!

    Tuesday, May 13, 2008 5:03 PM
  • I am having the exact same problem, though in my case, the error occurs everywhere I am working 'hands-on' with the workbook - e.g. activating a sheet, changing a cell value, etc. I have no idea why, and I was hoping to find a bugfix, but to no avail.

     

    Still no news on the subject?

    Wednesday, November 12, 2008 9:56 AM
  •  

    Hi Bernhof,

     

    I have the same problem. Excel has errors but is not handling them. I try to figure out at the moment if this has something to do with the Excel installation.

     

    In which language do you use the Excel. The english installation is working fine for me but I have problems on a norwegian installation. Without error handling the code is running.

     

    At least it seems good to use a exit sub before the error handler.

    Wednesday, November 12, 2008 6:34 PM
  • The bug doesn't seem to be associated with opening a workbook but only occurs regarding Excel 2007.
    Any manipulation of a worksheet range object by means of VBA results in a subscript out of range-error (err.number 9). As already stated this type of error is not trappable since Excel does not raise an event.
    This is even true for selection changes which is demonstrated in the following code example:

    Sub TestErr()
    
        On Error GoTo ErrH
        Err.Clear   
    
        Selection.Offset(1, 0).Select 'Error invoked but not raised
    
        MsgBox Err.Number & vbCr & Err.Description
    
        Debug.Print 1 / 0 'Error is  raised
    
        MsgBox "Code cannot run here", vbInformation
    
        Exit Sub
    
    ErrH:
        MsgBox Err.Number & vbCr & Err.Description, vbExclamation
    
    End Sub
    
    
    • Edited by enijhuis Wednesday, April 29, 2009 11:26 AM
    Wednesday, April 29, 2009 11:06 AM
  • Did you find the sollution to this issue? I'm having the same problem right now and I dont know how to solve it!

    Please help!

    Monday, August 09, 2010 9:23 PM
  • Please describe your particular problem.
    • Edited by enijhuis Tuesday, August 10, 2010 12:06 PM typo
    Tuesday, August 10, 2010 11:55 AM
  • Thanks for your answer enijhuis. I kept on getting the err.number 9 when i was supposed to get err.number 0. I think I solved it using a err.clear even when it shouldnt be neccesary.

    Thanks again

    Tuesday, August 10, 2010 2:21 PM
  • enijhuis,

    The problem I was having appeared again. I'm getting err.number=9 when I'm supposed to be getting the defaul number 0. Below is the code I'm using. After the err.number is clerared, I rename the active sheet which actually renames itself without any problem but after this err.number = 9 when it should be 0 as no error has occured. 

    If Err.Number <> 0 Then
                i = 2
                Do Until Err.Number = 0
                    Err.Clear
                    ActiveSheet.Name = "UPDATE ERRORS(" & i & ")"
                    i = i + 1
                Loop
            End If

    Thanks in advance

    Wednesday, August 11, 2010 2:36 PM
  •  

    Hi,

    I had a similar problem and encountered error 9, in my case it was to do with copying sheets from one workbook to another.  The sheets in question had the property xlSheetHidden.  in excel 2003 this proved no problem, however in 2007 the sheets would not copy.  The error was not picked up at this stage though.

    Code:

    [ThisWorkbook.Worksheets("Sheet1").Copy After:=ActiveWorkbook.Worksheets("SheetA")]
    
    ActiveSheet.Name = "mySheet" 

     

    The error was raised only when the copied sheets were selected by the code:

    Dim newSheet As Worksheet
      
    Set newSheet = ActiveWorkbook.Worksheets("mySheet")

     

    The solution for excel 2007 compatibility was changing xlSheetHidden to xlSheetVisible on "Sheet1".  I guess it's an internal difference in how v2003 and v2007 access hidden worksheets.

     

    Hope this helps, took a while to crack.

    Thursday, November 04, 2010 10:24 AM