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:
Newton Excel Bach, not (just) an Excel Blog 