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?
How to obtain a dynamic list of worksheet functions?
-
- PlutoniumLounger
- Posts: 15655
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
How to obtain a dynamic list of worksheet functions?
He who plants a seed, plants life.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to obtain a dynamic list of worksheet functions?
A function name must always be followed by an opening parenthesis (
Perhaps that helps to parse the formula string.
Perhaps that helps to parse the formula string.
Best wishes,
Hans
Hans
-
- PlutoniumLounger
- Posts: 15655
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: How to obtain a dynamic list of worksheet functions?
Thanks Hans.HansV wrote:Perhaps that helps to parse the formula string.
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.
He who plants a seed, plants life.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to obtain a dynamic list of worksheet functions?
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)
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)
Best wishes,
Hans
Hans
-
- PlutoniumLounger
- Posts: 15655
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: How to obtain a dynamic list of worksheet functions?
Hans, thanks so much for the confirmation (that the equivalent of a Functions object is not available).HansV wrote:So you'd have to create a list of names,
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 .....
He who plants a seed, plants life.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to obtain a dynamic list of worksheet functions?
H@s off for the pun (or whatever you call this)...
Last edited by HansV on 02 Apr 2010, 11:40, edited 1 time in total.
Reason: to correct error (thanks, Steve!)
Reason: to correct error (thanks, Steve!)
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: How to obtain a dynamic list of worksheet functions?
Shouldn't it be "H@s off ..."H@ts off for the pun (or whatever you call this)...
Steve
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands