Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Cell Dependents (VBA/Excel/2003)

    Good Afternoon All

    If the active cell has dependents on the same sheet, the following code will provide a count of them.
    ActiveCell.Dependents.Count

    I have been unable to find code that will indicate whether there are dependent cells in other sheets of the workbook.

    Can anyone provide insight?

    T.I.A.
    Regards
    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Dependents (VBA/Excel/2003)

    I haven't actually tried it, but perhaps you can adapt the ideas from Jim Cone's code in Testing for a cell being dependat on others.

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell Dependents (VBA/Excel/2003)

    Thanks Hans
    Irrespective of whether this solves my particular problem, it appears to be a worthwhile tool to retain.
    Regards
    Don

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell Dependents (VBA/Excel/2003)

    Further to my last post; it should be noted that Jim Cone's code does not identify dependents of a cell unless that cell is a formula. I don't understand the code sufficient to offer an upgrade opinion, and the alligators are too active for me to attempt it at the moment. It also does not identify any off-sheet dependents.

    The existence of off-sheet dependents must be detectable in some manner, as their existence is identified with "Tools | Formula Auditing | Trace Dependents".
    Regards
    Don

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Dependents (VBA/Excel/2003)

    I've searched the newsgroups, but there doesn't seem to be an easy way to retrieve off-sheet dependents. Microsoft obviously programmed it into Excel, but they haven't exposed it in the Excel VBA object model. The only "tool" provided by Microsoft is Dependents, and as explicitly noted in the Help file, that works for references within the same sheet only.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Dependents (VBA/Excel/2003)

    You can get at them using the NavigateArrow method in conjunction with the ShowDependents method.

    I used this technique to build this tool (not available for free unfortunately!)
    Attached Images Attached Images
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Dependents (VBA/Excel/2003)

    Ah, thanks! Didn't know that, so I learned something new today!

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell Dependents (VBA/Excel/2003)

    Thank you Jan Karel

    I have taken your advice and created the attached module. Which although it satisfies my immediate needs is, I am certain, quite inelegant. It is anything but speedy, also it will only capture one off-sheet dependent for a given cell.

    Any thoughts will be appreciated.


    T.I.A.
    Attached Files Attached Files
    Regards
    Don

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Dependents (VBA/Excel/2003)

    The navigateArrow method has a couple of arguments, one of them enables you to specify which reference to follow of all off-sheet references (Those are al counted as a single dependent!) You'd have to upsize your look so you're always catching all offsheet refs. Then somehow check that you've had them all by checking if you have gotten any duplicates (Excel is smart enough not to list those).

    Another catch: if your formula has both A1:A100 and cell A2 in its arguments, you'll only get A1:A100 returned, A2 is blended in that range and omitted as a single dependent.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell Dependents (VBA/Excel/2003)

    Thanks Jan Karel
    I am unable to find a reference to upsize in Excel, and fail to grasp its relevance in this discussion. Can you help me out?

    T.I.A.
    Regards
    Don

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Dependents (VBA/Excel/2003)

    Sorry, there was a typo in my message. I meant to say:

    "You need to upsize your loop".

    In other words: you need to use a for-next loop that has a sufficiently high ending point so that it will always catch all off-sheet references, regardless of how many there are. So
    For i=1 to 10000
    'NavigateArrow method goes here
    'Check if the range it takes you to has been visited or not, or if the index has become higher than the # of off-sheet refs (by checking if the NavigateArrow method has generated an error or not).
    Next i
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell Dependents (VBA/Excel/2003)

    <P ID="edit" class=small>(Edited by wdwells on 15-Mar-07 16:45. Attachment included)</P>Thank you Jan Karel
    Your guidance has been most helpful; but there is a puzzlement.
    The attached file has only two cells on Sheet2 with data; both refer to Sheet1 cells.
    <UL><LI>A1 "=SUMPRODUCT(Sheet1!C7:C11,Sheet1!E7:E11)"
    <LI>A3 "=Sheet1!C9"[/list] Sheet1 has 11 off-sheet dependants, however I get different answers depending on what else is going on in Sheet1. Changing the formula in cells D3 and D4 of Sheet1 affect the results of the code as follows: <pre>Changed Formulae
    $D$3 5
    $D$4 40

    Sheet1
    # Source Dependant
    1 - Sheet1(7,3)-----Sheet2$A$1
    2 - Sheet1(7,5)-----Sheet2$A$1
    3 - Sheet1(8,3)-----Sheet2$A$1
    4 - Sheet1(9,3)-----Sheet2$A$3
    5 - Sheet1(9,3)-----Sheet2$A$1
    6 - Sheet1(10,3)-----Sheet2$A$1

    Sheet2
    # Source Dependant
    Finished
    ##################################################

    Changed Formulae
    $D$3 =SUM(E7:E11)
    $D$4 40

    Sheet1
    # Source Dependant
    1 - Sheet1(7,3)-----Sheet2$A$1
    2 - Sheet1(7,5)-----Sheet2$A$1
    3 - Sheet1(8,3)-----Sheet2$A$1
    4 - Sheet1(8,5)-----Sheet2$A$1
    5 - Sheet1(9,3)-----Sheet2$A$3
    6 - Sheet1(9,3)-----Sheet2$A$1
    7 - Sheet1(9,5)-----Sheet2$A$1
    8 - Sheet1(10,3)-----Sheet2$A$1
    9 - Sheet1(10,5)-----Sheet2$A$1

    Sheet2
    # Source Dependant
    Finished
    ##################################################


    Changed Formulae
    $D$3 5
    $D$4 =SUM(C7:C11)

    Sheet1
    # Source Dependant
    1 - Sheet1(7,3)-----Sheet2$A$1
    2 - Sheet1(7,5)-----Sheet2$A$1
    3 - Sheet1(8,3)-----Sheet2$A$1
    4 - Sheet1(9,3)-----Sheet2$A$3
    5 - Sheet1(9,3)-----Sheet2$A$1
    6 - Sheet1(10,3)-----Sheet2$A$1
    7 - Sheet1(11,3)-----Sheet2$A$1
    8 - Sheet1(11,5)-----Sheet2$A$1

    Sheet2
    # Source Dependant
    Finished
    ##################################################

    Changed Formulae
    $D$3 =SUM(E7:E11)
    $D$4 =SUM(C7:C11)

    Sheet1
    # Source Dependant
    1 - Sheet1(7,3)-----Sheet2$A$1
    2 - Sheet1(7,5)-----Sheet2$A$1
    3 - Sheet1(8,3)-----Sheet2$A$1
    4 - Sheet1(8,5)-----Sheet2$A$1
    5 - Sheet1(9,3)-----Sheet2$A$3
    6 - Sheet1(9,3)-----Sheet2$A$1
    7 - Sheet1(9,5)-----Sheet2$A$1
    8 - Sheet1(10,3)-----Sheet2$A$1
    9 - Sheet1(10,5)-----Sheet2$A$1
    10 - Sheet1(11,3)-----Sheet2$A$1
    11 - Sheet1(11,5)-----Sheet2$A$1

    Sheet2
    # Source Dependant
    Finished
    ##################################################
    </pre>

    Can someone explain what is going on?

    T.I.A.
    Attached Files Attached Files
    Regards
    Don

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Dependents (VBA/Excel/2003)

    Hi,

    Remove the Exit For, it prevents all cells to be analysed.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cell Dependents (VBA/Excel/2003)

    Thank you Jan Karel
    You have once again demonstrated not only your depth of knowledge, but your patience as well.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •