Showing numerical values of formulae cell references

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

  strVal = "' " & .Formula & "; " & strFormula
        End With
  FormA(i, 1) = strVal
  i = i + 1

    With Selection
        If .Areas.Count > 1 Then
  Set Outrange = .Areas(2)
  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

This entry was posted in Excel, VBA and tagged , , , , . Bookmark the permalink.

9 Responses to Showing numerical values of formulae cell references

  1. Steve Sexton says:

    Wow, what a great way to document a worksheet.


  2. ikkeman says:

    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?


  3. Georg says:

    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
    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 . Perhaps your macro might be turned into a matrix function this way?


    • dougaj4 says:

      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., 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.


  4. Pingback: how can I have excel show me the numbers used in formulas

  5. Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s