(Solved) – In vba how do I ReDim an array that is an element of another array

  • by
(solved)-–-in-vba-how-do-i-redim-an-array-that-is-an-element-of-another-array

I have 6 dynamic arrays. I want to include the names of these 6 arrays in another array. I then want to select, one at a time, each of the 6 dynamic arrays and ReDim the selected array to a size determined by the number of rows in an input file. The code I have used is giving me a compile error, see below:

    ```
    '================================================================
    ' DICTIONARIES
    '
    Dim FilePathNames               As Dictionary
    Dim WorkBookNames               As Dictionary
    Dim Sheet1Names                 As Dictionary
    Dim Sheet2Names                 As Dictionary
    '==============================================================
    ' GENERIC WORKBOOK OBJECT NAMES
    ' XXX(0) = FULL WORKBOOK PATH
    ' XXX(1) = WORKBOOK NAME
    ' XXX(2) = WORKSHEET1 NAME
    ' XXX(3) = WORKSHEET2 NAME (FieldXControl)
    ' XXX(4) = NUMBER OF ROWS IN WORKSHEET1
    ' XXX(5) = NUMBER OF ROWS IN WORKSHEET2
    Public XXX(6) As Variant
    Public CCC()  As Variant
    Public Keys() As String
    '================================================================
    Dim CMI()     As String
    Dim CML()     As String
    Dim CMN()     As String
    Dim CM1()     As String
    Dim CM2()     As String
    Dim CMS()     As String
    ```

    ```
    CCC = Array("CMI()", "CML()", "CMN()", "CM1()", "CM2()", "CMS()")
    ```
    ```
    'The following code is placed between a For loop 
    'Will loop through this 6 times conditioned on the For loop
    'Not a simple 0, 1, 2 etc.
    '
    Call DictionaryKeys(R)  'R is an integer conditioned by the For loop
    Call ObjectNames(Keys)
    Call ActivateFile       'A Sub that will activate XXX(1)
    XXX(4) = GetLastRowExt(XXX(1), XXX(2), "A") 'Function to get last row of XXX(2)
    XXX(5) = GetLastRowExt(XXX(1), XXX(3), "A") 'Function to get last row of XXX(3)

    ReDim CCC(N)(XXX(5) - 2)     '<===========Get a compile erro
                                 'Hope you can see what I'm trying to do
                                 'N is an integer between 0 and 5
                                 'How do I code for this?
    Go and do other stuff
    Next                         'End of For loop
    ```

    ```
    Public Sub ObjectNames(XXK As String)
    XXX(0) = FilePathNames.Item(XXK)
    XXX(1) = WorkBookNames.Item(XXK)
    XXX(2) = Sheet1Names.Item(XXK)
    XXX(3) = Sheet2Names.Item(XXK)
    End Sub
    ```

Leave a Reply

Your email address will not be published. Required fields are marked *