This post follows from a discussion at Eng-Tips
The original question was for a way of displaying the value of each component of an Excel formula. It is easy to display the formula, rather than the result, for all the formulae in any worksheet, using File(or Office) – Options – Advanced – Display options for this worksheet (or Tools – Options – View in Excel 2003 and earlier), but if you want to display the value of each cell reference in the formula, there is no built-in way to do this.
A link was provided to a macro by forum member macropod, which provided the required functionality, sending the results to a message box. I have modified this routine to work on a column of formulae, rather than a single cell, and to write the results either to the adjacent column, or if a second range is selected, to this second range. It would be convenient to write this routine as a Function, rather than a sub, but the routine used the method “Range.precedents.Cells”, and it seems that this does not work inside a function.
A sample worksheet, including open source code, may be downloaded from Getrefs.xls
The macro code and screenshots of the macro in operation are shown below:
Sub GetRefs() Dim MyRange As Range, strFormula As String, strVal As String, FormCell As Range Dim NumRows As Long, FormA() As String, i As Long, Outrange As Range NumRows = Selection.Rows.Count ReDim FormA(1 To NumRows, 1 To 1) i = 1 For Each FormCell In Selection.Areas(1).Cells With FormCell strFormula = .Formula For Each MyRange In .Precedents.Cells With MyRange strVal = " " & Range(.Address).Value & " " strFormula = Replace(strFormula, .Address, strVal) strFormula = Replace(strFormula, .Address(RowAbsolute:=False), strVal) strFormula = Replace(strFormula, .Address(ColumnAbsolute:=False), strVal) strFormula = Replace(strFormula, .Address(RowAbsolute:=False, ColumnAbsolute:=False), strVal) End With Next strVal = "' " & .Formula & "; " & strFormula End With FormA(i, 1) = strVal i = i + 1 Next With Selection If .Areas.Count > 1 Then Set Outrange = .Areas(2) Else Set Outrange = Selection.Offset(0, 1) End If End With Outrange.Value = FormA End Sub