Dana: help with your Demo() macro to "expand" formulas

[joeu2004]

Dana (and other VBA experts),

In the thread "Expanding Formulas", you offered the macro below as a
(great!) starting point. I made some minor modifications.

The Replace() operation seems to make the appropriate substitution
only for relative references of the form B1. For example, absolute
references of the form $B$1 are not replaced.

The root cause of the problem seems to be that p.Address(false,false)
always returns a relative reference, even if the original reference is
absolute. I presume that the Replace() operation is ineffective
because "B1" does not match "$B$1" in the formula.

Changing things to p.Address(true,true) rectifies that; but of course,
then relative references are not substituted.

Can the macro be changed easily to substitute all forms of references?

The modified macro:

Sub Demo()
Dim s, p, a, v
Dim cell

Debug.Print "-----------"
For Each cell In Selection
s = cell.Formula
Debug.Print s
On Error Resume Next
p = cell.Precedents
If Err = 0 Then
For Each p In cell.Precedents
a = p.Address(False, False)
v = p.Value
s = Replace(s, a, v)
Next p
End If
Debug.Print s
Debug.Print Evaluate(s)
Range("Sheet2!" & cell.Address).Formula = s
Next cell
End Sub

 

[Harlan Grove]

The Replace() operation seems to make the appropriate substitution
only for relative references of the form B1. For example, absolute
references of the form $B$1 are not replaced. ....
Can the macro be changed easily to substitute all forms of
references? ....
For Each p In cell.Precedents
a = p.Address(False, False)
v = p.Value
s = Replace(s, a, v)
Next p

....

For Each p In cell.Precedents
s = Replace(Replace(s, "$", ""), p.Address(0, 0), p.Value2)
Next p

Note also the use of Value2 rather than Value. This guards against any
of the precedent cells containing Date values (i.e, containing
nonnegative numbers formatted as dates or times). If the formula were
=A1+A2 and A1 contained the date 7 Jun 2007 and A2 contained 1, I
don't think you'd want the formula converted to =6/7/2007+1 (US date
formatting), which would evaluate to 1.00042707666026 rather than
39241. As a practical matter, always use Value2 rather than Value.