none
Running VBA macros in C#

    Question

  • HI

    I've been looking for an answer in a lot of places, but I always get the response, and unfortunately this is not very helpful.

    What i would like to do is to run macros written in VBA (in a workbook, or worksheet, or module in excel) from an Excel C# add-in.

    I've been here http://support.microsoft.com/kb/306683/en-us and i have user the method RunMacro:

    private void RunMacro(object oApp, object[] oRunArgs)
    {
         oApp.GetType().InvokeMember("Run",
         System.Reflection.BindingFlags.Default |
         System.Reflection.BindingFlags.InvokeMethod,
         null, oApp, oRunArgs);
    }
    
    


    This works just fine only if the macro is written in a module in Excel; If the macro is written in a workbook or a worksheet, then it just cannot find the specified macro.

    What i would like to be able to do is run macros that are written in workbooks and worksheets apart from those in the modules.

    Can this be done? I guess it can, i just don't know how....Looking forward to a solution.

    Saturday, June 26, 2010 2:00 PM

Answers

  • I played around with this scenario a bit further. In addition to the options described earlier in this thread, it appears that you can actually use Application.Run to call a VBA macro you add to a Sheet or ThisWorkbook object in your workbook by qualifying the name of the macro that you pass to Application.Run with the name of the Sheet/ThisWorkbook object that contains it (I apologize if this is obvious knowledge to anyone reading this thread, but I didn't see this mentioned above). For example:

    Globals.ThisWorkbook.Application.Run("Sheet1.TestRun");

    This appears to work just fine for me for calling VBA macros in a Sheet or ThisWorkbook object. Basically, if the name of the macro listed in the Macros dialog box has a prefix before it, then it appears that you need to include that prefix when you specify the macro name in Application.Run.

    Interestingly, the same thing does not seem to be a requirement in Word. In Word, if I add a macro to the ThisDocument object, I can call that macro from my VSTO code without specifying the "ThisDocument." prefix - unless there are multiple macros with the same name in different objects (for example, ThisDocument.TestRun and Module1.TestRun)

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by CalinIlie Wednesday, June 30, 2010 12:22 PM
    Tuesday, June 29, 2010 5:53 PM

All replies

  • Hi CalinIlie

    the problem you're running into is that code in a worksheet or workbook is actually contained in a class object. IOW, it's going to be a method of the class. While code in a module is not hooked up to any particular object and thus "belongs" to the application. In C# terms you can think of it as being "static" - not belonging to any particular object instance.

    If you want to use code in a worksheet or workbook, you first need to instantiate an object and assign the worksheet or workbook to the object. Then you should be able to call any (public) Sub via that object. For example, in a VB.NET Add-in:

        Dim wb As Excel.Workbook
        For Each wb In Globals.ThisAddIn.Application.Workbooks
          If InStr(wb.FullName, "TestRunMacro") <> 0 Then
            wb.Sheets(1).TestRun() 'Works when TestRun is in the Sheet1 class
            'Application.Run "TestRunMacro.xslm!TestRun" 'Error if TestRun is in a class
          End If
        Next
    

     


    Cindy Meister, VSTO/Word MVP
    Monday, June 28, 2010 9:44 AM
  • HI,

    Thank you for the explanation. Now it makes sense. But the thing is that i cannot find a method in the Excel.Workbook class that calls a macro. I have noticed that Excel.Application has this method called "run" which runs the "global" macros in the module and as i said i couldn't find a similar one in the workbook class.

    I want to write this add-in in c# so do you have any other suggestions please?

    Tuesday, June 29, 2010 11:21 AM
  • Hi CalinIlie

    It does not matter whether you use C# or VB.NET. I just happened to have a VB.NET add-in at hand in which I could test. But it works the same way for both languages (or, indeed, any other language).

    The key is that a worksheet or workbook is a class object. "Public Sub" macros in the VBA-code-behind modules are METHODS of the class object. You must create a class object, and call the method. This means you must also know in which code-behind-class module the macro is.

    Some C# code to run a specific macro would look something like this (but I'm "translating" off the top of my head - not creating an Add-in and testing! So there could be small syntax errors.)

        Excel.Workbook wb = Globals.ThisAddIn.Application.Workbooks[1];
        wb.Sheets[1].TestRun(); //TestRun is the name of the Public Sub


    Cindy Meister, VSTO/Word MVP
    Tuesday, June 29, 2010 2:43 PM
  • Hi Cindy,

    It's actually not quite true that C# and VB.NET would be the same here. These calls to VBA methods are all late-bound, and VB.NET is doing the reflection for you behind the scenes. The code you show would fail if using Option Strict On. Equivalent C# code would also fail for C# 3.0 and below, but using the 'dynamic' keyword in C# 4.0 would probably work, but I've not tested.

    So explicit use of reflection code could be required, depending on the scenario.


    Mike Rosenblum, Excel MVP
    Tuesday, June 29, 2010 4:18 PM
  • I can confirm that if you're using C# and targeting the .NET Framework 4, you can use the dynamic keyword to call directly into a VBA macro defined in the Sheet1 object of the workbook.

    dynamic worksheetObj = Globals.Sheet1.InnerObject;
    worksheetObj.TestRun();

    If your macro is defined in the ThisWorkbook object or a different Sheet object, simply modify this code to call into the corresponding VSTO class (Globals.ThisWorkbook, etc.) as necessary.

    If you're targeting the .NET Framework 3.5 or earlier, here is an example of the reflection code you'd need to write to accomplish the same thing.

    Excel.Worksheet interopSheet = Globals.Sheet1.InnerObject;
    interopSheet.GetType().InvokeMember("TestRun", 
      System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod,
      null, interopSheet, null);
    Also note that if you're targeting Excel 2007 or later, the workbook must in a macro-enabled format (.xlsm, .xltm, .xls, etc.) and macros must be allowed to run (the workbook must be in a trusted location, or macros must be enabled in the Trust Center settings).
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, June 29, 2010 4:47 PM
  • I played around with this scenario a bit further. In addition to the options described earlier in this thread, it appears that you can actually use Application.Run to call a VBA macro you add to a Sheet or ThisWorkbook object in your workbook by qualifying the name of the macro that you pass to Application.Run with the name of the Sheet/ThisWorkbook object that contains it (I apologize if this is obvious knowledge to anyone reading this thread, but I didn't see this mentioned above). For example:

    Globals.ThisWorkbook.Application.Run("Sheet1.TestRun");

    This appears to work just fine for me for calling VBA macros in a Sheet or ThisWorkbook object. Basically, if the name of the macro listed in the Macros dialog box has a prefix before it, then it appears that you need to include that prefix when you specify the macro name in Application.Run.

    Interestingly, the same thing does not seem to be a requirement in Word. In Word, if I add a macro to the ThisDocument object, I can call that macro from my VSTO code without specifying the "ThisDocument." prefix - unless there are multiple macros with the same name in different objects (for example, ThisDocument.TestRun and Module1.TestRun)

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by CalinIlie Wednesday, June 30, 2010 12:22 PM
    Tuesday, June 29, 2010 5:53 PM
  • Ok that's great information...

    One last thing that I would add is that I would generally recommend calling the VBA routine directly (using reflection, if required) instead of using the Application.Run. The reason is that if the VBA method throws an exception when being executed via Application.Run, the user is shown the Visual Basic dialog box and the Application.Run method hangs, awaiting a response from the user. If you call the VBA routine directly, however, then any exceptions thrown are propagated back to the .NET caller, which can then handle the exception in whatever manner desired.


    Mike Rosenblum, Excel MVP
    Tuesday, June 29, 2010 6:02 PM
  •  

    I'm in .NET 4 using Excel 2010 in a VBA app with a variety of worksheets, class modules, etc....and am pushing VBA functionality down the stack to C# in order to speed things up. I can call in to C#, but calling back to VBA has been problematic.

    I've attempted both techniques (reflection and then use of the dynamic keyword). I'm calling a Sub in a Class Module from C# and am getting a runtime exception in both cases. This Excel App is already instantiated and running. I'm calling MarshalPortfolio from VBA to do some work (see call below) and I pass in the Excel instance name xlApp because I need to call back in to VBA to do additional work with the Sub. Example snippet below showing both branches.

    I'm betting I need to call the method on something other than the Excel Application instance (Worksheet, Workbook, etc...)? Guidance?

    Thanks.....


    VBA

    Public Sub DoKbTest()

       MsgBox "Run"

    End Sub

     

     

    C#

    public void RunMacro(object oApp, object[] oRunArgs)

            {

                oApp.GetType().InvokeMember("Run",

                    System.Reflection.BindingFlags.Default |

                    System.Reflection.BindingFlags.InvokeMethod,

                    null, oApp, oRunArgs);

            }

     

         public void MarshalPortfolio(object xlApp,ref Scripting.Dictionary PortfolioList,int MinTrials, int MaxTrials,double ExpectedValue,double Closeness,string CloseMetName, int CloseMetTime)

            {            

                iExcel.Application instance;

                instance = (iExcel.Application)xlApp;  

                instance.Visible = true;

     

                dynamic excelobj = null;

                excelobj = instance.Application;

        /* this branch throws a runtime exception saying System._ComObject does not contain a definition for DoKbTest

                excelobj.DoKbTest();

                /* this branch throws a runtime exception -2146232828

                this.RunMacro(instance, new Object[] { "DoKbTest" });


     

    Friday, January 21, 2011 11:54 AM
  • Sorry to answer myself. Upon closer re-read of the above thread, the reflection technique, dynamic keyword technique, and Application.Run technique are for calling Macros. I want to call a Sub in a Class Module from C#. As Ms. Meister states, Subs are methods of the Class. So, I need to do as her 29JUN post instructs regarding creating the class and then calling the method (Sub) on it.

     


    Saturday, January 22, 2011 9:26 PM
  • For the benefit of others.....I have learned that VBA Class Modules (different from Standard Modules) can have two levels of privacy when instantiating them: 1-Private and 2-PublicNotCreateable that are set to Private by default. Changing the property to PublicNotCreateable on my Class Module results in a compilation error.  Standard Modules do not have this issue. If you create a Standard Module in Excel VBA (Module1) that pops a message box, and then call the Sub with the C# code below it works just dandy. The URL at the bottom points to more detail on how you can use a Standard Module as a class factory for a Class Module, which I don't want to do. I want the current running instance because of all the members/objects it contains. This makes it -really- challenging to call back/forth between Excel VBA and a c# appendage. Is there another pathway?

     

    Public Sub MessageTest()

    MsgBox "Test"

    End Sub

     

     

    public void MarshalPortfolio(iExcel.Application xlApp)

            {

                Type ApplicationType = xlApp.GetType();

                ArrayList args = new ArrayList();

                args.Add("MyExcelSpreadsheet.Module1.MessageTest");

                ApplicationType.InvokeMember("Run", BindingFlags.Default | BindingFlags.InvokeMethod, null, xlApp, args.ToArray());

     

    Here's more: http://stackoverflow.com/questions/1731496/how-do-i-access-a-class-module-in-excel-vba-from-c  

    Monday, January 24, 2011 11:49 AM