How to test if a specified cell contains a formula?

[lovallee]

Hello

Which formula can I use to test if a specified cell contains a formula?

Louis

 

[T. Valko]

Thanks, Bill. Yes, that works GREAT. Dave

You're welcome. Thanks for the feedback!

 

[DBrownBear]

Now I have a similar question: When a student enters just a number in a cell I can use your ISFUNCTION VBA to test for that.
Students sometimes will curcumvent ISFUNCTION by entering =1234 (that is, an answer without showing the formula to get it).
How do I test for "=" followed by only a number?

 

[T. Valko]

Now I have a similar question: When a student enters just a number in a cell I can use your ISFUNCTION VBA to test for that.
Students sometimes will curcumvent ISFUNCTION by entering =1234 (that is, an answer without showing the formula to get it).
How do I test for "=" followed by only a number?

Not sure what you're asking about.

If a user enters the following in a cell:

=1234

The IsFormula UDF correctly evaluates that to be a formula and =IsFormula(A2) returns TRUE.

Are you saying that this type of formula should not be considered a formula for your own purposes?

 

[DBrownBear]

Hi, Biff,

For the example I used, a student problem might be "Add multiply 1.234 by 1000."
I'm trying to test for EITHER if a student has entered a number, e.g. 1234 in a cell, or =1234, instead of the equired math frormula solving the problem.

So, yes, I would like both 1234, or =1234 to return FALSE.

Do you have a suggestion how to test if either a number or a number (only) following an equal sign is present?

 

[DBrownBear]

ARRGH. I should have said:
For the example I used, a student problem might be "Multiply 1.234 by 1000."
and pay attention to what I wrote...
Dave

 

[T. Valko]

Hi, Biff,

For the example I used, a student problem might be "Add multiply 1.234 by 1000."
I'm trying to test for EITHER if a student has entered a number, e.g. 1234 in a cell, or =1234, instead of the equired math frormula solving the problem.

So, yes, I would like both 1234, or =1234 to return FALSE.

Do you have a suggestion how to test if either a number or a number (only) following an equal sign is present?

How are you implementing this?

Let's assume the user enters either 1234 or =1234 in cell A2.

Are you wanting to enter the IsFormula function in a cell or, are you using the IsFormula function as a conditional formatting rule to highlight cells?

If you're entring IsFormula in a cell, which cell?

 

[DBrownBear]

I'm entering this formula in a "grading" cell, say for your example where the student entered their answer in A2, the "grading" cell might be D2.

 

[T. Valko]

I'm entering this formula in a "grading" cell, say for your example where the student entered their answer in A2, the "grading" cell might be D2.

Ok, you'll need another UDF...

Function GetFormula(cell_ref As Range) As String
    If cell_ref.HasFormula Then
    GetFormula = cell_ref.Formula
    Else
    GetFormula = ""
    End If
End Function

I'm not sure what result you're wanting so I'll just show you what I've done and we can tweak as needed.

If the user enters a formula like =1234 or just a number like 1234 in cell A2...

This formula entered in D2 will return FALSE:

=IF(IsFormula(A2),IF(COUNT(-SUBSTITUTE(GetFormula(A2),"=","")),FALSE,"OK"),IF(COUNT(A2),FALSE,"OK"))

If the user enters some other formula like =10*2 or =SUM(X1:X10) then the formula will return "OK".

 

[DBrownBear]

Hi, and thanks.
I wonder if I'm doing something wrong.
I tried the GetFormula() UDF in a module where I also had your IsFormula() UDF, for the following cell reference arguments: 3, =3, and =3^2.
IsFormula returned, in the above order, "FALSE", "TRUE", "TRUE" to these three arguments.
GetFormula() returned "#Name?" to all three.

Am I doing something wrong here?
Dave

 

[T. Valko]

Hi, and thanks.
I wonder if I'm doing something wrong.
I tried the GetFormula() UDF in a module where I also had your IsFormula() UDF, for the following cell reference arguments: 3, =3, and =3^2.
IsFormula returned, in the above order, "FALSE", "TRUE", "TRUE" to these three arguments.
GetFormula() returned "#Name?" to all three.

Am I doing something wrong here?
Dave

What version of Excel are you using?

When cell A2 contains:

3, the result in cell D2 should be _____.
=3, the result in cell D2 should be _____.
=3^2, the result in cell D2 should be _____.
nothing (the cell is empty or blank), the result in cell D2 should be _____.