UDF Valid Names

Hi Adrian

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?

Thanks, Bob
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:

  IsInternal: Boolean;
  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.
For compatibility mode, see the first point in this section: http://www.tmssoftware.biz/flexcel/doc/vcl/guides/api-developer-guide.html#considerations-about-excel-2007-support

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.

Cheers, Bob
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.