Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"Block Calculate" in XL2K - Possible?

Status
Not open for further replies.

MeGustaXL

Technical User
Aug 6, 2003
1,055
GB
I've got a couple of columns similar to this:
Code:
[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:
Code:
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 [thumbsup2]



Chris

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

 
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
 
Hi PH, and Thanks for the rapid response! [smile]

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

 
Hmmm.. Not sure about that!

I did this:
Code:
[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

 
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

 
Had a look in the VBA Help - Gasp!! [surprise] 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 [cheers]



Chris

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

 
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
 
Hi Geoff,

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

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.

Code:
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 [wink] )

Chris

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

 
*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..." [lol]

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.
[bigsmile]


Chris

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

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top