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
Select a single column range and run GetRefs
Results written to the adjacent column
Select two, non-adjacent ranges
Results are written to the second selected range
Newton Excel Bach, not (just) an Excel Blog 
Wow, what a great way to document a worksheet.
LikeLike
I did nt get the procedure can u please explain me
LikeLike
The procedure is shown in the screen shots in the post.
If there is something not working, or something specific you don’t understand, please let us know.
LikeLike
When I try to use the GetRef Macro on a formula referencing cells on another sheet it finds no references (no .Precedents.Cells found for Each MyRange).
is this extension possible?
LikeLike
ikkeman – I’m looking into this, and will post more details when I have time, but you may like to look into:
http://www.ozgrid.com/forum/showthread.php?t=17028&page=1
http://www.mrexcel.com/forum/showthread.php?t=291149&highlight=Precedents
http://stackoverflow.com/questions/5541342/when-called-from-an-excel-vba-udf-range-precedents-returns-the-range-and-not-its
The first two address the precedents on other sheets issue, and the last one gives more details about why the sub won’t work as a UDF.
LikeLike
Hi Doug, nice macro, especially in a multi-lingual environment when the names of the functions are translated automatically by Excel… AFAIK a function is allowed to change only the cell(s) in which it is entered. So when I want to display a(n error) message along with the return value of a function without forcing the user by chance to click “yes” in thousand Msgboxes, I implement a matrix function. See this (pedagogic) example:
Function Cnv0to15Int_Int2HexStr(ByVal j As Integer)' returns a single hexadecimal figure as string in ["0",...,"9","A",..."F"]
' corresponding to decimal number in [0,15]
Dim msgStr As String
Dim myResults(2)
If ((j 15)) Then
' decimal range of a single hexadecimal figure is [0;15], so 0<=j>Cnv0to15Int_Int2HexStr<<: argument = " + Str(j) + " is out of bounds [0;15]!"
myResults(1) = msgStr
ElseIf (j >Cnv0to15Int_Int2HexStr<>Cnv0to15Int_Int2HexStr<= 2) And (Application.Caller.Columns.Count = 1)) Then
Cnv0to15Int_Int2HexStr = Application.Transpose(myResults)
ElseIf ((Application.Caller.Rows.Count = 1) And (Application.Caller.Columns.Count >= 2)) Then
Cnv0to15Int_Int2HexStr = myResults
Else
Cnv0to15Int_Int2HexStr = myResults(0)
End If
End Function
If at least two rows/columns are selected the message is placed below/besides the result, otherwise, only the result is returned. I learned the trick with the
Application.Caller-object from Chip Pearson’s page at http://www.cpearson.com/excel/ReturningArraysFromVBA.aspx . Perhaps your macro might be turned into a matrix function this way?LikeLike
Hi Georg – the problem in this case is that the precedents property doesn’t work when it is called from a UDF, so I don’t think using application.caller will work in this case, but it’s a useful technique.
I have used it occasionally (e.g. https://newtonexcelbach.wordpress.com/2011/01/24/finding-all-range-names-in-a-specified-range/), but usually when I return an array as the result of a UDF I just assign the whole array to the function output. In that case the user has to select the output range in the correct orientation, so perhaps I should think about using application.caller to check when the output is a single row or column, and transposing the output when required.
LikeLike
Pingback: how can I have excel show me the numbers used in formulas
Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog