Passing worksheet object to another sub getting "object doesn't support this property or method"

Question

I'm writing a macro that along other things formats a bunch of much sheets, one subroutine I've written needs to accept a worksheet object I've defined and used earlier. When i try and call the subroutine with the worksheet object as a parameter i get an "object doesn't not support this property or method"

Option Explicit

Public wb As Workbook

Public data, summary_month, summary_item, summary_charge, summary_pro As Worksheet

Public i, n, x, y, z As Variant

Public libName(), itype(), tcharge(), tprofile() As Variant

Public profile, pay_lib, item_auth, total_paid, month, item_type, chr_type As Variant

Public Sub main()

Set wb = ThisWorkbook
Set data = wb.Sheets(1)
Set summary_month = wb.Sheets(2)
Set summary_item = wb.Sheets(3)
Set summary_charge = wb.Sheets(4)
Set summary_pro = wb.Sheets(5)

With data
    Set pay_lib = .Range( _
                    .Cells(2, 2), _
                    .Cells(2, 2).End(xlDown))
    Set item_auth = .Range( _
                    .Cells(2, 4), _
                    .Cells(2, 4).End(xlDown))
    Set month = .Range( _
                    .Cells(2, 3), _
                    .Cells(2, 3).End(xlDown))
    Set total_paid = .Range( _
                    .Cells(2, 10), _
                    .Cells(2, 10).End(xlDown))
    Set item_type = .Range( _
                    .Cells(2, 5), _
                    .Cells(2, 5).End(xlDown))
    Set chr_type = .Range( _
                    .Cells(2, 6), _
                    .Cells(2, 6).End(xlDown))
    Set profile = .Range( _
                    .Cells(2, 8), _
                    .Cells(2, 8).End(xlDown))

End With


'''''''''''''''''''''''''''''''''''''''''''''
''''''''''some other stuff'''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''


change_colors (summary_month)

End Sub

Sub change_colors(wksheet As Worksheet)

With wksheet
    For Each i In .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
        If i.Row Mod 2 = 0 Then
            .Range(.Cells(i.Row, i.Column), .Cells(i.Row, i.Column).End(xlToRight)).Interior.ColorIndex = 31
        Else
            .Range(.Cells(i.Row, i.Column), .Cells(i.Row, i.Column).End(xlToRight)).Interior.ColorIndex = 2
        End If
    Next i
End With

when the subroutine is called the 'with' statement should alternate colors based on row, and from my testing there doesn't seem to be anything round with that, i get the error when the subroutine is being called. both subs are in the same module.

Clearly there is something i'm not fully understanding with how worksheet objects work any help would be greatly appreciated

1 Thing, if you pass an object as variable to another sub, you don't need to turn the variable to global. Second thing: `Public data, summary_month, summary_item, summary_charge, summary_pro As Worksheet` here you are only declaring `summary_pro` as worksheet, the others are variants. — Damian, Apr 24 '19 at 13:53
Try `change_colors summary_month`. https://stackoverflow.com/questions/5413765/what-are-the-rules-governing-usage-of-brackets-in-vba-function-calls — SJR, Apr 24 '19 at 14:00
@Damian and SJR thanks alot! i had tried SJR solution earlier which didn't work but soon as i did it with Damian suggestion it solved the problem!! thanks alot — Phl3bas, Apr 24 '19 at 14:03

score 0 · Answer 1 · answered Apr 24 '19 at 14:08

declared worksheets separately rather than inline

Option Explicit

Public wb As Workbook

Public i, n, x, y, z As Variant

Public libName(), itype(), tcharge(), tprofile() As Variant

Public profile, pay_lib, item_auth, total_paid, month, 
item_type, chr_type As Variant

Public Sub main()

Dim data As Worksheet
Dim summary_month As Worksheet
Dim summary_item As Worksheet
Dim summary_charge As Worksheet
Dim summary_pro As Worksheet

Set wb = ThisWorkbook
Set data = wb.Sheets(1)
Set summary_month = wb.Sheets(2)
Set summary_item = wb.Sheets(3)
Set summary_charge = wb.Sheets(4)
Set summary_pro = wb.Sheets(5)

and also made sure the subroutine was called as below rather than in parenthesis

change_colors summary_month

Passing worksheet object to another sub getting "object doesn't support this property or method"

1 Answers1