Variant Multidimensional & Initialize RRS feed

  • Question

  • I need a 2D variant array to hold about a 100 values (10 x 10).  I need to initialize it with fixed values.  I know of 2 options but both have issues.

    Option 1 - Creates a true 2D array but it doesn't support continuation lines or at least I haven't figured it out.

    Option 2 - Creates an array of arrays.  More difficult to work with but supports continuation lines.


    1. Are there other options? 
    2. Is there a way were option 1 can have continuation lines?

    Sub TestVar()
      Dim x As Variant
      Dim y As Variant
      Dim i As Integer
      Dim j As Integer
     '**** Option 1 ****
      x = [{"String1", 1.25, 1, True; "String2", 1.54, 2, False}]
      For i = 1 To UBound(x, 1)
        For j = 1 To UBound(x, 2)
         Debug.Print x(i, j)
        Next j
      Next i
      '**** Option 2 ****
      y = Array(Array("String1", 1.25, 1, True), _
                Array("String2", 1.54, 2, False))
      For i = 0 To UBound(y, 1)
        For j = 0 To UBound(y(i))
         Debug.Print y(i)(j)
        Next j
      Next i
    End Sub

    Saturday, November 11, 2017 2:14 AM

All replies

  • Hi,

    I'm not certain what you want to do. And I guess....

    Private AryX(9, 3) As Variant
    ' ---[Initialize]
    Private Sub btn_Initialize_Click()
        Dim myRow, myCol As Integer
        For myRow = 0 To 9
            For myCol = 0 To 3
                AryX(myRow, myCol) = Cells(myRow + 11, myCol + 2).Value
        ' ---
        MsgBox "[ Initialize ] completed !!"
    End Sub
    ' ---[Show] Private Sub btn_Show_Click() Dim myRow, myCol As Integer For myRow = 0 To 9 For myCol = 0 To 3 Cells(myRow + 11, myCol + 7) = AryX(myRow, myCol) Next Next End Sub
    ' ---[Clear] Private Sub btn_Clear_Click() Range("G11:J20").Value = "" ' --- MsgBox "[ Clear ] completed !!" End Sub
    I hope this would be helpful.


    Saturday, November 11, 2017 6:55 AM
  • Ashidacchi, thanks for the suggestion.  I need to keep the array initialization in VBA code.  I use GIT for version control.  Versioning spreadsheet data is cumbersome.  If it was a lot of data I would use a database like Access of SqLite.
    Saturday, November 11, 2017 2:00 PM
  • Hi mogulman52,

    I'm not sure I can understand your situation.
    How many records (in Excel, they are called "Row") will be in a data table?
    How you want to retrieve information and update it?
    How many people will access the database at the same time?  

    It depends on your requirements whether Excel, Access DB or SqlLite is better. 



    Saturday, November 11, 2017 11:17 PM
  • It is fairly simple.  I need a 10 x 10 variant array and I want to initialize it VBA.  The information is fixed and used in the addin (.xlam).  It is used for parameters required for the addin.  The user of the addin won't see the array. 
    Sunday, November 12, 2017 2:51 AM
  • Hi mogulman52,

    I'm wondering why you mentioned about database. I hope you will provide information related with your issue.  And I have no idea about your addin (.xlam). 



    Sunday, November 12, 2017 3:35 AM
  • I'm not sure why the addin makes a difference but here goes.  The addin creates reports which have numerous pivot tables and graphs.  I created a generic pivot table and graph modules that are data driven by the contents of variant array.  If I need to change or add a graph I simply change the contents of the array.  No coding, easy to maintain and regression test.
    Sunday, November 12, 2017 2:45 PM
  • Hi mogulman52,

    Sorry, I can hardly understand your situation. I'm not so good at English.
    Is your code (initializing array) written in your addin?



    Monday, November 13, 2017 1:53 AM