Run-time error '424': Object required

[Phil Bewig]

I have extracted from a larger program the following
code to color the selected cells green and their
dependents red:

Sub ColorDeps1()
Dim C As Range
Dim X As Range
For Each C In Selection
C.Interior.ColorIndex = 4 'green
If HasDependents(C) Then
For Each X In C.Dependents
X.Interior.ColorIndex = 3 'red
Next X
End If
Next C
End Sub

This code works properly. However, a slight change
causes the code to fail:

Sub ColorDeps2()
Dim C As Range
For Each C In Selection
C.Interior.ColorIndex = 4 'green
If HasDependents(C) Then
ReColorDeps (C)
End If
Next C
End Sub

Sub ReColorDeps(C As Range)
Dim X As Range
For Each X In C.Dependents
X.Interior.ColorIndex = 3 'red
Next X
End Sub

The code fails with the line ReColorDeps(C) in
the ColorDeps2 subroutine highlighted in yellow.
The only difference is that the loop over the
dependents has been cut out of the main subroutine
and placed into a separate subroutine of its own.

Unfortunately, in the larger program from which
this is a part I don't have the option of including
the loop over the dependents in the main body of
the code; it must be in a subroutine as above.

I don't understand what is wrong with ColorDeps2.
Can anyone explain the error message and tell me
how to fix my code?

By the way, my implementation of HasDependents
works but looks ugly. Does anyone have suggestions
of how to make it better?

Function HasDependents(C As Range)
On Error GoTo NoDependents
If C.Dependents.Count > 0 Then
HasDependents = True
Exit Function
End If
NoDependents:
HasDependents = False
End Function

Thanks to all for your help.

Phil

 

[Chip Pearson]

Phil,

Change
ReColorDeps (C)
to
ReColorDeps C

In general, you never want to use parentheses when passing
arguments to Sub procedures.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Doug Glancy]

Chip,

I didn't know that. Can you explain a little more?

Thanks,

Doug

 

[Phil Bewig]

Phil,

Change
ReColorDeps (C)
to
ReColorDeps C

In general, you never want to use parentheses when passing
arguments to Sub procedures.

Of course. I knew that. But I've spent more time lately
programming other languages than VBA, and I simply couldn't
see that error no matter how long I looked at it.

Thanks for the quick answer.

Phil