Hard drives are mechanical devises and are therefore limited in speed of throughput (The transfer of data into working RAM.) Remember that 20 MG Derivatives spreadsheet? well, back then open excel and click on the file and it took over a minute for it to load. Today a standard SSD can read sequential data at a speed of about 550 megabytes per second (Mbps) and write it at 520 Mbps. In contrast, a fast HDD may carry out sequential reads and writes at just 125Mbps. That’s 4 times faster but the reality is SSDs are far faster. See Paul Ruben’s Article Here.
The result of this blazing speed and nearly unlimited memory is that applications like Excel are more capable than ever. Excel Spreadsheets Complexity, Speed, Overhead and Data have all grown massively. There has been another change: User impatience and intolerance for wait times. First let me say I am admittedly guilty of losing all tolerance of long load/Save times. I bought the 128 SSD laptop I am typing on and it is as I say Instant on and instant off.
So, what does all this mean to an Excel VBA Developer? Avoiding what I call Overhead. The best way for me to describe Overhead is through a recent experience I had with a Client. The Client’s dashboard was a work of art but, It was highly dependent on thousands of VLOOKUP’s. Each little VLOOKUP is overhead because when the workbook recalculates, it goes through each formula and recalculates using precious time and that happens when the workbook is open. Oh, you say that’s easy I’ll just turn recalculation to manual from automatic of I’ll recalculate only sections. I can attest that in a world where distribution of PDFs of select portions of a dashboard is the norm, there is a real danger in messing with recalculation settings that you could send out data not updated. Further, the client’s workbook had a combo box of projects. The change event on that combo box updated the rather complex stacked graphs dashboard. All of which took time to update. Initially that combo box was loaded when the sheet was activated but that proved too slow for the Client, so I changed it to a double click on the combo box. I changed another combo box to a data validation list which seem to have minimal overhead and was simpler for the Client.
Tips for elimination overhead
1. Question the use of excessive Open event routines. Must they run every time the spreadsheet or worksheets are open.
2. Like a surgeon that is “scalpel happy”, For a developer there is a temptation to add code as a solution. Ask yourself is code the simplest and best approach?
3. VLOOKUPS are often a sign that the spreadsheet is crying out for some automation but do so judiciously. Ask the question will this help with overhead?
4. Look for VLOOKUPS that are duplicated, would a “=” to a value already ‘looked up’ work better?
You can code the best Excel application, in the world but if its slow it may never be appreciated. Success is as much about perception as it is in delivering a product that works.
If you would like to say hello and Buy me a cup of coffee please follow the link.
