Call VSTO Excel Plugin from Macro Button VBA

55 views Asked by At

I created my VSTO Excel Plugin and want to call this from VBA Macro Button. But I am facing an issue while clicking on the button

Error: Object variable or with block variable not set.

I add this in my ThisAddin file

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    refreshConnection = new RefreshConnection();
    Application.COMAddIns.Item("ProgID").Object = refreshConnection;
}

In place of progID I used my ID I created.

And in another File where my Excel plugin Ribbon button class is there which consist of my excel plugin button click [ComVisible(true)] [ClassInterface(ClassInterfaceType.None)] this I included before my class.

Below is my VBA macro module code

Sub Button1_Click()

    On Error GoTo ErrorHandler
    Dim addIn As COMAddIn
    Dim automationObject As Object

    Set addIn = Application.COMAddIns("ProgId")
    Set automationObject = addIn.Object

    automationObject.RefreshWorkbookConnection
    Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description
End Sub

Please provide me the step-by-step implementation how can I achieve this.

0

There are 0 answers