Excel VBA setting multiple sheets as a variable

Question

I have a workbook that is used for project payback over a number of years. I have set up some code in ThisWorkbook to lock the results worksheets, but allow users to open or close column groups (to allow them to hide or unhide the month columns of each year). My current code, which works nicely, looks like this:

Sheet10.Protect Password:="password", UserInterfaceOnly:=True
Sheet10.EnableOutlining = True

Sheet11.Protect Password:="password", UserInterfaceOnly:=True
Sheet11.EnableOutlining = True

and so on for 4 more sheets (and it works).

What I would like to do is, define a variable that stores the sheet identifiers and run a For Each / Next loop on the real code.

But I cannot get a variable declaration to work that doesn't throw some compile or runtime error.

My favourite construction is

Dim wSheet as Worksheet
wSheet = Array(Sheet10, Sheet11, Sheet14)

For Each wSheet in Workbook
    wSheet.Protect Password:="password", UserInterfaceOnly:=True
    wSheet.EnableOutlining = True
Next wSheet

But it fails on my setting wSheet... I have tried several variants but it nearly always fails on that second line (doesn't matter whether I use sheet index, sheet name etc). Any thoughts?

Answer 1

I couldn't get the first posted answer to work. Here's what worked for me, taken from an answer to this question posted by user @Dee:

Sub Test()
    Dim sheetsArray As Sheets
    Set sheetsArray = ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

    Dim sheetObject As Worksheet

    ' change value of range 'a1' on each sheet from sheetsArray
    For Each sheetObject In sheetsArray
        'Do something
    Next sheetObject
End Sub

Answer 2

The problem in your code is the declaration in the first line:

Dim wSheet as Worksheet

either make it an array declaration:

Dim wSheet() as Worksheet

or make it a variant like this:

Dim wSheet as Variant

or this:

Dim wSheet