First post here, hope I document this appropriately.
I have a large workbook that utilizes 20 or so dynamic ranges. When I change a value in this workbook it calculates pretty slowly. While dynamic ranges are important for this workbook, I've noticed that they add significant calculation time to the workbook. For example just typing a value in any cell that doesn't affect downstream calculations still has lag due to calculations. I'm almost certain this is because dynamic ranges are volatile, so every time any cells is activated, the workbook calculates to make sure the dynamic ranges haven't changed.
I've attached 2 workbooks, one using dynamic vs one using static ranges. The "Data to be populated" tab has a button that runs some code that changes some input cells to trigger calculations. The tab then captures the calculation time info for a full calculation (Ctrl+Alt+F9), Recalculate (F9), and each sheet calculation time (test for yourself if you want). These 2 workbooks are identical except one uses dynamic ranges and the other static. The dynamic workbook has a higher volatility ratio as well as significantly higher calc time, almost 5x slower. You can easily see the difference if you have the calculation set to automatic and just type random numbers in any cell in both workbooks. While they both are always recalculating, the one with dynamic ranges has a noticeable lag while the static range file seems instantaneous.
These workbooks are a base example of my much larger workbook. I generally only load the tables initially when I open the files (ie table 1 and 2 get loaded) but possibly could adjust them while in the file. I'm wondering if others have had this issue and what their proposed solution is? These are my ideas:
- Best Option - make the "Tables" sheet, which host the dynamic ranges, not calculate after being populated. This way the dynamic ranges are not constantly being calculated to check if the range changes. So in essence the dynamic range is calculated once, then it acts the same as static ranges going forward. Only if you go in that sheet and change the tables should the dynamic range recalculate. I've tried setting these sheets to disable calculation in the VB properties for the dynamic range sheets and this didn't work.
- Workable but not ideal Option - have VB code that makes dynamic ranges, calculates them, then converts them to static ranges. Somewhat problematic if changing tables more than once.
I've been investigating this for weeks to figure out what is slowing down the workbook. Now that I've found the problem, I'm hoping someone much smarter than I has an answer. I appreciate any help.
Cheers,
Grant
Bookmarks