0

As in the below three sub procedures, i intend to set the color (and the fonts and other more not shown here in the sample codes, but the issue remains the same) of a range when some conditions are met . Calling directly the Sub Set_Range_YELLOW_Color from my main working Sub has no error, and the code line works fine. But when i tried to call a procedure that makes a call to Set_Range_YELLOW_Color , i got an error message: Run-time Error '424' Object required. The error appears within the Do_Unapprouved_Work_MarkUp procedure at the line calling Set_Range_YELLOW_Color highlighted. I still can not figure out my wrong. Thanks for anyone who can help thru this.


Private Sub Set_Range_YELLOW_Color(myRange As Range)

    With myRange.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = RGB(255, 255, 193)
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
End Sub


Private Sub Do_Unapprouved_MarkUp(myRange As Range)

    Set_Range_YELLOW_Color (myRange) ' Error appears here and this line is highlighted

End Sub


Sub test2()

'    Sheets("--BROUILLON--").Select
    
    Set_Range_YELLOW_Color (ThisWorkbook.Worksheets("--BROUILLON--").Range("AA10"))
    Do_Unapprouved_MarkUp (ThisWorkbook.Worksheets("--BROUILLON--").Range("AA11"))

End Sub

Defining a local range object Dim aRange As Range in the Do_Unapprouved_Work_MarkUp sub, and using Set aRange = myRange , and then calling the coloring Sub Set_Range_YELLOW_Color , it does not work either. It gives the exact same error at the same line.

Private Sub Do_Unapprouved_MarkUp(myRange As Range)

    Dim aRange As Range
    Set aRange = myRange

    Set_Range_YELLOW_Color (aRange) ' Error appears here and this line highlighted

End Sub

When i call the fromatting procedure directly the code works well. But that makes me rewrite many many times a set of formatting codes that i would like to congregate as much as possible. I expect to find the way to call the Set_Range_YELLOW_Color indirectly thru a third Sub procedure , from the main Sub procedure.

J-Pierre Avognon
  • 1
  • 2
  • 1
    Remove the brackets `( )` when calling the sub. It's forcing the range to be passed by value. – Kostas K. Nov 04 '22 at 12:39
  • You have made a common error for newbies in VBA. Brackets are only used then a return value is required. This **Never** occurs for subs, only functions. By including brackets around aRange you have made an expession which yeilds a temporary value (a copy of aRange). Hence, your code updates the copy of aRange, not the original aRange. – freeflow Nov 04 '22 at 12:43
  • Yes , Sorry i forget mentionning that i am very new at VBA. The above two recommendations solve my problem. Thanks a lot to this Forum. Continuous Improvement ... – J-Pierre Avognon Nov 04 '22 at 12:53
  • @freeflow, it's not a copy of the `aRange`, but implicitly its `.Value`, I believe. – BigBen Nov 04 '22 at 13:20
  • Another option is named parameters: `Set_Range_YELLOW_Color myRange:=aRange`. – BigBen Nov 04 '22 at 13:36
  • @Bigben you are probably right. Whatever its a copy of whatever aRange represents which is the important point. – freeflow Nov 04 '22 at 17:02

0 Answers0