Martin Fishlock
12/14/2006 11:13:00 PM
The problem stems from the dimensioning of the array.
On your first redim to set it to 1 elementwhich is in fact to elements 0 and
1.
On your second redim you tried to change the lower bound which you are not
allowed to do.
You can either
set option base 1
or you can
ReDim FullSheetNames(1 to 1) As String
As an aside you can improve your code because you cannot have a sheetname
="" thats unless somebody nows differently:
Sub FillArray()
Dim i As Integer
Dim FullSheetNames() As String
ReDim FullSheetNames(1 To ThisWorkbook.Worksheets.Count) As String
For i = 1 To ThisWorkbook.Worksheets.Count
FullSheetNames(i) = ThisWorkbook.Worksheets(i).Name
Next i
End Sub
--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.
"BOBODD" wrote:
> The sub below runs into a "Subscript out of Range" error when it gets to
> ReDim Preserve FullSheetNames(1 To i) As String.
>
> There is a case select at the beginning of this sub that vets some of the
> sheets, but it isn't relevant to this error. Can anyone tell me what I'm
> missing?
>
> Private Sub FillArray()
> Dim i As Integer
> Dim wks As WorkSheet
> Dim SheetNames As String
> Dim FullSheetNames() As String
> For Each wks In ThisWorkbook.Worksheets
> If SheetNames <> "" And i > 1 Then
> ReDim Preserve FullSheetNames(1 To i) As String
> FullSheetNames(i) = SheetNames
> i = i + 1
> ElseIf SheetNames <> "" And i = 1 Then
> ReDim FullSheetNames(1) As String
> FullSheetNames(1) = SheetNames
> i = i + 1
> End If
> Next
> End Sub