Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with passing collection as parameter
I am trying to pass a collection to a sub but I keep getting thi error: Compile Error: Argument not optional The collection is a collection of strings filled like this: For i = 0 To finalrow - 3 assyNums(i) = Range("C" & i + 2) Next i I use this command to call the sub: "Matching (assyNums)" where Matching is the sub and, assyNums is collection. The Sub looks like this: "Private Sub Matching(assyNums)" - OR - (if i add optional) "Private Sub Matching(Optional assyNums)" Inside the sub all i really do, is a for loop that accesses ever element, so like "For i = 0 To assyNums.Count" Any help would be greatly appreciated -- McManCS ----------------------------------------------------------------------- McManCSU's Profile: http://www.excelforum.com/member.php...fo&userid=2437 View this thread: http://www.excelforum.com/showthread.php?threadid=39181 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with passing collection as parameter
The construct you describe is an array, not a collection.
this worked for me: Sub AB() Dim assyNums() As String Dim finalrow As Long finalrow = Cells(Rows.Count, 3).End(xlUp).Row ReDim assyNums(0 To finalrow - 1) For i = 0 To finalrow - 3 assyNums(i) = Range("C" & i + 2) Next i Matching assyNums End Sub Private Sub Matching(assyNums() As String) For i = LBound(assyNums) To UBound(assyNums) Debug.Print i, assyNums(i) Next End Sub -- Regards, Tom Ogilvy "McManCSU" wrote in message ... I am trying to pass a collection to a sub but I keep getting this error: Compile Error: Argument not optional The collection is a collection of strings filled like this: For i = 0 To finalrow - 3 assyNums(i) = Range("C" & i + 2) Next i I use this command to call the sub: "Matching (assyNums)" where Matching is the sub and, assyNums is collection. The Sub looks like this: "Private Sub Matching(assyNums)" - OR - (if i add optional) "Private Sub Matching(Optional assyNums)" Inside the sub all i really do, is a for loop that accesses every element, so like "For i = 0 To assyNums.Count" Any help would be greatly appreciated! -- McManCSU ------------------------------------------------------------------------ McManCSU's Profile: http://www.excelforum.com/member.php...o&userid=24379 View this thread: http://www.excelforum.com/showthread...hreadid=391811 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with passing collection as parameter
Ok, well youre right on that, but I want to be able hold user create objects in a collection. What would i have to change to make tha happen? Can I hold user created objects in an array -- McManCS ----------------------------------------------------------------------- McManCSU's Profile: http://www.excelforum.com/member.php...fo&userid=2437 View this thread: http://www.excelforum.com/showthread.php?threadid=39181 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error with passing collection as parameter
Sub AB()
Dim assyNums() As Variant Dim finalrow As Long finalrow = Cells(Rows.Count, 3).End(xlUp).Row ReDim assyNums(0 To finalrow - 1) For i = 0 To finalrow - 3 set assyNums(i) = Range("C" & i + 2) Next i Matching assyNums End Sub Private Sub Matching(assyNums() As String) For i = LBound(assyNums) To UBound(assyNums) Debug.Print i, assyNums(i).Address(external:=True) Next End Sub so you can hold objects in a variant array or an array dimensioned the same type as the object or using the generic object type. for a collection ( I use a Range object here, but you should be able to use a user created object I would think Sub AC() Dim AssyNums As New Collection For i = 1 To 10 AssyNums.Add Cells(i, 3), Cells(i, 3).Text Next Matching1 AssyNums End Sub Private Sub Matching1(AssyNums As Collection) For Each itm In AssyNums Debug.Print itm.Value Next End Sub "McManCSU" wrote in message ... Ok, well youre right on that, but I want to be able hold user created objects in a collection. What would i have to change to make that happen? Can I hold user created objects in an array? -- McManCSU ------------------------------------------------------------------------ McManCSU's Profile: http://www.excelforum.com/member.php...o&userid=24379 View this thread: http://www.excelforum.com/showthread...hreadid=391811 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a Sub's name as parameter | Excel Programming | |||
Passing parameter to a query | Excel Programming | |||
?Passing argument/parameter | Excel Programming | |||
?Passing argument/parameter | Excel Programming | |||
Passing a parameter to Excel | Excel Programming |