ParamArray warning

[Grok]

I ran across an interesting bug in my code, caused by my not paying attention to the details of what I was doing.  There was a function which was written to pass a stored procedure name, and a variable number of parameters to that function.  It was this functions job to connect to the database and execute the stored procedure, passing in each entry from the ParamArray as string parameters to the stored procedure.  Such as:

Code Select Expand

'abbreviated for clarity
Public Function GetRsSp(ByVal spName As String, ParamArray Args() As Variant) As ADODB.Recordset
   SQL = " '" & Join(Args, "', '") & "'"
   SQL = spName & " " & SQL
   Call Connect(conn)
   Set rs = New ADODB.Recordset
   rs.CursorLocation = adUseClient
   rs.Open SQL, conn, adOpenStatic, adLockReadOnly
   Set rs.ActiveConnection = Nothing
   Set GetRsSp = rs
   Call Disconnect(conn)
End Function

Called using this syntax, and it works great.

Code Select Expand


   'call with no parameters
   Set GetCabinets = GetRsSp("kiGetCabinets")

   'call with one parameter
   Set rs = GetRsSp("kfGetDocToIndex", SrcId)


The problem occurred when I tried to call it with an unknown number of parameters.  That is to say, even I did not know how many parameters I was passing.  This happened because my design was database-driven, for an open design, and the user might write a stored procedure with several parameters.  I parse the string, getting the spname and list of parameters, join them into an array, then tried calling GetRsSp(spname, myarray) as such:

Code Select Expand

   spName = Split(domaincheck, " ")(0)
   parms = Split(domaincheck, " ", 2)(1)
   myarray = Split(parms,",")
   Set rs = GetRsSp(spName, myarray)  


Doing this, I kept getting Error 13-Type Mismatch in GetRsSp!  Very annoying.  The problem here was myarray is indeed an array, so when passed to a ParamArray in GetRsSp, it actually became..... the first element in the ParamArray!  Thus, VarType(Args(0)) returned 8204.  Unfortunately I did not have time to rewrite everything, so I had to loop through Args looking for IsArray() of each element, then appending each element to a string, finally converting everything back to a string array!  Whew.

[Adron]

A related problem that I had: Can you have a variable number of arguments function call another function with the arguments you received (i.e. pass an unknown number of arguments on to another function)?