Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-15-2012, 08:07 AM
JDevsFan JDevsFan is offline re-naming arrays in VBA? Windows 7 64bit re-naming arrays in VBA? Office 2010 64bit
Novice
re-naming arrays in VBA?
 
Join Date: Mar 2012
Posts: 3
JDevsFan is on a distinguished road
Exclamation 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
so on so forth. That is a simple version of what I am trying to do, it is more detailed than that.
Reply With Quote
  #2  
Old 03-15-2012, 08:34 AM
JDevsFan JDevsFan is offline re-naming arrays in VBA? Windows 7 64bit re-naming arrays in VBA? Office 2010 64bit
Novice
re-naming arrays in VBA?
 
Join Date: Mar 2012
Posts: 3
JDevsFan is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 03-15-2012, 08:36 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline re-naming arrays in VBA? Windows 7 32bit re-naming arrays in VBA? Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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
Reply With Quote
  #4  
Old 03-15-2012, 08:40 AM
JDevsFan JDevsFan is offline re-naming arrays in VBA? Windows 7 64bit re-naming arrays in VBA? Office 2010 64bit
Novice
re-naming arrays in VBA?
 
Join Date: Mar 2012
Posts: 3
JDevsFan is on a distinguished road
Default

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

Last edited by JDevsFan; 03-15-2012 at 08:41 AM. Reason: forgot text
Reply With Quote
  #5  
Old 03-15-2012, 08:44 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline re-naming arrays in VBA? Windows 7 32bit re-naming arrays in VBA? Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

No worries. Glad you got it sorted!
Reply With Quote
Reply

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

Other Forums: Access Forums

All times are GMT -7. The time now is 10:54 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft