4

A number of calls into Excel's XLL API are only permitted by Excel-DNA if the function's ExcelFunction attribute specifies IsMacroType=true. What I'm unclear of is why simply adding this to all of my functions and being done with it is not a good idea; I'm assuming it is not otherwise it would be the default.

Something to do with volatility of function calculation by Excel itself? If there is a good resource online describing the pros and cons of IsMacroType=true I'd love to see it!

James Webster
  • 4,046
  • 29
  • 41

1 Answers1

7

The IsMacroType=true attribute changes the parameters Excel-DNA uses when registering the function, which is done with a call to xlfRegister, as documented here: https://msdn.microsoft.com/en-us/library/office/bb687900.aspx In particular, Excel-DNA adds a "#" to the end of the pxTypeText parameter.

The documentation says:

Placing a # character after the last parameter code in pxTypeText gives the function the same calling permissions as functions on a macro sheet. These are as follows:

  • The function can retrieve the values of cells that have not yet been calculated in this recalculation cycle.

  • The function can call any of the XLM information (Class 2) functions, for example, xlfGetCell.

If the number sign (#) is not present:

  • evaluating an uncalculated cell results in an xlretUncalced error, and the current function is called again once the cell has been calculated;
  • calling any XLM information function other than xlfCaller results in an xlretInvXlfn error.

Some disadvantages of marking a function as IsMacroType=true:

  • they cannot be multi-threaded - Excel-DNA will not add the "$" suffix when registering, even if they are marked as IsThreadSafe=true.
  • if they contain at least one parameter of type object that is marked [ExcelArgument(AllowReference=true)] then the function is automatically considered as volatile by Excel (even if the function is explicitly marked as IsVolatile=false.)

Further, my understanding is that such functions are treated differently in the dependency processing during Excel calculations. So you might expect some changes on the order in which sheets calculate. I have no reference or reproduction for this, though.

My recommendation is to only set IsMacroType=true in exceptional cases, when you know it is certainly required and you are prepared to investigate any issues that might arise.

Govert
  • 16,387
  • 4
  • 60
  • 70