Solved: How to Detect Formula Is Present

[Cyberdude]

I thought I had seen a function called ISFORMULA somewhere, but perhaps not. What's the best non-VBA way to detect if a cell contains a formula or not?

[Zack Barresse]

You can't without VBA. A UDF would look like this ...


Option Explicit

Public Function IsFormula(celRef As Range) As Boolean
IsFormula = celRef.HasFormula
End Function

[mdmackillop]

Hi Sid,
You could use Edit/GoTo/Special/Formula/OK and colour the selected cells for a quick visual check.
Regards
Malcolm

[Cyberdude]

Thanx, guys. I was hoping that somewhere there was a function I could use in a formula. I suppose I could use Zack's contribution and put it in my Personal.xls for general reference. Thanx, Zack. :bow:

[Zack Barresse]

And Sid, you did remember correctly ... http://www.vbaexpress.com/kb/getarticle.php?kb_id=324

[Cyberdude]

Oops ... I KNEW I had seen it somewhere. :bug:

[Emily]

I thought I had seen a function called ISFORMULA somewhere, but perhaps not. What's the best non-VBA way to detect if a cell contains a formula or not?

May be you are looking for Macro 4.0 GET.FORMULA

Suppose A1 = Sin(45)

Insert - Name - Define
Names in Workbooks: IsFormulaThere
Refer to: =GET.FORMULA(!$A$1)
C1= IsFormulaThere --> shows "= Sin(45)"

[Bob Phillips]

May be you are looking for Macro 4.0 GET.FORMULA

Suppose A1 = Sin(45)


Insert - Name - Define
Names in Workbooks: IsFormulaThere
Refer to: =GET.FORMULA(!$A$1)
C1= IsFormulaThere --> shows "= Sin(45)"


Excel4 macros are notoriously unstable, copying and pasting the formula to another worksheet is know to cause Excel to crash. This is one situation where I would reverse my maxim of using Excel functioanlity where possible. the VBA solution is just so much safer.