Calculate Selective Workbook

[mavest]

When a customer has several workbooks open, one being mine. I would like to force my entire workbook to recalculate without causing the customer's other workbooks to recalculate.

Unfortunately, toggling between
Excel.Application.Calculation = xlCalculationAutomatic
and
Excel.Application.Calculation = xlCalculationManual
causes all workbooks to recalculate.

I found that I can use
Worksheets("myWSht&quot.calculate
and scroll through my worksheets, but this is tedious. Also for other reasons (I hope to emulate the VBA technique in REXX a language of dinosaurs.) I would not like to do this.

Does anyone know how to force a single workbook to recalculate without causing other workbooks to recalculate?? Thanks! for your help!


-Mike

 

[xlbo]

sorry - calculation doesn't seem to apply to workbooks
You could use

For each ws in thisworkbook.worksheets
ws.calculate
next

or SHIFT+F9 will manually calc only the active worksheet...

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[VBAjedi]

I don't know a "silver bullet" command, but here's an efficient way to calculate each sheet:

Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
   sh.Calculate
Next sh

Let me know if that helps!


VBAjedi

 

[VBAjedi]

xlbo, you beat me to the draw!





VBAjedi

 

[mavest]

You may be correct that calculation does not apply to workbooks. It would save me alot of time if it did, because I will be programming this macor in REXX and not in VBA. REXX is alot more limited and I don't know how to return a list of strings or handles on the worksheets.

Also there is the problem that worksheets are often interconnected, so, recalculating each sheet once will not give you the same numberical values as F9.

Thanks!


-Mike

 

[RobBroekhuis]

Can you open the workbook in question in a separate instance of Excel, so that you don't have to worry about other workbooks the user may have open?


Rob