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

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" wrote in message
...
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

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" wrote
in message
...
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


.

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(Date 1,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,Date2VDate2)),
IF((Date1=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))+
IF((Date2<=VDate2),0,NETWORKDAYS(VDate2+1,Date2,NW Range)),NA())))

--

Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
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" wrote
in message
...
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


.

and any UDFs?

--

HTH

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


"Tom Ogilvy" wrote in 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(Date 1,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,Date2VDate2)),
IF((Date1=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))+
IF((Date2<=VDate2),0,NETWORKDAYS(VDate2+1,Date2,NW Range)),NA())))

--

Regards,
Tom Ogilvy

"ExcelMonkey" wrote in message
...
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" wrote
in message
...
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


.

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,Date2VDate2)),
IF((Date1=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))
+
IF((Date2<=VDate2),0,NETWORKDAYS
(VDate2+1,Date2,NWRange)),NA())))

--

Regards,
Tom Ogilvy

"ExcelMonkey"
wrote in message
...
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"
wrote
in message
...
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


.



.

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


On Tue, 15 Mar 2005 13:31:20 -0800, "ExcelMonkey"
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,Date2VDate2)),
IF((Date1=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))
+
IF((Date2<=VDate2),0,NETWORKDAYS
(VDate2+1,Date2,NWRange)),NA())))

--

Regards,
Tom Ogilvy

"ExcelMonkey"
wrote in message
...
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"
wrote
in message
...
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


.



.

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" wrote in message
...
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,Date2VDate2)),
IF((Date1=VDate1),0,NETWORKDAYS(Date1,VDate1-1,NWRange))
+
IF((Date2<=VDate2),0,NETWORKDAYS
(VDate2+1,Date2,NWRange)),NA())))

--

Regards,
Tom Ogilvy

"ExcelMonkey"
wrote in message
...
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"
wrote
in message
...
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


.



.