CODEWITHSUNDEEP
excelvba
GeekyFreaky
GeekyFreaky

Reputation: 293

Why can I not initialize a Worksheets object variable to a Worksheets collection?

I understand that it's good coding habit to use object variables. I am trying to create an object variable called wsc that will be initialized with an object reference to the active workbook's worksheets collection. See code below.

I tried setting the object variable data type to Worksheets but it did NOT work. It only works when I set the object variable data type to Object. This leads me to ask - is there a data type other than Object that will work in this case?

Sub FormatAllFormulas()

    Dim ws As Worksheet
    Dim wsc As Object   ' does not work if type is Worksheets - why?

    Set wsc = ActiveWorkbook.Worksheets

    For Each ws In wsc
        With ws.Cells.SpecialCells(xlCellTypeFormulas)
            .Style = "Currency"
            .Font.Bold = True
            .Interior.Color = 4908260
        End With
    Next ws

End Sub

Upvotes: 2

Views: 154

Answers (1)

Rory
Rory

Reputation: 34045

ActiveWorkbook.Worksheets returns Sheets not Worksheets so that is the type you should use.

Upvotes: 2

Related Questions