How to pass an array of collection object to a function?

How to pass an array of collection object to a function? Also i want to know how u have to design the function which is going to accept this parameter. I have tried with ByRef argument but it is giving an error 'Object variable or with block variable not set' when i try to access the parameter.

I am giving the code:

'##ModelId=3897F4F2009C
public Function UpdateUnit(byref colValues() as Collection) as string
on error GoTo UpdateUnit_Err
Dim i%

for i = LBound(colValues) to UBound(colValues)
MsgBox colValues(i).Item(1)
next

Exit Function

UpdateUnit_Err:
RaiseError Err.Number, Err.Source, etError, Err.Description
End Function




I am calling this function like this:


private Sub TransferData_Unit()

strSql = objCUtil.StrConv(LoadResString(4001), ":Mandant", colParams("Mandant"))

rsPps.Open strSql, cnnClient, adOpenStatic, adLockOptimistic

If Not rsPps is nothing then
With rsPps
If .RecordCount <> 0 then
objProg.Info_Tip1 = LoadResString(1503)
objProg.Min = 0: objProg.Max = .RecordCount

ReDim colFieldVals(.RecordCount) as new Collection

Do While Not .EOF
'Incrementing progress dialog
objProg.Value = .AbsolutePosition

'adding relevent values to the collection object
colFieldVals(.AbsolutePosition).Add !me_von, "me_von"
colFieldVals(.AbsolutePosition).Add !bez_von, "bez_von"
'Updating rececords to incoplan
.MoveNext
Loop

'Now saving data to incoplan
objIncoPlan.UpdateUnit colFieldVals()
End If

End With

End If

End Sub




Thanks in advance


Initiative is to success what a lighted match is to a candle...

The problem you were having (at least from a quick look at your code), was that you were not initializing the collections in the array before using them (and the line 'ReDim colFieldVals(.RecordCount) as new Collection' would not compile anyway).

If you want to try and pass the array, it's quite simple, eg:


'
Dim oCollections() as Collection
Dim i as Integer
'
ReDim oCollections(5)
'
for i = LBound(oCollections) to UBound(oCollections)
'
' Initialise each collection object
'
set oCollections(i) = new Collection
next
'
DoSomething oCollections()
'

private Sub DoSomething(oCollobject() as Collection)
Dim i as Integer
'
for i = LBound(oCollobject) to UBound(oCollobject)
'
' check referenced collection to see if it's been created/initialised
Debug.print oCollobject(i) is nothing
next
'
End Sub





Chris Eastwood

CodeGuru - the website for developers
http://codeguru.developer.com/vb

I myself found out the bug out of the code.
The bug was simple, i was using '!' mark for filling the data inside the collection,
ie like colFieldVals(i).Add !me_von,....
This '!' mark was filling lot of junk values inside the collection object. Then i changed the method like .Fields("me_von").value, then it works. Values are properly going and the function declaration for receiving collection array is also working.

Initiative is to success what a lighted match is to a candle...

I myself found out the bug out of the code.
The bug was simple, i was using '!' mark for filling the data inside the collection,
ie like colFieldVals(i).Add !me_von,....
This '!' mark was filling lot of junk values inside the collection object. Then i changed the method like .Fields("me_von").value, then it works. Values are properly going and the function declaration for receiving collection array is also working. Strange isn't it !!!



Initiative is to success what a lighted match is to a candle...