Calling VBA method of loaded addin in PowerPoint

Nov 9, 2012 at 2:20 PM

Hi all!

First of all, thanks a lot for this fantastic library! I have been working (and fighting) with interop calls to MS Office via .Net for so many years and now it's so easy to work with it.

I have a question concerning a problem which occurred today when I was trying to access a VBA method inside an addin.

The application is loaded like this, also the addin is loaded:

var app = new Application();
var addin = app.AddIns.Add("addinFileName.ppam");
addin.AutoLoad = MsoTriState.msoFalse;
addin.Loaded = MsoTriState.msoTrue;

When I set the visibility of the PowerPoint to true, I can see the addin is loaded properly.
Next, I open a presentation (I don't add the code because this is not important).
And now, I try to access a test method in the addin like this:

var result = app.Run("addinFileName.ppam!TestAddin", "test");

Before executing this code I tested the way I access the macro code (which is a simple public function expecting a string and returning also a string) inside the PowerPoint VBA editor directly....this works.

When I execute the above C# code I always get a COM exception saying:

"Sub or function not defined"

Does anybody out there have ever tried to access a macro of an addin with NetOffice?

Thanks a lot in advance.

 

Best regards,

Thomas

 

 

 

 

 

Nov 9, 2012 at 2:37 PM
Edited Nov 9, 2012 at 2:38 PM

Sorry, I put wrong code here:

The code to call the addin methods looks like this:

 

var result = app.Run("addinFileName.ppam!TestAddin", new object[] {"test"});

 

And also I found out, that calling a method without parameters work! Also the return value is available!

But methods with a parameter fail.

Just one thought from my side:

If the past I was working with a SafeComWrapper code here

http://www.codeproject.com/Articles/10888/SafeCOMWrapper-Managed-Disposable-Strongly-Typed-s

It had exactly the same problem!

I was able to solve this issue by adjusting the way parameters are passed to the interop object.

The problem could be inside the NetOffice implementation that the parameters I add to be passed to the VBA method will be added to the interop object itself!

What do you think?

Thanks and best regards,

Thomas

Nov 9, 2012 at 3:38 PM

I have checked the code and can offer a workaround (but I think you should make it cleaner):

The class NetOffice.Invoker.cs contains the MethodReturn method expecting the parameters.
There I have added an additional parameter (isRunMacroMethod) which should be set in the _Application.Run method.
Besides, I check if the parameters are available and contain both the VBA method name and the object array with the parameters.
 public static object MethodReturn(COMObject comObject, string name, object[] paramsArray, bool isRunMacroMethod = false)
        {
            try
            {
                if (comObject.IsDisposed)
                    throw new InvalidComObjectException();

                if ((Settings.EnableSafeMode) && (!comObject.EntityIsAvailable(name, SupportEntityType.Method)))
                    throw new EntityNotSupportedException(string.Format("Method {0} is not available.", name));
                
                object returnValue;
                if (isRunMacroMethod && paramsArray != null && paramsArray.Length == 2 && paramsArray[0].GetType() == typeof(string) && paramsArray[1].GetType() == typeof(object[]))
                {
                    var newParas = new List<object> {paramsArray[0]};
                    newParas.AddRange((object[])paramsArray[1]);
                    
                    returnValue = comObject.InstanceType.InvokeMember(name, BindingFlags.InvokeMethod | BindingFlags.GetProperty, null,
                   comObject.UnderlyingObject, newParas.ToArray(), Settings.ThreadCulture);
                }
                else
                {
                    returnValue = comObject.InstanceType.InvokeMember(name, BindingFlags.InvokeMethod | BindingFlags.GetProperty, null,
                    comObject.UnderlyingObject, paramsArray, Settings.ThreadCulture);
                }
                
                return returnValue;
            }
            catch (Exception throwedException)
            {
                DebugConsole.WriteException(throwedException);
                throw new System.Runtime.InteropServices.COMException(GetExceptionMessage(throwedException), throwedException);
            }
        }
Passing the VBA method parameters like this to the underlying COM object will allow you to access VBA methods with parameters.
I hope you can take my approach to fix that issue and to release a new version of your library.
Thanks and best regards,
Thomas
Nov 9, 2012 at 3:52 PM

Sorry for this way of posting...I have created a new entry in the issue tracker.