Invoking Excel macro function with Excel C API

user_4173739 0
Viewed 358

I'm new to XLL development and have been trying to invoke a registered function as a macro in my XLL. I'm trying to get write access to cells adjacent to my calling cell.

The xlSet function looks like it will give me the ability to do that. But it does not look like this function can be called from my Xll function https://docs.microsoft.com/en-us/office/client-developer/excel/xlset

From the API, it seems this function needs to be called from a macro. I've tried using the xlUDF function to "trick" excel into thinking I'm running a function as a macro, but haven't had much luck. The xlUDF function gets invoked, but xlSet still fails.

How would I invoke a macro that can call this function ? It seems C#/ExcelDna has the equivalent of :

object xlApp = ExcelDnaUtil.Application;
xlApp.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, xlApp, new object[]{macroName});

Many thanks in advance!

xll excel c++
1 Answers

It is not permitted to call a command from an XLL function for security reasons (especially for writing access..). In your case Excel prevents the calculation thread that runs your Xll function (A) to call your registered command (B) and so "xlset" because otherwise you could, for instance, crash Excel by modifying a cell (via the command) that may be one of the arguments of the function (A).

xlSet behaves as a Class 3 command-equivalent function; that is, it is available only inside a DLL when the DLL is called from an object, macro, menu, toolbar, shortcut key, or the Run button in the Macro dialog box (accessed from View tab on the ribbon starting in Excel 2007, and the Tools menu in earlier versions). source

So if you want to use xlset, you need to : 1) call it from a function registered as a macro and 2) you cannot call this macro from your xll, i.e the macro needs to be call by the end user or by the Excel Object Model (VBA, VSTO...).

Regarding ExcelDna, I believe it overcomes the problem by calling the macro (B) from another thread, a COM server, that access the Excel Object Model. In this case the XLL function (A) only delegates the call to a thread that is not restricted. But this may be dangerous.

Edit :

I talked with the OP in a chat and it turns out it wanted to expand the cell area via the Excel Object Model. But this is not needed anymore: since fall 2018 , Excel resizes automatically the xltypeMulti array returned by the UDF (dynamic array).

user_32055290
Related