re-naming arrays in VBA?

JDevsFan · #1 03-15-2012, 08:07 AM

I have two arrays, identicle in dimensions, identicle in size, exact same code pulling the data out of the arrays. The only difference is the amount of data in each and the data stored which does not matter. I want to create a loop that changes which array I am referencing in the VBA code each time through the loop. This will prevent an entire new section of code with the EXACT same code from the first array. I have attempted a few things but it appears you cannot set a variable to store a name and then use that name to access the data. Any thoughts... below is an quick example

Code:

 
dataL(50,50), dataF(50,50)
 
for i = 1 to # of arrays needed
 
   if i = 1 then
          arrayV = dataL
   else
          arrayV = dataF
    end if
 
         value = arrayV(1,1)
next i

so on so forth. That is a simple version of what I am trying to do, it is more detailed than that.

JDevsFan · #2 03-15-2012, 08:34 AM

I believe I have acheived my result...

I defined the variable wrong. It needs defined as a variant (may be usefule to use a different defining value but right now this one works) then just set the variable equal to the array wanted.

Quote:

Dim arrayV As Variant

for j = 1 to 2
if j = 1 then
arrayV = dataL
else
arrayV = dataF
end If
...code
next j

simple change :-S

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

JDevsFan · #4 03-15-2012, 08:40 AM

Thank you for that quick respons Colin. I read through it and it looks very good. It is a little more than I needed though; maybe I just do not code the correct way ;-). I did get it to work...

Quote:

I believe I have acheived my result...

I defined the variable wrong. It needs defined as a variant (may be usefule to use a different defining value but right now this one works) then just set the variable equal to the array wanted.

Quote:

Dim arrayV As Variant

for j = 1 to 2
if j = 1 then
arrayV = dataL
else
arrayV = dataF
end If
...code
next j
simple change :-S

Colin Legg · #5 03-15-2012, 08:44 AM

No worries. Glad you got it sorted!

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