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 Bewes" <antbewes@ummah.org> wrote in message
news:e976f66d.0503161030.49ce497c@posting.google.com...
> 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

[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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Phillips]

Doesn't that do so?

--

HTH

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


"ant" <anonymous@devdex.com> wrote in message
news:uFmdZEtKFHA.1308@TK2MSFTNGP15.phx.gbl...
>
> 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
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

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