Formula reference index?
 

Sorry I couldn't be more help..

After reading your post, I decided to research the problem a little more.

From what I can tell, you're on the right path. There has been some success
by others using NavigateArrow to track precedents over sheet boundaries.
Apparently this is not an easy task.
It can get pretty slow too.
http://groups.google.co.nz/groups?hl...52dd1bb%40mars


I had another idea to deconstruct a formula into it's base components
(operators, operands and functions) and test for ranges.
I've already had some success extracting arguments from formulas (ref my
website) though I think it'll be a bigger job to break down equations
further.
Probably easier just to use NavigateArrow

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Rob van Gelder" wrote in message
news:...
Not an easy one... I don't know of a property which reports it over the
sheet boundary.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Doug Glancy" wrote in message
...
Rob,

After I asked it occurred to me that that's all Help is talkng about.

I'm trying to trace off-sheet links and I was hoping for something that
also accessed references to other sheets and workbooks. Precendents and
DirectPrecedents don't do that, which is what brought me to NavigateArrow
in the first place. My code does a NavigateArrow and then compares the
sheet and workbook of the new activecell to those in which the link is.
However, if the arrow refers to a different workbook and that workbook is
closed then the activecell remains in the workbook with the link and my
test for an off-sheet precedent fails.

Thanks,

Doug

"Rob van Gelder" wrote in message
...
Sub test()
Dim rng As Range

For Each rng In Range("A5").DirectPrecedents
Debug.Print rng.Address
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Doug Glancy" wrote in message
...
From VBA help for the NavigateArrow method is this description of the
its second parameter:
"ArrowNumber Optional Variant. Specifies the arrow number to
navigate; corresponds to the numbered reference in the cell's formula."

I'm interested in accessing the "numbered reference." I imagine
something like:
range("A1").formula.references(1),
but I can't figure out anything. Is there a way to access the
references in a formula?

Thanks,

Doug Glancy

Formula reference index?
 

Rob,

Thanks for the follow-up. I had seen that post too. It has the same
limitation I described, I think, in that if the workbook is closed Navigate
Arrow doesn't return an error and the activecell stays the same.

I might be able to check the formula against the workbook's linked file
collection.

Fortunately, it's just something I'm goofing around with. If I find
anything interesting solution, I'll let you know.

Doug Glancy

"Rob van Gelder" wrote in message
...
Sorry I couldn't be more help..

After reading your post, I decided to research the problem a little more.

From what I can tell, you're on the right path. There has been some
success
by others using NavigateArrow to track precedents over sheet boundaries.
Apparently this is not an easy task.
It can get pretty slow too.

http://groups.google.co.nz/groups?hl...52dd1bb%40mars


I had another idea to deconstruct a formula into it's base components
(operators, operands and functions) and test for ranges.
I've already had some success extracting arguments from formulas (ref my
website) though I think it'll be a bigger job to break down equations
further.
Probably easier just to use NavigateArrow

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Rob van Gelder" wrote in message
news:...
Not an easy one... I don't know of a property which reports it over the
sheet boundary.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Doug Glancy" wrote in message
...
Rob,

After I asked it occurred to me that that's all Help is talkng about.

I'm trying to trace off-sheet links and I was hoping for something that
also accessed references to other sheets and workbooks. Precendents
and
DirectPrecedents don't do that, which is what brought me to
NavigateArrow
in the first place. My code does a NavigateArrow and then compares the
sheet and workbook of the new activecell to those in which the link is.
However, if the arrow refers to a different workbook and that workbook
is
closed then the activecell remains in the workbook with the link and my
test for an off-sheet precedent fails.

Thanks,

Doug

"Rob van Gelder" wrote in
message
...
Sub test()
Dim rng As Range

For Each rng In Range("A5").DirectPrecedents
Debug.Print rng.Address
Next
End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Doug Glancy" wrote in message
...
From VBA help for the NavigateArrow method is this description of the
its second parameter:
"ArrowNumber Optional Variant. Specifies the arrow number to
navigate; corresponds to the numbered reference in the cell's
formula."

I'm interested in accessing the "numbered reference." I imagine
something like:
range("A1").formula.references(1),
but I can't figure out anything. Is there a way to access the
references in a formula?

Thanks,

Doug Glancy