VBA - subscript out of range error crashing Excel

I've been working on a Visual Basic script for a colleague some time that has been exhibiting an weird bug that I just cannot get to the bottom of. When run, the script sometimes works fine, but a lot of the time gives me a "Subscript out of range" error that promptly crashes Excel.

The first thing I'd like to know is why Excel crashes, even when I have error-catching code in the script. At the start of the script I have the line:

On Error GoTo ErrorCatch

With the following lines at the bottom:

ErrorCatch:

    MsgBox "Error " & Err.Number & " detected - " & Err.Description _
            & ".  DebugCheckpoint = " & DebugCheckpoint & "."
    Err.Clear
    DisableSpeedBoosts

DebugCheckpoint is a character string that is changed at various points during the script to help narrow down where the script crashes. The last line of that refers to a pair of functions within a separate module of the same spreadsheet. At the start, I run the following function:

Sub EnableSpeedBoosts()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False

End Sub

While I have the reverse at the end:

Sub DisableSpeedBoosts()

    'Restore previous settings
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.EnableEvents = True

End Sub

However, the script still crashes Excel even with that line commented out, so it may not be relevant.

Can anyone explain why Excel still crashes, even though the error has been caught and cleared? That's the first thing that doesn't make sense.

As for the script itself, what I am trying to do is populate a table of master data on the first sheet from a similarly-structured table in an import file, while checking for actual and potential duplicate lines. What's supposed to happen is something like this:

• The user run the script and selects the import file through a dialog box,

• If a valid file is chosen, the script runs some basic checks on the first sheet to see if the table on that sheet conforms to a set format,

• If that check passes, the script then populates an array (ImportArray) using that table and a second array from the master table (DataArray).

• The script also initialises two blank arrays, one for the items to be added to the master table (AddArray), and one for the actual and potential duplicates to be added to a second table on the same spreadsheet (LogArray).

• The script then goes through the ImportArray, runs a number of checks for actual and potential duplicate lines between this and the DataArray, and adds the line to the AddArray, or the LogArray, or both, in the case of potential duplicates.

• Finally, once it reaches the end of the ImportArray, the script appends the AddArray to the master table and the LogArray to the duplicates table, prints a friendly message, and exits.

This may not be the best or most efficient way of doing this, but I think the logic is fairly sound and when it works it works quite well. However, there's a weird, intermittent bug in the code that I cannot explain.

When I first created this, the code ran fine on my machine, but promptly crashed my colleagues computer. Both of us are using Excel 2013 on Windows 7 on company comuters, so there's not an awful lot of scope for customisations or variations, but the script worked fine on my computer but crashed on his. The error message is always the same, some form of "Subscript out of range" error, but I've not been able to explain why, especially since the exact same script was able to import the exact same file on my computer with no problems.

Running some more tests and adding some debug code, I've now been able to reliably re-create the error on my computer. I've narrowed the crash to a single line, but it now makes even less sense than before. The last line in the following snippet is crashing not just the script, but Excel as well:

If (PopulateArray(ImportArray) = False) Then

    MsgBox "Failed to load import file.  Aborting."
    Exit Sub

End If

DebugCheckpoint = "ImportArray tests"
Debug.Print "ImportArray tests"
Debug.Print "ImportArray = (" & LBound(ImportArray, 1) & " to " & UBound(ImportArray, 1) _
        & ", " & LBound(ImportArray, 2) & " to " & UBound(ImportArray, 2) & ")"
' Crashes on the next line
Debug.Print "ImportArray(1, 1) = " & ImportArray(1, 1)

The PopulateArray function prompts the user for an import file and runs some validation on the file. If no file is selected or the validation tests fail it returns false. The second-to-last Debug.Print line displays the dimensions of the ImportArray, while the one that crashes it simply tries to display the first element. Here's what the Immediate window looks like using a valid import file:

ImportArray tests
ImportArray = (1 to 143, 1 to 5)

The line "Debug.Print "ImportArray(1, 1) = " & ImportArray(1, 1)" doesn't show up on this window, so presumably is the one crashing the script, but I don't understand why. We know from the previous line that the array has 143 x 5 records both with base one, so how can item (1, 1) be "out of range?" I've tried various pieces of code in different parts of the script to try and explain what's happening but to no avail. The array just seems to randomly disappear.

Can anyone explain what's going on here, or at least give me some pointers to try and investigate this further. Even though I've narrowed the crash to one line, I can see absolutely no reason why that line is causing the crash. I also can't see why it used to work fine but now it crashes, when all I've done is add some debug code.

Please help me understand what's going on if you can.

EDIT:

OK, I've narrowed this down quite a bit and added some error-checking code to the Populate() function so it should be easier to see what's going on now. Here's the main script in it's entirety:

Sub ImportArrayTest()

    Dim ImportArray() As Variant

    If (PopulateArrayTest(ImportArray) = False) Then

        MsgBox "Failed to load import file.  Aborting."
        Exit Sub

    End If

    Debug.Print "ImportArray tests"
    Debug.Print "ImportArray = (" & LBound(ImportArray, 1) & " to " & UBound(ImportArray, 1) _
            & ", " & LBound(ImportArray, 2) & " to " & UBound(ImportArray, 2) & ")"
    Debug.Print ImportArray(3, 3)


End Sub

While this is the trimmed-down Populate() function:

Function PopulateArrayTest(ImportTable As Variant) As Boolean

    Dim ImportFile As Workbook
    Dim ImportFileName As Variant
    PopulateArrayTest = False

    ImportFileName = Application.GetOpenFilename("Excel (*.xls*),*.xls*", 1, "Please select report")
    Set ImportFile = Application.Workbooks.Open(ImportFileName)

    With ImportFile.Worksheets(1)

        ImportTable = .Range(.Cells(5, 1), .Cells(.Range("a5").End(xlDown).Row, 5))

    End With

    Debug.Print "ImportTable tests"
    Debug.Print "ImportTable = (" & LBound(ImportTable, 1) & " to " & UBound(ImportTable, 1) _
        & ", " & LBound(ImportTable, 2) & " to " & UBound(ImportTable, 2) & ")"
    Debug.Print "ImportTable (1, 1) = " & ImportTable(1, 1)     

    PopulateArrayTest = True
    ImportFile.Close

End Function

The script now crashes on the line "Debug.Print ImportTable(1, 1)" in the PopulateArrayTest function and takes Excel with it. The output in the immediate window looks like this:

ImportTable tests
ImportTable = (1 to 143, 1 to 5)

So the ImportTable is being correctly re-sized according to the size of the table in the import file, but accessing it gives a "Subscript out of range error." I really can't see what the problem is here. Can anyone see what I'm missing?