#1
|
|||
|
|||
re-naming arrays in VBA?
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 |
#2
|
|||
|
|||
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:
|
#3
|
||||
|
||||
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 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 |
#4
|
|||
|
|||
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:
Last edited by JDevsFan; 03-15-2012 at 08:41 AM. Reason: forgot text |
#5
|
||||
|
||||
No worries. Glad you got it sorted!
|
Thread Tools | |
Display Modes | |
|
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 |