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" <(E-Mail Removed)> wrote in message
news:BADDC292-FA60-4C7B-AB8E-(E-Mail Removed)...
> 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