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:
Like for example:
if not TXlsNamedRange.IsValidRangeName('MOF8', IsInternal) then
raise Exception.Create('This is not a valid name!');
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;
Thanks again, Bob
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.
The list of internal functions is here:
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.