My Blog

Calling Excel Macros programmatically in VB.Net

2

Let’s say we have the following Macros in Excel

Sub RunMe()
    MsgBox "Called from VB.net Client", vbInformation, "Demo to run Excel macros from VB.net"
End Sub

Sub ShowMsg(msg As String, title As String)
    MsgBox msg, vbInformation, title
End Sub

Let’s save the Excel File as Sample.xlsm to C:. If you notice the first macro doesn’t take an argument and the second one take 2 arguments. Let’s try and call that from vb.net.

Now open the a new project in VB.net and put two command buttons on it. Your project should look like this.

Now add a reference to Excel Object Library. Covered here (Section: Setting up VB.Net to Work with Excel).

Once you have the references set up, use this code:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        '~~> Define your Excel Objects
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook

        '~~> Start Excel and open the workbook.
        xlWorkBook = xlApp.Workbooks.Open("C:\book1.xlsm")

        '~~> Run the macros.
        xlApp.Run("RunMe")

        '~~> Clean-up: Close the workbook and quit Excel.
        xlWorkBook.Close(False)

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        '~~> Define your Excel Objects
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook

        '~~> Start Excel and open the workbook.
        xlWorkBook = xlApp.Workbooks.Open("C:\book1.xlsm")

        '~~> Run the macros.
        xlApp.Run("ShowMsg", "Hello from VB .NET Client", "Demo 2nd Button")

        '~~> Clean-up: Close the workbook and quit Excel.
        xlWorkBook.Close(False)

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
    End Sub

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class
Snapshots
  • Button 1
  • Button 2

Calling Excel macros from vb.net is easy as you must have discovered by now.

  1. dlmille
    dlmille03-23-2012

    Nice.

    Recently, I had occasion to use the OnKey method from VB.Net and was disappointed that I couldn’t call a VB.Net function directly using this not so great “feature”. What I had to do was have a VBA routine in Excel that VB.Net would call as a result of the OnKey method.

    Or, is there a better way to do this?

    Dave

  2. Ashwin
    Ashwin06-11-2014

    You are amazing mate – thanks a lot for this! Very little online about how to create macros on an excel workbook using vb .net. Lots of people saying “no you can’t do it”. You saved my day. Cheers.