newbie needs help with collections
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 |
newbie needs help with collections
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 |
newbie needs help with collections
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 |
All times are GMT +1. The time now is 07:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com