re-naming arrays in VBA?

Colin Legg · #3 03-15-2012, 08:36 AM

There are a few different ways you could do this. I'll suggest two which you should be able to immediately integrate into your current code.

One option might be to have an array of arrays, which would look something like the below. So varrArrays has two elements, each of which is an array.

Code:

Sub Example1()
    
    Dim varrArrays(0 To 1)
    Dim sarrNames(0 To 1, 0 To 1)
    Dim larrValues(0 To 2, 0 To 1)
    
    Dim r As Long, c As Long, x As Long
    
    'put some values in the arrays for this example
    sarrNames(0, 0) = "Colin"
    sarrNames(0, 1) = "Legg"
    sarrNames(1, 0) = "John"
    sarrNames(1, 1) = "Doe"
    
    larrValues(0, 0) = 1
    larrValues(0, 1) = 2
    larrValues(1, 0) = 3
    larrValues(1, 1) = 4
    larrValues(2, 0) = 5
    larrValues(2, 1) = 6

    'put our two arrays into an array
    varrArrays(0) = sarrNames
    varrArrays(1) = larrValues
    For x = LBound(varrArrays, 1) To UBound(varrArrays, 1)
        
        For r = LBound(varrArrays(x), 1) To UBound(varrArrays(x), 1)
            For c = LBound(varrArrays(x), 2) To UBound(varrArrays(x), 2)
                Debug.Print varrArrays(x)(r, c)
            Next c
        Next r
        
    Next x
    
End Sub

Instead of using an array of arrays, you might find it easier to have a Collection of arrays. Like this:

Code:

Sub Example2()
    
    Dim colArrays As Collection
    Dim sarrNames(0 To 1, 0 To 1)
    Dim larrValues(0 To 2, 0 To 1)
    
    Dim r As Long, c As Long
    Dim vArray
    
    'put some values in the arrays for this example
    sarrNames(0, 0) = "Colin"
    sarrNames(0, 1) = "Legg"
    sarrNames(1, 0) = "John"
    sarrNames(1, 1) = "Doe"
    
    larrValues(0, 0) = 1
    larrValues(0, 1) = 2
    larrValues(1, 0) = 3
    larrValues(1, 1) = 4
    larrValues(2, 0) = 5
    larrValues(2, 1) = 6
    'put our two arrays into a collection
    Set colArrays = New Collection
    colArrays.Add sarrNames
    colArrays.Add larrValues
    
    For Each vArray In colArrays
        
        For r = LBound(vArray, 1) To UBound(vArray, 1)
            For c = LBound(vArray, 2) To UBound(vArray, 2)
                Debug.Print vArray(r, c)
            Next c
        Next r
        
    Next vArray
    
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Naming a cell then linking with a certain amount of charachters looses content.	shabbaranks	Excel	1	12-27-2011 01:06 AM
Auto file naming	Rong Peng	Word VBA	0	07-29-2011 07:37 AM
Auto-File Naming/ Default Directory Saves	sgill32	Word	2	11-06-2008 02:12 PM