2

I was trying to conditionally format cells whose value didn't depend on anything else so I thought applying ISFORMULA to each cell would do the trick but then realized that if a cell has something like =10^23 then it too, would be a formula even though its value is constant.

A cell with formula =10+5 would have the function I'm looking for return FALSE and a cell with =A5+3 would have the function return TRUE.

  • 2
    It would be a hacky solution, but perhaps you could use the FORMULATEXT function to look for alphabetic characters. – davidmneedham Nov 6 '17 at 14:57
  • 1
    I know you could do it with VBA.. Probably compare the cell's formula with the cell's text and show those that are equal to be true. Kind of depends what you are to do with the results though – Eric F Nov 6 '17 at 15:25
  • No need for a hacky solution. Excel has a formula for this: =ISREF(value). It returns true if the formula contains a reference. But it sees =A1+A2 as normal, not reference. See also: techonthenet.com/excel/formulas/isref.php – LPChip Nov 6 '17 at 15:46
  • It seems like the Precedents.Count property could be used, but I'm having trouble coming up with a full solution using this method. – davidmneedham Nov 6 '17 at 17:26
4

Here's a UDF that should work in most of the cases:

Function HAS_REF(r As Range) As Boolean
    HAS_REF = (r.Formula <> r.FormulaR1C1)
End Function

The Formula and FormulaR1C1 properties of a range happen to be the same unless it contains a cell reference.

Exception: If the cell contains a reference to a named range, both will be equal and your cell won't get highlighted.

Update:

Here's a version that checks for range names as well:

Function HAS_REF(r As Range) As Boolean
    Dim i As Long

    HAS_REF = (r.Formula <> r.FormulaR1C1)

    If HAS_REF Then Exit Function

    For i = 1 To ThisWorkbook.Names.Count
        If InStr(r.Formula, ThisWorkbook.Names(i).Name) Then
            HAS_REF = True
            Exit Function
        End If
    Next i
End Function

Of course, if you're not using range names in your workbook, the function I posted earlier should suffice.

About Precedents attribute:

I tried using the Precedents attribute of the range to check for references. But it doesn't seem to work. I've tried the following:

  • Directly using r.Precedents in the UDF to check if the range had any precedents. Assigning it to a range variable simply assigned the r (rather than r.Precedents).

  • Using a helper sub routine to do the actual checking by calling the sub from function. That didn't work either. However, when you comment out the line in function that calls the sub procedure, the sub works and checks for precedents properly.

  • Tried using global variables which are accessible by both the UDF and the helper sub routine. Doesn't make a difference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.