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


[vba]Option Explicit

Public Function IsFormula(celRef As Range) As Boolean
IsFormula = celRef.HasFormula
End Function[/vba]

[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.

[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.

[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)

1. Insert - Name - Define
2. Names in Workbooks: IsFormulaThere
3. 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)

1. Insert - Name - Define
2. Names in Workbooks: IsFormulaThere
3. 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.