Counting Formulas in Spreadsheet

[ExcelMonkey]

Does anyone know how to search a cell and identify and
count each formula in that cell. Then pass this to an
array which adjusts for redundant strings.

Thanks

[Tom Ogilvy]

A cell can only have one formula.

for each cell in selection
if cell.hasformula then
cnt = cnt + 1
sFormula = cell.Formula
end if
Next

you can't pass things to arrays and arrays don't adjust. Perhaps you have
some subroutine that does this. Then you would call it

ProcessFormula sFormula


--
Regards,
Tom Ogilvy



"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> Does anyone know how to search a cell and identify and
> count each formula in that cell. Then pass this to an
> array which adjusts for redundant strings.
>
> Thanks

[ExcelMonkey]

sorry Tom. What I meant to say is it possible to count
functions within the cell formula?

THanks


>-----Original Message-----
>A cell can only have one formula.
>
>for each cell in selection
> if cell.hasformula then
> cnt = cnt + 1
> sFormula = cell.Formula
> end if
>Next
>
>you can't pass things to arrays and arrays don't adjust.
Perhaps you have
>some subroutine that does this. Then you would call it
>
> ProcessFormula sFormula
>
>
>--
>Regards,
>Tom Ogilvy
>
>
>
>"ExcelMonkey" <[email protected]> wrote
in message
>news:[email protected]...
>> Does anyone know how to search a cell and identify and
>> count each formula in that cell. Then pass this to an
>> array which adjusts for redundant strings.
>>
>> Thanks
>
>
>.
>

[Tom Ogilvy]

I expect it is. You would probably need a formula parser about as powerful
as Excel's built in parser.

Otherwise, you would need a list of all the functions you want to identify.

Maybe someone else has a better idea.

for a sample formula:

=IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS(Date1,Date2,NWRange),
IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND(Date1>=VDate1,Date2<=VDate2)),
MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS(VDate1,VDate2,NWRange)),
IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND(Date1>=VDate1,Date2>VDate2)),
IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))+
IF((Date2<=VDate2),0,NETWORKDAYS(VDate2+1,Date2,NWRange)),NA())))

--

Regards,
Tom Ogilvy

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> sorry Tom. What I meant to say is it possible to count
> functions within the cell formula?
>
> THanks
>
>
> >-----Original Message-----
> >A cell can only have one formula.
> >
> >for each cell in selection
> > if cell.hasformula then
> > cnt = cnt + 1
> > sFormula = cell.Formula
> > end if
> >Next
> >
> >you can't pass things to arrays and arrays don't adjust.
> Perhaps you have
> >some subroutine that does this. Then you would call it
> >
> > ProcessFormula sFormula
> >
> >
> >--
> >Regards,
> >Tom Ogilvy
> >
> >
> >
> >"ExcelMonkey" <[email protected]> wrote
> in message
> >news:[email protected]...
> >> Does anyone know how to search a cell and identify and
> >> count each formula in that cell. Then pass this to an
> >> array which adjusts for redundant strings.
> >>
> >> Thanks
> >
> >
> >.
> >

[Bob Phillips]

and any UDFs?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> I expect it is. You would probably need a formula parser about as
powerful
> as Excel's built in parser.
>
> Otherwise, you would need a list of all the functions you want to
identify.
>
> Maybe someone else has a better idea.
>
> for a sample formula:
>
> =IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS(Date1,Date2,NWRange),
> IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND(Date1>=VDate1,Date2<=VDate2)),
>
MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS(VDate1,VDate2,NWRange)),
> IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND(Date1>=VDate1,Date2>VDate2)),
> IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))+
> IF((Date2<=VDate2),0,NETWORKDAYS(VDate2+1,Date2,NWRange)),NA())))
>
> --
>
> Regards,
> Tom Ogilvy
>
> "ExcelMonkey" <[email protected]> wrote in message
> news:[email protected]...
> > sorry Tom. What I meant to say is it possible to count
> > functions within the cell formula?
> >
> > THanks
> >
> >
> > >-----Original Message-----
> > >A cell can only have one formula.
> > >
> > >for each cell in selection
> > > if cell.hasformula then
> > > cnt = cnt + 1
> > > sFormula = cell.Formula
> > > end if
> > >Next
> > >
> > >you can't pass things to arrays and arrays don't adjust.
> > Perhaps you have
> > >some subroutine that does this. Then you would call it
> > >
> > > ProcessFormula sFormula
> > >
> > >
> > >--
> > >Regards,
> > >Tom Ogilvy
> > >
> > >
> > >
> > >"ExcelMonkey" <[email protected]> wrote
> > in message
> > >news:[email protected]...
> > >> Does anyone know how to search a cell and identify and
> > >> count each formula in that cell. Then pass this to an
> > >> array which adjusts for redundant strings.
> > >>
> > >> Thanks
> > >
> > >
> > >.
> > >
>
>

[ExcelMonkey]

What is a formula parser. I guess I do not that I can
test to see if its a formula. If yes, then I can count
brakcets "(" and search between the brackets for existing
excel functions from a list.


>-----Original Message-----
>I expect it is. You would probably need a formula
parser about as powerful
>as Excel's built in parser.
>
>Otherwise, you would need a list of all the functions
you want to identify.
>
>Maybe someone else has a better idea.
>
>for a sample formula:
>
>=IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS
(Date1,Date2,NWRange),
>IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND
(Date1>=VDate1,Date2<=VDate2)),
>MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS
(VDate1,VDate2,NWRange)),
>IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND
(Date1>=VDate1,Date2>VDate2)),
>IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))
+
>IF((Date2<=VDate2),0,NETWORKDAYS
(VDate2+1,Date2,NWRange)),NA())))
>
>--
>
>Regards,
>Tom Ogilvy
>
>"ExcelMonkey" <[email protected]>
wrote in message
>news:[email protected]...
>> sorry Tom. What I meant to say is it possible to count
>> functions within the cell formula?
>>
>> THanks
>>
>>
>> >-----Original Message-----
>> >A cell can only have one formula.
>> >
>> >for each cell in selection
>> > if cell.hasformula then
>> > cnt = cnt + 1
>> > sFormula = cell.Formula
>> > end if
>> >Next
>> >
>> >you can't pass things to arrays and arrays don't
adjust.
>> Perhaps you have
>> >some subroutine that does this. Then you would call
it
>> >
>> > ProcessFormula sFormula
>> >
>> >
>> >--
>> >Regards,
>> >Tom Ogilvy
>> >
>> >
>> >
>> >"ExcelMonkey" <[email protected]>
wrote
>> in message
>> >news:[email protected]...
>> >> Does anyone know how to search a cell and identify
and
>> >> count each formula in that cell. Then pass this to
an
>> >> array which adjusts for redundant strings.
>> >>
>> >> Thanks
>> >
>> >
>> >.
>> >
>
>
>.
>

[Myrna Larson]

But WHY do you want to know this? Is it really worth the effort?


On Tue, 15 Mar 2005 13:31:20 -0800, "ExcelMonkey"
<[email protected]> wrote:

>What is a formula parser. I guess I do not that I can
>test to see if its a formula. If yes, then I can count
>brakcets "(" and search between the brackets for existing
>excel functions from a list.
>
>
>>-----Original Message-----
>>I expect it is. You would probably need a formula
>parser about as powerful
>>as Excel's built in parser.
>>
>>Otherwise, you would need a list of all the functions
>you want to identify.
>>
>>Maybe someone else has a better idea.
>>
>>for a sample formula:
>>
>>=IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS
>(Date1,Date2,NWRange),
>>IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND
>(Date1>=VDate1,Date2<=VDate2)),
>>MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS
>(VDate1,VDate2,NWRange)),
>>IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND
>(Date1>=VDate1,Date2>VDate2)),
>>IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))
>+
>>IF((Date2<=VDate2),0,NETWORKDAYS
>(VDate2+1,Date2,NWRange)),NA())))
>>
>>--
>>
>>Regards,
>>Tom Ogilvy
>>
>>"ExcelMonkey" <[email protected]>
>wrote in message
>>news:[email protected]...
>>> sorry Tom. What I meant to say is it possible to count
>>> functions within the cell formula?
>>>
>>> THanks
>>>
>>>
>>> >-----Original Message-----
>>> >A cell can only have one formula.
>>> >
>>> >for each cell in selection
>>> > if cell.hasformula then
>>> > cnt = cnt + 1
>>> > sFormula = cell.Formula
>>> > end if
>>> >Next
>>> >
>>> >you can't pass things to arrays and arrays don't
>adjust.
>>> Perhaps you have
>>> >some subroutine that does this. Then you would call
>it
>>> >
>>> > ProcessFormula sFormula
>>> >
>>> >
>>> >--
>>> >Regards,
>>> >Tom Ogilvy
>>> >
>>> >
>>> >
>>> >"ExcelMonkey" <[email protected]>
>wrote
>>> in message
>>> >news:[email protected]...
>>> >> Does anyone know how to search a cell and identify
>and
>>> >> count each formula in that cell. Then pass this to
>an
>>> >> array which adjusts for redundant strings.
>>> >>
>>> >> Thanks
>>> >
>>> >
>>> >.
>>> >
>>
>>
>>.
>>

[Dana DeLouis]

One option may be the use of Regular Expressions. If you can set a vba
library reference to VBScript REgular Expressions, then here is one possible
option to get you started. This is quick and dirty, so you'll have to add
more features. I'm not good at this. I used a SubMatch to work around
relative addresses (ie $A$1 vs. the word boundary \b)
The function "Check" is not the best, but the idea is to see if the word is
an Address, or a number.

Sub Dem0()
Dim RE
Dim Match
Dim Matches
Dim s

Const Words As String = "[^a-z]([A-Za-z0-9$]+)"

s = "=DATE(YEAR(A1)+1,MONTH($A$1),DAY(A1)+PI())"

Set RE = New RegExp
RE.Global = True
RE.IgnoreCase = True
RE.Pattern = Words
Set Matches = RE.Execute(s)

For Each Match In Matches
Debug.Print Match.SubMatches(0), Check(Match.SubMatches(0))
Next

End Sub

Function Check(s As String) As Boolean
Dim t, c

On Error Resume Next
t = Range(s).Address
c = c + Sgn(Err.Number)
' Err.Clear
c = c + IsNumeric(s) + 1
Check = c = 2
End Function

' = = = = = = = = = = = = = = = = =

Returns:
DATE True
YEAR True
A1 False
1 False
MONTH True
$A$1 False
DAY True
A1 False
PI True

It flagged True for most of the "Functions", but this is not tested very
well. I'm sure there are better ways.

--
Dana DeLouis
Win XP & Office 2003


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> What is a formula parser. I guess I do not that I can
> test to see if its a formula. If yes, then I can count
> brakcets "(" and search between the brackets for existing
> excel functions from a list.
>
>
>>-----Original Message-----
>>I expect it is. You would probably need a formula
> parser about as powerful
>>as Excel's built in parser.
>>
>>Otherwise, you would need a list of all the functions
> you want to identify.
>>
>>Maybe someone else has a better idea.
>>
>>for a sample formula:
>>
>>=IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS
> (Date1,Date2,NWRange),
>>IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND
> (Date1>=VDate1,Date2<=VDate2)),
>>MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS
> (VDate1,VDate2,NWRange)),
>>IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND
> (Date1>=VDate1,Date2>VDate2)),
>>IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))
> +
>>IF((Date2<=VDate2),0,NETWORKDAYS
> (VDate2+1,Date2,NWRange)),NA())))
>>
>>--
>>
>>Regards,
>>Tom Ogilvy
>>
>>"ExcelMonkey" <[email protected]>
> wrote in message
>>news:[email protected]...
>>> sorry Tom. What I meant to say is it possible to count
>>> functions within the cell formula?
>>>
>>> THanks
>>>
>>>
>>> >-----Original Message-----
>>> >A cell can only have one formula.
>>> >
>>> >for each cell in selection
>>> > if cell.hasformula then
>>> > cnt = cnt + 1
>>> > sFormula = cell.Formula
>>> > end if
>>> >Next
>>> >
>>> >you can't pass things to arrays and arrays don't
> adjust.
>>> Perhaps you have
>>> >some subroutine that does this. Then you would call
> it
>>> >
>>> > ProcessFormula sFormula
>>> >
>>> >
>>> >--
>>> >Regards,
>>> >Tom Ogilvy
>>> >
>>> >
>>> >
>>> >"ExcelMonkey" <[email protected]>
> wrote
>>> in message
>>> >news:[email protected]...
>>> >> Does anyone know how to search a cell and identify
> and
>>> >> count each formula in that cell. Then pass this to
> an
>>> >> array which adjusts for redundant strings.
>>> >>
>>> >> Thanks
>>> >
>>> >
>>> >.
>>> >
>>
>>
>>.
>>

[Bob Phillips]

Hi Myrna,

I'm with you, and I think that Tom's reply (and I hope the OP got this) was
suggesting the same thing. If he does go this route, it is almost guaranteed
that it will not be comprehensive.

Bob


"Myrna Larson" <[email protected]> wrote in message
news:[email protected]...
> But WHY do you want to know this? Is it really worth the effort?
>
>
> On Tue, 15 Mar 2005 13:31:20 -0800, "ExcelMonkey"
> <[email protected]> wrote:
>
> >What is a formula parser. I guess I do not that I can
> >test to see if its a formula. If yes, then I can count
> >brakcets "(" and search between the brackets for existing
> >excel functions from a list.
> >
> >
> >>-----Original Message-----
> >>I expect it is. You would probably need a formula
> >parser about as powerful
> >>as Excel's built in parser.
> >>
> >>Otherwise, you would need a list of all the functions
> >you want to identify.
> >>
> >>Maybe someone else has a better idea.
> >>
> >>for a sample formula:
> >>
> >>=IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS
> >(Date1,Date2,NWRange),
> >>IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND
> >(Date1>=VDate1,Date2<=VDate2)),
> >>MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS
> >(VDate1,VDate2,NWRange)),
> >>IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND
> >(Date1>=VDate1,Date2>VDate2)),
> >>IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))
> >+
> >>IF((Date2<=VDate2),0,NETWORKDAYS
> >(VDate2+1,Date2,NWRange)),NA())))
> >>
> >>--
> >>
> >>Regards,
> >>Tom Ogilvy
> >>
> >>"ExcelMonkey" <[email protected]>
> >wrote in message
> >>news:[email protected]...
> >>> sorry Tom. What I meant to say is it possible to count
> >>> functions within the cell formula?
> >>>
> >>> THanks
> >>>
> >>>
> >>> >-----Original Message-----
> >>> >A cell can only have one formula.
> >>> >
> >>> >for each cell in selection
> >>> > if cell.hasformula then
> >>> > cnt = cnt + 1
> >>> > sFormula = cell.Formula
> >>> > end if
> >>> >Next
> >>> >
> >>> >you can't pass things to arrays and arrays don't
> >adjust.
> >>> Perhaps you have
> >>> >some subroutine that does this. Then you would call
> >it
> >>> >
> >>> > ProcessFormula sFormula
> >>> >
> >>> >
> >>> >--
> >>> >Regards,
> >>> >Tom Ogilvy
> >>> >
> >>> >
> >>> >
> >>> >"ExcelMonkey" <[email protected]>
> >wrote
> >>> in message
> >>> >news:[email protected]...
> >>> >> Does anyone know how to search a cell and identify
> >and
> >>> >> count each formula in that cell. Then pass this to
> >an
> >>> >> array which adjusts for redundant strings.
> >>> >>
> >>> >> Thanks
> >>> >
> >>> >
> >>> >.
> >>> >
> >>
> >>
> >>.
> >>
>

[tony h]

Just a thought. Surely the words in a formula are either a formula name or an address. If you parse the words then validate them as either an address or a name (check against the names object) then any remaining will be formula.

But again one has to wonder why?

[Tom Ogilvy]

He said he was developing some type of audit application as I recall.

--
Regards,
Tom Ogilvy

"Myrna Larson" <[email protected]> wrote in message
news:[email protected]...
> But WHY do you want to know this? Is it really worth the effort?
>
>
> On Tue, 15 Mar 2005 13:31:20 -0800, "ExcelMonkey"
> <[email protected]> wrote:
>
> >What is a formula parser. I guess I do not that I can
> >test to see if its a formula. If yes, then I can count
> >brakcets "(" and search between the brackets for existing
> >excel functions from a list.
> >
> >
> >>-----Original Message-----
> >>I expect it is. You would probably need a formula
> >parser about as powerful
> >>as Excel's built in parser.
> >>
> >>Otherwise, you would need a list of all the functions
> >you want to identify.
> >>
> >>Maybe someone else has a better idea.
> >>
> >>for a sample formula:
> >>
> >>=IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS
> >(Date1,Date2,NWRange),
> >>IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND
> >(Date1>=VDate1,Date2<=VDate2)),
> >>MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS
> >(VDate1,VDate2,NWRange)),
> >>IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND
> >(Date1>=VDate1,Date2>VDate2)),
> >>IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))
> >+
> >>IF((Date2<=VDate2),0,NETWORKDAYS
> >(VDate2+1,Date2,NWRange)),NA())))
> >>
> >>--
> >>
> >>Regards,
> >>Tom Ogilvy
> >>
> >>"ExcelMonkey" <[email protected]>
> >wrote in message
> >>news:[email protected]...
> >>> sorry Tom. What I meant to say is it possible to count
> >>> functions within the cell formula?
> >>>
> >>> THanks
> >>>
> >>>
> >>> >-----Original Message-----
> >>> >A cell can only have one formula.
> >>> >
> >>> >for each cell in selection
> >>> > if cell.hasformula then
> >>> > cnt = cnt + 1
> >>> > sFormula = cell.Formula
> >>> > end if
> >>> >Next
> >>> >
> >>> >you can't pass things to arrays and arrays don't
> >adjust.
> >>> Perhaps you have
> >>> >some subroutine that does this. Then you would call
> >it
> >>> >
> >>> > ProcessFormula sFormula
> >>> >
> >>> >
> >>> >--
> >>> >Regards,
> >>> >Tom Ogilvy
> >>> >
> >>> >
> >>> >
> >>> >"ExcelMonkey" <[email protected]>
> >wrote
> >>> in message
> >>> >news:[email protected]...
> >>> >> Does anyone know how to search a cell and identify
> >and
> >>> >> count each formula in that cell. Then pass this to
> >an
> >>> >> array which adjusts for redundant strings.
> >>> >>
> >>> >> Thanks
> >>> >
> >>> >
> >>> >.
> >>> >
> >>
> >>
> >>.
> >>
>

[ExcelMonkey]

The rationale behind this is that yes I am building an
auditing tool. One of the things that would be useful
would be to have a tool that looks for specific and
frequent formula errors (i.e YearFrac using 360 vs 365
days or say a match without the 0 at the end). I already
have all the Excel functions loaded into a combo box. But
it would be more meaningful to have the combo box loaded
with actual functions used in the spreadsheet. Yes it
maybe a lot of work and I may just default to the entire
function list that I am currently using.



>-----Original Message-----
>But WHY do you want to know this? Is it really worth the
effort?
>
>
>On Tue, 15 Mar 2005 13:31:20 -0800, "ExcelMonkey"
><[email protected]> wrote:
>
>>What is a formula parser. I guess I do not that I can
>>test to see if its a formula. If yes, then I can count
>>brakcets "(" and search between the brackets for
existing
>>excel functions from a list.
>>
>>
>>>-----Original Message-----
>>>I expect it is. You would probably need a formula
>>parser about as powerful
>>>as Excel's built in parser.
>>>
>>>Otherwise, you would need a list of all the functions
>>you want to identify.
>>>
>>>Maybe someone else has a better idea.
>>>
>>>for a sample formula:
>>>
>>>=IF(OR(Date2<VDate1,VDate2<Date1),NETWORKDAYS
>>(Date1,Date2,NWRange),
>>>IF(OR(AND(Date1<=VDate1,Date2>=VDate2),AND
>>(Date1>=VDate1,Date2<=VDate2)),
>>>MAX(0,NETWORKDAYS(Date1,Date2,NWRange)-NETWORKDAYS
>>(VDate1,VDate2,NWRange)),
>>>IF(OR(AND(Date1<=VDate1,Date2<=VDate2),AND
>>(Date1>=VDate1,Date2>VDate2)),
>>>IF((Date1>=VDate1),0,NETWORKDAYS(Date1,VDate1-
1,NWRange))
>>+
>>>IF((Date2<=VDate2),0,NETWORKDAYS
>>(VDate2+1,Date2,NWRange)),NA())))
>>>
>>>--
>>>
>>>Regards,
>>>Tom Ogilvy
>>>
>>>"ExcelMonkey" <[email protected]>
>>wrote in message
>>>news:[email protected]...
>>>> sorry Tom. What I meant to say is it possible to
count
>>>> functions within the cell formula?
>>>>
>>>> THanks
>>>>
>>>>
>>>> >-----Original Message-----
>>>> >A cell can only have one formula.
>>>> >
>>>> >for each cell in selection
>>>> > if cell.hasformula then
>>>> > cnt = cnt + 1
>>>> > sFormula = cell.Formula
>>>> > end if
>>>> >Next
>>>> >
>>>> >you can't pass things to arrays and arrays don't
>>adjust.
>>>> Perhaps you have
>>>> >some subroutine that does this. Then you would call
>>it
>>>> >
>>>> > ProcessFormula sFormula
>>>> >
>>>> >
>>>> >--
>>>> >Regards,
>>>> >Tom Ogilvy
>>>> >
>>>> >
>>>> >
>>>> >"ExcelMonkey" <[email protected]>
>>wrote
>>>> in message
>>>> >news:[email protected]...
>>>> >> Does anyone know how to search a cell and identify
>>and
>>>> >> count each formula in that cell. Then pass this
to
>>an
>>>> >> array which adjusts for redundant strings.
>>>> >>
>>>> >> Thanks
>>>> >
>>>> >
>>>> >.
>>>> >
>>>
>>>
>>>.
>>>
>
>.
>

[tony h]

So you have a list of all available functions and want to populate a combo with any of those functions that exist on a sheet.

In this case rather than parse each formula for all functions all you need to do is search all formulae for existance of the names (suitably delimited so that you don't mistake SUM for SUMPRODUCT) once you have found one occurance you can then populate your combo.

Hope this helps,