creating a collection of Sheets references

[Eric]

I want to treat all the sheets in a workbook, except for the first one, the
same way in various methods. So I wanted do something like:
Public NonControlWorksheetCollection As New Collection

Private Sub createNonControlWorksheetCollection()
' populate a collection of Sheets that doesn't include the Control Worksheet
' assumes the control worksheet is worksheet 1
Dim counter As Integer
Dim wks As Worksheet
Debug.Assert ThisWorkbook.Worksheets(1).Name = CONTROL_WKS_NAME
For counter = 2 To (ThisWorkbook.Worksheets.Count)
Set wks = ThisWorkbook.Worksheets(counter)
NonControlWorksheetCollection.Add (wks) <--------------------error:
method or property not supported
Next counter
End Sub

Since this didn't work, I did the below two methods, but it seems there
should be a more elegant solution.
TIA
-------------------------------------------------------------
Private Sub createNonControlWorksheetCollection()
' populate a collection of Sheets that doesn't include the Control Worksheet
' assumes the control worksheet is worksheet 1
Dim counter As Integer
Debug.Assert ThisWorkbook.Worksheets(1).Name = CONTROL_WKS_NAME
For counter = 2 To (ThisWorkbook.Worksheets.Count)
NonControlWorksheetCollection.Add (counter)
Next counter
End Sub

Public Function getWorksheet(index As Integer)
' To ease the burden of object creation in methods needing
nonControlWorksheets
' returns a Worksheet object for the passed index
Dim wks As Worksheet
Set wks = ThisWorkbook.Worksheets(index)
End Function

 

[Chip Pearson]

Eric,

Remove the parentheses from the following line of code in your
first procedure:

NonControlWorksheetCollection.Add (wks)
<--------------------error:

When you include parens, Excel tries to evaluate the argument; in
this case it tries to retrieve the default property of the
worksheet object. Since the worksheet object has no default
property, you get an error.

In general, never use parens around arguments to Sub procedures
unless you know what you're doing.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com