FORMULA VS VALUE

[Kathy Niebur]

Is there a function in Excel or functions I could group together to determine
if the contents of a given cell are a formula vs. a value ? For example, I
would want to evaluate the contents of a cell displaying 3,450 to determine
if that was a number typed into the cell (a numeric value) vs the result of a
formula in the cell (=E5+E6) for example. I know how to manually display the
formulas in a worksheet. I want to write a formula to CHECK for the presence
or absence of a formula in a given cell. Being able to check for the EXACT
formula would be even better. For example, =IF(B3<>"=E5+E6","NOT
CORRECT',"CORRECT")

Any help?

 

[Gary''s Student]

Try this tiny UDF:

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

so that if A1 contains:
1
and A2 contains:
=1
then =isformula(A1) will return FALSE
and =isformula(A2) will return TRUE


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

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

 

[Pete_UK]

You could set up a simple user-defined function (UDF) like this:

Function IsFormula(mycell As Range) As Boolean
IsFormula = mycell.HasFormula
End Function

and then use it like this:

=IsFormula(A2)

to check if A2 contains a formula (returns TRUE or FALSE).

Hope this helps.

Pete

 

[Mike H]

Hi,

the easiest is a UDF.

Alt +F11 to open VB editor, Right click 'This Workbook' and insert module
and paste the code below in

Function IsFormula(rng As Range) As String
If rng.HasFormula Then
IsFormula = "Correct"
Else
IsFormula = "Not Correct"
End If
End Function


Call with
=IsFormula(a1)

Mike

 

[Ashish Mathur]

Hi,

Though this does not get you exactly what you want, you can conditionally
format all the formula based cells. Please follow the undermentioned
procedure:

1. Go to Insert > Name (name the formula as “cellhasformulaâ€) and in the
“Refers to†box, type the following formula = get.cell(48,indirect(“rcâ€,false));
2. Now in the conditional formatting dialog box, type cellhasformula and use
the desired formatting.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com