How to Access Array Made With Arrays?

[Xbox_360]

Hello, I am a novice to VBA, but I'm getting there.

I am trying to build a program, and one of its aspect is to use two-dimensional arrays.

One of the functions get directories, where it fetches an array of folder directories, an array of spreadsheet directories, and then stores both of those arrays into one return array. The end-goal is to use both of them to find all spreadsheets in a given directory through a recursive subroutine.

The way I'm going about this is (See latter half of code):

Function GetDirectory(FileDirectory As String)
  ' variables
    Dim FolderName As String
    Dim FolderArray As Variant
    Dim FileArray As Variant
    Dim DirectoryName As String
   
    ' initialize arrays
    FolderArray = Array()
    FileArray = Array()
   
    ' create first entry
    ' specifies directories, folders, files as read-only, and no-attribute files
    DirectoryName = Dir(FileDirectory & "\*", 17)
    ' pass the first two DOS directories "." and ".."
    DirectoryName = Dir()
    DirectoryName = Dir()
    ' Get folders and spreadsheets from directory
    Dim FolderArrayIndex As Integer
    Dim FileArrayIndex As Integer
    FolderArrayIndex = 0
    FileArrayIndex = 0
   
    Do While DirectoryName <> ""
        ' if the file is a folder (absence of extension)
        If InStr(DirectoryName, ".") = 0 Then
            ' expand the array, creating another slot for another element
            ReDim Preserve FolderArray(FolderArrayIndex)
            FolderArray(FolderArrayIndex) = DirectoryName
            FolderArrayIndex = FolderArrayIndex + 1
        ' else if the file is a spreadsheet
        ElseIf InStr(LCase(DirectoryName), ".xls") > 0 Then
            ' expand the array, creating another slot for another element
            ReDim Preserve FileArray(FileArrayIndex)
            FileArray(FileArrayIndex) = DirectoryName
            FileArrayIndex = FileArrayIndex + 1
        End If
        ' fetch next element
        DirectoryName = Dir()
    Loop
   
    ' load arrays into a master array and return
    GetDirectory = Array(FolderArray, FileArray)
End Function

I have tried to access this array, but it seems like I can only access its bounds, so something like this works:

Dim MasterArray As Variant
MsgBox(UBound(MasterArray))

However, this does not work, and from what I have read it should be the proper syntax for multi-dimensional arrays:

Dim MasterArray As Variant
MsgBox(MasterArray(1, 1)

So my question is: did I create a two-dimensional array correctly? It gives no compile errors, so I assume so. How would I access the elements within the arrays of the array that I created?

Keep in mind that I am a novice to VBA, Microsoft's docs are my best friend... most of the time.

 

[cmowla]

It appears that you are not appending to the array. Instead of:

ReDim Preserve FileArray(FileArrayIndex)

Try:

ReDim Preserve FileArray(UBound(FileArray) + 1)

(The same for the FolderArray, of course.)

Let us know if that solves the issue.

 

[Xbox_360]

It appears that you are not appending to the array. Instead of:

ReDim Preserve FileArray(FileArrayIndex)

Try:

ReDim Preserve FileArray(UBound(FileArray) + 1)

(The same for the FolderArray, of course.)

Let us know if that solves the issue.

That's one way, though that's already what I am doing (and admittedly, in extra steps than necessary). I have it set up so that an index begins at 0, then whenever a file is found it appends the string directory to that index, and then increments the index.

I'm already doing that:

Function GetDirectory(FileDirectory As String)
    ' . . .
    ' Get folders and spreadsheets from directory
    ' . . .
    Dim FileArrayIndex As Integer
    ' . . .
    FileArrayIndex = 0 ' begin indexing at zero
   
    Do While DirectoryName <> ""
        ' . . .
        ' else if the file is a spreadsheet
        ElseIf InStr(LCase(DirectoryName), ".xls") > 0 Then
            ' expand the array, creating another slot for another element
            ReDim Preserve FileArray(FileArrayIndex) ' append an index at the index (at the end of the array)
            FileArray(FileArrayIndex) = DirectoryName ' append string at index
            FileArrayIndex = FileArrayIndex + 1 ' increment index
        End If
        ' fetch next element
        DirectoryName = Dir()
    Loop
   
    ' load arrays into a master array and return
    GetDirectory = Array(FolderArray, FileArray)
End Function

I changed it, however, to accommodate your suggestion (and I think it would work better considering I was making unnecessary variables):

Function GetDirectory(FileDirectory As String)
  ' variables
    Dim FolderName As String
    Dim FolderArray As Variant
    Dim FileArray As Variant
    Dim DirectoryName As String
   
    ' initialize arrays
    FolderArray = Array()
    FileArray = Array()
   
    ' create first entry
    ' specifies directories, folders, files as read-only, and no-attribute files
    DirectoryName = Dir(FileDirectory & "\*", 17)
    ' pass the first two DOS directories "." and ".."
    DirectoryName = Dir()
    DirectoryName = Dir()
    ' Get folders and spreadsheets from directory
   
    Do While DirectoryName <> ""
        ' if the file is a folder (absence of extension)
        If InStr(DirectoryName, ".") = 0 Then
            ' expand the array, creating another slot for another element
            ReDim Preserve FolderArray(UBound(FolderArray) + 1)
            FolderArray(UBound(FolderArray)) = DirectoryName
        ' else if the file is a spreadsheet
        ElseIf InStr(LCase(DirectoryName), ".xls") > 0 Then
            ' expand the array, creating another slot for another element
            ReDim Preserve FileArray(UBound(FileArray) + 1)
            FileArray(UBound(FileArray)) = DirectoryName
        End If
        ' fetch next element
        DirectoryName = Dir()
    Loop
   
    ' load arrays into a master array and return
    GetDirectory = Array(FolderArray, FileArray)
End Function

Problem is, however, I still an error when I try running:

Dim DirectoryArray As Variant
    DirectoryArray = GetDirectory(FileDirectory)
    MsgBox (UBound(DirectoryArray(1))) ' this works and gives me an expected results
    MsgBox (DirectoryArray(1, 1)) ' this throws an error: "subscript out of range"

 

[Rick Rothstein]

I am not exactly sure how your function is to be used. However, I can tell you from the last assignment within the function that it would be something like this...

MsgBox GetDirectory(2)(4)

Note that each index is in its own set of parentheses... the first picks which array to use and the second picks which element in that array to return.

 

[Xbox_360]

Some source I found on this
Also by Rick Rothstein's explanation.

It appears I was using the wrong syntax.
I come from Java and thought it was going to be like Java.
Apparently there are two ways to access arrays, each depending on the type of array.
Multidimensional arrays (arrays of arrays that have the same length) us the syntax MyArray(Element1, Element2)
My array is a nested—or alternatively, jagged—array. By putting an array into an array, I am making a nested array instead, where only some, if not none, of the arrays are of equal length. Nested arrays use the syntax MyArray(Element1)(Element2).

Really, my code is fine, but my access was wrong, because I though both types of arrays would use the same syntax, but they don't.

 

[Xbox_360]

I am not exactly sure how your function is to be used. However, I can tell you from the last assignment within the function that it would be something like this...

MsgBox GetDirectory(2)(4)

Note that each index is in its own set of parentheses... the first picks which array to use and the second picks which element in that array to return.

Found out about it and made and wrote up an update to the thread just before you replied. Thanks for still helping though.