Search code examples
excel-dna

What are the advantages/disadvantages of marking function IsMacroType in Excel-DNA?

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!

Solution

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