Excel 2010 Redim Preserve crash

Question

I am somehow able to crash Excel 2010 by entering the following into the IDE in a brand new workbook:

private sub foo
    redim v(,1 to 3)

OK, you're not likely to type that ... but here's what really happened:

private sub foo
    dim v(1 to N, 1 to M)
    ...
    M = New_Value
    redim preserve v(,1 to M)  ' seemed reasonable ... then CRASH !!!

It's funny that VBA requires that you be explicit with the 1st param of a 2D redim preserve (in that you're not allowed to alter the 1st dimension). But it's not funny when the penalty for a simple syntax error is a hard crash. This is an IT dept with a reasonably clean environment (no installed addins, starting w empty project pane in IDE), and "redim v(,1 to M)" crashed my neighbor's machine too - so it's not just me.

I am wondering if this behavior occurs with others, and am posting it just in case it saves someone else the hours I wasted rebuilding my workbooks until I pinned down the bug.

Answer 1

The crash occurs from both the omission of the first dimension, or perhaps better phrased, an omission at nLastDimension - 1, and also use of the TO keyword in the following dimension.

I took your small sample and tried to make it NOT crash :P

Option Explicit 'Just wanted to make the Interpreter more picky

Private Sub test()
    'Go through some basic declaration
    Dim v() As Variant
    Dim M As Integer
    Dim New_Value As Integer

    ReDim v(2, 2) 'No Problem
    ReDim Preserve v(1, 1 To M) ' No Problem
    ReDim Preserve v(1 To M) ' No Problem
    ReDim Preserve v(,400) 'Compile Error

    'Crash on this
    'ReDim Preserve v(,1 To M) 'BOOM!!!
    'and this:
    'ReDim Preserve v(,1 To 2) 'BOOM!!!
    'and this:
   'ReDim Preserve v(1 to 5,,1 To 2) 'BOOM!!!


End Sub

Bottom line, none of the code before the "BOOM" lines will cushion the fall. A compiler error I would understand, but VBA is not coded to handle

ReDim myVar(,x TO y)

I used Office 2007.

---

For kicks and grins, I also tried this code in the following places:

1. Access '97 (CRASH)
2. Visual Basic 5 IDE (CRASH)
3. BASIC environment for OpenOffice Calc. (See below)

It did not crash and I received the following two compiler errors: First this:

Then this: