How can I unpack a VBA variant into multiple arguments to ParamArray?

0
votes

I have a VBA function that will execute parameterized SQL.

Public Function Execute(connection As ADODB.connection, ByVal sql As String, ParamArray parameterValues()) As ADODB.Recordset
    'Returns a connected ADODB.Recordset that contains the results of the specified parameterized query.

    Dim parameters() As Variant
    parameters = parameterValues
    
    Set Execute = ExecuteInternal(connection, sql, parameters)
    
End Function

The number of parameters can vary at runtime, and I construct the sql string dynamically using a selected range. An example sql string is:

DELETE FROM my_table WHERE id NOT IN (?, ?, ?)

I wish to pass the values in the range to the function also, but of course the naive approach of passing the range.value to the Execute function doesn't cause the range to be unpacked.

With New SqlCommand
    Set rs = .Execute(con, sql, myRange.value)
End With

How can I unpack the variant into the ParamArray? I'm basically looking for the Python * unpack operator.

Forgive me if it's a stupid question, but VBA is not my primary language.

2
excelvba
This is not going to work. When calling a procedure in VBA that has a ParamArray, the line that makes the call does NOT pass an array. It passes one or more discreet arguments (as the last arguments) and VBA does the work (behind the scenes) of bundling these special arguments as an array. Your code cannot do the same. You are passing ONE argument that happens to be an array. To get the effect that you want while using the ParamArray, your calling code would need to be something like this: Set rs = .Execute(con, sql, myRange1.Value, myRange2.Value, myRange3.Value) – Excel Hero
Thanks @ExcelHero, that's the conclusion I'd come to also. If you want to add this as an answer I'll accept. – MarkNS

2 Answers

1
votes

When calling a procedure that accepts a ParamArray in VBA, the line that makes the call does NOT actually pass an array. It passes one or more discreet arguments (as the last arguments) and VBA does the work (behind the scenes) of bundling these special arguments as the ParamArray.

Your code cannot do the same. You are passing ONE argument that happens to be an array, and behind the scenes, VBA assigns your entire array as the one and only element in the ParamArray.

To get the outcome that you want, your calling code would need to be something like this (assuming that each range is just one cell):

Set rs = .Execute(con, sql, myRange1.Value, myRange2.Value, myRange3.Value)
0
votes

You could try this. This basicly "unpacks" the paramArray into the paramters of the Command Object.

Public Function Execute(connection As ADODB.connection, ByVal sql As String, ParamArray parameterValues()) As ADODB.Recordset
    'Returns a connected ADODB.Recordset that contains the results of the specified parameterized query.

    Dim i As Long
    Dim Cm As ADODB.Command
    Set Cm = New ADODB.Command
    Cm.AcctiveConnection = cn
    Cm.CommandType = adCmdText
    Cm.CommandText = sql

    For i = LBound(parameterValues) To UBound(parameterValues
       Cm.Parameters.Append Cm.CreateParameter("Param", adBigInt, adParamInput, 0, parameterValues(i))
    Next i
    
    Set Execute = Cm.Execute
    Set Cm = Nothing
End Function