Hi,

I am having some trouble with collections. I'm trying to pass a
collection to a function to do some work, but whenever I get a compile
error "argument not optional". Everything seems to look ok, I've tried
a lot of variations on this, here is my code:


Code:
--------------------


Dim results As New Collection

' code here to get results

Dim topResults As New Collection

' call get top results function
topResults = getTopResults(results) ' fails here


' top results function
Public Function getTopResults(ByRef list As Collection) As Collection

Dim maxByProgram As Collection
Dim topResults As Collection

maxByProgram = getMaxByProgram(list)

Dim i As Integer
For i = 0 To list.Count
Dim result As Score
Dim Program As String

result = list(i)
Program = Score.Program

Dim max As Integer
max = maxByProgram.Item(Program).Score

If Score.Score = max Then
topResults.Add result
End If
Next i

getTopResults = topResults

End Function

--------------------


Any help on this would be much appreciated.


--
rozner
------------------------------------------------------------------------
rozner's Profile: http://www.excelforum.com/member.php...o&userid=25794
View this thread: http://www.excelforum.com/showthread...hreadid=392006

rozner,

This will only get you started...
'-------------------------
Sub NewsGroupTest()
Dim results As Collection
Dim topResults As Collection

Set results = New Collection
' code here to get results

' call get top results function
Set topResults = getTopResults(results)

Set results = Nothing
Set topResults = Nothing
End Sub
'-------------------------

Jim Cone
San Francisco, USA



"rozner" wrote in
message ...
Hi,
I am having some trouble with collections. I'm trying to pass a
collection to a function to do some work, but whenever I get a compile
error "argument not optional". Everything seems to look ok, I've tried
a lot of variations on this, here is my code:
Code:
--------------------
Dim results As New Collection

' code here to get results

Dim topResults As New Collection

' call get top results function
topResults = getTopResults(results) ' fails here


' top results function
Public Function getTopResults(ByRef list As Collection) As Collection

Dim maxByProgram As Collection
Dim topResults As Collection

maxByProgram = getMaxByProgram(list)

Dim i As Integer
For i = 0 To list.Count
Dim result As Score
Dim Program As String

result = list(i)
Program = Score.Program

Dim max As Integer
max = maxByProgram.Item(Program).Score

If Score.Score = max Then
topResults.Add result
End If
Next i

getTopResults = topResults

End Function

--------------------
Any help on this would be much appreciated.
rozner

Untested--too many functions don't exist for me (getMaxByProgram for example).

But this made it by the error:

Set topResults = getTopResults(results)

Collections are objects. To assign objects, you need to use the Set keyword.

For example:
dim rng as range
set rng = range("a1:B9")

rozner wrote:

Hi,

I am having some trouble with collections. I'm trying to pass a
collection to a function to do some work, but whenever I get a compile
error "argument not optional". Everything seems to look ok, I've tried
a lot of variations on this, here is my code:

Code:
--------------------


Dim results As New Collection

' code here to get results

Dim topResults As New Collection

' call get top results function
topResults = getTopResults(results) ' fails here


' top results function
Public Function getTopResults(ByRef list As Collection) As Collection

Dim maxByProgram As Collection
Dim topResults As Collection

maxByProgram = getMaxByProgram(list)

Dim i As Integer
For i = 0 To list.Count
Dim result As Score
Dim Program As String

result = list(i)
Program = Score.Program

Dim max As Integer
max = maxByProgram.Item(Program).Score

If Score.Score = max Then
topResults.Add result
End If
Next i

getTopResults = topResults

End Function

--------------------

Any help on this would be much appreciated.

--
rozner
------------------------------------------------------------------------
rozner's Profile: http://www.excelforum.com/member.php...o&userid=25794
View this thread: http://www.excelforum.com/showthread...hreadid=392006

--

Dave Peterson