Redim Preserve works great on the last dimension of an array, but if I
want to change a 2x2 array to a 3x3 array, leaving the 2x2 data intact,
I've had to:

1. transfer all the data to a temporary 3x3 array using "for" loops;
2. redim the orginal array to 3x3;
3. repopulate the original array with another set of "for" loops; &
4. clear the temporary array (redim array(0)).

Question: Is there a faster means? The actual arrays have several
million elements each.

For example, is there a way to "set" the original array equal to the
temporary array after step 1? Or is there a way to "rename" the
temporary array dynamically so that the code thinks it's the original
array? Or any other idea whatsoever. Thanks in advance.:)


---
Message posted from http://www.ExcelForum.com/

"PatFinegan " wrote...
Redim Preserve works great on the last dimension of an array, but if I
want to change a 2x2 array to a 3x3 array, leaving the 2x2 data intact,
....
Question: Is there a faster means? The actual arrays have several
million elements each.

How are you initializing the original array? If you know how large the final
array should be, why not dimension the array to the final dimensions from
the start?

But the more fundamental question would be if you really are using arrays
with millions of elements, why are you using either Excel or VB[A] at all?
If your arrays are that big, Excel/VB[A] will be rather slow working with
them.

Harlan's comments notwithstanding,

Function myReDim(x() As Double, _
ByVal new1 As Long, ByVal new2 As Long) _
As Double()
Dim newX() As Double, i As Long, j As Long
ReDim newX(0 To new1, 0 To new2)
For i = 0 To UBound(x, 1)
For j = 0 To UBound(x, 2)
newX(i, j) = x(i, j)
Next j
Next i
myReDim = newX
End Function

Sub testIt()
Dim x() As Double, i As Long, j As Long
ReDim x(0 To 1, 0 To 1)
For i = 0 To 1
For j = 0 To 1
x(i, j) = i + j
Next j
Next i
x = myReDim(x, 2, 2)
MsgBox x(1, 1) & ", " & UBound(x, 1) & ", " & UBound(x, 2)
End Sub

Also, if you are familiar and comfortable using variants within
variants to create structures that resemble arrays, use the code below.
Note that this creates 1D array in a variant and each element of the
array contains a 1D array. So, one cannot refer to an element as
x(i,j) but must use x(i)(j). I don't know how the code below will
compare with the above code, but it is also a fundamental concept for
other data structures such as a triangular matrix.

Sub myVarReDim(ByRef x As Variant, _
ByVal new1 As Long, ByVal new2 As Long)
Dim i As Long, temp As Variant
If InStr(1, TypeName(x), "(", vbTextCompare) < 1 Then
ReDim x(0 To new1)
ReDim temp(0 To new2)
For i = 0 To new1
x(i) = temp
Next i
Exit Sub
End If
Dim OldBound As Long
OldBound = UBound(x)
For i = 0 To UBound(x)
temp = x(i)
ReDim Preserve temp(0 To new2)
x(i) = temp
Next i
ReDim Preserve x(0 To new1)
ReDim temp(0 To new2)
For i = OldBound + 1 To new1
x(i) = temp
Next i
End Sub
Sub testVariant()
Dim x As Variant, i As Long, j As Long
Call myVarReDim(x, 1, 1)
For i = 0 To 1
For j = 0 To 1
x(i)(j) = i + j
Next j
Next i
Call myVarReDim(x, 2, 2)
MsgBox x(1)(1) & ", " & UBound(x, 1) & ", " & UBound(x(1), 1)

End Sub


--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , PatFinegan
says...
Redim Preserve works great on the last dimension of an array, but if I
want to change a 2x2 array to a 3x3 array, leaving the 2x2 data intact,
I've had to:

1. transfer all the data to a temporary 3x3 array using "for" loops;
2. redim the orginal array to 3x3;
3. repopulate the original array with another set of "for" loops; &
4. clear the temporary array (redim array(0)).

Question: Is there a faster means? The actual arrays have several
million elements each.

For example, is there a way to "set" the original array equal to the
temporary array after step 1? Or is there a way to "rename" the
temporary array dynamically so that the code thinks it's the original
array? Or any other idea whatsoever. Thanks in advance.:)


---
Message posted from http://www.ExcelForum.com/

PatFinegan wrote in message ...
Redim Preserve works great on the last dimension of an array, but if I
want to change a 2x2 array to a 3x3 array, leaving the 2x2 data intact,
I've had to:

1. transfer all the data to a temporary 3x3 array using "for" loops;
2. redim the orginal array to 3x3;
3. repopulate the original array with another set of "for" loops; &
4. clear the temporary array (redim array(0)).

Question: Is there a faster means? The actual arrays have several
million elements each.

For example, is there a way to "set" the original array equal to the
temporary array after step 1? Or is there a way to "rename" the
temporary array dynamically so that the code thinks it's the original
array? Or any other idea whatsoever. Thanks in advance.:)


---
Message posted from http://www.ExcelForum.com/

Hi,

Alan Beban has a workbook with custom functions which will allow you
to resize the dimensions of an array, not just the last one. Search
for his name and you'll find the link.

It is possible to Redim all the dimensions of an array by transposing
twice. Here is one simple example.

'----------
Sub ResizeArray()
Dim Arr1
Dim x As Long, y As Long

ReDim Arr1(1 To 2, 1 To 2)
For x = 1 To 2
For y = 1 To 2
Arr1(x, y) = Rnd
Next y
Next x

Arr1 = Application.Transpose(Arr1)
ReDim Preserve Arr1(1 To 2, 1 To 3)
Arr1 = Application.Transpose(Arr1)
ReDim Preserve Arr1(1 To 3, 1 To 3)

End Sub
'-----------

Regards,
Jay

I can't vouch for "faster", but with the functions in the freely
downloadable file at http://home.pacbell.net/beban available to my
workbook, the following took 2 seconds:

Sub testit2()
Dim arr() As Long
ReDim arr(1 To 2000, 1 To 1000)
ResizeArray arr, 2001, 1001
End Sub

Alan Beban

PatFinegan < wrote:
Redim Preserve works great on the last dimension of an array, but if I
want to change a 2x2 array to a 3x3 array, leaving the 2x2 data intact,
I've had to:

1. transfer all the data to a temporary 3x3 array using "for" loops;
2. redim the orginal array to 3x3;
3. repopulate the original array with another set of "for" loops; &
4. clear the temporary array (redim array(0)).

Question: Is there a faster means? The actual arrays have several
million elements each.

For example, is there a way to "set" the original array equal to the
temporary array after step 1? Or is there a way to "rename" the
temporary array dynamically so that the code thinks it's the original
array? Or any other idea whatsoever. Thanks in advance.:)


---
Message posted from http://www.ExcelForum.com/

Thanks for the mention, Jay; I have posted a reply to this thread.

By the way, Application.Transpose as included in your code below will
fail in Excel2000 and earlier if the array has more than 5461 elements
(the OP mentioned millions of elements), and will not preserve the type
of the array; e.g, even if small enough, if Arr1 in your code were
declared as Long or Double, the code would convert it to a Variant()
type array.

Alan Beban

Jay Petrulis wrote:
PatFinegan wrote in message ...

Redim Preserve works great on the last dimension of an array, but if I
want to change a 2x2 array to a 3x3 array, leaving the 2x2 data intact,
I've had to:

1. transfer all the data to a temporary 3x3 array using "for" loops;
2. redim the orginal array to 3x3;
3. repopulate the original array with another set of "for" loops; &
4. clear the temporary array (redim array(0)).

Question: Is there a faster means? The actual arrays have several


Hi,

Alan Beban has a workbook with custom functions which will allow you
to resize the dimensions of an array, not just the last one. Search
for his name and you'll find the link.

It is possible to Redim all the dimensions of an array by transposing
twice. Here is one simple example.

'----------
Sub ResizeArray()
Dim Arr1
Dim x As Long, y As Long

ReDim Arr1(1 To 2, 1 To 2)
For x = 1 To 2
For y = 1 To 2
Arr1(x, y) = Rnd
Next y
Next x

Arr1 = Application.Transpose(Arr1)
ReDim Preserve Arr1(1 To 2, 1 To 3)
Arr1 = Application.Transpose(Arr1)
ReDim Preserve Arr1(1 To 3, 1 To 3)

End Sub
'-----------

Regards,
Jay

Dear Tushar, Jay and Alan,

I am slogging through your suggestions. Thank you so much!!

Actually, the real situation is an n-dimensional data cube generated b
a Monte Carlo experiment on different combinations of financia
instruments covering various categories of risks and businesses
depending on user preference.

So, rather than regenerate the entire data cube once a user decide
that he wants to include, say, one more business unit or one more laye
of cover, I wanted to keep the other independent data items intact, an
just augment the cube. For example, one data cube is ValCube(s, t1, i
c, b, r, t2), where s = number of simulations (redimensione
constantly, since I test for convergence and there is no way to kno
the answer before you begin), t1 = time of event, c = list o
companies, i = specific item being simulated (event rate, even
severity, accounting result, etc.), bu = list of business units pe
company, r = risk classes, and t2 = time of repercussions from event
in time period t1.

My original method (transfer a() to b() then back to a redimensione
a() )works quickly enough, but I knew it wasn't elegant. The transpos
idea is neat since no single dimension has more than 200 elements. It'
the multiplicative combinations that add up, not the single dimension
(I presently use 2 GB RAM to run and store 1000+ sims, and am assumin
a client workstation with 8 GB will get me to the magic 10,000 quit
easily -- important for precision in derivative pricing).

In response to the first post, I originally coded everything through
database, but it was about 1000 times slower that manipulating array
in memory. I really prefer to write nothing to a hard drive except
couple of seeds.

Thanks again to everyone!!

Pat Finegan:

--
Message posted from http://www.ExcelForum.com

Dear Tushar,

Your single-transfer method works great on any number of dimensions an
is obviously twice as fast as my original 2-transfer approach. Alan'
tools are cool but I was getting bogged down in generalizing th
ResizeArray function from 3 to N dimensions. Thanks again.

Pat Finega

--
Message posted from http://www.ExcelForum.com

In fact, while reading your explanation of where you needed this
capability, I was thinking of just that very point -- using variants
within variants just might be the way to go. <smile

While intrinsically slower than real arrays, they could easily be
faster for complex array (or vector) level operations.

For strictly 2D variants-within-variants, you could tighten up the code
I shared with:

If new2 < UBound(x(i)) Then
For i = 0 To UBound(x)
temp = x(i)
ReDim Preserve temp(0 To new2)
x(i) = temp
Next i
End If

A long time ago, I wrote a VBA class based on this concept. It used
recursive calls to implement data structures and structure capabilities
that are otherwise difficult, if not impossible, to implement. For
example, it allowed one to create a triangular matrix, where 'row' i
has i elements); it also allowed one to resize *any* dimension of a N
dimension array while preserving the existing data.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , PatFinegan
says...
Dear Tushar,

Your single-transfer method works great on any number of dimensions and
is obviously twice as fast as my original 2-transfer approach. Alan's
tools are cool but I was getting bogged down in generalizing the
ResizeArray function from 3 to N dimensions. Thanks again.

Pat Finegan


---
Message posted from http://www.ExcelForum.com/

Tushar Mehta wrote:
. . . A long time ago, I wrote a VBA class based on this concept. . . . it also allowed one to resize *any* dimension of a N
dimension array while preserving the existing data.


Are you willing to share it with us?

Alan Beban

Some months back Harlan Grove posted a function that provided some
functionality for up to 6 dimensions. I think that at the time he
mentioned that the VBA Syntax did not accommodate generalizing the code;
his code basically restated itself for each additional dimension, as
does the ResizeArray function.

Tushar Mehta mentioned in this thread that he had written code that
allowed one to resize *any* dimension of a N dimension array while
preserving the existing data, but I haven't seen anything posted. At
any rate, I don't know how to generalize the ResizeArray code. I did, in
about 5 to 10 minutes add the capability to handle a fourth dimension,
and an arbitrary number of additional dimensions could be readily added
that way. I can't imagine it adds much time to the execution speed,
since the number of dimensions and the type of the variable are still
dealt with only singly, once determined.

By the way, how many dimensions does your application need to accommodate?

Alan Beban

PatFinegan < wrote:
Dear Tushar,

Your single-transfer method works great on any number of dimensions and
is obviously twice as fast as my original 2-transfer approach. Alan's
tools are cool but I was getting bogged down in generalizing the
ResizeArray function from 3 to N dimensions. Thanks again.

Pat Finegan


---
Message posted from http://www.ExcelForum.com/

I was reasonably sure I shared it about a year and a half ago, but
maybe not...

In any case, I'll see if I can dig up the code from wherever I put it.

The basic idea is that since everything is a variant, there are few
restrictions on what cannot be done. The price, of course, is
performance in that all access is through variant data structures.

Here's an example I put together that allows the resizing of any
dimension of a array of variants in variants.

Option Explicit
Sub ReDimN(ByRef aStruc, ByVal whatDim As Integer, ByVal newSize As
Integer)
Dim oldDim As Integer, i As Integer
'this code is missing safety checks
If whatDim = 1 Then
oldDim = UBound(aStruc)
ReDim Preserve aStruc(1 To newSize)
For i = oldDim + 1 To newSize
aStruc(i) = aStruc(oldDim)
Next i
Else
For i = LBound(aStruc) To UBound(aStruc)
ReDimN aStruc(i), whatDim - 1, newSize
Next i
End If
End Sub
Sub test2D()
Dim aStruc, temp, i As Integer
ReDim aStruc(1 To 8)
'we need temp because VB won't allow redim aStruc(i) (1 to 10)
ReDim temp(1 To 10)
For i = 1 To 10
temp(i) = i
Next i
For i = LBound(aStruc) To UBound(aStruc)
aStruc(i) = temp
Next i
ReDimN aStruc, 1, 12
ReDimN aStruc, 2, 15
End Sub
Sub test3D()
Dim aStruc, temp, i As Integer, j As Integer, k As Integer
ReDim aStruc(1 To 8)
'we need temp because VB won't allow redim aStruc(i) (1 to 10)
ReDim temp(1 To 10)
For i = LBound(aStruc) To UBound(aStruc)
aStruc(i) = temp
Next i
ReDim temp(1 To 5)
For i = LBound(aStruc) To UBound(aStruc)
For j = LBound(aStruc(i)) To UBound(aStruc(i))
For k = LBound(temp) To UBound(temp)
temp(k) = i * j * k
Next k
aStruc(i)(j) = temp
Next j
Next i
ReDimN aStruc, 1, 10
ReDimN aStruc, 2, 12
ReDimN aStruc, 3, 8
End Sub

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar Mehta wrote:
. . . A long time ago, I wrote a VBA class based on this concept. . . . it also allowed one to resize *any* dimension of a N
dimension array while preserving the existing data.


Are you willing to share it with us?

Alan Beban