title: VBA ADODB parameter as dynamic Excel Range
body: <p>I'm trying to pass a list of parameters to a SQL query in VBA, I have an excel table with a list of codes in a range that is dynamic, could 2, could be 2000. I want to then return all data from SQL where the codes match. Below I'm trying to define an ADODB parameter as an array, then I want to pass that to SQL.</p>
  <p>However defining the parameter variable as a range gives a type mismatch. Or Object required if I don't define as an array;</p>
  <pre><code>Dim conn As New ADODB.Connection
  conn.Open &quot;Provider=SQLOLEDB; Data Source=XXXXXXXXX; Initial Catalog=CDB; Integrated Security=SSPI;&quot;
  Dim code(0) As ADODB.Parameter
  Set code(0) = wb.ActiveSheet.Range(Cells(2, colCode), Cells(rowCount, colCode)).Value
  'Dim code As ADODB.Parameter
  'Set code = wb.ActiveSheet.Range(Cells(2, colCode), Cells(rowCount, colCode)).Value
  'Dim code(rowCount - 1) As ADODB.Parameter
  'Set code(rowCount - 1) = wb.ActiveSheet.Range(Cells(2, colCode), Cells(rowCount, colCode)).Value
  Set cmd = New ADODB.Command
  Set rs = New ADODB.Recordset
  With cmd
      .ActiveConnection = conn
      .CommandType = adCmdText
      .Parameters.Append .CreateParameter(&quot;@code&quot;, adVarChar, adParamInput, 10000, code)
      .CommandTimeout = 0
  End With
  
  sql = &quot;SELECT * FROM table WHERE code in (?);&quot;
  cmd.CommandText = sql
  Set rs = cmd.Execute
  </code></pre>