Is a cell a formula or value?

[todd]

I'm stumped, I thought there used to be a =isformula to test whether a cell
is a formula or not.

How can I test for that to see if a formula got overwritten which I do want
it to do if a user chooses.

 

[T. Valko]

You can use a VBA UDF (user defined function):

Function IsFormula(cell_ref As Range)
IsFormula = cell_ref.HasFormula
End Function

Then you'd use it just like any other worksheet function:

=IsFormula(A1)

This will return either TRUE or FALSE.

You could also use this function to conditionally format the cells.

To use this:

Open the VBE editor: ALT F11
Open the Project Explorer: CTRL R
Locate your file name in the project explorer pane on the left.
Right click the file name
Select: Insert>Module
Copy the code above and paste into the window on the right
Return back to Excel: ALT Q

Set the conditional formatting...
Select the cell(s) in question. Assume this is cell A1.
Goto Format>Conditional Formatting
Formula Is: =IsFormula(A1)
Click the Format button
Select the desired style(s)
OK out

 

[Harlan Grove]

You can use a VBA UDF (user defined function):

Function IsFormula(cell_ref As Range)
    IsFormula = cell_ref.HasFormula
End Function ....
This will return either TRUE or FALSE.

....

Actually it could also return #VALUE! if you try to pass it anything
that isn't a range reference. At the risk of slight overengineering,
you could use

Function isformula(c As Variant)
If Not TypeOf c Is Range Then
isformula = CVErr(xlErrRef)
Else
isformula = c.HasFormula
End If
End Function

which would return #REF! when the argument isn't a range reference. If
this udf would be used as just one term among many in longer formulas,
this could provide more meaningful diagnostics.