0

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.

greybeard
  • 2,249
  • 8
  • 30
  • 66
Alastair
  • 63
  • 8

1 Answers1

2

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?

braX
  • 11,506
  • 5
  • 20
  • 33
  • @Alastair: We here, when somebody answer our question (in a very helpful way), tick the code left side check box, in order to make it **accepted answer**. If somebody else will search for a similar issue, he will know that the answer solved your problem and the code works (now). – FaneDuru Nov 11 '20 at 10:30
  • 1
    @FaneDuru, sorry, I tried to do that when the question was answered, but it was too early for me to click. – Alastair Nov 11 '20 at 11:47