none
Array Variant RRS feed

  • 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 2-dimensional 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

    Tuesday, August 9, 2016 8:48 AM

Answers

  • You have to pre-define 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
    Tuesday, August 9, 2016 9:10 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
    Tuesday, August 9, 2016 10:30 AM

All replies

  • You have to pre-define 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
    Tuesday, August 9, 2016 9:10 AM
  • many thanks Felipe.
    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
    Tuesday, August 9, 2016 10:30 AM
  • thanks Andreas!
    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/en-US/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.
    Wednesday, August 10, 2016 6:20 AM