How to traverse cell.Dependents

[joeu2004]

First, I am surprised the following alone does not work when there are
no dependents:

dim dep as Range
for each dep in cell.Dependents
[....]
next dep

Normally, the For statement works just fine when it has nothing to do.

But okay, the above produces an error when there are no dependents
("no cells were found").

So what do I test to prevent the error? I thought:

if cell.Dependents.Count > 0 then
for each dep in cell.Dependents
[...]
next dep
end if

But that does not work. I also tried IsNull() and IsEmpty(), to no
avail.

So, what's the correct magic incantation to use?

 

[JLGWhiz]

dim dep as Range
If Not dep Is Nothing Then
for each dep in cell.Dependents
[....]
next dep
End If

 

[Dave Peterson]

I'd use:

Option Explicit
Sub testme()

Dim Dep As Range
Dim AllDeps As Range
Dim Cell As Range

Set Cell = ActiveCell

Set AllDeps = Nothing
On Error Resume Next
Set AllDeps = Cell.Dependents
On Error GoTo 0

If AllDeps Is Nothing Then
'do nothing
Else
For Each Dep In Cell.Dependents
MsgBox Dep.Address
Next Dep
End If

End Sub

First, I am surprised the following alone does not work when there are
no dependents:

dim dep as Range
for each dep in cell.Dependents
[....]
next dep

Normally, the For statement works just fine when it has nothing to do.

But okay, the above produces an error when there are no dependents
("no cells were found").

So what do I test to prevent the error? I thought:

if cell.Dependents.Count > 0 then
for each dep in cell.Dependents
[...]
next dep
end if

But that does not work. I also tried IsNull() and IsEmpty(), to no
avail.

So, what's the correct magic incantation to use?

 

[joeu2004]

Dim AllDeps As Range
[....]
    Set AllDeps = Nothing
    On Error Resume Next
    Set AllDeps = Cell.Dependents
    On Error GoTo 0
    If AllDeps Is Nothing Then

Thanks. I have verified the need for all the elements of your
solution. But I'm still puzzled. If we can set a Range variable
(AllDeps) to Nothing, and if we can test a Range variable (AllDeps)
for "is nothing", why can't we test a property that returns a Range
object (Dependents) for "is nothing" directly?

I know the following does not work:

If cell.Dependents is Nothing Then

But I don't understand why not. If a property returns a Range object,
why doesn't it return Nothing, which seems to be a (null) Range
object, since it can be assigned to a Range variable without error.
(I cannot find an explanation of Nothing.)

 

[Dave Peterson]

I have no idea why it doesn't work.

But if you put a watch on that variable and then step through the code, you'll
see that alldeps doesn't become nothing.

Dim AllDeps As Range
[....]
Set AllDeps = Nothing
On Error Resume Next
Set AllDeps = Cell.Dependents
On Error GoTo 0
If AllDeps Is Nothing Then

Thanks. I have verified the need for all the elements of your
solution. But I'm still puzzled. If we can set a Range variable
(AllDeps) to Nothing, and if we can test a Range variable (AllDeps)
for "is nothing", why can't we test a property that returns a Range
object (Dependents) for "is nothing" directly?

I know the following does not work:

If cell.Dependents is Nothing Then

But I don't understand why not. If a property returns a Range object,
why doesn't it return Nothing, which seems to be a (null) Range
object, since it can be assigned to a Range variable without error.
(I cannot find an explanation of Nothing.)