Hi

What's the best way to check if a certain spreadsheet function is used in a
cell?
For instance, we want to check if cell A1 uses SUM function considering that
A1 may contain:
1. A1 ="Sum"
2. A1 =SUM(B1:B2)
3. A1 =If(A1="Sum","True",SUM(B1:B2))
These is not a complete list of possibilities. I just wanted to show that
search a formula string for certain words is not the best solution.
Any ideas?

Kind regards
IgorM

Sub dk()
MsgBox Range("B2").Formula
End Sub

Will give you the formula used in B2.


"IgorM" wrote in message
...
Hi

What's the best way to check if a certain spreadsheet function is used in
a cell?
For instance, we want to check if cell A1 uses SUM function considering
that A1 may contain:
1. A1 ="Sum"
2. A1 =SUM(B1:B2)
3. A1 =If(A1="Sum","True",SUM(B1:B2))
These is not a complete list of possibilities. I just wanted to show that
search a formula string for certain words is not the best solution.
Any ideas?

Kind regards
IgorM

You'll have to do your own parsing and if you have any User Defined Functions,
it can be difficult (for me, impossible) to be accurate.

Option Explicit
Function myFunc(str as string)
myfunc = application.evaluate(str)
end function

And this in a cell:
=myeval("sum(a1:a3)")
or:
=myeval("s"&CHAR(117)&CHAR(54*2+1)&"(a1:a3)")


IgorM wrote:

Hi

What's the best way to check if a certain spreadsheet function is used in a
cell?
For instance, we want to check if cell A1 uses SUM function considering that
A1 may contain:
1. A1 ="Sum"
2. A1 =SUM(B1:B2)
3. A1 =If(A1="Sum","True",SUM(B1:B2))
These is not a complete list of possibilities. I just wanted to show that
search a formula string for certain words is not the best solution.
Any ideas?

Kind regards
IgorM

--

Dave Peterson