CODEWITHSUNDEEP
arraysexcelvbamultidimensional-array
G.M
G.M

Reputation: 365

Multidimensional Array redim

Once again I need some help :)

I have a List of values And for each line it should check if there is another row where the values of column D and E are identical to the current row. So all values where Column D and E are identical need to be grouped together.

His part seems to be working fine but I wanted to store the Pairs/groups in a multidimensional Array, and thats where it gets tricky.

MultiArrPaerchen(m, n) = VarBasisZeile 
m = m + 1 
ReDim Preserve MultiArrPaerchen(0 To m, 0 To n)

This should be the important part of the code. A new value should be added and then the array is enlarged and redim'ed. The error itself occurs in the last line. Any ideas? its the first time I use multidimensional arrays, so its quiet possible that I made a mistake here.

Thanks alot in advance.

Just to make sure I got right what @user10779473 said:

ReDim MultiArrPaerchen(0 To 1000, 0 To 1000)

'The arrays gets filled and with every new value m or n increases when necessary

ReDim Preserve MultiArrPaerchen(0 To m, 0 To n)
Application.Transpose (MultiArrPaerchen)
ReDim Preserve MultiArrPaerchen(0 To n, 0 To m)
Application.Transpose (MultiArrPaerchen)

End Sub

In the end my Array has exactly the size I need it to be. Only problem my arraysize cant get above 1000?

After trying the above version I still get the same error (9) on the first 

ReDim Preserve MultiArrPaerchen(0 To m, 0 To n)

line. At this point m is 120 and n is 18 so regular, fitting numbers i would assume.

Thanks alot so far, its great new knowledge for me :)

It works :D or at least half of it ^^ Maybe a mistake with the transponse?

This is the current code:

ReDim Preserve MultiArrPaerchen(LBound(MultiArrPaerchen, 1) To UBound(MultiArrPaerchen, 1), LBound(MultiArrPaerchen, 2) To n)
Application.Transpose (MultiArrPaerchen)
ReDim Preserve MultiArrPaerchen(LBound(MultiArrPaerchen, 1) To UBound(MultiArrPaerchen, 1), LBound(MultiArrPaerchen, 2) To m)
Application.Transpose (MultiArrPaerchen)

And the VBA Local window shows Variant (0 to 1000 and 0 to 120). 120 is the fitting value, the oher one should be 18 but seemingly the m value doesnt get shortend.

Something i noticed while F8 ing through the code is that it is first Variant (0 to 1000 and 0 to 18) and then (0 to 1000 and 0 to 120). So the issue really seems to be the transpose in between.

Upvotes: 0

Views: 473

Answers (3)

user10779473
user10779473

Reputation:

You can ReDim either or both ranks of an array at any time but if you use the Preserve argument (which retains values previously set), you can only change the last rank (second dimension).

However you can Transpose¹ the array to flip the ranks of a 2-D array, modify the new last rank, then Transpose to return the 2-D array to its original form. This will either expand or truncate the 'rows' (first rank) of a 2-D array.

MultiArrPaerchen(m, n) = VarBasisZeile 
m = m + 1 
application.transpose(MultiArrPaerchen)
ReDim Preserve MultiArrPaerchen(0 To n, 0 To m)
application.transpose(MultiArrPaerchen)

This takes time and calculation, especially if performed repetitively in a large loop. As mentioned in comments (thanks K.Dᴀᴠɪs), it is significantly better to predict your upper boundaries (UBound) of each rank first or make the initial limits sufficiently large enough to handle any circumstances and reduce the array with a single transpose/redim/transpose after the loop is complete.

Addendum

This would fix your recent addition to your question.

dim m as long, n as long
ReDim MultiArrPaerchen(0 To 1000, 0 To 1000)
m = 0
n = 0

for each something in something else
    if some condition then 
        MultiArrPaerchen(m, n) = something
        m = m + 1
        n = n + 1
    end if
next something

'm is now 199, n is now 255

ReDim Preserve MultiArrPaerchen(lbound(MultiArrPaerchen, 1) To ubound(MultiArrPaerchen, 1), lbound(MultiArrPaerchen, 2) To n)
Application.Transpose (MultiArrPaerchen)
ReDim Preserve MultiArrPaerchen(lbound(MultiArrPaerchen, 1) To ubound(MultiArrPaerchen, 1), lbound(MultiArrPaerchen, 2) To m)
Application.Transpose (MultiArrPaerchen)

debug.print lbound(MultiArrPaerchen, 1) & ":" & ubound(MultiArrPaerchen, 1)
debug.print lbound(MultiArrPaerchen, 2)& ":" & ubound(MultiArrPaerchen, 2)

'result in Immediate window
0:199
0:255

¹ Transpose has an upper limit of a signed short integer; 1.e. 32,767. If you require larger transpositions, you'll have to write your own or get one from several examples on this site.

Upvotes: 1

PaichengWu
PaichengWu

Reputation: 2689

In VBA, you can only change the size of the last dimension in the array.

Since your MultiArrPaerchen(m, n) is a two-dimension array, the size of m cannot be changed.

Use transpose function to move your first dimension to second, then redim to change the size.

dim MultiArrPaerchen as variant

MultiArrPaerchen = Application.Transpose(VarBasisZeile) 
ReDim Preserve MultiArrPaerchen(lbound(MultiArrPaerchen, 1) to ubound(MultiArrPaerchen, 1), lbound(MultiArrPaerchen,2) to ubound(MultiArrPaerchen, 2) + 1)

Upvotes: 0

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You can grab the dimensions of your array upfront.

So, count the number of times that the dupe in the rows occur, then set the dimensions.

Since it's unclear how you're using your multidimensional array, here's how you can get set the dimensions on a 1-D array before you ever assign a value to it (hence, no need to use Preserve at all) using the worksheet function: COUNTIF():

Dim dupeCount As Long
dupeCount = WorksheetFunction.CountIf([D:D], [E1])
ReDim MultiArrPaerchen(dupeCount - 1)

You can then proceed using the same method to get your upper-boundary of the second dimension - but I am not sure of your criteria for that so I cannot provide an example.

Upvotes: 0

Related Questions