Show formula of a cell, but values instead of refe

Question

I am using a formula to show a cells formula in another cell.

I want to show the values of each reference in this formula, instead of the reference.

Ex:

=$R$16+R19*($T$15-$R$16)

Want it to be

=3+2*(4-2)

Function I am using now to show formula as it is

Function GetFormula(Cell As Range) As String
   GetFormula = Cell.Formula
End Function

Answer 1

Here is a very basic example which can handle cases like

=B1+C1-D1/E1
=$B$1+C1*($B$1-$D$1)
=B1+C1-(D1/E1)
=B1+C1-(D1/E1)
=$B$1*C1*($B$1/$D$1)

Assumptions:

1. Let's say the above formulas are in cell A1 to A5
2. All cells are in the same sheet
3. There is no worksheet function like SUM, Vlookup etc
4. No Error handling done. Assuming that you will take care of it

Sample Worksheet

Code:

Sub Sample()
    Dim i As Long
    For i = 1 To 5
        Debug.Print ConvertToValues(Range("A" & i))
    Next i
End Sub

Function ConvertToValues(rng As Range)
    Dim sTmp As String, sOpr As String, sOrig As String
    Dim s As String, v As String
    Dim MyAr
    Dim i As Long

    sOpr = "+,/,-,*,&,(,),{,},[,]"

    MyAr = Split(sOpr, ",")

    sTmp = Replace(rng.Formula, "$", "")
    sOrig = sTmp

    For i = LBound(MyAr) To UBound(MyAr)
        sTmp = Replace(sTmp, MyAr(i), "SIDROUT")
    Next i

    MyAr = Split(sTmp, "SIDROUT")

    For i = LBound(MyAr) To UBound(MyAr)
        s = MyAr(i)

        If Len(Trim(s)) <> 0 Then
            v = Range(s).Value
            sOrig = Replace(sOrig, s, v)
        End If
    Next i

    If sOrig <> "" Then _
    ConvertToValues = "=" & sOrig
End Function

Output

Note:

Let me know if the above code fails in a particular scenario and I will update the code.