Excel-DNA's GET.FORMULA wrapper not returning some formulas

[Neil T]

Within an Excel XLL written in C#, I am using the following code to obtain the formula in a worksheet cell via Excel-DNA (wrapping GET.FORMULA in Excel's C API):

    var formula = XlCall.Excel(XlCall.xlfGetFormula, cellRef) as string;

In most scenarios, this works correctly. However, when the cell's formula is long or complex, formula is null.

Typically, the formula needs to be in excess of about 240 characters before there is a problem. However, certain much shorter formulas can cause failures when they are particularly complex or nested. For example, the following Excel formula (just 139 characters):

=IF(A1,IF(TRUE,IF(NOT(SUM(B1,C1,D1,E1,F1)),IF(TRUE,"",G1&H1&"q0"&I1&IF(LEN(J1)=0,"",IF(K1=0,TEXT(L1,"0"),TEXT(M1,"q"&REPT("0",N1))))),),),)

will sometimes cause the above code to return null.

By "sometimes" I mean that (for example) the code succeeds if the Excel formula is in cell A10 of a workbook but fails if it is in cell A11! (The workbook is essentially otherwise blank.)

My guess is that this is a limitation of Excel's C API rather than Excel-DNA, but I cannot find any Microsoft documentation or related third-party articles on this topic. I have not yet been able to determine any definitive rules for the behaviour.

The only workaround I have found is to use XlCall.Excel(XlCall.xlfGetCell, 6, cellRef), wrapping GET.CELL, to obtain the formula. This succeeds, but the return value is in A1 or absolute R1C1 format (depending on the Excel session setting), not the relative RC format as returned by GET.FORMULA, which is what I need.

I would be grateful for confirmation that this is indeed a limitation of the C API (not the Excel-DNA wrapper), and for any further information or references regarding the limitation or suggestions of better workarounds.

Many thanks,

Neil