Subscript out of range?

[Dave Birley]

Here is my initialization of an array:

Dim shtList As Sheets

Set shtList = Worksheets(Array("Earnings Balance 2003 Q4 Page 2", _
"Earnings Balance 2003 Q4", "Earnings Balance 2003 Q3 Page
2", _
"Earnings Balance 2003 Q3", "Earnings Balance 2003 Q2 Page
2", _
"Earnings Balance 2003 Q2", "Earnings Balance 2003 Q1 Page
2", _
"Earnings Balance 2003 Q1", "Earnings Balance 2002 Q4 Page
2", _
"Earnings Balance 2002 Q4", "Earnings Balance 2002 Q3 Page
2")) ', _
"Earnings Balance 2002 Q3", "Earnings Balance 2002 Q2 Page
2", _
"Earnings Balance 2002 Q2", "Earnings Balance 2002 Q1 Page
2", _
"Earnings Balance 2002 Q1", "Earnings Balance 2001 Q4 Page
2", _
"Earnings Balance 2001 Q4", "Earnings Balance 2001 Q3 Page
2", _
"Earnings Balance 2001 Q3", "Earnings Balance 2001 Q2 Page
2", _
"Earnings Balance 2001 Q2", "Earnings Balance 2001 Q1 Page
2", _
"Earnings Balance 2001 Q1", "Earnings Balance 2000 Q4 Page
2", _
"Earnings Balance 2000 Q4", "Earnings Balance 2000 Q3 Page
2", _
"Earnings Balance 2000 Q3", "Earnings Balance 2000 Q2 Page
2", _
"Earnings Balance 2000 Q2", "Earnings Balance 2000 Q1"))

Note the commenting out after element 11. If I move that commenting element
down one row, I get a "Subscript out of range" error, whereas in the form
here I do not get the error. As I need to get all 31 elements into the array
short of doing:

Dim shtList(1 To 31) As Sheets
shtList(1) = Sheets("Earnings Balance 2003 Q4 Page 2")
shtList(2) = Sheets("Earnings Balance 2003 Q4")
....
shtList(31) = Sheets("Earnings Balance 2000")

What is it that I should be doing?

 

[JLGWhiz]

Maybe check you spelling. The subscript out of range error indicates that
VBA cannot find the data element you have specified for one or more of many
reasons.
Item not open, misspelled, etc. You have to figure out why it can't find it.

 

[David Sisson]

What are you trying to achieve?
This put the names of worksheets (yes?) into an array.

Option Base 1
Sub test()

Dim shtList As Variant
Dim A As Integer

Set shtList = Array("Earnings Balance 2003 Q4 Page 2", _
"Earnings Balance 2003 Q4", "Earnings Balance 2003 Q3 Page 2", _
"Earnings Balance 2003 Q3", "Earnings Balance 2003 Q2 Page 2", _
"Earnings Balance 2003 Q2", "Earnings Balance 2003 Q1 Page 2", _
"Earnings Balance 2003 Q1", "Earnings Balance 2002 Q4 Page 2", _
"Earnings Balance 2002 Q4", "Earnings Balance 2002 Q3 Page 2", _
"Earnings Balance 2002 Q3", "Earnings Balance 2002 Q2 Page 2", _
"Earnings Balance 2002 Q2", "Earnings Balance 2002 Q1 Page 2", _
"Earnings Balance 2002 Q1", "Earnings Balance 2001 Q4 Page 2", _
"Earnings Balance 2001 Q4", "Earnings Balance 2001 Q3 Page 2", _
"Earnings Balance 2001 Q3", "Earnings Balance 2001 Q2 Page 2", _
"Earnings Balance 2001 Q2", "Earnings Balance 2001 Q1 Page 2", _
"Earnings Balance 2001 Q1", "Earnings Balance 2000 Q4 Page 2", _
"Earnings Balance 2000 Q4", "Earnings Balance 2000 Q3 Page 2", _
"Earnings Balance 2000 Q3", "Earnings Balance 2000 Q2 Page 2", _
"Earnings Balance 2000 Q2", "Earnings Balance 2000 Q1")

For A = 1 To 31
MsgBox ("Array Number " & A & " - " & shtList(A))
Next A


End Sub

 

[Dave Birley]

Sure helps a bunch when you have an idea what you're looking for! I got it
all excited earlier when I tried to put each one of those pups on its own row
with a continuer at the end -- too many, so I doubled them up. I was assuming
this was a similar problem -- but, with your help, I discovered that one of
the Sheets had a spare " " at the end of its name -- kablooey! Tweaked the
Sheet's name, and all is lovely in the valley!

Thanks a bunch.

 

[Dave Birley]

Yupsies -- one of my Sheets had an errant " " at the end of its name! Thanks
for your input.

 

[Dave Birley]

Actually the "Error" wasn't in the way I initialized the Array at all -- one
of the WS has a rogue " " at the end of its name, so for my process it was
"misspelled". <g>!