title: Calling VBA functions with varying parameter counts from Powershell
body: <p>I'm trying to make a Powershell script that can call any Excel macro. This works, as long as I know the amount of parameters beforehand. If I try to make this generic by using all 30 of the <a href="https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel._application.run" rel="nofollow noreferrer">Microsoft.Office.Interop.Excel.Application.Run</a> method parameters, I run into the following error message: <strong>Exception calling "Run" with "31" argument(s): "Number of parameters specified does not match the expected number."</strong></p>
  
  <p>Here's my attempt at this so far, starting with the contents of the .ps1 file:</p>
  
  <pre class="lang-bsh prettyprint-override"><code>param (
      [Parameter()][ValidateNotNullOrEmpty()][string]$excel_file_location,
      [Parameter()][ValidateNotNullOrEmpty()][string]$macro_name,
      [Parameter()][object[]]$macro_parameters
  )
  
  $par = (,$null) * 30
  
  if($macro_parameters) {
      for ($i = 0; $i -lt $macro_parameters.Count -and $i -lt $par.Count; $i++) {
          $par[$i] = $macro_parameters[$i]
      }
  }
  
  $excel_app = New-Object -ComObject Excel.Application
  $excel_app.Workbooks.Open($excel_file_location)
  $excel_app.Run($macro_name, $par[0], $par[1], $par[2], $par[3], $par[4], $par[5], $par[6], $par[7], $par[8], $par[9], $par[10], $par[11], $par[12], $par[13], $par[14], $par[15], $par[16], $par[17], $par[18], $par[19], $par[20], $par[21], $par[22], $par[23], $par[24], $par[25], $par[26], $par[27], $par[28], $par[29])
  $excel_app.Quit()
  </code></pre>
  
  <p>The Excel VBA file for testing purposes is a blank Excel 2016 .xlsm file with this macro:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Sub DoTheThing(x As String)
      Cells.Select
      Selection.Style = "Good"
      Range("A1").Select
      ActiveCell.FormulaR1C1 = x
      ActiveWorkbook.SaveAs Filename:="C:\Temp\macrotest2.xlsm", _
          FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
  End Sub
  </code></pre>
  
  <p>The command that results in the error (where ExecuteExcelVBA.ps1 and macrotest.xlsm are the described files above):</p>
  
  <pre class="lang-bsh prettyprint-override"><code>ExecuteExcelVBA.ps1 macrotest.xlsm DoTheThing (,"A1 value")
  </code></pre>
  
  <p>If I change the $excel_app.Run line to remove all the parameters after <code>$par[0]</code>, it works.</p>
  
  <p>Can I change my $par array values to some default that will have Excel ignore those parameters?</p>
  
  <p>Comments on inefficiencies are welcome too.</p>