Is Cell a formula or a value

  • Thread starter Thread starter terri
  • Start date Start date
T

terri

I would like to create a formula that will tell me if another cell is a value
or if it contains a formula in it? So for example in cell A2, I want to know
is cell A1 has "=B1+B2" or does it have a 5 in it. I'm assuming it would be
some kind of IF statement, but not sure much beyond that.

thx.
 
Use this tiny UDF:

Public Function isformula(r As Range) As Boolean
isformula = r.HasFormula
End Function

So
=isformula(A1)
will display TRUE if A1 contains a formula, otherwise FALSE>
 
I'm getting a #NAME? error. When I look at my total list of functions I
don't see ISFORMULA. I'm running Excell 2003 -- is it a version issue?
 
No, its simply a User Defined Function (UDF).


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function:

=isformula(A1) in some cell

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
 
Back
Top