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:
Pingback: #Excel Super Links #131 – shared by David Hager | Excel For You
Pingback: UDF to replace cell references with values | Newton Excel Bach, not (just) an Excel Blog