Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
TheVisionThing wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
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. -- Regards, Tom Ogilvy "TheVisionThing" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
"Alan Beban" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
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. -- Regards, Tom Ogilvy "TheVisionThing" wrote in message m... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
"Tom Ogilvy" wrote in message
... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
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. -- Regards, Tom Ogilvy "TheVisionThing" wrote in message m... "Tom Ogilvy" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Out of Memory: Array Transpose
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. :) -- Dana DeLouis Win XP & Office 2003 "TheVisionThing" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose array sum | Excel Worksheet Functions | |||
Transpose Array | Excel Discussion (Misc queries) | |||
create an array with unique items IN MEMORY | Excel Worksheet Functions | |||
does Excel have memory limit of data Array? | Excel Programming | |||
Excel: Array & Memory | Excel Programming |