How to set cell values using Excel12v C interface

Question

I have an Excel12v function using XLOPER to set some values on an Excel sheet. I can create XLLs fine as per Microsoft's XLL guide. I authored xladd-derive for Rust which enables this an allows returning scalars and ranges of values very simply.

However I would like, rather than return a value, to set a random cell to a value. There is xlSet function demonstrated below that does this and works fine.

short WINAPI xlSetExample()
{
   XLOPER12 xRef, xValue;
   xRef.xltype = xltypeSRef;
   xRef.val.sref.count = 1;
   xRef.val.sref.ref.rwFirst = 204;
   xRef.val.sref.ref.rwLast = 205;
   xRef.val.sref.ref.colFirst = 1;
   xRef.val.sref.ref.colLast = 1;
   xValue.xltype = xltypeInt;
   xValue.val.w = 12345;
   Excel12v(xlSet, 0, 2, (LPXLOPER12)&xRef, (LPXLOPER12)&xValue);
   return 1;
}

but only works if it's called from a VBA macro

Sub test()
        Application.Run("xlSetExample","12345")
End Sub

Is there an equivalent xlf* or xlc* function that allows one to set cell values but do not need to be called from a VBA macro

Answer 1

In general, Excel prevents spreadsheet functions from changing the values in cells. In effect, spreadsheet functions are given a read-only view of the values in the sheet.

This is the documentation for xlSet which states:

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).

The reason for this is to prevent circular references or other actions that would break or confuse the calculation tree. Excel would struggle to determine dependencies between cells if a function in one cell could change other cells' contents.

Consider the hypothetical function AddOne() which takes a number, adds one and uses this to set the cell immediately to the right via xlSet (or otherwise). What would happen if the formula in cell A1 were =AddOne(B1)?

This Excel SDK reference gives more information. Namely:

Different Types of Functions

Excel4 and Excel12 distinguish among three classes of functions. The functions are classified according to the three states in which Excel might call the DLL.

Class 1 applies when the DLL is called from a worksheet as a result of recalculation.

Class 2 applies when the DLL is called from within a function macro or from a worksheet where it was registered with a number sign (#) in the type text.

Class 3 applies when a DLL is called from an object, macro, menu, toolbar, shortcut key, ExecuteExcel4Macro method, or the Tools/Macro/Run command. For more information, see Excel Commands, Functions, and States.

Only Class 3 functions can call xlSet.

So, in summary, the Excel application really doesn't want users to change one cell from a function call in another. As always, if you work hard enough you could probably achieve this (eg get the COM application object pointer by some method and modify the cell that way, or set up a callback to modify the cell asynchronously), but you might have unpredictable results.