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
– SJR, Commented Nov 18, 2016 at 15:06
See here for possible alternatives stackoverflow.com/questions/23393123/… — SJR
– SJR, Commented Nov 18, 2016 at 15:13

RiverBanana · Accepted Answer · 2016-11-18 15:32:14Z

2

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)

answered Nov 18, 2016 at 15:32

RiverBanana

1201 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mohamad TAGHLOBI · Accepted Answer · 2016-11-18 16:33:09Z

0

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!

answered Nov 18, 2016 at 16:33

Mohamad TAGHLOBI

5916 silver badges11 bronze badges

Collectives™ on Stack Overflow

Redimensioning an array within an Excel VBA function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related