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

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

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

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