I'm writing an Excel COM add-in, using Excel-DNA to register add-in functions. Within the add-in functions I'm using the Excel-DNA wrapper for Excel's C API to get various information from worksheets, such as:
var caller = XlCall.Excel(XlCall.xlfCaller);
var isFormula = XlCall.Excel(XlCall.xlfGetCell, 48, cellReference)
var formula = XlCall.Excel(XlCall.xlfGetFormula, cellReference)
and so on.
If I change the Office display language, Excel automatically translates all formulas within worksheets into the local language. For example, if I switch to German, =SQRT(4) becomes =WURZEL(4).
If I now use VBA to read the cell formula, I can read it either in English (using .Formula) or the local language (using .FormulaLocal). I believe something similar is possible using COM; for example, the NetOffice package can be used to read the formulas in English, regardless of the display language.
My problem is that I can't find any way to do this using the C API (via Excel-DNA's wrapper). The methods using xlfFormula and xlfGetCell both return the formula in the local language.
Furthermore, Excel symbols are also translated: for example, if I set cell A2 to the formula =SQRT(A1) and then switch to German, Excel-DNA returns the RC-style formula as =WURZEL(Z(-1)S) instead of =SQRT(R[-1]C). This means that a manual conversion would need to handle not only translated formula names but also the RC=>ZS conversion and the different uses of square or round brackets. Other display languages presumably have their own quirks.
The GET.CELL / xlfGetCell method has a 'FormulaUntranslated' option; the documentation says this "Returns the formula in the active cell without translating it (useful for international macro sheets)" but unfortunately this doesn't seem to work in this case.
Because I need to read the formulas within a performant add-in function, using COM or VBA isn't really an option for me. Parsing and translating the formula myself seems crazy when Excel knows the English version of the formula but at the moment I don't have a better way.
Summary: Is it possible to use Excel's C API to get the default (English) version of a formula, even if Excel's display language is not English? Or does anyone have a suggestion as to how I could achieve this another way?
