Direction of looping through objects

[Erasmus]

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

 

[Peter T]

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]

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










- 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.

 

[Erasmus]

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.

 

[Peter T]

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

 

[Erasmus]

On the VBE's main menu, View, Immediate Window, or simply Ctrl-g


Fair enough


That can't be right at all


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.