Determine if a formula contains absolute, relative or mixed references

[ttt123]

I would like to make some functions to determine the different reference types in a formula. I have code that functions correctly if the formula only has a single reference type, but not if it has more than one reference type.

So for example:

HasAbsoluteReference("=$A$1*$B$1") 'correctly returns True

but

HasAbsoluteReference("=$A$1*B1") 'incorrectly returns False since the formula has both an absolute reference and a relative reference

Does anyone have any suggestions for modifying these formulas so that we can check each reference type in the formula individually instead of the entire formula as a whole?

Public Function HasAbsoluteReference(formulaA1 As String) As Boolean
    Dim convertedFormula As String
    convertedFormula = Application.ConvertFormula(formula:=formulaA1, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
    HasAbsoluteReference = StrComp(convertedFormula, formulaA1, vbTextCompare) = 0
End Function

Public Function HasRelativeReference(formulaA1 As String) As Boolean
    Dim convertedFormula As String
    convertedFormula = Application.ConvertFormula(formula:=formulaA1, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
    HasRelativeReference = StrComp(convertedFormula, formulaA1, vbTextCompare) = 0
End Function

Public Function HasMixedReference(formulaA1 As String) As Boolean
    Dim convertedFormula1 As String, convertedFormula2 As String
    convertedFormula1 = Application.ConvertFormula(formula:=formulaA1, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelRowAbsColumn)
    convertedFormula2 = Application.ConvertFormula(formula:=formulaA1, FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlAbsRowRelColumn)
    HasMixedReference = StrComp(convertedFormula1, formulaA1, vbTextCompare) = 0 Or StrComp(convertedFormula2, formulaA1, vbTextCompare) = 0
End Function

 

[lrobbo314]

Try this.

Book1
	A	B	C	D	E
1	15	Absolute			6
2	15	Mixed			9
3	15	Relative
Sheet2

Cell Formulas
Range	Formula
A1	A1	=$E$1+$E$2
B1:B3	B1	=ref(A1)
A2	A2	=$E$1+E2
A3	A3	=E1+E2

Function REF(r As Range)
Dim FX As String:       FX = r.Formula
Dim xAny As Boolean:    xAny = False
Dim xAll As Boolean:    xAll = True

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[$?A-Z]+\d+"
    Set matches = .Execute(FX)
    For Each m In matches
        If InStr(m.Value, "$") Then
            xAny = True
        Else
            xAll = False
        End If
    Next m
End With

If xAny And xAll Then REF = "Absolute"
If xAny And Not (xAll) Then REF = "Mixed"
If Not xAny And Not xAll Then REF = "Relative"
    
End Function