VBA can't get array to resize

Question

I am trying to get my array to resize to the length of the number of sheets in my workbook but keep getting

Subscript out of range

For context my sheet names are "Year XXXX"

Dim sheetsForCalendar() As Integer    

For Each Worksheet In ThisWorkbook.Sheets
    Dim i As Integer: i = 0
    Dim calendarYear() As String: calendarYear() = Split(CStr(Worksheet.Name))
    ReDim Preserve sheetsForCalendar(i)

    If calendarYear(0) = "Year" Then
        Dim calendarYearAsInt As Integer: calendarYearAsInt = calendarYear(1)
        sheetsForCalendar(i) = calendarYearAsInt
    End If

    i = i + 1
Next

For Each element In sheetsForCalendar
    MsgBox (sheetsForCalendar(element))
Next

I am using ReDim, (see line 5) and still getting the error.

nothing
– nothing

2016-02-14 20:44:06 +00:00
Commented Feb 14, 2016 at 20:44 — nothing
– nothing, Commented Feb 14, 2016 at 20:44

KekuSemau · Accepted Answer · 2016-02-14 20:51:18Z

1

Beside other problems in your code, this here is wrong:

For Each element In sheetsForCalendar
    MsgBox (sheetsForCalendar(element))
Next

element takes the values in sheetsForCalendar, ie. 2015, 2016 for example, it is not the index.
Either loop through for n = 0 to UBound(sheetsForCalendar) or just MsgBox element.

You should read on how to single-step through your code (F8 and place cursor over variables to see their content). This will help you find your errors easily (also how you 'mistreated' i as Mark Snyder pointed out).

answered Feb 14, 2016 at 20:51

KekuSemau

6,8424 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Snyder · Accepted Answer · 2016-02-14 20:44:50Z

1

I believe the problem is that you set i = 0 at the beginning of your For Each loop, so no matter how many sheets you have, the ReDim statement always uses the value 0. If you move the Dim statement for variable i above the For Each statement, it should work better. HTH

answered Feb 14, 2016 at 20:44

Mark Snyder

497 bronze badges

Collectives™ on Stack Overflow

VBA can't get array to resize

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related