Search code examples
excelperformancedatabase-performanceunresponsive-progressbar

Working with huge Excel content - presetting dependency chain

Am trying to build a large excel solution. The Excel file (200MB xlsb) has a sheet with 300,000 rows X 100 columns, containing formulae with long chain of dependencies.

Have incorporated most Excel performance tips. No a single volatile function. No array formulae. Eliminated forward references (cell formulae always refer only to previous rows and/or cells on the left in same row and/or sheets prior in alphabetic order, no external links). Am using 64-bit Excel 2013 on 2.2GHz 4GB system, have disabled AutoSave (huge file), disabled multi-threading (single dependency chain). For those wondering, used optimized VBA code to create 30 million formulae of excel code.

The file takes some minutes to open. Smart recalc (F9) works great <1s on small changes. Full Calculation using (Ctrl+Alt+F9) takes 30s. Full Calculation Rebuild using (Shft+Ctrl+Alt+F9) takes 3mins to build dependency & calculation sequence (noticed as cursor busy), and 30s for Calculation (status bar shows Calculating). File close with/without Save takes longer than File Open. Deleting all these formulae takes forever (cancelled after >1hr). AutoFilter is extremely slow (file unresponsive). Many other operations have become slow.

  1. Can we tell Excel to not bother identifying dependency tree and work left-to-right top-to-bottom alphabetic worksheets.
  2. Given Calc speed is already optimized, Are there additional ways to improve Response Rates for excel operations like Autofilter, insert/delete row/col, etc.

To replicate: Range("B2:CW300001").formula="=A2+len(F1)"

Any guidance will be greatly helpful.

Solution

  • I finally found that, yes, dependency chain can be preprogrammed, by self-creating calcChain.xml in the xml of excel file. However, Excel will automatically modify it if the excel file creates a backward dependency in the chain, or Ctrl+Alt+Shift+F9 is called. http://phincampbell.com/Improving%20Excel's%20Calculation%20Performance%20using%20Calculation%20Chain%20and%20Dependency%20Tree%20Data%20from%20calcTree.xml.html