I have an excel workbook that runs some VBA code when a button is clicked. The function i want to call is shown below. I want to call the function from Visual Studio using VB. Is there any way of making an instance of the workbook and calling the function? (Excel Add-ins are needed for the function which is why I need the workbook.) Thanks in advance guys.
Private Sub CommandButton2_Offline_Click()
Wanting to invoke a Click event handler means one thing: you're looking at a macro that's implemented in the event handler itself.
Move it out. Handlers are invoked by their event source; invoking them "manually" is terrible practice; you don't want to do that. Not to mention handlers can only exist in a class module, which means you need to get ahold of an object instance of that class in order to be able to locate its public members - its private members will be even more complicated to get to. If you're lucky the class is a worksheet module, so you get a workbook-scope instance of it for free.
In any case, the last thing you want to do is "just make the handler Public" and invoke it directly. Refactor instead:
Private Sub CommandButton2_Offline_Click()
GoOffline
End Sub
Ideally you'd put the method in a standard module:
Public Sub GoOffline()
'...
' all the code that's currently in CommandButton2_Offline_Click
'...
End Sub
That way all you need to do is invoke Application.Run with "ModuleName.GoOffline" as an argument, from your .NET code.
As for making an instance of the workbook, there's a factory method for that: Workbooks.Open - your .NET code will need to have an Excel.Application instance to work with.