Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Direction of looping through objects
I've got a For loop cycling through all the selected cells and
manually calculating them. However, it always calculates them by row and it calculates much faster if going down the way but I don't want to select each column and run my macro individually. Any ideas for making the for loop move to the next cell down as it's looping through the selected cells? Sub CalcRange() Dim nocells, currcell As Double Dim Cell As Object ActiveSheet.Activate nocells = Selection.Cells.count currcell = 0 For Each Cell In Selection currcell = currcell + 1 Application.StatusBar = Int(currcell * 100 / nocells) & "% complete" Cell.Calculate Next Cell Application.StatusBar = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Direction of looping through objects
Generally a for each loop as you have is the most efficient. However I've
also noticed in some scenarios it's faster to loop by columns. Following is untested aircode, just for ideas Dim rng as Range, rC as range, rR as range, cell as range dim i as long, nRwsCnt as long Set rng = Selection For each rC in rng.Columns For each cell in rC.Cells cell.calculate occasionally by index can be faster nRwsCnt = rng.Rows.Count For each rC in rng.Columns for i = 1 to nRwsCnt rC(i).calculate Chances are updating statusbar in iach loop takes up more time than the actual process. For acuracy of about 1/18 sec's compare times with dim t as double t = Timer ' do stuff t = Timer - t debug.? t Curiosity, why calculate every cell in a loop rather than say the worksheet Regards, Peter T "Erasmus" wrote in message ... I've got a For loop cycling through all the selected cells and manually calculating them. However, it always calculates them by row and it calculates much faster if going down the way but I don't want to select each column and run my macro individually. Any ideas for making the for loop move to the next cell down as it's looping through the selected cells? Sub CalcRange() Dim nocells, currcell As Double Dim Cell As Object ActiveSheet.Activate nocells = Selection.Cells.count currcell = 0 For Each Cell In Selection currcell = currcell + 1 Application.StatusBar = Int(currcell * 100 / nocells) & "% complete" Cell.Calculate Next Cell Application.StatusBar = False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Direction of looping through objects
On Jul 29, 12:18*pm, "Peter T" <peter-t@discussions wrote:
Generally a for each loop as you have is the most efficient. However I've also noticed in some scenarios it's faster to loop by columns. Following is untested aircode, just for ideas Dim rng as Range, rC as range, rR as range, cell as range dim i as long, nRwsCnt as long Set rng = Selection For each rC in rng.Columns For each cell in rC.Cells cell.calculate occasionally by index can be faster nRwsCnt = rng.Rows.Count For each rC in rng.Columns for i = 1 to nRwsCnt rC(i).calculate Chances are updating statusbar in iach loop takes up more time than the actual process. *For acuracy of about 1/18 sec's compare times with dim t as double t = Timer ' do stuff t = Timer - t debug.? t Curiosity, why calculate every cell in a loop rather than say the worksheet Regards, Peter T "Erasmus" wrote in message ... I've got a For loop cycling through all the selected cells and manually calculating them. However, it always calculates them by row and it calculates much faster if going down the way but I don't want to select each column and run my macro individually. Any ideas for making the for loop move to the next cell down as it's looping through the selected cells? Sub CalcRange() * *Dim nocells, currcell As Double * *Dim Cell As Object * *ActiveSheet.Activate * *nocells = Selection.Cells.count * *currcell = 0 * *For Each Cell In Selection * * * *currcell = currcell + 1 * * * *Application.StatusBar = Int(currcell * 100 / nocells) & "% complete" * * * *Cell.Calculate * *Next Cell * *Application.StatusBar = False End Sub- Hide quoted text - - Show quoted text - I'm pulling in results from stochastic runs which are stored in large dbf files. It pulls in each column(variable) seperately over time periods going down for 40 to 50 years (at each month this is up to 600 rows worth). The way it works when you open the dbf file, you're much faster pulling in the time periods for one variable in one file than flipping between a few files, getting all variables at time 0 and then moving on to time 1. If I can set it to calculate the column, it should speed up calculations from 20-30 minutes down to within 5. I'm off to lunch but will try this after - thanks for that, does look like it should work. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Direction of looping through objects
Peter,
Thanks, got that working fine - although where does the debug thing show up? I use progress bar to get a rough idea as these sheets take so long to calculate anyway. If we don't know where they're at, half the time we crash the macro (or completely shut excel if it's just calculating the whole sheet). In answer to your question tho - there may be sections that we want to pull through without having to wait ages for the rest of the sheet to update. As an idea, the sheet I'm currently pulling in (one of a few in this wb & many wb's) has 31,325 numbers to pull in from 4 temporary files. And the calculations are pretty slow as it's opening dbf files to find the results we need. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Direction of looping through objects
"Erasmus" wrote in message
Thanks, got that working fine - although where does the debug thing show up? On the VBE's main menu, View, Immediate Window, or simply Ctrl-g I use progress bar to get a rough idea as these sheets take so long to calculate anyway. Fair enough If we don't know where they're at, half the time we crash the macro (or completely shut excel if it's just calculating the whole sheet). That can't be right at all In answer to your question tho - there may be sections that we want to pull through without having to wait ages for the rest of the sheet to update. As an idea, the sheet I'm currently pulling in (one of a few in this wb & many wb's) has 31,325 numbers to pull in from 4 temporary files. And the calculations are pretty slow as it's opening dbf files to find the results we need. Wouldn't Automatic calculation work OK for your needs. Regards, Peter T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Direction of looping through objects
On Jul 29, 2:59*pm, "Peter T" <peter-t@discussions wrote:
"Erasmus" wrote in message Thanks, got that working fine - although where does the debug thing show up? On the VBE's main menu, View, Immediate Window, or simply Ctrl-g I use progress bar to get a rough idea as these sheets take so long to calculate anyway. Fair enough If we don't know where they're at, half the time we crash the macro (or completely shut excel if it's just calculating the whole sheet). That can't be right at all In answer to your question tho - there may be sections that we want to pull through without having to wait ages for the rest of the sheet to update. As an idea, the sheet I'm currently pulling in (one of a few in this wb & many wb's) has 31,325 numbers to pull in from 4 temporary files. And the calculations are pretty slow as it's opening dbf files to find the results we need. Wouldn't Automatic calculation work OK for your needs. Regards, Peter T Sadly automatic calculation would render our remote / desktop pc's useless for about an hour after opening any spreadsheet. It's just a lot of data - pulled in using an add-in (effectively just a macro spreadsheet) provided by another company and each cell is just really slow pulling in results from database files and calculating them. To be fair tho, the smallest database file we use for results is around 100MB, the largest somewhere between 2 and 3 GB. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel VBA Class Objects - Parent & Successor Objects | Excel Programming | |||
Looping Thru Objects in UserForms | Excel Programming | |||
Unable to remove Sheet objects in the Microsoft Excel Objects | Excel Programming | |||
looping and objects | Excel Programming | |||
Direction for looping through a Selection. | Excel Programming |