How do I access a class module in Excel VBA from C#?

I have an Excel add-in w开发者_如何学Cith a class module. I want to instantiate the class module in C# and call a method on it. How do I do that?

If you really need access to an instance of the class, you could do the following:

• Generate a type library for a COM interface that you want to expose from your VBA class (e.g. IMyComInterface)

• Add a reference to this type library in your VBA project

• Implement the interface in your VBA class module - e.g. MyVbaClass (use the Implements keyword):

  Option Explicit
  Implements IMyComInterface
  
  Private Sub IMyComInterface_SomeMethod(...)
      ...
  End Sub
  ...
  

• Reference the same type library in your C# project

• Create a ComVisible C# class with a method that accepts a reference to the VBA interface instance. Something like:

  public class MyVbaLoader
  {
      public IMyComInterface MyComInterface
      {
          get { return myComInterface; }
          set { myComInterface = value; }
      }
  }
  

• Write a "factory" method in a VBA standard module, that takes an object as a ByRef parameter. This object should assume the object passed as an argument has a property "MyComInterface" and should set this property to a new instance of the VBA class MyClass.

  Public Sub MyFactoryMethod(MyVbaLoader As Object) 
      Dim objClass As MyVbaClass
      Set objClass = New MyVbaClass
      ... any initialization of objClass here ...
  
      ' Pass a reference to the VBA class to the C# object MyVbaLoader
      MyVbaLoader.MyComInterface = objClass
  End Sub
  

• Call the factory method from your C# code. Assuming you have opened the workbook and have a refence "workbook" in your VBA code, the code will look something like:

  MyVbaLoader loader = new MyVbaLoader();
  workbook.Application.Run("MyModule.MyFactoryMethod", loader, Type.Missing, ... , Type.Missing);
  // we now have a reference to the VBA class module in loader.MyComInterface
  // ...
  

As you can see, it's rather complex. Without more detail of the problem you're trying to solve it's difficult to say whether this complexity is justified, or whether there's a simpler solution.

If the above isn't clear, let me know and I'll try to clarify.

Basically you can't return a value from a VBA macro called from your C# code using Application.Run, so you have to resort to passing an object by value that has a method or property that can be called from VBA to set the instance.

VBA class modules have only two instancing modes: private, and public-not-creatable. So, you can't even instantiate them in another VB(A) project, let alone from C#.

However, there's nothing to stop you having a standard module that acts as a class factory. So, if your class module is Foo then you can have a method in a standard module called NewFoo that instantiates a new Foo for you and returns it to the caller. The Foo object would obviously have to be public-not-creatable.

[Your NewFoo method can take parameters, so you can simulate parameterized constructors, which aren't available in VBA.]

EDIT: detail on how to call VBA function (in a standard module) from C# and get the return value using Application.Run.

private static object RunMacro(Excel.Application excelApp, string macroName, object[] parameters)
{
    Type applicationType = excelApp.GetType();

    ArrayList arguments = new ArrayList();

    arguments.Add(macroName);

    if (parameters != null)
        arguments.AddRange(parameters);

    try
    {
        return applicationType.InvokeMember("Run", BindingFlags.Default | BindingFlags.InvokeMethod, null, excelApp, arguments.ToArray());
    }
    catch (TargetInvocationException ex)
    {
        COMException comException = ex.InnerException as COMException;

        if (comException != null)
        {
            // These errors are raised by Excel if the macro does not exist

            if (    (comException.ErrorCode == -2146827284)
                ||  (comException.ErrorCode == 1004))
                throw new ApplicationException(string.Format("The macro '{0}' does not exist.", macroName), ex);
        }

        throw ex;
    }
}

Note that you could omit all that try...catch stuff - all it's doing is handling the specific error where the macro does not exist and raising a more meaningful exception in that case.

The object returned from the function can be cast to whatever type you need. For example:

object o = RunMacro(excelApp, "MyModule.MyFunc", new object[] { "param1", 2 });
if (o is string)
{
    string s = (string) o;
    Console.WriteLine(s);
}

Assuming that the function actually returns an instance of your VBA-defined class object, then you can call the methods of that object in the same way, again using InvokeMember:

object o = RunMacro(excelApp, "MyModule.MyFunc", new object[] { "param1", 2 });

// assume o is an instance of your class, and that it has a method called Test that takes no arguments
o.GetType().InvokeMember("Run", BindingFlags.Default | BindingFlags.InvokeMethod, null, o, new string[] {"Test"});

If you're doing a lot of these calls, then obviously you can hide the ugly detail by creating wrapper methods to make the call for you.

Please note that I've typed most of this from memory, so I fully expect there to be syntax, logical and possibly even factual errors :-)