Display Evaluated formula of another cell

[Mat]

Hi,

I'm pretty sure there's a way to do this in VBA.

I would like to show the evaluated formula (with the numbers, not the cell reference) in a cell, based on another cell formula.

Let say I have :
A2 = 3
B3 = 4
D3 = 5
A1 = "=A2+B3*D3"

Then B2 = "=EvaluatedFormula(A1") would display : "=2+4*5"

So I need a routine that will replace the addresses by their actual value in a formula.

B2 = "=SUBSTITUTEADDRESSBYVALUE(FORMLATEXT(B2))

Thank you

Mathieu

 

[Mat]

I found a code to extract cell reference from a cell, so I end up with that code, that does the job for now, but it's not bulletproof. It doesn't work with named range which would be required.

Function DisplayFormula(Rg As Range) As String
'Updateby Extendoffice
    Dim xRetList As Object
    Dim xRegEx As Object
    Dim I As Long
    Dim xRet As String
    Application.Volatile
    Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
    With xRegEx
        .Pattern = "('?[a-zA-Z0-9\s\[\]\.]{1,99})?'?!?\$?[A-Z]{1,3}\$?[0-9]{1,7}:)\$?[A-Z]{1,3}\$?[0-9]{1,7})?"
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With
    Set xRetList = xRegEx.Execute(Rg.Formula)
    If xRetList.Count > 0 Then
        For I = 0 To xRetList.Count - 1
            xRet = xRet & xRetList.Item(I) & ";"
        Next
        DisplayFormula = Left(xRet, Len(xRet) - 2)
    Else
        DisplayFormula = "No Matches"
    End If
   
    Dim formul As String
    formul = Rg.Formula
    Dim tmp As Variant
    tmp = Split(DisplayFormula, ";")
    For I = 0 To UBound(tmp)
        formul = Replace(formul, tmp(I), Format(Range(tmp(I)).Value, Range(tmp(I)).NumberFormat))
       
    Next I
    DisplayFormula = Right(formul, Len(formul) - 1)
    DisplayFormula = Replace(DisplayFormula, "*", " · ")
    DisplayFormula = Replace(DisplayFormula, "-", " - ")
    DisplayFormula = Replace(DisplayFormula, "+", " + ")
    DisplayFormula = Replace(DisplayFormula, "/", " / ")
End Function