Is it possible to recalculate a single workbook?

[pinkfloydfan]

Hi all

I appreciate that you could recalculate each worksheet within a
workbook separately but unless you knew the correct order of dependency
then some results would be wrong when doing this.

So, is it possible to recalculate (with correct dependency) a single
workbook?

Thanks

Lloyd

 

[WhytheQ]

....according to the online help it doesn't look too promising! (could
you loop through the worksheets a few times?....I'm not sure if this
would mean that knowing the dependency isn't so important)
Rgds
J



Calculate Method
See AlsoApplies ToExampleSpecificsCalculates all open workbooks, a
specific worksheet in a workbook, or a specified range of cells on a
worksheet, as shown in the following table.

To calculate Follow this example
All open workbooks Application.Calculate (or just Calculate)
A specific worksheet Worksheets(1).Calculate
A specified range Worksheets(1).Rows(2).Calculate

expression.Calculate
expression Optional for Application, required for Worksheet and
Range. An expression that returns an object in the Applies To list.

Example
This example calculates the formulas in columns A, B, and C in the used
range on Sheet1.

Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate

 

[pinkfloydfan]

Unfortunately I don't think that will work. To give some more specific
information, I have done the following:

1) Written a control workbook that builds an object from specified
inputs
2) I have an xla that creates those objects and has other functions to
manipulate the data in a specific object and produce certain results
3) In a second Workbook I use those functions to give me the
information I am seeking...but this workbook has a number of
interlinked worksheets so that the dependencies are reasonably
complicated.

I would like to be able to change tweak the inputs that build the
object point-by-point, rebuild the object and see what the change in
result is on the second workbook and then output that list of data.

I suppose that I could close all other workbooks, then run the Tweak
macro, then reopen the closed workbooks but that seems a bit clumsy to
me.

Does anyone have a better solution please?

 

[Guest]

Set calculation to manual, then try:

Activeworkbook.Calculate

 

[pinkfloydfan]

A good idea and one I originally tried BUT Calculate is not a method of
ActiveWorkbook or ThisWorkbook

Thanks
Lloyd

 

[Guest]

Guess your right - my error. Looks like you are back at application, or
calculating each sheet twice or more. Not sure what you are gaining.

you can look at
http://www.decisionmodels.com which might give you some ideas

 

[pinkfloydfan]

Thanks Tom, that website actually solved my problem in this way:

I inserted the following code before the rest of the Tweak macro:

For Each sh In Workbooks("Setup.xls").Sheets
sh.EnableCalculation = False
Next

And this at the end of it:

For Each sh In Workbooks("Setup.xls").Sheets
sh.EnableCalculation = True
Next

The result is that any recalculations do not impact the worksheets in
the Setup.xls workbook. You have to turn it back on otherwise the
sheets won't recalculate at all. What's also nice is that this setting
is not saved with the workbook

Cheers
Lloyd