Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 968
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Active Cell? = Iteration / Maximum Change Issue Mark_S Excel Discussion (Misc queries) 0 November 23rd 10 02:09 AM
Performance Issue KS Excel Discussion (Misc queries) 1 March 22nd 10 06:06 PM
Excel2007 Conditional Formatting Slow Performance Issue Michael F Excel Worksheet Functions 1 June 26th 09 05:42 PM
Excel Performance issue Sandy Excel Discussion (Misc queries) 0 September 14th 05 01:50 PM
Excel Viewer performance issue Macca101 Excel Discussion (Misc queries) 0 April 28th 05 12:09 PM


All times are GMT +1. The time now is 12:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"