How to loop through different arrays?

Question

Hi,

   I need to pass different arrays as paramter to a Sub and I need to call the sub inside a loop. So how do I cycle through different arrays in each iteration?

For example:

type1, type2, type3 etc are my arrays

'Calling Sub

For i = 1 to UBound(allAddress)

 Call Populate("type" & i)

Next i
'called sub
Sub Populate(ByRef Adrs() as string)

Gives me "Array or User -defined type expected" error. I have searched everywhere for a solution and it seems that using a multidimensional array could be an option. But I cannot use it because the dimensions are dynamic. My problem is I cannot use, for eg: "type" & 1 for type1 array!

Answer 1

"type" & i is simply a string that refers to nothing, just the 5 characters "type1" and no more. If you try and pass that as an argument declared as a string array it will indeed fail.

One way you could do it in a small loop is

For i = 1 to  UBound(allAddress)
Select Case i
Case 1: Call Populate(type1)
Case 2: Call Populate(type2)
etc

It's not clear what you are trying to do overall but most likely there are better ways of doing it, maybe several, including no doubt use of a multidimensional array.

Peter Thornton

Answer 2

It's not the best way, but here is a way that uses the names as they are ...

Sub Test()
  Dim type1(1), type2(1)
  type1(0)=1
  type1(1)=2
  type2(0)=3
  type2(1)=4
  For i = 1 to 2
    Execute "Populate type" & i
  Next
End Sub
sub populate(arg)
  msgbox join(arg, ", ")
end sub

I wouldn't try to type the Populate argument as a string.  It is an array.  I'd leave it un-typed and let the interpreter figure out how to handle it.

---

Tom Lavedas

Answer 3

Hi Tom,

 I am not an expert VBA programmer. Do I need to provide the method for "Populate type" or is it the same as

Call Populate("type" & i) ? . Your code as it is, gives Sub or Function not defined error at the Execute line!

My actual requirement was that I need to pass an array of values to one worksheet at a time in each iteration, so that I dont have to repeat about 100 lines of code in the Populate Sub, for each worksheet and each array. To put in other words, each time I called Populate, the loop must go through one worksheet and one type of array.

Thanks for the responses anyway and spending your time.

Regards,

jjag

Answer 4

I'm sorry to say that that is what can be done in VBScript, but I now see that it is not supported in VBA.  The problem with it is that the Execute statement is not available in VBA.  So, you need to change your construct to be either an array of arrays or a two dimensional array.

Two dimensional array:

Sub Test()
  Dim atype(1, 1), arg(1)
  atype(0, 0) = 1
  atype(0, 1) = 2
  atype(1, 0) = 3
  atype(1, 1) = 4
  For i = 0 To UBound(atype, 1)
    For j = 0 To UBound(atype, 2): arg(j) = atype(i, j): Next
    populate arg
  Next
End Sub
Sub populate(arg)
  MsgBox Join(arg, ", ")
End Sub

Array of arrays:

Sub Test()
  Dim atype(1)
  atype(0) = Array(1, 2)
  atype(1) = Array(3, 4)
  For i = 0 To UBound(atype, 1)
    populate atype(i)
  Next
End Sub
Sub populate(arg)
  MsgBox Join(arg, ", ")
End Sub

---

Tom Lavedas

Answer 5

Hi Tom,

  I think I am nearly there with your valuable inputs. Eventhough I know the size of atype array, the arrays inside atype are all dynamic. So I cannot intialise atype as

atype(0) = Array(1, 2)
atype(1) = Array(3, 4)

My exact requirement is to store different arrays for different set of occurrences, all strings starting with "LI" in one array, all strings starting with "TI" in another array and so on. Then after finding the occurrences and storing them in different arrays, I can then pass these arrays in each iteration of the Populate Sub so that one set of values gets copied to one worksheet and another set of values gets copied to another worksheet and so on. The code for the search and storing arrays is as follows:

allAddres = Array("LI1", "LI2", "TI301015", "TI301016")
 
 i = 1
With Workbooks("Tags1").Sheets("DB")
 For j = 1 To 4
  For Each cell In .Range(.Cells(3, 1), .Cells(23, 1))
  If InStr(UCase(cell), allAddres(j)) Then
  ReDim Preserve types(i)
  types(i) = cell.Value
  atype(j) = types
  i = i + 1
  
  End If
  Next
 Next j

Right now I am getting output like:              atype(1) = Array("LI1")      

                                                                atype(2) = Array("LI1","LI2")

when my desired output is:                         atype(1) = Array("LI1","LI2")

                                                                atype(2) = Array("TI301015","TI301016") 

                                                                                                           
Thanks once again for the swift responses.

Answer 6

I'm still not certain I know what you are trying to accomplish, but this is the best that I can guess ...

Sub test()
Dim j, cell, allAddress, atype(1)
allAddress = Array("LI", "TI")
' 
With ActiveSheet
  For Each cell In .Range(.Cells(3, 1), .Cells(23, 1))
    For j = 0 To UBound(allAddress)
      If UCase(Left(cell, 2)) = allAddress(j) Then
        atype(j) = atype(j) & cell.Value & "|"
      End If
    Next j
  Next cell
'
  For j = 0 To 1
    atype(j) = Split(atype(j), "|")
  Next j
End With
  MsgBox "aType(0): " & Join(atype(0), ", ") & vbNewLine _
       & "aType(1): " & Join(atype(1), ", ")
End Sub

It assumes there is a list of data that you want to process in column A starting in the third row, something like this ...

LI1

LI2

TI301015

TI301016

TI301017

TI301018

TI301019

LI3

LI4

LI5

LI6

LI7

TI301020

TI301021

TI301022

TI301023

TI301024

TI301025

TI301026

TI301027

TI301028 HTH,

---

Tom Lavedas

Answer 7

If you don't know anything about an array - dimensions, types of members, initialised or Empty - then your best bet is a For... Each iteration:

Private Sub IterateArray(InputArray As Variant)
Dim varX As Variant
For Each varX In InputArray
     Populate varX  
     ' performs some operation on this variable -
     ' assuming it's passed byRef into 'Populate'
    
Next varX
End Sub

Note that the iterator works on a Variant - not a typed variable. You may need an explicit cast to pass it into Populate().

You *might* also have problems if InputArray was declared 'Dim InputArray() As String'  instead of  'Dim InputArray As Variant'  - typed arrays might not support the native iterator for variants.

After that, you're left using the limited type-checking capabilities of VBA, and the Lbound() / Ubound() functions. Share And Enjoy, as we say in Sirius Cybernetics.

Answer 8

Hi Tom,

Thanks for the masterclass. You saved me lot of trouble and time. In effect the following line:

atype(j) = atype(j) & cell.Value & "|" 

  instead of: 

  types(i) = cell.Value
  atype(j) = types 

provided the solution. Then I could loop through different arrays inside atype array which is what I needed exactly!

My code in question looks like this now, for anybody looking for help in similar issue:

Global alltypes(1 To 20)

For j = 1 To UBound(allAddress)
  If InStr(UCase(cell), allAddress(j)) Then
  alltypes(j) = alltypes(j) & cell.Value & "|"     'Array of diferent types arrays
  End If
Next j

For j = 1 To UBound(allAddress)
 alltypes(j) = Split(alltypes(j), "|")
Next j

For v = 1 To UBound(allAddress)
 Call Populate(alltypes(v))
Next v

Sub Populate(Adrs) 
'code here

Big thank you for all who have put in the effort and valuable time.

Regards,

jjag