Creating Dynamic Array to Select Multiple Worksheets In Excel

[Rob41982]

Working in Microsoft Excel, I have been trying to create an array using an unspecified number of cell values in a list, and then use that list to select worksheet pages of the same name in the workbook, but have been getting Subscript out of range errors. While building the array the code ensures all the specified pages exist. Here's more or less what i'm trying to do:

While cell.value <> &quot;&quot;
X=X+1
GoodArray(X) = Value
(goto next cell)
Wend
Sheets(GoodArray).Select

This isn't working, but both of these do:

1. Sheets(&quot;Sheet1&quot;,&quot;Sheet2&quot.Select

2. B = array(&quot;Sheet1&quot;,&quot;Sheet2&quot
Sheets(B).Select

I tried ReDim'ing the GoodArray to one higher each time i wrote to it, so it would be exactly the right length, but that didn't fix anything. Wasn't sure if that was my problem or not.

Any suggestions? Thanks!

 

[zemp]

To create a dynamic array don't place any dimensions in the original declaration.

dim array() as double ' dymanic array.

When you know the size then.

redim array(5) as double

To increase the array by one and keep the information already in it use

redim preserve array(6) as double

To change the size and clear the array.

redim array(9) as double

Hope this helps. Thanks and Good Luck!

zemp

 

[MarkSweetland]

There can't be any empty elements of the array or you'll get the Subscript out of range error. If you Dim the Array with an extra (empty) element, Excel will still try to select the &quot;empty&quot; sheet.

I tried it using the actual names of the sheets:

Sub Form_Load
Dim xlapp As New Excel.Application
Dim xlwb As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim xlSheetArr() As String

xlapp.Visible = True
Set xlwb = xlapp.Workbooks.Add
If xlwb.Worksheets.Count = 0 Then
For i = 0 To 4
xlwb.Worksheets.Add
Next
End If
i = 0 ' zero is the first element
For Each xlsheet In xlwb.Worksheets
ReDim Preserve xlSheetArr(i)
xlSheetArr(i) = xlsheet.Name
i = i + 1 'bump by one in case of another
Next
xlapp.Worksheets(xlSheetArr).Select
End Sub




Mark

 

[zemp]

The subscript out of range error does not occur on an empty element but rather when you try to access an element that does not exist. Thanks and Good Luck!

zemp

 

[Rob41982]

Mark -

It was the array element 0 that was getting me! I wrote the original array to 0 to (x-1) instead of 1 to X and it worked perfectly, thanks for the idea!

Rob