CODEWITHSUNDEEP
excelvba
Alastair
Alastair

Reputation: 83

Making a Collection of Sheet Names

I'm trying to generate a collection of all the sheets in a workbook.

Sub collMaker()

Dim coll As Collection, ws As Worksheet, x As String

For i = 2 To Application.Sheets.count
    x = Application.Sheets(i).name
    coll.Add Item:=x, Key:=x
Next i

End Sub

I get a runtime error '91' on the coll.add line.

Upvotes: 0

Views: 196

Answers (1)

braX
braX

Reputation: 11755

You need to use New when creating your collection, or it will just be defined as Nothing, and you cannot add something to nothing.

It's also a good idea to Dim all of your variables.

Sub collMaker()

Dim coll As New Collection, ws As Worksheet, x As String
Dim i As Integer

For i = 2 To Application.Sheets.Count
     x = Application.Sheets(i).Name
     coll.Add Item:=x, Key:=x
Next i

End Sub

What does the keyword 'New' do in VBA?

Upvotes: 2

Related Questions