Why Does 2D Dynamic Array Throw "Subscript out of Range" Error at ReDim Preserve Statement?

P

promalley2

New Member
Joined
Nov 16, 2009
Messages
24
Good morning all!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
I’m new to working with arrays in general but am attempting to increase my understanding. That said, I have a question about a small issue I’m running into. What the code below is trying to accomplish is: create a dynamic 2D array by using the ReDim Preserve statement and increasing the size of the array by one each time around the loop. I’ve been able to successfully create three different successful versions of the script below with the instances being:<o:p></o:p>
<o:p> </o:p>
1. A dynamic 1D array <o:p></o:p>
2. A static, fully dimensioned 2D array<o:p></o:p>
3. A dynamic 2D array that understands its initial length by examining a “.Count” property of the thing I’m creating a list for.<o:p></o:p>
<o:p> </o:p>
But for the scenario below, I’m trying to create the 2D array in a dynamic way that doesn’t depend on knowing the value of the eventual total length by the examination of the “.Count” property of the list of objects (I may not always have it available). The plan was to use the ReDim and Preserve statements and an incrementing variable as the associated driver. So each time through the loop, the counter variable would increase by one, the originally dimensioned dynamic array would be ReDim-ed accordingly to include one additional element, and any information in the array would be preserved. However, when I run the script below, it will run through the initial pass of the loop, but throw an error on the subsequent pass when it comes time for the array to be ReDim-ed and Preserved to include an additional element (relative to the rowIndex counter variable). <o:p></o:p>
<o:p> </o:p>
I keep getting “error 9: subscript out of range” on line 30 at the ReDim Preserve statement. I can’t figure out why it won’t re-dimension the new array based on the rowIndex number. The code is below, with comments, for anyone to examine. If someone with some 2D dynamic array experience can point out my error, it would be much appreciated. Thanks in advance for your time and suggestions!<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Code: 
[FONT=Times New Roman]Public Sub ListFileNameNtypeInExcel(session1 As Object)<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]Dim numRows As Integer<o:p></o:p>[/FONT]
[FONT=Times New Roman]Dim anArray() As Variant<o:p></o:p>[/FONT]
[FONT=Times New Roman]Dim models As Object/Collection<o:p></o:p>[/FONT]
[FONT=Times New Roman]Dim model As Object<o:p></o:p>[/FONT]
[FONT=Times New Roman]Dim rowIndex As Integer<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman]'Set models class/collection to the list of models in the memory.<o:p></o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman]‘Set models = session1.ListModels<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman]'Initialize For-Each loop and integer increment variable where each model returned from the 'list [/FONT][FONT=Times New Roman]in the memory will correspond to a row in a location on a spreadsheet.  For this example, 'the [/FONT][FONT=Times New Roman]increment integer will be dislayed in the "D" column of sheet1, the model filename will be 'displayed[/FONT][FONT=Times New Roman]in the "E" column of sheet1, and the type of model will be displayed in column "F" 'of sheet1.<o:p></o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman]For Each model In models<o:p></o:p>[/FONT]
[FONT=Times New Roman]    Dim columnIndex As Integer<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]    rowIndex = rowIndex + 1<o:p></o:p>[/FONT]
[FONT=Times New Roman]    Cells(rowIndex, 4) = rowIndex<o:p></o:p>[/FONT]
[FONT=Times New Roman]    <o:p></o:p>[/FONT]
[FONT=Times New Roman]    columnIndex = 0<o:p></o:p>[/FONT]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman]'Initializes the new element of the array, each time around the loop, with the first (or next) 'filename [/FONT][FONT=Times New Roman]of the model found in the memory query.<o:p></o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman][B][COLOR=red]30    ReDim Preserve anArray(1 To rowIndex, 2)       [/COLOR][/B][/FONT]
[FONT=Times New Roman][B][COLOR=red]‘I GET AN ERROR HERE EVERYTIME[/COLOR][/B][/FONT]
[FONT=Times New Roman][/FONT][COLOR=red][FONT=Times New Roman][B]‘AFTER THE FIRST ITERATION[/B][/FONT][FONT=Times New Roman]<o:p></o:p>[/FONT][/COLOR]
[FONT=Times New Roman]<o:p> </o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman]'Initialize the For-Each loop that will roll through the columns in each row, in this case there 'are [/FONT][FONT=Times New Roman]only two columns so the columnIndex will either be 1 or 2.  Accordingly, either the 'filename of [/FONT][FONT=Times New Roman]the model or it's model type will be displayed in the spreadsheet depending on 'which piece of [/FONT][FONT=Times New Roman]information it is.<o:p></o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman]            For columnIndex = 1 To 2<o:p></o:p>[/FONT]
[FONT=Times New Roman]                    If columnIndex <> 2 Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]                        anArray(rowIndex, columnIndex) = model.fileName<o:p></o:p>[/FONT]
[FONT=Times New Roman]                        Cells(rowIndex, 5) = anArray(rowIndex, columnIndex)<o:p></o:p>[/FONT]
[FONT=Times New Roman]                    Else<o:p></o:p>[/FONT]
[FONT=Times New Roman]                        anArray(rowIndex, columnIndex) = model.Type<o:p></o:p>[/FONT]
[FONT=Times New Roman]                            If model.Type = 0 Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                Cells(rowIndex, 6) = "ASSEMBLY"<o:p></o:p>[/FONT]
[FONT=Times New Roman]                            ElseIf model.Type = 1 Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                Cells(rowIndex, 6) = "PART"<o:p></o:p>[/FONT]
[FONT=Times New Roman]                            ElseIf model.Type = 2 Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                Cells(rowIndex, 6) = "DRAWING"<o:p></o:p>[/FONT]
[FONT=Times New Roman]                            ElseIf model.Type = 3 Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                Cells(rowIndex, 6) = "FORMAT"<o:p></o:p>[/FONT]
[FONT=Times New Roman]                            ElseIf model.Type = 4 Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                Cells(rowIndex, 6) = " REPORT"<o:p></o:p>[/FONT]
[FONT=Times New Roman]                            Else<o:p></o:p>[/FONT]
[FONT=Times New Roman]                                Cells(rowIndex, 6) = "unknown or unavailable file type"<o:p></o:p>[/FONT]
[FONT=Times New Roman]                            End If<o:p></o:p>[/FONT]
[FONT=Times New Roman]                        <o:p></o:p>[/FONT]
[FONT=Times New Roman]                    End If<o:p></o:p>[/FONT]
[FONT=Times New Roman]            Next columnIndex<o:p></o:p>[/FONT]
[FONT=Times New Roman]        <o:p></o:p>[/FONT]
[FONT=Times New Roman]    Next<o:p></o:p>[/FONT]
[FONT=Times New Roman]    <o:p></o:p>[/FONT]
[FONT=Times New Roman]Set models = Nothing<o:p></o:p>[/FONT]
[FONT=Times New Roman]Set model = Nothing<o:p></o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman]'For testing purposes only.<o:p></o:p>[/FONT]
[FONT=Times New Roman]'======================================================================<o:p></o:p>[/FONT]
[FONT=Times New Roman]'For rowIndex = 1 To numRows<o:p></o:p>[/FONT]
[FONT=Times New Roman]'    For columnIndex = 1 To 2<o:p></o:p>[/FONT]
[FONT=Times New Roman]'                MsgBox anArray(rowIndex, columnIndex)<o:p></o:p>[/FONT]
[FONT=Times New Roman]'    Next columnIndex<o:p></o:p>[/FONT]
[FONT=Times New Roman]'Next rowIndex<o:p></o:p>[/FONT]
[FONT=Times New Roman]'<o:p></o:p>[/FONT]
[FONT=Times New Roman]'For rowIndex = 1 To numRows<o:p></o:p>[/FONT]
[FONT=Times New Roman]'    For columnIndex = 1 To 2<o:p></o:p>[/FONT]
[FONT=Times New Roman]'        If columnIndex <> 2 Then<o:p></o:p>[/FONT]
[FONT=Times New Roman]'        Cells(rowIndex, 5) = anArray(rowIndex, columnIndex)<o:p></o:p>[/FONT]
[FONT=Times New Roman]'        Else<o:p></o:p>[/FONT]
[FONT=Times New Roman]'        Cells(rowIndex, 6) = anArray(rowIndex, columnIndex)<o:p></o:p>[/FONT]
[FONT=Times New Roman]'        End If<o:p></o:p>[/FONT]
[FONT=Times New Roman]'    Next columnIndex<o:p></o:p>[/FONT]
[FONT=Times New Roman]'Next rowIndex<o:p></o:p>[/FONT]
[FONT=Times New Roman]       <o:p></o:p>[/FONT]
[FONT=Times New Roman]End Sub[/FONT]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
You can only change the last dimension using Redim Preserve, as far as I can see, through the magnifying glass, you are trying to change the first dimension.
 
Upvote 0
Norie,

Thanks! I didn't realize it until you mentioned that you can't change the first dimension of an array with Redim, but I researched it some and found the same conclusion elsewhere. I found that in order to change the first dimension, what you have to do is create an "array of arrays" with each element in a 1D array having been declared a variant and then each variant then declared as a 1D array. Is there any other way to change the first dimension in a 2D dynamic array without going through that process?


Thanks again...
 
Upvote 0
I suppose you could transpose the array, then extend the 2nd dimension, then transpose it back again. A bit clumsy--and VBA's transpose function has its own limitations--but it may do the job for you.
 
Upvote 0
You could transpose the array, ie switch the dimensions around.

That might not be too much of a problem with a 2D array but you'd probably need to rewrite the code.
 
Upvote 0
Gentlemen,

Thank you for the suggestions, I'll evaluate the alternatives accordingly and if I re-do the code, I'll be sure to post the ultimate solution. I think I may just rely on knowing the length of the list from the ".Count" function for the time being.

Thanks again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

mehidy1437
  • Question
Final code required expert opinion
Replies
8
Views
704
mehidy1437
mehidy1437
S
  • Solved
Vba Array subscript out of range
Replies
7
Views
1K
Alex Blakenburg
A
C
  • Question
Loop validation, copy output from same range and paste in different ranges
Replies
1
Views
388
RoryA
RoryA
J
  • Question
Resize rows of a 2D Array
2
Replies
13
Views
1K
johnnyL
J
M
  • Solved
run-time error '9' subscript out of range
Replies
1
Views
446
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,214,851
Messages
6,121,931
Members
449,056
Latest member
denissimo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top