xladd | creating Excel addins in Rust

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.