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

Question

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.

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
– Excel Hero, Commented Nov 11, 2020 at 21:11
Thanks @ExcelHero, that's the conclusion I'd come to also. If you want to add this as an answer I'll accept. — MarkNS
– MarkNS, Commented Nov 12, 2020 at 11:04

marc_s · Accepted Answer · 2021-03-17 05:54:54Z

1

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)

edited Mar 17, 2021 at 5:54

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 12, 2020 at 17:02

Excel Hero

14.8k4 gold badges35 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MGP · Accepted Answer · 2020-11-12 19:32:30Z

0

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

edited Nov 12, 2020 at 19:32

answered Nov 12, 2020 at 17:17

MGP

2,6732 gold badges22 silver badges35 bronze badges

1 Comment

MarkNS Over a year ago

thanks @MG92, but I was trying to get a variant range into the ParamArray (which is the signature of a library I'm using), rather than unpack the ParamArray itself.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related