I've been creating various test user defined functions and have noticed some names (e.g. MOF8, MOF9) are shown with apostrophes around the name (e.g. ='MOF8'(R1C1, R2C1)). These then appear to cause a corruption in the Excel workbook (which Excel can repair by removing the formula). I'm assuming these are invalid names for some reason - is there a way to check for this?
Those are indeed invalid names, because it is not possible to distinguish from a cell reference: MOF8 can be read as column "MOF", row 8. (same as A1 is column A, row 1). Xlsx allows columns up to XFD, so most 3 letters + number identifiers are invalid.
UDF Names are internally range names, so they go by the same rules.
Excel does allow those in their internal names, so you can write =LOG10(10) in a cell and it will work, but you can't do the same with your internal functions.
The simplest way to see it is if you try to define a name "MOF8" in Excel:
var
IsInternal: Boolean;
begin
if not TXlsNamedRange.IsValidRangeName('MOF8', IsInternal) then
begin
raise Exception.Create('This is not a valid name!');
end;
Note: If you were to be working with legacy xls files, FlexCel has a "Compatibility mode" which limits the grid to 256 columns and will allow a name like MOF8 (but still not allow a name like A8). But you would have to work with xls files, not xlsx, and really, this is not a solution, just a workaround if you have existing files with this problem. For new files, you should try never to use Three-letters+Number names.
Thanks Adrian - of course I forgot that the constraints also apply to column names not just cell references! Didn't know about IsValidRangeName- that's very useful.
On a related note - I can see TExcelFile.IsDefinedFunction - but is
there a way to get a list of internal functions? I've only found the
private class var FuncsByName: TCellFunctionDataDictionary;
No, there is not a list, maybe something could be added, but the main issue is that the number of internal functions is not fixed: Every new Excel version adds new internal functions, and sometimes they add new internal functions in service packs.
But those are the actual functions. Some functions that you can find in FuncsByName, like ADD_COMMAND() are not really functions, but Excel gives them an id anyway.
