Detecting if a cell contains a function or just a value

[ScorchedLemonade]

Hi,

I'm curious as to a way to do this in Excel:

suppose A1 and A2 hold the same value but are calculated in different ways, namely

A1 just has 5 entered
A2 has something like =max(B2,C2)-D2 where B2=7, C2=1 and D2=2

I want to be able to detect that A1 is not calculated using references to other cells (or, more generally, just that what was entered doesn't start with "=", as in =max(3,5), although I guess it's possible at some low level an "=" might be assumed even if only 5 is entered). B2, C2 and D2 would hold data feeding from a (checkable) external source. Sometimes the data might not be reliable - in this case the formula would be overwritten with the correct value (to give something of the type in A1). In this case, the cell would be made bold. This can be done manually (and unbolded later - when the data is reliable) but it would be preferable to have some kind of formula in a conditional format that would check and make the cell bold or unbold as appropriate.

Thus, preferably with something like a conditional format with a formula (rather than with VB code), it would make anything of the first type bold (and wouldn't format things of the second type). Unfortunately, my naive attempts at this - like =left(A2,1)="=" or =iserror(search("/",A2)+1), =len(A1) - do not work as hoped as they all look at the value in A2, rather than the formula used to calculate A2, and are hence unable to distinguish the 2 possibilities.

If anyone can suggest a solution, I'd be most grateful. Thanks.

 

[PHV]

As we are in VBA forum, here a VBA suggestion:
compare the FormulaR1C1 property of the cell against its value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Hi,

Function IsFormula(rng) as Boolean
  with rng
    if .value = .formula then
      IsFormula = false
    else
      IsFormula = true
    end if
  end with
End Function

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ScorchedLemonade]

Thanks - that does indeed work (*). I guess I went to the wrong forum though (as I'd wanted to do it via conditional formatting).
(There didn't appear to be a one just for Excel - when I looked I got this, Microsoft Office and something curiously about squaring the circle - an impossible feat if restricted to compass and straight edge.)

(* a bit like this)
For i=a to b
With Cells(i, 1)
.Font.Bold = (Left(.FormulaR1C1, 1) <> "=")
End With
Next i

Thanks again.

 

[SkipVought]

This is the correct forum, since ther's no way to detect a formula with spreadsheet functions. Consequently, Conditional Formatting is not an option.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Crystalyzer]

Couldn't you create the function that skip suggests and then put it in the conditional formatting formula section?

Just a thought!

 

[VBAjedi]

Crystalyzer is correct. But first, Skip's code needs one tweak. The .Formula property returns a string, while the .Value property can return a string OR a number. So for cells that contain a number, Skip's function will incorrectly say they contain a formula. By having VBA convert .Value to a string, we avoid this:

Function IsFormula(rng As Range) As Boolean
  Dim x As String
  With rng
    x = .Value
    If x = .Formula Then
      IsFormula = False
    Else
      IsFormula = True
    End If
  End With
End Function

Now in the Conditional formatting box for any selected range, you can choose "Formula is" and enter this formula (where "A1" is the active cell in the selection):

=IsFormula(A1)

That should do it!


VBAjedi

 

[SkipVought]

VBA,

How TRUE

    If CStr(.Value) = CStr(.Formula) Then

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

And what about this ?
Function IsFormula(rng As Range) As Boolean
IsFormula = (CStr(rng.Value) <> CStr(rng.Formula))
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBAjedi]

Skip,
Somehow I knew that you would find a better way to fix your oversight than what I suggested! I don't think I've ever used Cstr before.

Anyway, I think PHV topped us both. . . nice, PH!

VBAjedi

 

[SkipVought]

Ha!

I changed the code earlier today but failed to repost ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[IvanMoala]

or just

Function IsFormula(Rg As Range) As Boolean
'// Only for single cell and not a selection
    IsFormula = Rg.HasFormula
End Function

you could also use the old Xl4 macro

Ivan F Moala

http://www.xcelfiles.com

 

[ScorchedLemonade]

Thanks all. I decided to go with IsntFormula = (CStr(rng.Value) = CStr(rng.Formula)) which worked, but then I changed to IsntFormula = Not(Rg.HasFormula) which also works.