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(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

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.

Like

I did nt get the procedure can u please explain me

Like

• dougaj4 says:

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.

Like

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?

Like

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 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?

Like

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

Like

This site uses Akismet to reduce spam. Learn how your comment data is processed.