3

I'm working with a rather large Excel document (~9MB) in size which has over 60 sheets and each containing many CUBEVALUE formulas in it.

This document takes over 2 minutes to open (not counting refreshing of values) and while i have read many recommendations e.g:

  • splitting of worksheets (not possible due to the nature of this file)
  • shorter formulas, (not possible)
  • tested on both 32 and 64 bit (performance is not notably different)

I was wondering if you guys came across any ways of optimising opening time for Excel without significantly altering the contents within in it, or any further suggestions.

Erika
  • 2,045
  • 7
  • 25
  • 31
  • 1
    I think it's cute that you think 9Mbs is '*rather large*'! –  Apr 11 '17 at 12:49
  • 1
    I had the same problem once and did a lot of research in that respect. Essentially it comes down to this: doing everything in bulks is much (!!) more efficient and faster. So, you'd have to think about all of your data requirements in Excel, pull all of that data in one GO from the server (into a separate sheet) and then pull the data from there using some Excel built-in formulas like this: http://stackoverflow.com/questions/42695455/control-database-form-excel-through-vba/42720826#42720826 The alternative is to do everything with VBA. There is nothing faster than that. But the output is fix. – Ralph Apr 11 '17 at 12:55

2 Answers2

3

  1. Save it as an Excel Binary Workbook (.xlsb). You can retain macros, the filesize will be 25-35% of the original and many operations (not just opening/closing) will be faster.

  2. Get rid of any volatile functions that are recalculating the worksheet unnecessarily. INDIRECT, OFFSET, ADDRESS, TODAY and NOW are among the list of volatile functions. Most can be replaced with non-volatile alternatives.

  3. Improve the remaining calculation of the workbook by making worksheet formulas and functions more efficient. Help on this is available at Code Review - Excel, a StackExchange partner. No examples supplied so no specific help offered.

  4. Improve any sub procedure cod run times at the same site. Large blocks should be processed 'in-memory' with arrays, not looped through cell-by-cell, etc. Again, no examples supplied so no specific help offered.

Community
  • 1
  • 1
  • 1
    Thank you so much for this! XLSB made a massive difference: 30% smaller file size, 1/3rd of the previous opening time. – Erika Apr 11 '17 at 14:56
1

If you use corporate network try first downloading the file to you local computer and then opening.

It may also depend on existence of links to other files, try to reduce their number to minimum if there are any.

Nontheless, the volume of data in your file - is the main driver of opening time.

Artem
  • 32
  • 5