使用Redim Preserve扩展Excel二维数组时遇“下标越界”错误求助

Hey Jake, sorry you're stuck with that frustrating subscript out of range error—those can be such a headache, especially when you’ve already checked the basics like your functions and the expand variable. Let’s break down some less obvious troubleshooting angles to track down the issue:

• Check if you’re modifying the wrong dimension
  ReDim Preserve has a critical limitation in VBA: it only lets you resize the last dimension of a multi-dimensional array. If your array is structured as arr(rows, columns) (the usual for Excel data), you can only expand/shrink the column count—not the row count. If you’re trying to resize the first dimension (rows) with ReDim Preserve, that’s guaranteed to throw an error. Double-check which dimension you’re targeting in your final ReDim line.

• Verify array initialization and base indices
  VBA arrays default to 0-based indexing, but arrays pulled directly from Excel ranges (via Range.Value) are 1-based. If you’re mixing these two, your calculations for the new array size might be off. For example:

  • If your original array is 1-based (LBound(arr,2) = 1) and you calculate the new column count as oldCols + expand, make sure your ReDim uses 1 To oldCols + expand for the second dimension—not 0 To ....
  • Also, ensure your initial array declaration is properly dynamic: Dim arr() instead of a fixed-size declaration like Dim arr(1 to 5, 1 to 3) (fixed-size arrays can’t be resized with ReDim unless you first erase them).

• Check for off-by-one errors in the new size
  Even if expand looks correct, double-check how you’re calculating the new upper bound. For example:

  • If your original array’s second dimension goes from 1 To 10, and expand = 5, the new upper bound should be 15—not 14 or 16. A tiny miscalculation here will lead to subscript errors when you try to access the new elements.
  • Use Debug.Print UBound(arr, 2) before and after ReDim Preserve to confirm the size matches your expected value.

• Rule out hidden dimension mismatches
  If you’re pulling data from a function, even if the function runs "fine", it might be returning an array with a different dimension structure than you expect. For example:

  • A function might return a 1D array instead of 2D, or swap rows and columns (e.g., arr(columns, rows) instead of arr(rows, columns)). This would throw off your ReDim logic entirely. Use Debug.Print LBound(arr,1), UBound(arr,1), LBound(arr,2), UBound(arr,2) to inspect the array’s dimensions before resizing.

• Check for variable overflow
  If you’re working with large arrays, make sure your size variables are using the correct data type. Integer in VBA can only hold up to 32767—if your new array size exceeds that, you’ll get an overflow (which might manifest as a subscript error). Switch to Long for all variables related to array sizing to avoid this.

• Inspect the final assignment line
  Since the error is thrown on the last line, double-check what that line is doing. If it’s assigning a value to the array, make sure the indices you’re using fall within the newly resized array’s bounds. For example, if you expanded columns to 15, don’t try to assign to arr(row, 16)—that’s an immediate subscript out of range.

Hopefully one of these points hits the mark. If you can share a snippet of the relevant code (especially the ReDim line and the final error line), it’d be easier to pinpoint the exact issue, but these troubleshooting steps should cover most common hidden problems.

内容的提问来源于stack exchange，提问作者JJB