"Block Calculate" in XL2K - Possible?

[MeGustaXL]

I've got a couple of columns similar to this:

[b]Name         Formula[/b]
Thing1      =IF(Y IN Month,1,0)
Thing2      =IF(Y IN Month,1,0)
Thing3      =IF(Y IN Month,1,0)
Thing4      =IF(Y IN Month,1,0)
.....etc
Thing15     =IF(Y IN Month,1,0)

And a macro which does something like:

For N = 1 to Z
    Calculate
    [green]'Do things with the results[/green]
Next N

So far, so simple and dandy. The problem comes when the number of "Things" being Calculated grows large, say more than fifty. The macro becomes Veeeerrrrrryy Sslooooowww! Obviously, this is because the Calculate has to ripple down through all the "Things" in the list, as well as any other formula cells on the sheet.

Is there some alternate syntax or method I could, say, select all the Thing Formulas and kinda "Flash" calculate them, like a parallel register when strobed?

ANy ideas gratefully received



Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[PHV]

You may try something like this in the macro:
Application.ScreenUpdating = False
Activesheet.EnableCalculation = False
' your stuff here
Activesheet.EnableCalculation = True
Application.ScreenUpdating = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MeGustaXL]

Hi PH, and Thanks for the rapid response!

I've already got ScreenUpdating turned off elsewhere, and calculation set to manual, with iterations allowed but set to 1, because there are deliberate circular references in some of the cells.

What does "Activesheet.EnableCalculation = False" do?


Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[MeGustaXL]

Hmmm.. Not sure about that!

I did this:

[blue]Activesheet.EnableCalculation = False[/blue]
For N = 1 to Z
    Calculate
    'Do things with the results
Next N
[blue]Activesheet.EnableCalculation = True[/blue]

And nothing happens. Zip. Nada. Zilch. Nowt. Nichts.


Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[MeGustaXL]

But it is lightning quick!! Hee-Hee! [ROFLMAO]

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[MeGustaXL]

Had a look in the VBA Help - Gasp!! and found that I could do this:

Worksheets(1).Rows(2).Calculate

so I named the range "DatAble", and put this in the code:

Blah, Blah..
[DatAble].Calculate
More Blah..

And it seems a teensy bit quicker, but not so I'd give it a medal.

Unless anyone has a turbo-charged method, I think I'll stick with this for now.

Thanks for being there



Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[xlbo]

If you have to recalc for each iteration, I'm afraid it's gonna be slow as it will be a function of how long the formulae themselves take to recalc - no real way of speeding it up unless (as PHV suggested) you can do all your calcs in 1 go at the end....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MeGustaXL]

Hi Geoff,

Alas, I need to tabulate and plot the results of each iteration.

There are a load of IF..THEN cells over the sheet which test various other calculation results too, and these are Pivoted and copied onto tables in other sheets.

For N = 1 to Z
    Calculate
    'Do things with the results
Next N

there are 14 "Things done with the results", and Z could be up to 32,000 iterations.

I guess I'll get a supply of good books in to read while it runs! (Maybe even an Excel Programming book or two )

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[MeGustaXL]

*CLUNK* of penny dropping!
Now I understand why PHV gave me code to stop the spreadsheet calculating - it's because the thread title says "Block Calculation..."

No, No, No, I meant "Do the calculations all at once, in a Block

Now I see why PHV's method did

Zip. Nada. Zilch. Nowt. Nichts.

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin

 

[xlbo]

lol - 'fraid it's gonna be reading time then !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question