Excel Complexity, Speed, Overhead and Data
As a Microsoft Office Developer, I can tell you there are some amazingly complex Excel Applications out there. For example: Did you know that new complex derivative products are often modeled, valued and perfected in Excel? That is because of the speed it can be accomplished. Provide a Quant (slang for Financial Engineer) with a powerful PC and some time and BAM you have a financial product model. Sure, once the product is adopted, hopefully perfected and there are millions of dollars that are at risk it will get coded into a more industrialized application.
Times have changed the speed and size of what was once limited memory and hard drives once advertised in Megabytes (MB) are now commonly measured in Gigabytes (GB). That’s a thousand-fold increase. Want to add a Terabyte (roughly a thousand gigabytes) of external memory to your laptop – sure that’s under $200.
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.
Raymond Mills, M.B.A., M.S. has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst. His specialty was using Excel to get Big Databases including Teradata, Oracle, Sequel Server and Sybase to give up their secrets.
Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.”
If you have a challenge with Excel, Access or Word and would like to speak with Ray,
you can get his contact details by clicking here: Contact Me