Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,comp.apps.spreadsheets
|
|||
|
|||
Run-time error '424': Object required
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '424': Object required
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 "Phil Bewig" wrote in message om... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error '424': Object required
Chip,
I didn't know that. Can you explain a little more? Thanks, Doug "Chip Pearson" wrote in message ... 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 "Phil Bewig" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming,comp.apps.spreadsheets
|
|||
|
|||
Run-time error '424': Object required
"Chip Pearson" wrote in message ...
Phil, Change ReColorDeps (C) to ReColorDeps C In general, you never want to use parentheses when passing arguments to Sub procedures. 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime error '424': Object Required | Excel Discussion (Misc queries) | |||
"Microsoft Visual Basic runtime error '424' object required". | Excel Worksheet Functions | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
Dget in VBA gets Object required run-time error? | Excel Programming | |||
error 424 - Object Required | Excel Programming |