[Rolando Mota]

I'm having all sorts of trouble figuring out how Excel tracks a dependent or precedent that points to another worksheet or workbook. The following code only returns a cell that has dependents in the same worksheet.

on error resume next
for each cell in ActiveSheet.UsedRange
  numDependents = 0
  numDependents = cell.dependents.count
  if numDependents then
    x = x & " " & cell.Address
  end if
next
msgbox(x)

I've even tried looking through the cell (or whatever you want to name it) object that is created in the routine in the locals window and i can't find any reference to off sheet or off workbook dependents or precedents in its objectmodel. Where is this information stored?

What is even dumber is you can turn on the precedent arrows that point to off sheet/workbook dependents, but can't pull the address information from them.

I know I'm missing some fundamental concept in how Excel stores information for their calculations...somone please enlighten me before I pull all my hair out.

[mark007]

I've never really used these properties before but I believe what you are after are the properties:

.dependents
.directdependents
.precedents
.directprecedents

All of these return a range.

For example the following placed a boredr round all the cells that were used in the formula contained in cell d15:

[VB]
With Range("d15").Precedents
.BorderAround xlSolid, xlMedium
End With
[VB]

Hope that helps

[Mike Rosenblum]

I'm really sorry to tell you this... but there is no easy way to get the .Dependents or .Precedents that lie on another Worksheet or Workbook. It's simply a limitation of these properties, which is not well documented, unfortunately.

If you bring up online help on .Precedents, you'll see that it reads:

"Returns a Range object that represents all the precedents of a cell. This can be a multiple selection (a union of Range objects) if there's more than one precedent. Read-only."

So no clue there. But if you read help on .Dependents you'll see:

"Note: The Dependents property only works on the active sheet and can not trace remote references."

It is a bummer, I must admit. Not sure why they limited it like this. One possibility is that Ranges, although technically possible to handle multi-worksheet selections (MSFT calls them 3D ranges) don't really work well in my experience. So they might have had to return a Collection or Array of Ranges, which they may have deemed too much trouble for either them or the User (us). Or it may have to do with how their calculation table is built that it is a lot easier for them to retrieve references that lie on the same WS (such references certainly calculate faster, anyway).

If you are REALLY determined, you will have to go through the cell's formula, parsing it. This will be a LOT of work. I almost don't know where to begin, although one key character you will want to focus on would be the exclamation point ("!"), delineating an address on another Worksheet. Be sure that this is a really important task if you decide to go in this direction, because it will almost certainly turn into a sink-hole of time.

Sorry for the bummer answer...
Mike

[Rolando Mota]

What I wanted to do is find all cells in a set of workbooks that have no precedents and have dependents (the input cells). I didn't want to parse cell formulas because I don't see a good way to find cell dependents using that method.

Using the .ShowDependents and .NavigateArrows methods for the ActiveCell will work if you compare the full address of the active cell before and after you activate the methods. However, when i loop through the ActiveRange on each worksheet of each workbook this method is grossly inefficient. The spreadsheet I'm working with is on the order of 5 million cells and I'd rather not wait an hour or more for the macro to run (i turned on the timer to verify this and I'm not exaggerating).

If someone could show me a better way of doing this I'd greatly appreciate it.

[Mike Rosenblum]

Checing a Worksheet's .SpecialCells(xlCellTypeConstants) will get you a whole set of Cells with no precedents. Youl just have to check For Each Cell within that group to see if they have Dependents.

This idea will miss "silly" formulas such as "=13" or "=1+2" but for the most part, this will get them all. It's a good start, anyway.

If you need to pick up "=13" situations as well, then you should make use of WS.SpecialCells(xlCellTypeFormulas) and loop through those, also looking for where .Precedents = Nothing and .Dependents <> Nothing.

Beware, that you'll need some error handling to handle when .SpecialCells(), .Precedents, and .Dependents = Nothing.

-- Mike

[Rolando Mota]

Thanks for the help Mike. Just for reference this is what I ended up using to find precedents and dependents. One glitch though...you have to have all the workbooks you are linked to open. Otherwise it will not be able to activate the precedent or dependent cell on that workbook. Not the most efficient solution I’ve ever created lol but it works lol.

Const PRECEDENT As Integer = 1
Const DEPENDENT As Integer = 2

Function findDependent(workbookName As String, worksheetName As String, cellAddress As String, precOrDep As Integer)
'Description:  Returns true for a given cell if it has a precedent or dependent otherwise false.
'It works for off sheet and off workbook dependents as long as the workbook it is linked to is open during the check.
'Requred Parameters:
    'workbookName is the name of the workbook the cell you want to check is in
    'worksheetName is the name of the worksheet the cell you want to check is in
    'cellAddress is the address of the cell you want to check
    'precOrDep should be set to the global variable PRECEDENT or DEPENDENT
'Author:    Scott Rossell
'Date:      9-2-03

    Dim itExists As Boolean
    ' not necessary
    'On Error GoTo errorSection
    
    'need to have the correct sheet and cell active for this to work right.
    Workbooks(workbookName).Activate
        Worksheets(worksheetName).Activate
        Range(cellAddress).Activate
    Sheets(worksheetName).ClearArrows
    Select Case precOrDep
        Case PRECEDENT
            ActiveCell.ShowPrecedents
            ActiveCell.NavigateArrow True, 1 'index starts at 1 not 0
            If ActiveCell.Address = cellAddress And ActiveCell.Worksheet.Name = worksheetName Then
                itExists = False
            Else
                itExists = True
            End If
        Case DEPENDENT
            ActiveCell.ShowDependents
            ActiveCell.NavigateArrow False, 1
            If ActiveCell.Address = cellAddress And ActiveCell.Worksheet.Name = worksheetName Then
                itExists = False
            Else
                itExists = True
            End If
    End Select
    
    If itExists Then
        'If there was dependent change active cell back to the origional cell and return true
        Workbooks(workbookName).Activate
        Worksheets(worksheetName).Activate
        Range(cellAddress).Activate
        findDependent = itExists
        Exit Function
    Else
        'if there were no dependents or precedents return a false value and exit.
        findDependent = itExists
        Exit Function
    End If
    Exit Function
    
'not necessary
'errorSection:
'    Select Case Err
'        Case 9
'            Resume Next
'        Case Else
'            MsgBox (Err & " " & Error(Err))
'    End Select

End Function

[Mike Rosenblum]

For a continuing discussion and several solutions to this problem, please see:

http://www.xtremevbtalk.com/showthread.php?t=317069

-- Mike