martes, 14 de agosto de 2012 9:02
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?
type1, type2, type3 etc are my arrays
'Calling Sub For i = 1 to UBound(allAddress) Call Populate("type" & i) Next i
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!
- Editado jjag martes, 14 de agosto de 2012 9:05
Todas las respuestas
martes, 14 de agosto de 2012 10:17Moderador
"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)
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.
martes, 14 de agosto de 2012 19:56
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 subI 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.
jueves, 16 de agosto de 2012 10:33
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.
jueves, 16 de agosto de 2012 14:06
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
- Editado Tom Lavedas jueves, 16 de agosto de 2012 14:07
sábado, 18 de agosto de 2012 1:12
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.
martes, 21 de agosto de 2012 20:32
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
- Marcado como respuesta jjag martes, 11 de septiembre de 2012 9:46
viernes, 24 de agosto de 2012 12:56
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.
- Editado Nigel Heffernan viernes, 24 de agosto de 2012 12:57 Code formatting
martes, 11 de septiembre de 2012 10:09
Thanks for the masterclass. You saved me lot of trouble and time. In effect the following line:
atype(j) = atype(j) & cell.Value & "|"
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.