How to find if a cell with formula has the function within it using VBA?

Question

I am trying to identify if a cell is just a simple reference (e.g. "=A2") as supposed to has some kinds of function/calculation included (e.g. "=sum(A2:A3)") . Basically, I am looking for a wheel that is already built for hasFunction or hasArithmeticOperations. Done the research, couldn'd find any.

Really having a hard time to figure out a more simple solution than a case by case (have written it out in one of the answers so please let me know if you found error or anything) since I couldn't think of a way to identify... The easiest way I could think about is checking for parenthesis. And yet, user input is hard to predict (e.g. "=(A2)"). In addition, I would like it to take care of all the simple operators (-,+,/,*,&,^, etc.). There has to be a easier way then just laying all of these out right?

Let me know what's your take on this.

Answer 1

I think you are looking for a regular expression.

The main problem is that for Excel, everything that begins with a "=" is a formula, and there's no distinction between types of functions. Bar regular expressions, you could parse for a pattern with two while loops, the first creeping from left to right as long as the character is a letter, the second taking over when it's a number. If you reach the end of the forumla by this, you successfully identified a reference. As you noted, you'd have to care about outer paranthesis and similar stuff...

Do you mind to elaborate what you are trying to achieve? Maybe there's a more "think outside the box" solution if more context is provided...

Answer 2

I actually have the following solution. Anyone who is interested can copy/paste the following code to check for any cell selected and see if I have missed out on any edge case.

The thinking is like this: even if the user choose to use parenthesis, we don't want anything immediately before the "(".

Similarly, we want to get rid of any simple arithmetic operators (+,-,-,*,/,^). I put down both subtraction and negate here since for some input language (e.g. Chinese), the two symble could be different? Not sure about that but don't want to risk it...

Lastly, we don't want a range of cells (i.e. no ":" or ",").

Sub test()
    Dim rng As range
    Set rng = Selection

    MsgBox (referenceOnly(rng))

End Sub

Then we have the function that test if it is only a reference

Function referenceOnly(rng As range) As Boolean

    ' Three situations tht it might has formula
    ' 1: has anything before "(" except for "="
    ' 2: has any of the simple specialsmetic operators
    ' 3: has ":" which refers to a range of cells
    referenceOnly = True

    Dim str As String
    If rng.HasFormula Then
        str = rng.Formula
    Else
        referenceOnly = False
        Exit Function
    End If

    ' start of checks
    Dim i As Integer

    ' start pos of "("
    Dim startPos As Integer

    ' check 1
    startPos = InStr(2, str, "(")
    If startPos > 0 Then
        If startPos <> 2 Then
            referenceOnly = False
            Exit Function
        End If
    End If

    ' referenceOnly 2 and 3
    Dim specials(1 To 6) As String
    specials(1) = chr(43)   '+
    specials(2) = chr(45)   '-
    specials(3) = chr(46)   '-
    specials(3) = chr(42)   '*
    specials(4) = chr(47)   '/
    specials(5) = chr(94)   '^
    specials(6) = chr(58)   ':

    For i = 2 To Len(str)
        If IsInArray(Mid(str, i, 1), specials) Then
            referenceOnly = False
            Exit Function
        End If
    Next i

End Function

Helper function to see if some element is in the array. Copied the code from somewhere :)

Private Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean

'INPUT: Pass the function a value to search for and an array of values of any data type.
'OUTPUT: True if is in array, false otherwise

    Dim element As Variant
    On Error GoTo IsInArrayError: 'array is empty
        For Each element In arr
            If element = valToBeFound Then
                IsInArray = True
                Exit Function
            End If
        Next element
    Exit Function
IsInArrayError:
    On Error GoTo 0
    IsInArray = False
End Function

Just found out that someone down-voted this thread. Not sure what is that about lol

Answer 3

I built out my approach suggested in the comments to see if it would work. The results held true for the following use cases:

$A$1 | =A2 | False
$A$1 | =SUM(B2:B3) | True
$A$1 | =(A2) | False
$A$1 | =6+2 | False
$A$1 | =ORA2+ORA3 | False
$A$1 | =VLOOKUP(B1,C:C,2,0) | True

Option Explicit

Sub test()

    Dim rng As Range
    Set rng = Range("A1")

    If rng.HasFormula Then

        Debug.Print rng.Address & " | " & rng.Formula & " | " & hasOperation(Range("A1"))

    End If

End Sub

Function hasOperation(rng As Range) As Boolean

    Dim formulaText As String
    formulaText = rng.Formula

    Dim strippedFormula As String
    strippedFormula = cleanOperators(formulaText)

    If Not IsNumeric(strippedFormula) Then

        Dim testRange As Range
        On Error Resume Next
        Set testRange = Range(strippedFormula)
        On Error GoTo 0

        If testRange Is Nothing Then

            'clear out precedents from string to see if something left
            strippedFormula = clearPrecedents(strippedFormula, rng)

            If strippedFormula <> vbNullString Then
                hasOperation = True
            End If

        End If

    End If

End Function

Function cleanOperators(whichText As String) As String

    Dim holdingString As String

    holdingString = Replace(whichText, "(", "")
    holdingString = Replace(holdingString, ")", "")
    holdingString = Replace(holdingString, "+", "")
    holdingString = Replace(holdingString, "-", "")
    holdingString = Replace(holdingString, "*", "")
    holdingString = Replace(holdingString, "/", "")
    holdingString = Replace(holdingString, "^", "")
    holdingString = Replace(holdingString, ":", "")
    holdingString = Replace(holdingString, "=", "")

    cleanOperators = holdingString

End Function

Function clearPrecedents(stringToClear As String, rng As Range)

    Dim finalresult As String
    finalresult = stringToClear

    Dim prec As Range
    For Each prec In rng.Precedents
        finalresult = Replace(finalresult, prec.Address(0, 0), "")
    Next

     clearPrecedents = finalresult

End Function