Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing part of cell reference in INDEX - MATCH formula | Excel Worksheet Functions | |||
Cell color index formula reference instead of =counta | Excel Discussion (Misc queries) | |||
Index match formula works in for one worksheet reference but not another | Excel Worksheet Functions | |||
INDEX function need to have col reference to be formula | Excel Worksheet Functions | |||
Formula reference index? | Excel Programming |