Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iteration performance issue
Hi,
I am trying to programmatically iterate through rows, picking the values of two cells per row, doing some (simple arithmetic) calculations and writing output to another sheet. (One write per 32 reads in average.) I can't figure out what I am doing wrong, because it takes some awful lot of time. My input worksheet has 31000 rows and it takes some 40min to step through them. I am using Worksheet.Cells(y, x) syntax to retrieve and write values from/to cells. Can't believe Excel is so lame, hope a faster syntax exists. Even Access steps through a recordset faster than this. Could not find anything relevant in Help. Range transfer to/from VBA array would be great. Using Excel XP 2002 on a 1.5GHz Pent with 512M RAM. Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iteration performance issue
Can't do much if you don't post your code.
one can hardly program to paragraph 1. However, it sounds like you could put a formula in the destination sheet and drag fill it down the column to do what you want. -- Regards, Tom Ogilvy "J.Smith" wrote in message ... Hi, I am trying to programmatically iterate through rows, picking the values of two cells per row, doing some (simple arithmetic) calculations and writing output to another sheet. (One write per 32 reads in average.) I can't figure out what I am doing wrong, because it takes some awful lot of time. My input worksheet has 31000 rows and it takes some 40min to step through them. I am using Worksheet.Cells(y, x) syntax to retrieve and write values from/to cells. Can't believe Excel is so lame, hope a faster syntax exists. Even Access steps through a recordset faster than this. Could not find anything relevant in Help. Range transfer to/from VBA array would be great. Using Excel XP 2002 on a 1.5GHz Pent with 512M RAM. Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iteration performance issue
Range transfer to/from VBA array would be great.
This method is reasonably fast Sub klhjok() Dim vArr As Variant application.screenupdating=False application.calculation=xlManual ' ' get range into variant containing array ' vArr = Worksheets("fred").Range("A3:L50") ' ' show an element in the arry ' MsgBox vArr(10, 10) ' ' change element in array ' vArr(10, 10) = 123456 ' ' put array back to sheet ' in different place ' Worksheets("fred").Range("A3:L50").Offset(3, 20) = vArr application.screenupdating=true application.calculation=xlAutomatic End Sub regards Charles ______________________ Decision Models The Excel Calculation Site. www.DecisionModels.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iteration performance issue
vArr = Worksheets("fred").Range("A3:L50") Worksheets("fred").Range("A3:L50").Offset(3, 20) = vArr Oh, THAT's how you get a range into/out of an array? Great! Didn't know that. application.calculation=xlAutomatic Wasn't aware of that, either. I will definitely try these two methods to speed up the code. (Screenupdating has already been taken care of). Thanks a lot, Charles! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Iteration performance issue
The "application.calculation=xlManual" did the trick!
There was even no need to deal with an array - the same 13K rows got processed in 11sec (as oppsed to 40 min), which is fine by me. I think I got an idea where the problem was. My input sheet with 13K rows has 3 columns with "cascading" formulae (the next row takes it's input from the previous one) which apparently got recalculated each time values got written to the output sheet. (I found it out by removing the "writing code" and running it with autoCalc enabled - the result was about as quick as the complete code with autoCalc disabled). The funny thing is that the cells I was writing to were NOT used as inputs by any formula. Why would Excel recalculate them? (It's more of a rhetorical question.) Many thanks again, Charles! J.Smith "Charles Williams" wrote in message ... Range transfer to/from VBA array would be great. This method is reasonably fast Sub klhjok() Dim vArr As Variant application.screenupdating=False application.calculation=xlManual ' ' get range into variant containing array ' vArr = Worksheets("fred").Range("A3:L50") ' ' show an element in the arry ' MsgBox vArr(10, 10) ' ' change element in array ' vArr(10, 10) = 123456 ' ' put array back to sheet ' in different place ' Worksheets("fred").Range("A3:L50").Offset(3, 20) = vArr application.screenupdating=true application.calculation=xlAutomatic End Sub regards Charles ______________________ Decision Models The Excel Calculation Site. www.DecisionModels.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Active Cell? = Iteration / Maximum Change Issue | Excel Discussion (Misc queries) | |||
Performance Issue | Excel Discussion (Misc queries) | |||
Excel2007 Conditional Formatting Slow Performance Issue | Excel Worksheet Functions | |||
Excel Performance issue | Excel Discussion (Misc queries) | |||
Excel Viewer performance issue | Excel Discussion (Misc queries) |