Counting Formulas in Spreadsheet
 

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

Counting Formulas in Spreadsheet
 

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

Counting Formulas in Spreadsheet
 

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


.

Counting Formulas in Spreadsheet
 

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


.

Counting Formulas in Spreadsheet
 

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


.

Counting Formulas in Spreadsheet
 

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


.



.

Counting Formulas in Spreadsheet
 

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


.



.

Counting Formulas in Spreadsheet
 

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


.



.

Counting Formulas in Spreadsheet
 

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


.



.

Counting Formulas in Spreadsheet
 

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

--
Regards,
Tom Ogilvy

"Myrna Larson" wrote in 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"
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


.



.

Counting Formulas in Spreadsheet
 

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"
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


.



.


.