Issues when calling a VBA macro from an XLL

[Alex]

Hi, I'm trying to call a VBA macro from an XLL written in C# like this: 

XlCall.Excel(XlCall.xlUDF, "MacroName","data");

However, I'm having several issues:

1) Some places in the code XlCall.Excel works, and sometimes it throws a System.AccessViolationException.  When am I allowed to call it?

2) Even when the XlCall.Excel does work and the VBA debugger shows the "data" parameter value being passed to the macro, VBA cannot seem to insert it on the spreadsheet. 

If I do something like: 

Sheet1.Range("A1").Value = data 

it will throw an  "Application defined or object defined error." Even if I specify the workbook name, it does the same.

3) If the C# code that contains XlCall.Excel is called from an Excel button, Excel says "Cannot run the macro..." but if called from a function inserted in a worksheet cell, Excel can find it, but again with issue #2.

Any help is much appreciated.

Thanks,

Alex

[Govert van Drimmelen]

Hi Alex,

The Excel C API (XlCall.Excel(...)) can be safely be called in these contexts:

* Inside your AutoOpen handler.

* Inside an Excel-DNA UDF function.

* Inside an Excel-DNA macro.

* Inside you IExcelAddIn AutoOpen and AutoClose implementations.

Places where you can't use the C API:

* Ribbon callback methods.

* Excel COM object model event handlers.

* Any thread that is not the main Excel thread or a calculation thread when Excel is doing multithreaded calculation (for functions marked as thread-safe - only limited calls allowed by Excel in this context).

* The main thread when Excel didn't initiate the call, e.g. a timer or Windows form handler, even if it is running on the main thread.

From any context or thread you can schedule code to run in a macro context where the C API is supported, by calling ExcelAsyncUtil.QueueAsMacro(...).This will schedule the code to run as a macro, as soon as Excel is ready (calculations are complete, user not editing a formula etc.)

Similarly, there are some restrictions on when the COM object model is safe to use from your add-in (if you get the root COM object with a call to ExcelDnaUtil.Application). You should only use the COM object model when running on the main thread, never for cross-thread calls. The COM  object model is safe to call in these contexts:

* Ribbon callback methods.

* Macro context (e.g. public void methods marked with [ExcelCommand]).

* COM object event handlers.

It's not clear whether the COM object model is safe to use in a UDF context when called from a formula in a calculation. Microsoft says this is not supported, but some calls to the object model seem to work fine in this context.

------------

In general Excel does not allow you to make changes to the sheet from a function. So if you tried to call ExcelReference.SetValue(..) inside a function, that would always fail. Excel has some other restrictions on what C API calls can be made inside a function. Some C API features are only available in functions that are marked as IsMacroType=true,  like the xlfGetXXX information functions.

------------

I don't know much about calls to xlUDF and VBA. You can keep the following in mind:

* There are separate xlUDF and xlcRun C API calls. I'm not sure exactly how they differ, but I guess xlUDF only works with VBA functions, not Subs.

* You can also initiate a VBA procedure with the COM object model call Application.Run(...).

* I'm confused about how the names work when calling VBA macros - sometimes you need the sheet or workbook name, other times not. Sometime you need brackets or single quotes, other times not. It's very confusing.

* Remember that there might be a difference between the Active sheet (which is the sheet that the user has focus on) and the Current sheet (which is the sheet where a calculation is called from). This might make a difference with what your VBA calls see.

------------

If you really want to have a user-defined function to have side-effects like writing stuff to the sheet (something I think is dangerous and should never be done) then the way to do it is to schedule a macro (with ExcelAsyncUtil.QueueAsMacro) from inside your function which will then run and apply the sheet changes after the calculation is complete.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Alex [safp...@gmail.com]
Sent: 23 April 2017 01:35 PM
To: Excel-DNA
Subject: [ExcelDna] Issues when calling a VBA macro from an XLL

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Alex]

Govert, thanks for the response. 

In case it would help anyone, the way that worked for me to avoid all these issues is like this:

  object xlApp = ExcelDnaUtil.Application;

  xlApp.GetType().InvokeMember("Run", BindingFlags.InvokeMethod,

                      null, xlApp, new object[] { macroName, data });

[Govert van Drimmelen]

Hi Alex,

 

Since .NET 4, the ‘dynamic’ type makes late-binding COM calls from C# a bit easier.

 

You can also write your code as:

 

dynamic xlApp = ExcelDnaUtil.Application;

xlApp.Run(macroName, data);

 

-Govert

--

[Alex]

Hi Govert, thanks for the tip. 

One more thing: Is it possible to call a VBA macro from an open workbook other than the one that called the C# function?

If I specify the workbook like this: xlApp.Run("[WbName.xlsm]!MacroName","data") it will throw an exception saying  that workbook is already open.

I also tried activating the workbook first, but it still used the first workbook:

dynamic xlApp = ExcelDnaUtil.Application

xlApp.Workbooks["WbName.xlsm"].Activate();

xlApp.Run(macroName, data)

[Govert van Drimmelen]

Have you tried it as

xlApp.Run("WbName!MacroName","data")

If that still doesn't work, I don't really have any other suggestions.

(Except to move the macro code to the .NET side.)

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Alex [safp...@gmail.com]

Sent: 02 May 2017 05:12 PM
To: Excel-DNA
Subject: [ExcelDna] Re: Issues when calling a VBA macro from an XLL

[Alex]

That way it worked, thanks!

[Govert van Drimmelen]

This is snippet from the XLL SDK on running a VBA macro from an XLL:

/*

** xlUDFExample

**

** This function demonstrates how to run a macro from an XLL. It runs

** a macro called TestMacro in a workbook called BOOK1.XLSX

*/

__declspec(dllexport) short WINAPI xlUDFExample(void)

{       

XLOPER12 xMacroName, xMacroRef, xRes;

xMacroName.xltype = xltypeStr;

xMacroName.val.str = L"\034[BOOK1.XLSX]Macro1!TestMacro";

Excel12(xlfEvaluate, &xMacroRef, 1, (LPXLOPER12)&xMacroName);

Excel12(xlUDF, &xRes, 1, (LPXLOPER12)&xMacroRef);

return 1;

}