How to obtain a dynamic list of worksheet functions?

[ChrisGreaves]

I'm using Excel2000, and am confident that the functions available differ from those available in Excel2002 or 2003.

I can obtain lists of built-in functions from the web, but can't find a way to obtain a list of built-in functions dynamically.

I'm parsing Excel cell formulae, and can identify a range name by searching the workbook.Names object.
I can recognize a cell reference by the Letter(s)Digit(s) convention.
I'd like to be able to recognize a string as a valid function name (SUM, COUNT etc.) without using a static list, which list would tie me to a specific version of Excel.
Ideally I'd be able to search a list of ALL functions, classified as either user-defined or built-in.
Any clues? Suggested avenues?

[HansV]

A function name must always be followed by an opening parenthesis (
Perhaps that helps to parse the formula string.

[ChrisGreaves]

Perhaps that helps to parse the formula string.

Thanks Hans.
I know the lexical/syntactic elements, but once I have found an identifier that isn't
(1) a cell identifier e.g. "C34"
(2) A known range name e.g. "Tax_Rebate" (which is a valid range name because I can find it in the workbook's Names object
I am left with an identifier e.g. "Greaves".

The question is: Is "Greaves"an Excel function, a user-defined function, or did the user forget to define a range name?

I think of MSWord's Styles object, where I can loop through it and locate a valid name, and determine if it is BuiltIn.

Given the identifier "Greaves", I'd like to do the equivalent of looping through a Functions object and determine if the identifier is in the table (and hence is a built-in or user-defined function).
If the identifier "Greaves" is not in the Names object and is not in the Functions object, then I would make the assumption that the user is using an undefined range name in the formula, and tell them so.

The question boils down to: Is there a Functions object or equivalent in Excel?

(Pauses to re-read Hans's reply and then ...)
Oh! Now I see what you are saying:
An identifier followed by a left-parenthesis might be assumed to be a function call.
Thus "Greaves(....." would allow the parser to suspect that it is a function, and perhaps assume that, but "Greaves+ ...." would allow the parser to suspect that the identifier is an undefined range name.
It's not foolproof, but it's a lot better than throwing my hands up in the air.

[HansV]

As far as I know, the list of worksheet functions is not available to the programmer. There is a WorksheetFunction object whose methods are those worksheet functions that can be used in VBA, but this does not include all functions - the ones that have direct VBA equivalents are omitted. Moreover, I don't know of a way to enumerate the names of the methods (functions) in code.
So you'd have to create a list of names, either in a (hidden) worksheet, or in an array.
For Excel 2003: List of worksheet functions (by category)
For Excel 2007: List of worksheet functions (by category)

[ChrisGreaves]

So you'd have to create a list of names,

Hans, thanks so much for the confirmation (that the equivalent of a Functions object is not available).
I'm going to take your suggestion and build a list of all known functions, all versions.
If the identifier appears in the list AND the identifier is immediately followed by a left-parenthesis, I shall assume that the user knows what they are doing.
Otherwise I shall treat the identifier as a range name.

My purpose is to identify likely sources of errors; the user (me!) already knows that there is an error, so the occasional false positive/negative isn't fatal; it just reduces the accuracy by a small percentage and I can live with th@.

th@: I couldn't resist; not enough coffee in me yet .....

[HansV]

H@s off for the pun (or whatever you call this)...

[sdckapr]

H@ts off for the pun (or whatever you call this)...

Shouldn't it be "H@s off ..."

Steve

[HansV]

Yes, of course!