Redimensioning an array within an Excel VBA function

Question

I am trying to write a function that takes an array as input, does some manipulations to it - among others, redimensioning it by adding an additional row - and then outputs the modified array. However, when I try to redim the array, I get an error "Subscript out of range".

To illustrate my problem, I have created a simplified coding framework, with one function...:

Function funcTestFunction(Number As Integer, Arr As Variant)

ReDim Preserve Arr(0 To Number, 0 To 4)
funcTestFunction = Arr

End Function

... and a subroutine:

Sub subTestSubroutine()

Dim Number As Integer
Dim Arr() As Variant

Number = 10
ReDim Arr(0 To Number - 1, 0 To 4)
Arr = funcTestFunction(Number, Arr)

End Sub

In this framework, when I execute subTestSubroutine, I get a "Subscript out of range" error, which is located at the ReDim line in function funcTestFunction.

What am I doing wrong here?

Edit #1:

Even the following code does not work:

Sub subTestSubroutine()

Dim Number As Integer
Dim Arr() As Variant

Number = 10
ReDim Arr(0 To Number - 1, 0 To 4)
ReDim Preserve Arr(0 To Number, 0 To 4)

End Sub

There may be other issues but you can only redim the last dimension of an array. — SJR, Nov 18 '16 at 15:06
See here for possible alternatives http://stackoverflow.com/questions/23393123/redim-preserve-subscript-out-of-range — SJR, Nov 18 '16 at 15:13

score 2 · Accepted Answer · answered Nov 18 '16 at 15:32

I would add a comment if I could...

One thing I did in this situation was use Application.Transpose. I wanted to add more rows to my 2D array, but you can really only add more columns with Redim Preserve. So you can transpose it once, add the number of columns, and then transpose it back.

transposeArr = Application.Transpose(Arr)
ReDim Preserve transposeArr(0 To 4, 0 To number)
funcTestFunction = Application.Transpose(transposeArr)

score 0 · Answer 2 · answered Nov 18 '16 at 16:33

The error was caused by resizing Arr () to Number-1 in subTestSubroutine and you are sending it Number in funcTestFunction.

Try with this code should work:

Function funcTestFunction (Number As Integer, Arr As Variant)

  ReDim Preserve Arr (0 To Number - 1, 0 To 4)
  FuncTestFunction = Arr

End Function

Hope this can help!

Redimensioning an array within an Excel VBA function

2 Answers2