Disable all calculations on some sheets, while allowing all calculations on others.

Bishko

New Member
Joined
Oct 14, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Hi everyone,

I've got the following problem:
I have an excel file (about 350MB), which as a total number of 72 sheets. Of those 72 sheets, about 30 of them are "input data" or to be more precise results from a structural analysis software. Each fo these sheets has about 12000 rows and 10 columns, which are "raw data". The next 30 sheets are combination sheets, where the "raw data" gets combined according to some relatively simple formulae. The last 12 sheets or so are just graphs visualising this information, so nothing special.

However, the excel is MASSIVE!!! It takes about 5 minutes to open, 10 minutes to save, loads about 5GB on to system memory and takes ages to process just one calculation because of all this information. Worst of all, the strucural model changes about once or twice a week, meaning the results have to re-imported into the "input-data" sheets, processed by the "combination sheets" and interpreted after that.

So, to speed things up, I would like to disable calculations completely on some sheets and leave it running on others (simulating a sort of "paste values only" situation), where excel only takes the last known value and does not recalcualte it at all.

After opening VBA and clicking on one of the sheets, I was able to find a property called "EnableCalculation" which was set to "True". I'm not sure if this is the right way, but it is poissible to create a button or a switch, which will let me change this from "True" to "False" and vice versa, as I need?

1602693242621.png


Sorry, I know I'm still a nood at VBA, but I'm trying to learn :))


Thank you in advance for your help!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
whats the file extension ?
 

Bishko

New Member
Joined
Oct 14, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Hi mole999,

I'm currently using both a .xlsx and .xlsm file extension. I have saved the file twice basically...
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Hi mole999,

I'm currently using both a .xlsx and .xlsm file extension. I have saved the file twice basically...
save it as xlsb and look at file size
 

Watch MrExcel Video

Forum statistics

Threads
1,118,265
Messages
5,571,216
Members
412,368
Latest member
saranbl
Top