Solved: Create New Collection

[jmenche]

Howdy,

I have a bunch of sheets in a workbook. I want to perform certain actions (protection, recalcs, etc.) only on certain sheets. I know the best way would be to create a new collection, add the worksheet names, and then loop through the collection to perform my tasks. This would also help with errors if users rearrange sheets (I use the index now). However, I am a novice and need a little guidance on how to do.

Any help is greatly appreciated.

[Killian]

Hi there,

A couple of key things to remember about collections:
Maye sure you use the "New" keyword in the declaration or when you first set the object variable
Manage the scope so you have access to it when you want to use it. The following basic example has the collection declared in the routine - the narrowest scope. It's more likely that you will want to declare it at module level so that you can write othe routines to use it. Likewise, you may want to make it global so it can be used across the whole project.[VBA]Sub temp()
Dim ws As Worksheet
Dim colMySheets As New Collection
Dim str As String

'add any sheet with "copy" in its name
'to the new collection
For Each ws In ActiveWorkbook.Sheets
If InStr(ws.Name, "copy") > 0 Then
colMySheets.Add ws
End If
Next ws

'show names of sheets in new collection
For Each ws In colMySheets
str = str & ws.Name & vbLf
Next ws
MsgBox str
End Sub[/VBA]Hope that's some help...

[jmenche]

Thanks Killian!

Where do I put it to make it global?

[jmenche]

Another question.

How would I write the global sub if I have a set list of worksheets that I want to declare in a collection? Assume I want to add Sheet1 and Sheet2 into a new collection.

Thanks again.

[Killian]

You make a global variable by declaring it as Public at the start of a standard module (outside any routines)[VBA]Public colMySheets As New Collection[/VBA]
Now you can initialize your custom collection from anywhere - perhaps in the WorkBook_Open event[VBA]Sub InitCollection()

Dim i As Long
'clear the collection
For i = 1 To colMySheets.Count
colMySheets.Remove 1
Next i

'add sheets to the collction by name
colMySheets.Add ThisWorkbook.Sheets("Sheet1")
colMySheets.Add ThisWorkbook.Sheets("Sheet2")

End Sub[/VBA]You can also refer to the collection from any other routine or module. For example, you might want to put your sheets in a list box on a form, so on the form initialize event[VBA]Private Sub UserForm_Initialize()
Dim ws As Worksheet

For Each ws In colMySheets
ListBox1.AddItem ws.Name
Next ws

End Sub[/VBA]

[jmenche]

Thanks!!!

[jmenche]

Killian,

I marked this as solved but have a follow up question. I did everything you said and call the sub in workbook.open. It doesn't work when I open the workbook however it does work when I just open the VBE and run the sub. Is there a scope issue here?

Any thoughts are greatly appreciated.

[jmenche]

Killian,

Here's some more info. As I navigate through my workbook, the collection has items and then has none. How often do I need to "fill up" the collection? I assumed that once you did it it stayed full.

Thanks again.

[Killian]

OK, I've been a bit vague, To see it working in practice, I've attached an example with some code comments so you can see how a collection can be managed.
The example views the collection through a userform, where you can also remove items from the collection or add the actrive worksheet.
The collection variable is declared as public in a standard module and a initialization routine in the same module (called from WorkBook_Open) sets it to a new collection.
From that point it is active and can be accessed by any routine. It will remain "live" all the time the standard module is loaded - which is all the time the WorkBook is open.

If your running through the code and break the program flow (or throw an error) you may find the variables lose their values, so you would need to re-initialize it in those debugging situations

You could extend the effective lifetime of the collection in two ways:
1) Use it in an AddIn that's loaded with Excel. That would make it available for the lifetime of the Excel session, so it could be used across multiple workbooks.
2) On workbook_close, save references to the collection members (on a worksheet, or in the Windows registry) and re-populate it with those items on Workbook_open

[jmenche]

Thanks again Killian!

I was testing the workbook a lot and getting errors. I just punted and call the collection sub every time I need it.

[Killian]

errors, you say..?