Out of Memory: Array Transpose

[TheVisionThing]

I'm using the following function to transpose a 2d array in Excel 2003.

Public Function TransposeArray(arrIn As Variant)
Dim i As Long, j As Long, lngUpperI As Long, lngUpperJ As Long,
intLoweri As Integer, intLowerj As Integer, arrOut() As Variant
lngUpperI = UBound(arrIn, 1)
lngUpperJ = UBound(arrIn, 2)
intLoweri = LBound(arrIn, 1)
intLowerj = LBound(arrIn, 2)
ReDim arrOut(intLowerj To lngUpperJ, intLoweri To lngUpperI)
For i = intLoweri To lngUpperI
For j = intLowerj To lngUpperJ
arrOut(j, i) = arrIn(i, j)
Next
Next
Set arrIn = Nothing
TransposeArray = arrOut
End Function

The function usually works fine, but it generates an out of memory error
message on a 190,000 by 4 2d array. I presume that is because the function
is creating a new array, arrOut, that's as big as the incoming array, arrIn.

Does anyone know how to break down such a function into digestible bits, so
that it doesn't run out of memory? The worksheet function
Application.Transpose is not an option, as it generates an error message
after about 64,000 rows.

Thanks,
Wayne C.

 

[Alan Beban]

I'm using the following function to transpose a 2d array in Excel 2003.

Public Function TransposeArray(arrIn As Variant)
Dim i As Long, j As Long, lngUpperI As Long, lngUpperJ As Long,
intLoweri As Integer, intLowerj As Integer, arrOut() As Variant
lngUpperI = UBound(arrIn, 1)
lngUpperJ = UBound(arrIn, 2)
intLoweri = LBound(arrIn, 1)
intLowerj = LBound(arrIn, 2)
ReDim arrOut(intLowerj To lngUpperJ, intLoweri To lngUpperI)
For i = intLoweri To lngUpperI
For j = intLowerj To lngUpperJ
arrOut(j, i) = arrIn(i, j)
Next
Next
Set arrIn = Nothing
TransposeArray = arrOut
End Function

The function usually works fine, but it generates an out of memory error
message on a 190,000 by 4 2d array. I presume that is because the function
is creating a new array, arrOut, that's as big as the incoming array, arrIn.

Does anyone know how to break down such a function into digestible bits, so
that it doesn't run out of memory? The worksheet function
Application.Transpose is not an option, as it generates an error message
after about 64,000 rows.

Thanks,
Wayne C.

In xl2000 I get a Type mismatch error from Set arrIn = Nothing; not
unexpected, since arrIn is not an Object variable. When I comment that
line out it works fine, as it does if I substitute Erase arrIn for Set
arrIn = Nothing.

Alan Beban

 

[Tom Ogilvy]

What do you gain by transposing an array of that size. I can't see anything
you could do with it as an "entity". So if you only need to reference
values in it, just reverse your thinking and transpose your indexes.

 

[TheVisionThing]

In xl2000 I get a Type mismatch error from Set arrIn = Nothing; not
unexpected, since arrIn is not an Object variable. When I comment that
line out it works fine, as it does if I substitute Erase arrIn for Set
arrIn = Nothing.

I wasn't erring out on the line 'Set arrIn = Nothing' but rather getting an
out of memory message on 'ReDim arrOut(intLowerj To lngUpperJ, intLoweri To
lngUpperI)'. Nevertheless I took up your good suggestion of using 'Errase
arrin' but it didn't solve the problem. Looks to me like the issue is that
there isn't enough memory to support two arrays of this large size - hence
my thought about transposing over in chunks.

Thanks,
Wayne C.

 

[TheVisionThing]

Tom,

Excellent point, but in this instance I'm adding records to an already large
2d array by using Redim Preserve. Since Redim Preserve will only increase
the second dimension of a 2d array, not the first dimension, I'm transposing
the array first and then transposing it back after adding the records. Now,
granted, instead of doing that, I can create a new temporary bigger array,
write the old records and the new records to it, and then delete the old
array instead, but I suspect I may encounter the same out of error message
I'm getting currently on the line:

ReDim arrOut(intLowerj To lngUpperJ, intLoweri To lngUpperI)

in my transpose function. In both instances I'm duplicating a large array
in memory.

I may experiment with this, though, unless someone has a better suggestion.

Thanks,
Wayne C.

 

[Tom Ogilvy]

100 x 4
you want to add rows,
so you transpose
add columns
transpose back.

now
101 x 4

Instead, start with

4 x 100

add columns (rows) whenever you need.

Just adjust your thinking.

 

[TheVisionThing]

100 x 4
you want to add rows,
so you transpose
add columns
transpose back.

now
101 x 4

Instead, start with

4 x 100

add columns (rows) whenever you need.

Just adjust your thinking.

--

Not that you were to know, but in this instance I can't start with 4 * 100
as I'm writing data ranges to arrays using the following function.

Public Function RangeToArr(objWs, objStartCell, intColOffset, intRowOffset)
Dim lngRangeBottom As Long, lngRangeRight As Long, objRange1 As Range
lngRangeBottom = xyCorner("Y", objStartCell.Offset(0, intColOffset))
lngRangeRight = xyCorner("X", objStartCell.Offset(intRowOffset, 0))
Set objRange1 = objWs.Range(objStartCell,
objStartCell.Offset(lngRangeBottom - objStartCell.Row, lngRangeRight -
objStartCell.Column))
RangeToArr = objRange1.Value
End Function

I don't have any control over the format of the data ranges supplied.

But thanks anyway.

Regards,
Wayne C.

 

[Tom Ogilvy]

No you don't have control of that, but you do have control

varr = RangeToArr(objWs, objStartCell, intColOffset, intRowOffset)

k = ubound(BigArray,2)
Redim Preserve BigArray(1 to 4, 1 to ubound(BigArray) + ubound(varr,1))
for i = 1 to ubound(varr,1)
for j = 1 to 4
BigArray(j,k) = varr(i,j)
k = k + 1
Next
Next

No transposing of either array.

but, I guess you don't really want a solution.

 

[TheVisionThing]

Tom,

Certainly didn't want to give the impression that I don't want a solution,
and apologize if I came across that way and for any obtuseness I displayed.
I have enormous respect for your abilities and for all the help you've
provided in this forum over the years.

I'll rewrite my code in this fashion this weekend, see if it avoids the 'out
of memory' error and report back.

Many thanks,
Wayne C.

 

[Dana DeLouis]

Don't know if this would help... Sometimes if it gets too complicated, a
Dictionary or Collection object can be very helpful.
Here's a quick Collection example. There are many ways to handle this.
This is a rather simple example to demo if this might be something worth
looking into.

Sub Demo()
Dim Col As New Collection
Dim v As Variant

Col.Add Array(1, 2, 3, 4), "Recond_1"
Col.Add Array(3, 4, 5, WorksheetFunction.Pi), "Recond_2"
'// You can keep adding Records here ...

'Update Record 1, 4th item:
v = Col(1)
v(4 - 1) = "4th item now 99"

Col.Remove (1)
Col.Add v, "Recond_1", 1

End Sub

Again, lots of different options here. Good luck. :>)

 

[Tushar Mehta]

A 190,000 x 4 variant array requires only 12MB of memory. Add the
array overhead and the memory requirement goes up by 20+190000*(4+4)
*bytes*

Maybe, the problem is caused by the OS running out of some other kind
of memory.

In any case, the code below works just fine in WinXP/XL2003.

Option Explicit

Sub testIt()
Dim i As Long, j As Long, Arr1(1 To 190000, 1 To 4) As Variant, _
Arr2() As Variant
ReDim Arr2(LBound(Arr1, 2) To UBound(Arr1, 2), _
LBound(Arr1) To UBound(Arr1))
For i = LBound(Arr2) To UBound(Arr2)
For j = LBound(Arr2, 2) To UBound(Arr2, 2)
Arr2(i, j) = i * j
Next j
Next i
For i = LBound(Arr2) To UBound(Arr2)
For j = LBound(Arr2, 2) To UBound(Arr2, 2)
Arr1(j, i) = Arr2(i, j)
Next j
Next i
End Sub


--
Regards,

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