Checking cell for Dependents
ExcelMonkey
How do you check, i n VBA, and see if a cell has any dependents. Is
there a property that I can check (Boolean) that will tell me whether
this is the case?
Range("A2"). [Dependent Property]
Thanks
--
ExcelMonkey
------------------------------------------------------------------------
ExcelMonkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5221
View this thread: http://www.excelforum.com/showthread.php?threadid=276188
crispbd
VBA has some built-in functions for identifying the dependents of a
range or cell:
Range("A1").Dependents.Select
or
ActiveCell.ShowDependents
--
crispbd
------------------------------------------------------------------------
crispbd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10880
ExcelMonkey
I was hoping that there would be dependent properties that I could test,
TRUE or FALSE. It looks as though the examples you have are methods.
Is it possible to test the method to see if it fails. That is if if
you go
cell.dependents.select
and there are not any dependents will the code fail. If so, can I trap
the error and pass this to a TRUE FALSE variable.
Thanks
RK
--
ExcelMonkey
------------------------------------------------------------------------
ExcelMonkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5221
Robin Hammond
for cells on the same sheet as the original. If you want it to be exhaustive
you also have to use the showdependents method and navigate method, then use
an error trap to see if a dependent exists in another sheet. If you want an
exhaustive suite of dependent, precedent and circularity testing tools, you
could check out my XspandXL add on my site. For the time being, this will
tell you if a range of one or more cells has any internal dependents.
Public Function HasInternalDependents(rngTest As Range) As Boolean
Dim rngCell As Range
Dim rngDep As Range
For Each rngCell In rngTest
Set rngDep = Nothing
On Error Resume Next
Set rngDep = rngCell.Dependents
On Error GoTo 0
If Not rngDep Is Nothing Then
HasInternalDependents = True
Exit Function
End If
Next rngCell
End Function
Robin Hammond
www.enhanceddatasystems.com
"ExcelMonkey" <ExcelMonk...@excelforum-nospam.com> wrote in message
news:ExcelMonk...@excelforum-nospam.com...
ExcelMonkey
Thaknks Robin. Quick question. If I wanted to count the number of
dependents the cell has, how would I incorporate this into your code?
I am assuming that I would use a Count Method on the rngDep. But since
the code is simply creating a boolean ouput (TRUE/FALSE) I am assuming
that a count will only be 1 or 0. Any ideas? Thnks
Public Function HasInternalDependents(rngTest As Range) As Boolean
Dim rngCell As Range
Dim rngDep As Range
For Each rngCell In rngTest
Set rngDep = Nothing
On Error Resume Next
Set rngDep = rngCell.Dependents
On Error GoTo 0
If Not rngDep Is Nothing Then
HasInternalDependents = True
Exit Function
End If
Next rngCell
End Function
ExcelMonkey
Better question for you Robin. I am using the code below to call your
function on a cell in that I call StartCell. I have created dependents
in the same sheet and several other sheets in my workbook. The code
below will actually select each depent cell. However you will notice
that I am using a For Each loop (1 to 1000000). I could not figure out
how to use a For Each loop with NavigateArrows. I also assumed that I
could limit the loop by putting a On Error stmt in so that when it
tried to navigate an error that does not exist it would create an error
and exit the loop. This is not working.
Now this is actually overkill, because all I really want to do is
identify WHEN a navigation arrow takes me to a sheet other than the
sheet that the cell is in. How do I trap the event that takes me to
another sheet? I don't really care where it goes, but I do care IF it
goes. Thanks
Sub Thing()
Dim Cell As Range
Dim HasDep As Boolean
Dim CountDep As Integer
Dim X As Double
Set Cell = Range("StartCell")
HasDep = HasInternalDependents(Cell)
If HasDep = True Then
Cell.ShowDependents
End If
For X = 1 To 1000000
On Error Resume Next
Cell.NavigateArrow True, 1
ActiveCell.NavigateArrow TowardPrecedent:=False, ArrowNumber:=X, _
LinkNumber:=1
On Error GoTo 0
Next X
End Sub
keepITcool
it was an interesting experiment..BUT...
Following works nicely for a few 'external dependendents'.
If you have more then say 500 or so, the .NavigateArrow takes
progressively longer to execute making this approach fairly limited in
it's application.
NOTE for external dependents you'll have to increment the the THIRD
parameter not the 2nd. I've built in a escape hatch to exit on more than
1024 deps.
Function ExternalDependents(SrcRange As Range) As Collection
Dim DstRange As Range, Externals As New Collection, n&
If TypeOf Application.Caller Is Range Then GoTo theExit
If SrcRange.Cells.Count > 1 Then GoTo theExit
On Error Resume Next
Application.ScreenUpdating = False
With SrcRange
If Not .DirectDependents Is Nothing Then
.ShowDependents True
.ShowDependents False
'Escape if there are too many...
Set DstRange = .NavigateArrow(False, 1, 1025)
If Err = 0 Then
Externals.Add CVErr(xlErrValue)
GoTo theExit
End If
n = 1
Do
Set DstRange = .NavigateArrow(False, 1, n)
If Err <> 0 Then Exit Do
Externals.Add DstRange
n = n + 1
Loop While n <= 1024
End If
End With
Stop
theExit:
Application.ScreenUpdating = True
Set ExternalDependents = Externals
End Function
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
ExcelMonkey wrote in message
<news:ExcelMonk...@excelforum-nospam.com>:
ExcelMonkey
Thank-you. However I am not sure how to use this? It does not seem as
though this returns a boolean. When I try to call it below I get an
error stmt saying "Argument not optional" inplying that I have not put
in an argument. Yet you only have one argument. What am I doing
wrong????
Sub Thing()
Dim Cell As Range
Dim HasIntDep As Boolean
Dim ExtIntDep As Boolean
Dim CountDep As Integer
Dim X As Double
Set Cell = Range("StartCell")
ExtIntDep = ExternalDependents(Cell)
End Sub
n = 1
--
keepITcool
NOTE: when replying from ExcelForum/ExcelTip be aware that other users
relying on NNTP will not be informed of your replies!
The webmaster of ExcelTip/ExcelForum has been repeatedly informed that he
is violating NNTP RTF specifications by not including the reference field
in the header of messages posted as Replies by users of ExcelTip/
ExcelForum.
hmm..
first note the syntax of the function in your quoted post IS NOT CORRECT.
(several dots inside the with/end with construct are missing.
(please check and compare to my original post)
Second note that my function returns a collection
Third and most important.. the function is insufficently tested and may
return erroneous results.
I'll sleep now, and give you a more complete answer with edits tomorrow.
(and i'll try to find the otherm messages in the thread)
Please answer if you just require only a BOOLEAN test IF a cel has
external references. Also note this arrow tracing is painfully slow if the
count goes up. to trace ONE cell which has 60000 dependents may take up to
a minute.
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
ExcelMonkey wrote in message
<news:ExcelMonk...@excelforum-nospam.com>:
>
> Thank-you. However I am not sure how to use this? It does not seem as
> though this returns a boolean. When I try to call it below I get an
> error stmt saying "Argument not optional" inplying that I have not put
> in an argument. Yet you only have one argument. What am I doing
> wrong????
>
> Sub Thing()
> Dim Cell As Range
> Dim HasIntDep As Boolean
> Dim ExtIntDep As Boolean
> Dim CountDep As Integer
> Dim X As Double
>
> Set Cell = Range("StartCell")
> ExtIntDep = ExternalDependents(Cell)
>
> End Sub
>
> Function ExternalDependents(SrcRange As Range) As Collection
>
> Dim DstRange As Range, Externals As New Collection, n&
>
> If TypeOf Application.Caller Is Range Then GoTo theExit
> If SrcRange.Cells.Count > 1 Then GoTo theExit
>
> On Error Resume Next
> Application.ScreenUpdating = False
>
> With SrcRange
> If Not .DirectDependents Is Nothing Then
> ShowDependents True
ExcelMonkey
Yes I am looking for a test (boolean) to see IF is has a dependent,
What I am doing is testing to see which cells in my spreadsheets are
inputs. The test will say:
If Hasprecedents(Range) = FALSE And If Hasdependenets(Range) = TRUE
Then
IsInput = TRUE
I was using your code to do the dependent part of the test.
Thnx
keepITcool
NOTE: when replying from ExcelForum/ExcelTip be aware that other users
relying on NNTP will not be informed of your replies!
ExcelTip/ExcelForum's script for handling replies is faulty and is in
violation of RFC 1036 - Standard for interchange of USENET messages - as
they do not include the REQUIRED REFERENCES field in the message header
(article 2.2.5 of the RFC)
'======================================================================
If you are a user from ExcelTip/ExcelForum
please contact your webmaster
mailto://IRU...@EXCELTIP.COM
again and again and again
until he does something about this !!!
'======================================================================
ExcelMonkey,
Ok. that makes it clearer.
I assume you're testing for precedents iso Specialcells(xlConstants)
because you have "input" cells which are in fact formulas?
="MyText" iso myText
=123*2 iso 246
else using speciallcells would be much quicker.
Q: is this correct?
Unfortunately Excel's (direct)precedents/dependents is a crippled method
as it will only test the dependency tree on the SAME sheet.
Q: Can you live with a "simple" test DirectDependents/Precedents on the
"SAME" sheet?
As already indicated in previous posts the tracearrow method CAN be very
slow..If you have many dependents.
I dont know wow often you'd need to run this check/ or how universal
your function must be..
An alternative approach would be to copy the sheet to new workbook..
and THEN check for external links.
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
ExcelMonkey wrote in message
<news:ExcelMonk...@excelforum-nospam.com>:
>
ExcelMonkey
I actually want to be able to test for dependents and precedents on
other sheets. effectively my models have inputs like all models. I
want to be able to test to see which cells are inputs in the entire
spreadsheets. I will eventually past the cell address of these onto a
summary page as a hyperlink. I have the paste as hyperlink piece
figures out but cannot seem to figure how to identify if in fact a cell
is an input.
So yes, it will be deemed an input if it does not have precedents and
of course it has to have dependents. AlL i need is a boolean
true/false and I am on my way. But I will need it to go beyond the
sheet that the cell is in.
Thx
keepITcool
.. following would work (on my limited test)
it will miss empty cells that have dependents but ONLY if they
are below or to the right of the usedrange.
If a cell is has NO local dependents but has MANY external dependents
you'll note the .NavigateArrow will slow things down. (in some case
bring it to a virtual standstill)
I do test for cells which DO have formulas but DONT use cell references.
(input like = 3*5)
Formulas using named ranges on other sheets(thus have an empty
PRECEDENTS object) may not be picked up correctly.
Adapt to what you actually want to do with the input cells.
(my code will set them to Unlocked and make m RED)
HTH and hope it's enough, cause I dont really want to continue this.
Cheerz!
Jurgen
Option Explicit
Sub UnlockInputCells()
Dim wks As Worksheet, rStart As Range, rCell As Range, n As Long
Set rStart = ActiveCell
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect
If .ProtectContents Then
MsgBox wks.Name & " is protected with a pw."
Exit Sub
End If
.Cells.Locked = True
.Cells.Interior.ColorIndex = xlNone
.ClearArrows
For Each rCell In .Range("a1", .UsedRange(.UsedRange.Count))
n = n + 1
If n Mod 100 = 1 Then
Application.StatusBar = rCell.Address(external:=True)
End If
If HasNoFormula(rCell) Then
If HasDependents(rCell) Then
rCell.Locked = False
rCell.Interior.ColorIndex = 3
End If
End If
Next
.EnableSelection = xlUnlockedCells
'.Protect vbNullString, True, True
End With
Next
rStart.Worksheet.Activate
rStart.Activate
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
Function HasNoFormula(rCell As Range) As Boolean
Dim l As Long
On Error Resume Next
With rCell
If Len(.Formula) = 0 Then
HasNoFormula = True
Else
If .HasFormula = True Then
If .Formula = .FormulaR1C1 Then
l = .Precedents.Count
If l > 0 Then HasNoFormula = True
End If
Else
HasNoFormula = True
End If
End If
End With
End Function
Function HasDependents(rCell As Range) As Boolean
Dim rTest As Range
On Error Resume Next
With rCell
If .DirectDependents Is Nothing Then
.ShowDependents
Set rTest = .NavigateArrow(0, 1, 1)
If rTest.Address(external:=True) <> rCell.Address(external:=True)
Then
HasDependents = True
.Worksheet.ClearArrows
End If
Else
HasDependents = True
End If
End With
End Function
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam
ExcelMonkey wrote in message
<news:ExcelMonk...@excelforum-nospam.com>:
>