MS Excel VBA: Find cells that have a specific formula type

Question

I want to automatically find cells that contain formulas only being a simple reference. For example:

=D20 or ='Sheet1'!D20.

The purpose is that I want to automatically color code these cells.

Is there a way in VBA to search for formula types?

Use that suggested method with a Like operator to find formulas that match your search patterns — JvdV
Look for cells that only have a single Precedent range, and match their Formula against a regex or like pattern. — Mathieu Guindon
@MathieuGuindon got a point. Due to the fact you can have all sort of cell references, RexEx might be your best bet (in combination with the Like operator). I tried to write a code that will do that in an efficient way (see below) — JvdV

JvdV JvdV · Accepted Answer · 2019-09-22T10:04:23

After some time to let this sink in, I think it's not that straightforward. Direct cell references can include simple references like:

=A1

But there are instances where you can refer to another cell in another sheet, another opened workbook and even another closed workbook. While this makes it difficult to use RIGHT, LEFT functions or even FORMULATEXT for the likes, the one thing that you can do is to check for patterns.

Any direct cell reference cannot include any other calculation, meaning it must exclude any unwanted operater (/,*,-,+). As far as I'm concerned this means that if we check a cell that only has ONE Precedent, the formula can NOT start with a digit NOR can it end with digits after any of the above mentioned operators.

Our best bet here, is to put these criteria in a regular expression. I'm by no mean a RegEx expert, but I think the following does the trick:

"^=.*[A-Z]+\$*\d+$"

Start of the line is =
Followed by any word/non-word character, 0-n times
End of the line must be digits, 1-n times, preceded by either just upper case alfabetic characters or a combination with the dollar sign (for absolute cell references)

The above works as long as we include a check for just one .Precedent and a check if the second character of the formula isn't numeric. The code example would then, for example, look like:

Sub FindFormualaType()

Dim rng1 As Range, rng2 As Range, cl As Range
Dim regex As New RegExp
Dim matches As Object
Dim tststr As String

With regex
    .Global = True
    .Pattern = "^=.*[A-Z]+\$*\d+$"
End With

With Sheet1 'Change to your sheet's codename
    Set rng1 = Intersect(.Cells, .Cells.SpecialCells(-4123))
    For Each cl In rng1
        On Error Resume Next
        If cl.Precedents.Count = 1 Then
            If Left(cl.Formula, 2) Like "=#" = False Then
                Set matches = regex.Execute(cl.Formula)
                If matches.Count = 1 Then
                    If Not rng2 Is Nothing Then
                        Set rng2 = Union(rng2, cl)
                    Else
                        Set rng2 = cl
                    End If
                End If
            End If
        End If
        On Error GoTo 0
    Next cl
End With

rng2.Interior.Color = vbGreen

End Sub

To explain some of the steps in the code above:

To use the RegExp you have to set a reference to it:
The code then makes use of .SpecialCells with xlCellTypeFormulas (-4123), and creates a .Range object through .Intersect. Therefore we don't have to loop all cells to check with .HasFormula (hopefully saving some time).
Next up is the loop through all cells in that newly created .Range object and check if Precendents.Count = 1. We need to capsule that into an On Error Resume Next because it would error out if no precedents are found, e.g.: a formula like ="Hello".
When the count equals 1, we check with the Like operator if we can savely execute the regex and test for a match on the used pattern.
Because I would rather do a fill operation in one go, I suggest to create another .Range object (rng2) and use Union to set this object.
Finally we can use something like rng2.Interior.Color = vbGreen to color all cells that just have a direct reference to just one cell.

Hope that helped. By no means I'm an RegEx expert and it can probably be done simpler.

Some background information on why I used the Like operator. Because I wanted to be sure no calculation is done with the cell reference, the first character in the equation can NOT be numeric. Within RegEx you got something called "lookahead". Though, unfortunately, I couldn't get the expression to work withing VBA (unsupported?) and therefor used the Like operator to make sure the second character was not numeric.

MS Excel VBA: Find cells that have a specific formula type

3 Answers