Extracting 'exact' local references from formula

[Ant Bewes]

Is there any way to get the exact string representing the local
references as the user entered it in the formula?

For example:

Cell A1 in Sheet1: "=Sheet2!G66+Sheet1!B$7-$A9"

Getting DirectPrecedents yields:
"$B$7" and "$A$9" for Address

What I *really* want is:
"Sheet1!B$7" and "$A9"

The frustrating thing is when I double click in the cell (edit mode)
Excel will colour these exact strings - But I can't find anyway of
getting to them in a macro :-(

Any help appreciated!

Antony

 

[Bob Phillips]

Antony,

A bit nasty, but is this of any use

Set rng = ActiveCell.DirectPrecedents
For Each area In rng.Areas
Debug.Print area.Parent.Name & "!" & area.Address
Next area


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[ant]

Thanks for the heads-up but I will need to preserve the nature of the
users reference, such as:

$A1 / A$1 / $A$1 or
R1C[1] / R[1]C1 / R[1]C[1] etc

Ant

 

[Bob Phillips]

Doesn't that do so?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ant Bewes]

Doesn't that do so?

um.. no!

This was the approach I originally took. I've actually solved the
bigger problem by a different means now - as ever! VBA is a real
obstacle course...