VBA to Switch Calcualtion from Manual to Automatic

Hi, Is it possible to have VBA to switch Calculation from Manual to Automatic once the workbook is completely opened and Switch from Automatic to Manual when click to the close button.

Thank you in advance.

In the Workbook_Open module :

​	Application.Calculation=xlManual

Click to expand...

in the Workbook_BeforeClose module :

Application.Calculation=xlAutomatic

Click to expand...

Hi Logit i have tried the same but its not making any impact while workbook open and close and while opening the calculation process in task bar shows Calculating (4 Processors) and which is taking a long time to open and make it stable to use.

My workbook size is 6.5 Megabites which takes a long time to open and i am looking for while opening the workbook calculation should be manual and once the workbook completely opened and ready to use then it should switch from Manual to Automatic and when i click on close the calculation process should be switch from Automatic to Manual.

I do not know what to do. I have tried all the ways to make it fast and reduce the size but unsuccessful.

.
Unless your workbook is truly a LARGE project, it sounds like maybe your workbook has a ton of "residual data" that is unseen but residing in what would appear to you as empty cells.

MAKE A COPY OF YOUR WORKBOOK AND PERFORM THE FOLLOWING :

On each sheet, go to the last row where data is visible.

Select the next row below that, which should appear empty. Select the entire row by clicking on the row number on the left side.

Now, simultaneously press CTRL / SHIFT / DOWN ARROW. This will auto-select all the other blank rows down to the bottom of the sheet.

Right click in the greyed area ... select DELETE.

That clears any unseen data from those 'blank cells'.

Repeat the same process for each sheet in your workbook. Then check the size of the workbook again.


If that cleared up the problem, either rename the copy workbook, or repeat the same process on the original.

If that doesn't clear up the problem, we'll have to look at something else to try.

Setting calculation to manual in workbook_open won't help as it's too late - the recalculation will already have occurred, if necessary. You need to save the workbook with calculation set to manual and make sure it's the first workbook yo uopen, or set calc to manual before you open it.

I feel JoePublic you are right. I did everything as suggested by Logit but the calculation process still shows when opening the workbook Calculating (4 processors) which is taking more than a two minutes to get workbook open and ready to use.

I completely fed up. How would i proceed with so slow workbook for the presentation.

Set calculation to manual before you open the workbook.