Why so hard to get a cell's formula dependents/precedents

[jasmith4]

I've been working on code which traverses a cell's formula dependents (precedents too -- please assume both throughout), and it seems Microsoft fights you all the way! If a cell's dependent is on a different tab, the cell's Dependents property yields an error! But you CAN use ShowDependents and then NavigateArrow -- but only if the tab that has the dependent is visible: what if it's hidden? And forget it if it's in a different file, and the file isn't open. Yet Excel's UI can put up a list of the dependents if you double-click the external-dependent arrow, so it must be possible to get a list of all dependents.

The only way I've found to get a complete list is to set up a double-loop, one for arrow number, one for external-link index, and keep trying NavigateArrow until the selection doesn't move. But again, each dependent's tab has to be visible, and each relevant file has to be open.

Is there REALLY no way of just getting a list or collection of dependents, which, if empty, would have a Count/CountLarge property equal to zero? Really, Microsoft??

[mancubus]

check this thread.

http://www.vbaexpress.com/forum/show...s-in-a-formula

[mikerickson]

Yes, It isn't easy.
I've written that double loop myself a few times. (And it misses any references to closed workbooks.)

Why is (are) .DependentCells is only the cells on the same sheet as the precedent cell.

It returns a Range object. The cells of a Range object have to all be on the same sheet. If .DependentCells returned all dependent cells, it would have to be a collection (to include dependents that were on different sheets). I don't know why Microsoft went the Range object choice, but that explains why DependentCells are all on the same sheet as the precedent cell.

I don't know why you are working on this, but (FYI) if you Cut/Paste (rather than Copy/Paste) all dependent and precedent references will adjust automatically.