I am trying to create an array that contains all of the worksheet names, starting with the 4th worksheet in the active workbook. I'm getting an error on 4th line when I try to redimension the array. What am I missing? There are currently 6 worksheets, the 3rd is hidden (in case that changes anything).
Dim i As Integer
Dim sheetsToSkip() As Variant
For i = 4 To Sheets.Count
ReDim Preserve sheetsToSkip(UBound(sheetsToSkip) + 1)
sheetsToSkip(UBound(sheetsToSkip)) = Sheets(i).Name
Next i
Here's a quick couple of examples, if you make a count of the 'valid' sheets first ( if you're avoiding including sheets by name ) - that'll help.
-- Version 1 - don't bother checking (just get all sheet names to an array )
Sub SheetNamesToArray()
Dim MyWb As Workbook
Dim MySheet As Worksheet
Dim SheetNameArray() As String ' Array of Sheet Names
Dim LP As Integer ' ( Generic Loop Variable )
Set MyWb = ActiveWorkbook
':: Redim to workbook count
ReDim SheetNameArray(MyWb.Sheets.Count)
':: Check Array Size
Debug.Print UBound(SheetNameArray) & " = " & MyWb.Sheets.Count
':: Set Array from Sheet Names
For LP = 1 To MyWb.Sheets.Count
SheetNameArray(LP) = Sheets(LP).Name
Debug.Print SheetNameArray(LP) & " :" & Sheets(LP).Name
Next LP
End Sub
-- Version 2 - Check names, save only valid ( using Inst to test if multiple sheets have a particualar string in name )
Sub SheetNamesToArray_And_Ignore_Names()
Dim MyWb As Workbook
Dim MySheet As Worksheet
Dim SheetNameArray() As String ' Array of Sheet Names
Dim LP, LPx As Integer ' ( Generic Loop Variable(s) )
Dim MyValidSheetsCount As Integer ' Array Size for only 'valid' sheets
':: Apply to active workbook
Set MyWb = ActiveWorkbook
For LP = 1 To MyWb.Sheets.Count
':: Skip over names to ignore..
If InStr(1, Sheets(LP).Name, "Four", vbTextCompare) = 0 Then
'::Iterate count of 'valid' sheets ::
MyValidSheetsCount = MyValidSheetsCount + 1
End If
Next LP
':: Redim to workbook count
ReDim SheetNameArray(MyValidSheetsCount)
LPx = 0
':: Set Array from Sheet Names
For LP = 1 To MyWb.Sheets.Count
':: Same test again, this time add to array, and iterate index variable ( LPX )
If InStr(1, Sheets(LP).Name, "Four", vbTextCompare) = 0 Then
SheetNameArray(LPx) = Sheets(LP).Name
'::Iterate array index ::
LPx = LPx + 1
End If
Next LP
Debug.Print "Found " & LPx & " valid sheets" & vbNewLine
':: Check only the 'valid' sheets are recorded
For LP = 0 To LPx - 1
Debug.Print "(" & Format(LP, "000#") & ") : " & SheetNameArray(LP)
Next LP
End Sub
With VBA there's probably more than a few paths to getting the same result.
For the original question, if you're just trying to skip past 4 and aren't concerned they might be out of order , do :
Sub ReallySimpleJustUseNumbers()
':: N.B. Sheets are indexed starting at 1, arrays begin at 0 ..
Dim MyWb As Workbook
Dim MySheet As Worksheet
Dim SheetNameArray() As String ' Array of Sheet Names
Dim LP As Integer ' ( Generic Loop Variable )
Set MyWb = ActiveWorkbook
':: Are there greater than four sheets in this workbook ?
If MyWb.Sheets.Count > 4 Then
':: Redim to workbook count
ReDim SheetNameArray(MyWb.Sheets.Count - 4)
Else
':: Polite Exit
MsgBox "Too Few Sheets buddy"
Exit Sub
End If
For LP = 4 To MyWb.Sheets.Count
SheetNameArray(LP - 4) = MyWb.Sheets(LP).Name
Next LP
':: Output array
For LP = 0 To UBound(SheetNameArray)
Debug.Print LP & " --- " & SheetNameArray(LP)
Next LP
End Sub
This'll simply start populating the array as defined as Count of Sheets - Four initial sheets, index starting at 0
@BigBen is on the right track. You only have to ReDim once:
Public Function ListWorkssheets()
Dim SheetsToSkip() As String
Dim Count As Integer
Dim Index As Integer
Count = ThisWorkbook.Worksheets.Count
ReDim SheetsToSkip(1 To Count)
For Index = 1 To Count
SheetsToSkip(Index) = ThisWorkbook.Worksheets((Index + 3 - 1) Mod Count + 1).Name
Next
' Verify.
For Index = LBound(SheetsToSkip) To UBound(SheetsToSkip)
Debug.Print Index, SheetsToSkip(Index)
Next
End Function
The problem is that your array sheetsToSkip does not have an upper bound in the first loop cycle yet. You can check if sheetsToSkip is an array in the first loop and then either dimension it for the first time or resize it.
Suggestion for the code:
Sub CreateSheetNameArray()
Dim i As Integer
'Brakets are removed
Dim sheetsToSkip As Variant
For i = 4 To Sheets.Count
If IsEmpty(sheetsToSkip) Then
ReDim sheetsToSkip(0 To 0)
Else
ReDim Preserve sheetsToSkip(UBound(sheetsToSkip) + 1)
End If
sheetsToSkip(UBound(sheetsToSkip)) = Sheets(i).Name
Next i
End Sub
Be aware that ReDim Preserve is slow. If you have a lot of loop cycles, you might consider to just dimension the array before the loop.
sheetsToSkipdoesn't have aUboundon the first iteration of the loop.