Visual Basic: How to declare an array of array ?

Question

Hi there.

I've googled this question several times, but I can't find something that works.
I want to declare an array of arrays (arrArr) of unspecified length. Actually, I work with Excel: if the sheet exists, fetch its array and store it in the array of arrays. I've declared this arrArr like this, and the program basically works this way:

dim arrArr as Variant
if sheetIsFound then
    sheetsFound = sheetsFound + 1
    arrArr(sheetsFound) = arrayOfTheSheet
end if

but it doesn't work: debug says that the array is empty. What's wrong in my (ill)logic ?

Thanks

Answer 1

When you say dim arrArr(), the array has no bounds. you must redim it to the number you want. if you want to preserve existing items, you need the preserve keyword. You can add watches and step through the code to see what is happening in each step.

Try this:

Dim arrArr() As Variant
ReDim arrArr(0) 'make an array with 1 item
If sheetIsFound Then
    sheetsFound = sheetsFound + 1
    ReDim Preserve arrArr(0 To (UBound(a) + 1)) 'add an index to the array
    arrArr(UBound(a)) = arrayOfTheSheet ' add the array to the new index in arrArr
End If

To access the arrays, use the syntax arrArr(0), arrArr(1), etc.

This page has some examples if you need more help: http://www.brainbell.com/tutors/Visual_Basic/Arrays.htm