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
Wow, what a great way to document a worksheet.
I did nt get the procedure can u please explain me
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.
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?
ikkeman – I’m looking into this, and will post more details when I have time, but you may like to look into:
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.
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
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
Cnv0to15Int_Int2HexStr = myResults(0)
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?
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.
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