Error btw Mac Excel 2003 and PC Excel 2007: "Run-time error '9' Subscript out of range"

[kachunyu]

Hi,

I have a Excel/VBA script written 5-6 years ago on a Mac running Excel -- probably 2003. (It is not an option to go back and work on that Mac and software version.) I need to get this to run in Excel 2007 on a PC.

This is my first time debugging VBA scripts. I've been able to remove some Mac-specific syntax that was causing problems. However I've run into a wall with a "Run-time error '9' Subscript out of range" error that has halted my progress.

As a novice at this, I may be including much more than is necessary so please bear with me. First the main script do_all_test() immediately calls multiple sub procedures:

Sub do_all_test()
Application.ScreenUpdating = False
get_all_file_names_auto
open_files
create_temp_name_list

The first queries the user for input Excel file(s):

Sub get_all_file_names_auto()
Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "TEMP"
num_files = InputBox("How many files are being analyze?")
For i = 1 To num_files
    filepath = Application.GetOpenFilename("Excel-files,*.xls", 1, "Select one file to open", , False)
    Sheets("TEMP").Cells(nextRow, 1) = filepath
    For j = 1 To 100
        If Mid(filepath, Len(filepath) - j, 1) = ":" Then
            Exit For
        End If
    Next j
    Sheets("TEMP").Cells(nextRow, 2) = Mid(filepath, 1, 1000)
    nextRow = nextRow + 1
Next i
Sheets("TEMP").Range("A1").Select
Do Until ActiveCell.Value = ""
    If ActiveCell.Value = ActiveWorkbook.Name Then
        ActiveCell.EntireRow.Delete shift:=xlUp
    End If
    ActiveCell.Offset(1, 0).Select
Loop
Sheets("TEMP").Range("A1").Select
Error:
End Sub

The next procedure opens the file(s):

Sub open_files()
this_file = ThisWorkbook.Name
For x = 1 To num_files
    Sheets("TEMP").Range(Cells(x, 1), Cells(x, 1)).Select
    Workbooks.Open Filename:=ActiveCell.Value
    Workbooks(this_file).Activate
    Next x
End Sub

Finally the "Run-time error '9'" shows up in the third procedure, at the Workbooks().Activate line:

Sub create_temp_name_list()
    Sheets("temp_name_list").Cells.Clear
For current_file = 1 To num_files
    Sheets("temp_name_list").Cells.Clear
    file_to_work_with = Sheets("TEMP").Cells(current_file, 2).Value
    Workbooks(file_to_work_with).Activate
    ...
    Next current_file
End Sub

What is causing the script to crash in Windows but not on the Mac? Messages on these forums reveal that others who run into the same error message usually discover it's an issue of not opening the Workbook correctly. But hasn't that been properly done in the open_files() procedure?

--kachun

 

[SteveInAlabama]

I just sat down to post a question that is very similar to yours - code that runs fine in Excel 2003, but fails in Excel 2007, on the Workbook_Open Event, usually at the line Application.ScreenUpdating = False. However, if I Step thru the offending line using F8, often the code will run. And oddly enough I can manually Run the entire Workbook_Open code from the programming screen and it runs without error.

I do not want to hijack your thread, so I will only say this: so far, the only 'work-around' I have found (by accident) is to open the old file in Excel 2010 and save it in the new .xlsm format. Then the Excel 2007 PC's having been able to open the file without error.

HTH

 

[kachunyu]

Hi Steve,

Thanks for the hint. I don't have Excel 2010, but I did save out an *.xlsm file in Excel 2007. Unfortunately I run into the same run-time error.

How did you add a stop in the VBA code so that you can step through it?

--kachun

 

[SteveInAlabama]

Does your code stop and come up with an error message, or run but not give you the desired results?

To watch your code run one line at a time, press ALT-F11 to get the VB editor, find your code and click on (in your case) the Application.ScreenUpdating = False line in your do_all_test sub. Your cursor should be blinking on that line, then press the F8 key to single-step thru each line of your code.

Just to clarify my prior post, I too could not get the conversion to work going from Excel 2003 to Excel 2007. That is what I first tried since that is what my customer wanted. I only tried the conversion from 2003 using 2010 as a 'what-the-heck' and that file application seems to work on the 2007 boxes. This is more than a one-time event, so I am sort of believing it, kind of like voodoo... But since it worked I could move on to other things.

 

[kachunyu]

Thanks for the info about getting into the VBA editor before I run the script. I placed a Breakpoint at my line:

file_to_work_with = Sheets("TEMP").Cells(current_file, 2).Value

And then proceeded to Step through the script. Unfortunately it fails immediately at the place where I was failing before:

Workbooks(file_to_work_with).Activate

The execution stops and I get an error dialogue with the "Run-time error '9'" message.

So at least in my attempt to use Excel 2007 on a *.xlsm file saved out from Excel 2007, I'm still where I was before. I will see if I can track down a version of Excel 2010 to test out the procedure that you suggested.

--kachun

 

[kachunyu]

I've just discovered that Excel 2010 doesn't work for me either. Running my script in Excel 2010 still results in the run-time error at the same line in the file. Saving it out as a *.xlsm file from Excel 2010, and then running it in Excel 2007 also results in the same run-time error.

--kachun

 

[RoryA]

add:

msgbox file_to_work_with

before the activate line and check that you do have a workbook open (in the same excel instance) by the name shown in the message.

 

[kachunyu]

A dialogue box pops up showing:

C:\Users\...\...\Spring10_Vicky.xls

After clicking okay to close the window, the script crashes like before. I've also used Debug.Print statements to print file_to_work_with in the Immediate window, and get the same output before it crashes.

--kachun

 

[shg]

A dialogue box pops up showing:

C:\Users\...\...\Spring10_Vicky.xls

When you do this ...

Workbooks(file_to_work_with).Activate

... the filename must NOT include the path. You're activating an open workbook, not a workbook on disk.

 

[kachunyu]

That's it! Thanks!

Okay, I've fixed at least that problem ...

--kachun