CODEWITHSUNDEEP
excelvba
Ting Ping
Ting Ping

Reputation: 1145

Subscript Out of Range for Looping Through Folder Code

I am trying to loop through the folder with the following code. However, I keep getting subscript out of range error. Could anyone explain what I could do to fix this issue?

Sub LoopThroughFolder()

    Const FileSpec As String = "*.xls"
    Dim y As Integer
    Dim MyFolder As String
    Dim MyFile As String
    Dim iDot As Integer
    Dim FileRoot As String
    Dim FileExt As String

    Dim ArrayData() As Variant


    For y = 2009 To 2030

        ReDim Preserve ArrayData(y, 12)
        MyFolder = ActiveWorkbook.Path & "\" & y & "\"

        i = 1
        MyFile = Dir(MyFolder & FileSpec)
        Do While Len(MyFile) > 0
            iDot = InStrRev(MyFile, ".")

            If iDot = 0 Then
                FileRoot = MyFile
                FileExt = ""
            Else
                FileRoot = Left(MyFile, iDot - 1)
                FileExt = Mid(MyFile, iDot - 1)
            End If

            MyFile = Dir
            ArrayData(y, i) = FileRoot
            MsgBox ArrayData(y, i)
            i = i + 1
        Loop

    Next y

End Sub

Upvotes: 1

Views: 81

Answers (3)

Floris
Floris

Reputation: 46415

Since you know what the largest value of y is going to be, why use the ReDim at all? It is extremely inefficient, since it involves relocating all your data if there is no additional (contiguous) space available, as well as copying the contents. Instead, do

ArrayData(2009 To 2030, 1 To 12) As Variant

and don't do any redimensioning.

This doesn't really fix the problem though, since your value of i is actually the one that grows indefinitely, and your ReDim never considers changing the i dimension (it is always 12).

Depending on what you want to do, you could consider using a "collection". With a collection you can specify any string as the index; thus you could index with CStr(y)&"-"&CStr(i)...

Alternatively, declare the array of type Variant with initial dimensions (2,12). When you find a file root, assign the corresponding year to (1,i) and the file root to (2,i). Now you have an array that does't contain a large number of empty elements (efficient), that can be re-dimensioned (along the second dimension), and that captures all the data of the original. In effect you have created your own "sparse array".

One more aside:

Contrary to what I mentioned in the comments above, when I tried to use the To construct iin Excel VBA and then inspected the variable, it seemed to do exactly what you would expect - but it wasn't possible to redim an array once it had been declared with upper and lower bounds.

Upvotes: 0

Pete Garafano
Pete Garafano

Reputation: 4913

According to this msdn link, You can only ReDim Preserve on the last dimension of an array, in your case, you could only change the 12 in ReDim Preserve ArrayData(y, 12). Changing the order of your array parameters will fix this problem.

Upvotes: 4

David Zemens
David Zemens

Reputation: 53653

Firstly, as @TheGreatCo mentions, you can only redim the last dimension of an array.

ReDim Preserve ArrayData(y, 12) is throwing the error because you are trying to redim the first dimension.

Try changing to:

ReDim Preserve ArrayData(12,y)

And to limit i, change your line that says Loop to say:

Loop While i <=12

However, this still seems overkill. And although it may not cause you any errors, it is certainly inefficient. You don't need y columns (2030 columns!!!) since you're never assigning anything, e.g., to ArrayData(12,1955).

You need to revise the way you're iterating over items and assign to the array more efficiently.

I would suggest:

ReDim Preserve ArrayData(12,y-2009)

You will then need to change:

ArrayData(i,y) = FileRoot
MsgBox ArrayData(i,y-2009)

Upvotes: 0

Related Questions