I have an Excel add-in with a class module. I want to instantiate the class module in C# and call a method on it. How do I do that?
0
Add a comment
|
2 Answers
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 SubCall 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.
2 Comments
Gary McGill
Nice answer, but in fact you can return a value from the VBA macro - see my answer for details. That would avoid the only ugly bit of your otherwise nice solution.
robodude666
Note: The VBA class has to be marked as
PublicNotCreatable or else you'll get an exception related to HRESULT 0x800A0062.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 :-)
4 Comments
to StackOverflow
But you still don't have a type library to generate the runtime callable wrapper (RCW)
Gary McGill
True, but you could invoke the methods of the class directly using InvokeMember. Not pretty or pleasant, but still possibe. If there's actually only one member you want to call, then it's easier than doing all the hook-up required in your (frankly excellent) answer.
to StackOverflow
+1, I wasn't aware that you could call a VBA Function from Application.Run and return a result. I'll rework my answer to take advantage of this. That leaves the OP with two solutions - implementing an interface from a type library in the VBA code from my answer, or accessing the VBA class members using reflection as in your response.
Rishabh Jain
minor correction to last step, use following: o.GetType().InvokeMember("Test", BindingFlags.Default | BindingFlags.InvokeMethod, null, o, new string[] {});