Okay, I developed the macro below based on my understanding of what I
thought you wanted originally and before I saw you latest posting. It shows
you the formula with all references replaced by their cell's content for the
active cell... it does this in a MessageBox so that you can view the results
in conjunction with the real formula in the Formula Bar. Is this acceptable?
If not, then please explain how you would want the "watch window" you
mentioned to work. Note that it would not be a dynamic display; rather, it
would be called from a macro like the code below is done.
Sub ShowCellValuesInFormula()
Dim R As Range
Dim X As Long
Dim LastRow As Long
Dim Frml As String
Dim Parts() As String
Dim WS As Worksheet
If ActiveCell.HasFormula And Not ActiveCell.HasArray Then
Frml = Replace(ActiveCell.Formula, "$", "")
On Error Resume Next
For Each WS In Worksheets
LastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row + 1
With WS.Range("A" & LastRow)
.Formula = Frml
If Err.Number = 0 Then
For Each R In .Precedents
Parts = Split(Frml, R.Address(0, 0))
For X = 0 To UBound(Parts) - 1
If Parts(X) Like "*!" Then
If Parts(X) Like "*" & WS.Name & "!" Then
Frml = Replace(Frml, R.Address(0, 0),
WS.Range(R.Address).Text, 1, 1)
Frml = Replace(Frml, WS.Name & "!", "", 1, 1)
End If
Else
Frml = Replace(Frml, R.Address(0, 0), Range(R.Address).Text,
1, 1)
End If
Next
Next
.ClearContents
End If
End With
Next
End If
MsgBox Frml
End Sub
--
Rick (MVP - Excel)
"IgorM" wrote in message
...
I realised that if I give you some background for the macro it might help
somehow.
I very often inspect many worksheets that have many long formulas (most of
them have reference to more than 10 worksheets). And because quite often
there are some errors (user typed in wrong numbers, etc) I've been using
F9 to quickly ckeck a value that is behind each reference. But what I'm
really willing to do is to use watch window. So I'm aiming at building a
macro that would automatically extract all references from a cell formula
and for each of the extracted reference add a watch in a watch window.
Hope this helps.
"Rick Rothstein" wrote in message
...
True, but I'm not sure that is how the OP wants to view the "evaluated"
formula. With Evaluate Formula, the evaluation is done step by step
rather than showing all the reference substitutions all at once. If I (or
someone else) can't come up with a method of bridging the sheets, that
may end up being the OP's only way to view the references.
--
Rick (MVP - Excel)
"shg" wrote in message
...
You could use the Evaluate Formula button on the Formula Auditing
toolbar.
--
shg
------------------------------------------------------------------------
shg's Profile: http://www.thecodecage.com/forumz/member.php?userid=13
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=25624