Execute an Office Macro from a .NET Application

I have a pretty extensive macro written in Excel VBA.  I had a need to call this macro from a .NET application.  Before I executed the VBA macro, I needed to set a couple of combobox controls located on the worksheet.

 

Most of the solutions found on the Internet referred to using VSTO (Visual Studio Tools for Office).  One article I came across (http://support.microsoft.com/kb/306683) explained how to setup a .NET procedure called RunMacro that would execute a VBA macro stored in an Office application.  The article demonstrates that this same RunMacro procedure could be used to call an Excel Macro or Word, Accesss, or PowerPoint.

 

My solution was to bring the RunMacro code from the above article into my .NET application.  Next, in my Excel VBA code, I added a proxy procedure that would receive arguments used to set the combobox controls and then execute the macro.

 

My proxy procedure in VBA Code was similar to:

Public Sub MyProxyProcedure(MyArgOne As String, MyArgTwo As String)
    Sheets("Sheet1").cboOne.Value = MyArgOne
    Sheets("Sheet1").cboTwo.Value = MyArgTwo
    Call MyVBAProcedure
End Sub

 

Public Sub MyVBAProcedure

    'DO SOMETHING HERE

End Sub

 

My .NET Code to execute the VBA macro was similar to:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

' requires .net reference to Microsoft.Office.Interop.Excel
DIM oExcel as New Microsoft.Office.Interop.Excel.Application
DIM oWB as Microsoft.Office.Interop.Excel.Workbook
oWB = oExcel.Workbooks.Open("c:\MyExcelWorkbook.xls")
RunMacro(oExcel, New Object() {"MyProxyProcedure", _
   strArgOne, strArgTwo})

End Sub

Private Sub RunMacro(ByVal oApp As Object, _
                                 ByVal RunArgs As Object())
  oApp.GetType().InvokeMember("Run", _
    Reflection.BindingFlags.Default Or _
    Reflection.BindingFlags.InvokeMethod, _
    Nothing, oApp, oRunArgs)

End Sub

 

Now I am able to make use of the VBA Code I have invested and integrate into new .NET applications.