Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fast export large 2-dim array to worksheet
Hello Excel Community, For many years, I have been programming in EXCEL VBA and often use 2 dimensional arrays, usually of type Double of Variant. Normally, I would declare the array such as : Dim aMatrix(10000,50) as Double Some math code would populate all the cells and I would then print the aMatrix to a worksheet by nesting in a double loop: for i... for j... Sheet1.Cells(i,j).Value = aMatrix(i,j) The problem here is that Excel really slows down after a couple of thousand. Is there a fast way to bulk copy an array to a worksheet ? I am looking for a function along the lines of FastCopytoWorkSheet (aMatrix() as double, aRange as Range) .... Is it best to just turn off screen updating or is there a more elegant way ? Many thanks and best regards, Alberto -- aafraga ------------------------------------------------------------------------ aafraga's Profile: http://www.excelforum.com/member.php...o&userid=31314 View this thread: http://www.excelforum.com/showthread...hreadid=510013 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fast export large 2-dim array to worksheet
Dim aMatrix(10000,50) as Double
' code the populates the aMatrix lb1 = lbound(aMatrix,1) lb2 = lbound(aMatrix,2) ub1 = ubound(aMatrix,1) ub2 = ubound(aMatrix,2) rws = ub1 - lb1 + 1 cols = ub2- lb2 + 1 Sheet1.Cells(1,1).Resize(rws,cols).Value = aMatrix Obviously, if you know the number of rows and columns, you don't need to calculate them as I have. However, without knowing option base, and you didn't specify the lower bound, I provided a generalized approach. The key bit is you can put it all down in one step. -- Regards, Tom Ogilvy "aafraga" wrote in message ... Hello Excel Community, For many years, I have been programming in EXCEL VBA and often use 2 dimensional arrays, usually of type Double of Variant. Normally, I would declare the array such as : Dim aMatrix(10000,50) as Double Some math code would populate all the cells and I would then print the aMatrix to a worksheet by nesting in a double loop: for i... for j... Sheet1.Cells(i,j).Value = aMatrix(i,j) The problem here is that Excel really slows down after a couple of thousand. Is there a fast way to bulk copy an array to a worksheet ? I am looking for a function along the lines of FastCopytoWorkSheet (aMatrix() as double, aRange as Range) ... Is it best to just turn off screen updating or is there a more elegant way ? Many thanks and best regards, Alberto -- aafraga ------------------------------------------------------------------------ aafraga's Profile: http://www.excelforum.com/member.php...o&userid=31314 View this thread: http://www.excelforum.com/showthread...hreadid=510013 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fast export large 2-dim array to worksheet
Sub test()
Dim are(1 To 10000, 1 To 50) As Double Dim r As Long, c As Long For c = 1 To 50 For r = 1 To 10000 ar(r, c) = r + (c / 100) Next Next Range(Cells(1, 1), Cells(10000, 50)).Value = ar End Sub Around this size it might be faster to start breaking down into smaller arrays before dumping. Regards, Peter T "aafraga" wrote in message ... Hello Excel Community, For many years, I have been programming in EXCEL VBA and often use 2 dimensional arrays, usually of type Double of Variant. Normally, I would declare the array such as : Dim aMatrix(10000,50) as Double Some math code would populate all the cells and I would then print the aMatrix to a worksheet by nesting in a double loop: for i... for j... Sheet1.Cells(i,j).Value = aMatrix(i,j) The problem here is that Excel really slows down after a couple of thousand. Is there a fast way to bulk copy an array to a worksheet ? I am looking for a function along the lines of FastCopytoWorkSheet (aMatrix() as double, aRange as Range) ... Is it best to just turn off screen updating or is there a more elegant way ? Many thanks and best regards, Alberto -- aafraga ------------------------------------------------------------------------ aafraga's Profile: http://www.excelforum.com/member.php...o&userid=31314 View this thread: http://www.excelforum.com/showthread...hreadid=510013 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fast export large 2-dim array to worksheet
Typo
Dim are(1 To 10000, 1 To 50) As Double Dim ar(1 To 10000, 1 To 50) As Double Spell checker must have changed "ar" to "are" without me noticing and pressing wrong button. Tom - I didn't see yours, didn't mean to duplicate. Regards, Peter T "Peter T" <peter_t@discussions wrote in message ... Sub test() Dim are(1 To 10000, 1 To 50) As Double Dim r As Long, c As Long For c = 1 To 50 For r = 1 To 10000 ar(r, c) = r + (c / 100) Next Next Range(Cells(1, 1), Cells(10000, 50)).Value = ar End Sub Around this size it might be faster to start breaking down into smaller arrays before dumping. Regards, Peter T "aafraga" wrote in message ... Hello Excel Community, For many years, I have been programming in EXCEL VBA and often use 2 dimensional arrays, usually of type Double of Variant. Normally, I would declare the array such as : Dim aMatrix(10000,50) as Double Some math code would populate all the cells and I would then print the aMatrix to a worksheet by nesting in a double loop: for i... for j... Sheet1.Cells(i,j).Value = aMatrix(i,j) The problem here is that Excel really slows down after a couple of thousand. Is there a fast way to bulk copy an array to a worksheet ? I am looking for a function along the lines of FastCopytoWorkSheet (aMatrix() as double, aRange as Range) ... Is it best to just turn off screen updating or is there a more elegant way ? Many thanks and best regards, Alberto -- aafraga ------------------------------------------------------------------------ aafraga's Profile: http://www.excelforum.com/member.php...o&userid=31314 View this thread: http://www.excelforum.com/showthread...hreadid=510013 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Best way to easily export large amount of data from Excel | Excel Discussion (Misc queries) | |||
range to VBA array, and doing this fast | Excel Programming | |||
Remove First Row from Variant Array FAST? | Excel Programming | |||
Reading Super Large Ranges from Worksheet into Array | Excel Programming | |||
Fast Export | Excel Programming |