Excel has a built-in function (FormulaText) to display a formula as cell references, rather than the result value.  It would be useful to be able to display the value of each cell reference as well, but there is no built-in function to do that.

It turns out it’s not easy to do with VBA either.  I have added a macro to do the job to the Eval2 spreadsheet:

Sub Addr2Val()
Dim PrecCells As Variant, CellRng As Range, NumP As Long, i As Long, Form As String
Dim CellAddr() As String, CellVal() As Double, xCell As Range, OutCols As Long

    Set CellRng = Application.ActiveCell
    Form = CellRng.Formula
    Form = Replace(Form, "$", "")
    Set PrecCells = CellRng.Precedents
    NumP = PrecCells.Count
    ReDim CellAddr(1 To NumP, 1 To 1)
    ReDim CellVal(1 To NumP, 1 To 1)
    i = 1
    For Each xCell In PrecCells
        CellAddr(i, 1) = xCell.Address
        CellAddr(i, 1) = Replace(CellAddr(i, 1), "$", "")
        CellVal(i, 1) = xCell.Value
        i = i + 1
    Next
    Form = Eval(Form, CellAddr, CellVal, 0)
    OutCols = Selection.Columns.Count
    If OutCols > 1 Then OutCols = OutCols - 1
    CellRng.Offset(0, OutCols).Value = " " & Form
    Set CellRng = Nothing
    Set PrecCells = Nothing
End Sub

The macro uses the Range.Precedents method to return the address and value of each cell reference in the formula, then calls the Eval user defined function (UDF) to convert the cell addresses in the formula to their numerical values.  It would be convenient to put this in a function, which could be called from the spreadsheet, but when entered in a UDF the .Precedents method does not return information on the cell precedents, it returns the information for the cell itself.  If anyone has any suggestions for converting the macro to a working UDF, please leave a comment.

The macro has been set up to return the results in the cell to the right of the selected cell, or if three or more columns are selected, in the top right cell of the selected range.  Note that the macro will write over  any contents in the results cell.

An example of the macro in use (with instructions) is shown in the screen-shot below:

The updated spreadsheet, including full open-source code and the example above, can be downloaded from:

Eval2.ZIP