How Determine, cell has got reference.

[Guest]

Hi, Kris here
I want to know how to determine wheather a cell in a worksheet has got
reference to other workshhet or reference in the same worksheet.

Thanks

 

[Peter T]

Hi Kris,

Sub Test()
Dim cel As Range, ar As Range
Dim rDeps As Range, rDirDeps As Range, rDirPrecs As Range

Set cel = Range("B2")

On Error Resume Next
Set rDeps = cel.Dependents
Set rDirDeps = cel.DirectDependents
Set rDirPrecs = cel.DirectPrecedents
On Error GoTo 0

If Not rDeps Is Nothing Then
For Each ar In rDeps.Areas
Debug.Print ar.Address
For Each c In ar.Cells
Debug.Print , c.Address
Next
Next
End If
If Not rDirDeps Is Nothing Then
For Each ar In rDirDeps.Areas
Debug.Print ar.Address
For Each c In ar.Cells
Debug.Print , c.Address
Next
Next
End If

If Not rDirPrecs Is Nothing Then
For Each ar In rDirPrecs.Areas
Debug.Print ar.Address
For Each c In ar.Cells
Debug.Print , c.Address
Next
Next
End If

End Sub

Regards,
Peter T

 

[Peter T]

Sorry, that doesn't answer your question at all, will look later

Regards,
Peter T

 

[Tim Williams]

Cell have a "Precedents" collection, but this doesn't include references to
other sheets, only the sheet the cell is in.

It would be tricky to determine absolutely (using VBA) whether there are any
references to other sheets - you will have to take into account such things
as named ranges. And of course any use of INDIRECT() will also pose a
problem.

I'd be interested in seeing what others might suggest though.

Tim

 

[Peter T]

Tricky! Tim has outlined the basic problem, namely the Dependents &
Precedents collections only include cells on same sheet. That implies a lot
of tedious parsing of cell formulas throughout the entire wb is required
looking for cell references, not to mention use of Indirect, Named formulas
and no doubt elsewhere such as CF's.

I had assumed, unfortunately wrongly it seems, that because Trace Dependents
& Precedents indicates references on other sheets there would be a simpler
way.

Regards,
Peter T

PS, I haven't checked the archives but this surely must have come up before.