How to determine if a cell formula contains Constants?

Question

When debugging or Quality Checking an Excel report at work I have found that the problem was because of text being hard coded inside a formula. I have heard this as being a Constant and Formula mixed cell.

Here are examples of what I see.

Constant =100

Constant =Facility

Formula cell =INDIRECT(ADDRESS(5,MATCH($A7&$B7&C$2,Data!$4:$4,0),,,$A$2))

Mixed cell =INDIRECT("Data!"&ADDRESS(5,MATCH($A7&$B7&C$2,Data!$4:$4,0)))

"Data!" is the Constant in the mixed cell, in this case the sheet name. If that sheet name ever changed, the formula would break. I have found and am using two conditional formats to highlight cells that are Constants and those that are formulas using this "Identify formulas using Conditional Formatting". I need to come up with a way to format those cells which contain these Constants inside of formulas.

I have found this question and tried using =IF(COUNT(SEARCH(CHAR(34),A1,1)),TRUE,FALSE) and FIND() to see if I could check if a cell had double quotes inside of it, but the SEARCH() returns FALSE since it is looking at the cells value and not it's contents. It returns TRUE if the cell contains "Constant" but if it is a formula it returns FALSE, such as if the cell contains ="Constant".

How can I find Constants inside formulas across a whole worksheet or workbook?

EDIT*

Thanks to Sidd's code below I have made a function in a module I can use in conditional formatting to at least highlight cells that contain quotes inside the cells.

Function FormulaHasQuotes(aCell)

  If InStr(1, aCell.Formula, """") Then
      FormulaHasQuotes = True
  Else
      FormulaHasQuotes = False
  End If

End Function

FormulaHasQuotes Conditional formatting pic

Its complex and not fool-proof, you need to parse the formula. My addin `Mappit!` uses regular expressions to indentify constants inf formulae,but in the case above it would pick out the `5` not `Data!` which is really a location rather than a constant. — brettdj, Sep 29 '13 at 01:56

score 1 · Accepted Answer · answered Sep 27 '13 at 19:23

1

Let's say your sheet looks like this.

enter image description here

Is this what you are trying?

Sub Sample()
    Dim ws As Worksheet
    Dim aCell As Range, FRange As Range

    '~~> Set this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find all the cells which have formula
        On Error Resume Next
        Set FRange = .Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If Not FRange Is Nothing Then
            '~~> Check for " in the cell's formula
            For Each aCell In FRange
                If InStr(1, aCell.Formula, """") Then
                    Debug.Print "Cell " & aCell.Address; " has a constant"
                End If
            Next
        End If
    End With
End Sub

When you run the above code, you get this output

Cell $A$2 has a constant
Cell $A$5 has a constant

Note: I have shown you how to do it for a sheet, i am sure you can replicate it for all sheets in a workbook?

answered Sep 27 '13 at 19:23

Siddharth Rout

147,039
17
206
250

Thats neat. But doesn't 3 and 4 contain constants too? – Steve Sep 27 '13 at 19:26
2

@Steve: As per OP, anything that is within QUOTES is a constant. Now `Constant` here is not be confused with the actual Constants like `1`, `24` etc... At least that is what I have understood from the question... – Siddharth Rout Sep 27 '13 at 19:29
Very first example was **Constant =100** – Steve Sep 27 '13 at 19:33
1

@Steve: True. But I don't see it being used in a formula in OP's post. What I see however is `if I could check if a cell had double quotes inside of it...` Lets wait for OP to clarify :) – Siddharth Rout Sep 27 '13 at 19:36
Thank you Sidd, this will help me in most if not all situations. However Steve does have a point that the Address formula contains the constant `5` I am not sure if there is a way to show if the cell contains a hard coded number like that `5`. – Scheballs Sep 30 '13 at 01:29
Thanks Siddharth Rout, I modified your Sub to be a function so I could use it in conditional formatting and it works to highlight cells that contain quotes inside of them. Thank you! +1, edited my question to show my function. – Scheballs Sep 30 '13 at 19:10

score 0 · Answer 2 · edited Apr 14 '20 at 20:06

Only very lightly tested...

For example will not handle "string" constants which contain embedded "

Sub Tester()

'add reference to "Microsoft VBScript Regular Expressions 5.5"
Dim regxnum As New RegExp, regexpchar As New RegExp
Dim matches As MatchCollection, m As Match

Dim rngF As Range, c As Range

    On Error Resume Next
    Set rngF = ActiveSheet.UsedRange.SpecialCells(xlFormulas)
    On Error GoTo 0

    If Not rngF Is Nothing Then

        regxnum.Pattern = "[^A-Z$](-?\d{1,}\.?\d{0,})"
        regxnum.Global = True

        regexpchar.Pattern = "(""[^""]{0,}"")"
        regexpchar.Global = True

        For Each c In rngF.Cells
            Debug.Print c.Formula
            Set matches = regxnum.Execute(c.Formula)

            For Each m In matches
                    Debug.Print c.Parent.Name, c.Address(), m.SubMatches(0)
            Next m

            Set matches = regexpchar.Execute(c.Formula)
            For Each m In matches
                    Debug.Print c.Parent.Name, c.Address(), m.SubMatches(0)
            Next m

        Next c
    End If
End Sub

How to determine if a cell formula contains Constants?

EDIT*

2 Answers2