Answered by:
Array Variant
Question

Hi
I am trying to create a dynamic array of type variant.
The code I have is :
Dim varRegion1 As Variant
varRegion1 = Array() ' which I believes tells VBA this is an array.
I now want this to be a 2dimensional dynamic array. I plan to loop (using I as the indiex) and populate the array but when I do the following:
varRegion1 (I,1) = "A"
varregion1 (I,2) = "B"
I get a run time error  (note the A and B are just examples, there will be othere values used)
what I am doing wrong please?
Many thanks
Peter
Answers

You have to predefine the size of you array before populating it:
Sub Example1() Dim varRegion1(1 To 5, 1 To 2) Dim i As Long, j As Long varRegion1(1, 1) = "A" varRegion1(1, 2) = "B" varRegion1(2, 1) = "C" varRegion1(2, 2) = "D" varRegion1(3, 1) = "E" varRegion1(3, 2) = "F" varRegion1(4, 1) = "G" varRegion1(4, 2) = "H" varRegion1(5, 1) = "I" varRegion1(5, 2) = "J" For i = LBound(varRegion1, 1) To UBound(varRegion1, 1) For j = LBound(varRegion1, 2) To UBound(varRegion1, 2) Debug.Print varRegion1(i, j) & ""; Next j Next i Debug.Print End Sub
Alternatively, you can use the much slower Redim Preserve to progressively add items:
Sub Example2() Dim varRegion1() As Variant Dim i As Long, j As Long Dim NumItems As Long NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 1) = "A" varRegion1(2, 1) = "B" NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 2) = "C" varRegion1(2, 2) = "D" NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 3) = "E" varRegion1(2, 3) = "F" NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 4) = "G" varRegion1(2, 4) = "H" NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 5) = "I" varRegion1(2, 5) = "J" For i = LBound(varRegion1, 1) To UBound(varRegion1, 1) For j = LBound(varRegion1, 2) To UBound(varRegion1, 2) Debug.Print varRegion1(i, j) & ""; Next j Next i Debug.Print End Sub
Notice that I inverted the array dimensions because Redim Preserve Statement applies only to the last dimension of an array.
http://www.ambienteoffice.com.br  http://www.clarian.com.br
 Marked as answer by py1 Tuesday, August 9, 2016 10:16 AM

varRegion1 = Array() ' which I believes tells VBA this is an array.
Yes, but that's an empty array.
If you want to have both dimensions dynamically you have to use an array of array, because you can only change the last dimension of any array. Means it is not possible to change the number "of rows" of a 2 dimensional array.
Andreas.
Option Explicit Sub Example_ArrayOfArrayToArray() Dim Arr, Temp Dim i As Integer, j As Integer 'Create a one dimensional array ReDim Arr(1 To 5) For i = 1 To 5 'Create an one dimensional array of array ReDimA Arr(i), 1, 3 'Fill some values For j = 1 To 3 Arr(i)(j) = i * 10 + j Next Next 'Convert into a 2D array Temp = ArrayOfArrayToArray(Arr) 'Write into the sheet Range("A1").Resize(UBound(Temp), UBound(Temp, 2)) = Temp 'Increase rows ReDim Preserve Arr(1 To 9) For i = 6 To 9 'Increase and shift columns ReDimA Arr(i), 2, 5 For j = 2 To 5 Arr(i)(j) = i * 10 + j Next Next Temp = ArrayOfArrayToArray(Arr) Range("A1").Resize(UBound(Temp), UBound(Temp, 2)) = Temp End Sub Sub ReDimA(ByRef Arr, LBoundA As Long, UBoundA As Long) 'Note: ReDim Arr(I)(LBoundA to UBoundA) don't work! ReDim Arr(LBoundA To UBoundA) End Sub Function ArrayOfArrayToArray(ByVal Arr) As Variant 'Converts a 1D array of 1D array to a 2D array Dim Result Dim i As Long, j As Long, l As Long, u As Long i = LBound(Arr) l = LBound(Arr(i)) u = UBound(Arr(i)) For i = LBound(Arr) + 1 To UBound(Arr) If LBound(Arr(i)) < l Then l = LBound(Arr(i)) If UBound(Arr(i)) > u Then u = UBound(Arr(i)) Next ReDim Result(LBound(Arr) To UBound(Arr), l To u) For i = LBound(Arr) To UBound(Arr) For j = LBound(Arr(i)) To UBound(Arr(i)) Result(i, j) = Arr(i)(j) Next Next ArrayOfArrayToArray = Result End Function
 Marked as answer by py1 Tuesday, August 9, 2016 11:58 AM
All replies

You have to predefine the size of you array before populating it:
Sub Example1() Dim varRegion1(1 To 5, 1 To 2) Dim i As Long, j As Long varRegion1(1, 1) = "A" varRegion1(1, 2) = "B" varRegion1(2, 1) = "C" varRegion1(2, 2) = "D" varRegion1(3, 1) = "E" varRegion1(3, 2) = "F" varRegion1(4, 1) = "G" varRegion1(4, 2) = "H" varRegion1(5, 1) = "I" varRegion1(5, 2) = "J" For i = LBound(varRegion1, 1) To UBound(varRegion1, 1) For j = LBound(varRegion1, 2) To UBound(varRegion1, 2) Debug.Print varRegion1(i, j) & ""; Next j Next i Debug.Print End Sub
Alternatively, you can use the much slower Redim Preserve to progressively add items:
Sub Example2() Dim varRegion1() As Variant Dim i As Long, j As Long Dim NumItems As Long NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 1) = "A" varRegion1(2, 1) = "B" NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 2) = "C" varRegion1(2, 2) = "D" NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 3) = "E" varRegion1(2, 3) = "F" NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 4) = "G" varRegion1(2, 4) = "H" NumItems = NumItems + 1 ReDim Preserve varRegion1(1 To 2, 1 To NumItems) varRegion1(1, 5) = "I" varRegion1(2, 5) = "J" For i = LBound(varRegion1, 1) To UBound(varRegion1, 1) For j = LBound(varRegion1, 2) To UBound(varRegion1, 2) Debug.Print varRegion1(i, j) & ""; Next j Next i Debug.Print End Sub
Notice that I inverted the array dimensions because Redim Preserve Statement applies only to the last dimension of an array.
http://www.ambienteoffice.com.br  http://www.clarian.com.br
 Marked as answer by py1 Tuesday, August 9, 2016 10:16 AM


varRegion1 = Array() ' which I believes tells VBA this is an array.
Yes, but that's an empty array.
If you want to have both dimensions dynamically you have to use an array of array, because you can only change the last dimension of any array. Means it is not possible to change the number "of rows" of a 2 dimensional array.
Andreas.
Option Explicit Sub Example_ArrayOfArrayToArray() Dim Arr, Temp Dim i As Integer, j As Integer 'Create a one dimensional array ReDim Arr(1 To 5) For i = 1 To 5 'Create an one dimensional array of array ReDimA Arr(i), 1, 3 'Fill some values For j = 1 To 3 Arr(i)(j) = i * 10 + j Next Next 'Convert into a 2D array Temp = ArrayOfArrayToArray(Arr) 'Write into the sheet Range("A1").Resize(UBound(Temp), UBound(Temp, 2)) = Temp 'Increase rows ReDim Preserve Arr(1 To 9) For i = 6 To 9 'Increase and shift columns ReDimA Arr(i), 2, 5 For j = 2 To 5 Arr(i)(j) = i * 10 + j Next Next Temp = ArrayOfArrayToArray(Arr) Range("A1").Resize(UBound(Temp), UBound(Temp, 2)) = Temp End Sub Sub ReDimA(ByRef Arr, LBoundA As Long, UBoundA As Long) 'Note: ReDim Arr(I)(LBoundA to UBoundA) don't work! ReDim Arr(LBoundA To UBoundA) End Sub Function ArrayOfArrayToArray(ByVal Arr) As Variant 'Converts a 1D array of 1D array to a 2D array Dim Result Dim i As Long, j As Long, l As Long, u As Long i = LBound(Arr) l = LBound(Arr(i)) u = UBound(Arr(i)) For i = LBound(Arr) + 1 To UBound(Arr) If LBound(Arr(i)) < l Then l = LBound(Arr(i)) If UBound(Arr(i)) > u Then u = UBound(Arr(i)) Next ReDim Result(LBound(Arr) To UBound(Arr), l To u) For i = LBound(Arr) To UBound(Arr) For j = LBound(Arr(i)) To UBound(Arr(i)) Result(i, j) = Arr(i)(j) Next Next ArrayOfArrayToArray = Result End Function
 Marked as answer by py1 Tuesday, August 9, 2016 11:58 AM


Hi py1,
Thanks for your posting question on MSDN forum.
I am glad to see that your issue has been resolved your issue by community member, but I think that this issue is related to VBA. So if you have any question about VBA, you could post them on MSDN forum for Visual Basic for Applications (VBA)
https://social.msdn.microsoft.com/Forums/enUS/home?forum=isvvba
The reason why we recommend posting appropriately is you will get the most qualified pool of respondents,
and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.
Thanks for your understanding.