IF Function to test formula in a cell

[Fred Holmes]

The IF() function is generally used to test the value in a cell. Is
there any way to get the IF() function (or some other function) to
test the formula in a cell?

For example, the formula in Cell E1 might be:

IF((The formula in Range("A1")="=+B1+C1"),"OK","Error")

Everything I've tried produces "Error" when the condition is in fact
true, i.e., the formula in Cell A1 is correct.

The purpose of this is to produce cells that are flags that alert when
a formula has been modified by some spreadsheet editing process and I
don't want the formula to change. Even "absolute" references seem to
change under some edits such as the insertion of a row.

And, oh by the way, I'm actually running the workbook with R1C1
Reference Style (because the formulae I'm interested in are literally,
character for character, identical in R1C1 reference style, and it's
easier to determine that they haven't changed by "visual" inspection.

Yes, I could just re-propagate the formulae any time there's a
question, but that's a "chore."

Excel 2000

Thanks for any help.

Fred Holmes

 

[Niek Otten]

With this little function you can "read" formulas

Function ShowFormula(a As Range) As String
ShowFormula = a.Formula
End Function

In your case, to get the R1C1 version, use a.FormulaR1C1 in the second line

To be able to use the function:

Open the VB Editor (ALT+F11)
Insert>Module
Paste the function in the code window

You can use the function in a cell, like in =ShowFormula(R1C1) or in a
formula

 

[Ron Rosenfeld]

The IF() function is generally used to test the value in a cell. Is
there any way to get the IF() function (or some other function) to
test the formula in a cell?

For example, the formula in Cell E1 might be:

IF((The formula in Range("A1")="=+B1+C1"),"OK","Error")

Everything I've tried produces "Error" when the condition is in fact
true, i.e., the formula in Cell A1 is correct.

The purpose of this is to produce cells that are flags that alert when
a formula has been modified by some spreadsheet editing process and I
don't want the formula to change. Even "absolute" references seem to
change under some edits such as the insertion of a row.

And, oh by the way, I'm actually running the workbook with R1C1
Reference Style (because the formulae I'm interested in are literally,
character for character, identical in R1C1 reference style, and it's
easier to determine that they haven't changed by "visual" inspection.

Yes, I could just re-propagate the formulae any time there's a
question, but that's a "chore."

Excel 2000

Thanks for any help.

Fred Holmes

You'll need a VBA function to return as a string the actual formula. You can
then use that function in your IF function.

To enter the VBA UDF, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

You can then use a formula of the type:

=IF(ShwFrm(A1) = "ur-formula"),"OK","Error")

==============================
Function ShwFrm(rg As Range) As String
ShwFrm = rg.Formula
End Function
===========================

or, if it is more convenient to return the formula string in RC notation, you
could use:

===========================
Function ShwFrm(rg As Range) As String
ShwFrm = rg.FormulaR1C1
End Function
==========================

You could also write a UDF to do the whole thing.

For example (not debugged):

=========================
Function ChkFrm(rg As Range, formula as String) As String
If formula = rg.FormulaR1C1 then 'or rg.Formula, depending
ChkFrm = "OK"
else
ChkFrm = "Error"
End If
End Function
========================

You could also use conditional formatting color those cells where the formula
did not agree with what it should be.

All kinds of solutions -- but you will require VBA for them.


--ron

 

[Don Guillett]

Drop the =+ in your formula to just =. This is NOT lotus.

in a macro
If ActiveCell.Formula = "=a1*3" Then MsgBox "OK"
in a formula
=IF(C3=A1*3,1,2)

Don Guillett
SalesAid Software
(e-mail address removed)

 

[Fred Holmes]

Many thanks to all for the help provided. Just what I needed.

Fred Holmes

 

[Don Guillett]

glad to help