Excel VBA Dynamic array

Question

I know we can write dynamic array My question is Can i write values 6*6 it will get me a 36 array

I want this to be saved in some cell as my values could be changed to 5*5 Basically my array is dynamic i want each result to be saved

Can you [edit] your question with some sample code or pseudo-code? — BigBen, Nov 27 '19 at 14:37
Are you trying to store an entire array into a single worksheet cell? — John Alexiou, Nov 27 '19 at 15:48

Pᴇʜ · Answer 1 · 2019-11-29T21:11:00.240

4

Note that if you have an array

Dim Arr()
ReDim Arr(1 To 6, 1 To 6)

you can only change the last dimension eg

ReDim Preserve Arr(1 To 6, 1 To 5)

but never the first one. Therefore what you ask for is not possible without loosing the data in the array.

The only workaround for that is to create a new array and move the data from the old array to the new one field by field.

edited Nov 29 '19 at 21:11

answered Nov 27 '19 at 14:41

Pᴇʜ

56,719
10
49
73

Upvoted. Not really sure what OP wants, but this is a good point to consider. – BigBen Nov 27 '19 at 14:44
1

@BigBen Not sure too ^^ but if that is what he tried to do, we can shorten the discussion completely because it's not possible, and he needs to change the approach completely. – Pᴇʜ Nov 27 '19 at 14:46
FYI posted an answer with other workarounds to `ReDim` both dimensions in a 2-dim array; you might be interested especially in the advanced features of the `Application.Index` function :-) +1 @PEH – T.M. Nov 29 '19 at 19:55
1

@T.M. Nice thoughts +1 :) Actually the `Index` solution is technically more like creating a new (smaller) array out of the old one and write it back into the old variable `v` rather than re-dimensioning the array. Even if that has the same result :) • Would be interesting to do some tests about speed of that workaround (or if pushing it into a range like your first idea is even faster). – Pᴇʜ Nov 29 '19 at 20:48
1

Note that if you Dim an array as shown here it's fixed size and cannot be Redim'd (with or without Preserve). If you Dim it as a dynamic array `Dim Arr()` then size it with `ReDim Arr(1 To 6, 1 To 6)` then the commentary around Preserve is true. – chris neilsen Nov 29 '19 at 21:09
As suggested I did some tests showing that the `Index` function prevails over the `Push to Range` method, i.e. it's roughly twice as fast: 100k rows are executed within 0.28 seconds, 500k in 1.45 secs and 1 million in 2,92 secs vs. 0.59, 2.97, 6,20 secs (whereas the workaround looping thru a newly built array is the fastest way, even 1M under 1 second - @PEH – T.M. Nov 30 '19 at 11:34
1

@T.M. wow I didn't expect the loop to be the fastest ^^ Nice job. – Pᴇʜ Nov 30 '19 at 16:56

score 1 · Answer 2 · answered Nov 29 '19 at 19:52

Write 6x6 array values back to sheet as if redimmed to 5x5

As far as I understand your intention, you want to - save 2-dim array values to a range (referenced by a given top cell) and - to get rid of the upper boundary in both dimensions for reasons whatever.

If not added one by one, you might have gotten the original data from a sheet range as follows

Dim v                               ' provide for a variant array
v = Sheet1.Range("A2").Resize(6, 6) ' assign all data to 1-based 2-dim array v

Note the fully qualified range reference via a sheet's CodeName, assuming basic data in a sheet with VBE Tool Window (Name) of e.g. Sheet1.

Attempt 1 - resize the hosting target range to receive array v

This would be the easiest way to get the needed values into the sheet, but with the disadvantage that you did'nt change the related array likewise, e.g.

With Sheet2
    .Cells.Clear            ' emptying any content in target sheet
    .Range("A2").Resize(5, 5) = v   ' start cell e.g. in A2 (leaving room for title row)
End With

To overcome this, you might want to copy to a temporary sheet and return the resized values only

    With Sheet2
        .Cells.Clear            ' emptying any content in target sheet
        .Range("A2").Resize(5, 5) = v   ' blueprint the entire array via a filtering bottle neck
        v = .UsedRange          ' resize the array, too :-)
    End With

Attempt 2 - redim the original array

In case you'd try to redimension the original array via ► ReDim or ReDim Preserve, PEH mentioned correctly, that it's only possible to change the last dimension, e.g.

ReDim Preserve Arr(1 To 6, 1 To 5)

but never the first one.

TL;DR

The cited answer states as well:

The only workaround for that is to create a new array and move the data from the old array to the new one field by field.

Out of inquisitiveness I tried to find other workarounds.

[1] Using the ► Application.Index function

    v = Application.Index(v, Evaluate("row(1:5)"), Array(1, 2, 3, 4, 5))

or alternatively via procedure call RD (short for ReDim)

RD v, 5, 5
'...

Sub RD(Arr, UBnd1&, UBnd2&)
    Arr = Application.Index(Arr, Evaluate("row(1:" & UBnd1 & ")"), Application.Transpose(Evaluate("row(1:" & UBnd2 & ")")))
End Sub

C.f. the advanced features and pecularities of the Application.Index function at Insert new first column in array without loops

[2] Using a listbox'es .List property

An alternative would be - to redim an array in its 2nd dim, - assign it to a ListBox.List created on the fly, - remove last rows and assign .List property back to the array

Caveat: the .List property returns a zero-based 2-dim array instead 1-based items see return 0-based array from UsedRange value to reflect the method.

[3] Possible memory methods avoiding too much loops, e.g. via MemCpy or CopyMemory

►Would be interested in any feed back knowing a valid answer, could pose a new question if wanted.

Excel VBA Dynamic array

2 Answers2