Optimization

Does Excel need optimization ?

Generally speaking no. The normal use of excel with a few sheets and straightforward formulas does not need any optimization. However, sometimes workbooks grow, especially if you try to have all related data in the same place. Also when you start creating User defined functions without care to whats good and whats bad, you can end up with long run times.

Workbook analyzer

Excel re-calculates any dependent formulas whenever you change a cell automatically by default. Once the recalculation times start to get high, there is a temptation to turn recalculation to manual. When this happens it leads to mistakes and sometimes wasted effort in trackin down problem with formulas that aren't really there. 

It's to deal with this - the identification of which areas of spreadsheet need optimization - that I developed the Workbook Analyzer.

Procedure profiler

Identifying choke points in  your user defined functions can be time consuming and frustrating. To help you profile what's taking time to process, you can use the procedure profiler.

Automatic code profiling

For a really quick start, you can insert and remove profiling code automatically using the automatic profiler

Advanced profiling

These capabilities introduce data structures that you can access for a really deep dive into your code. Take a look at some examples at Advanced profiling: Rogue Use Cases and the new classes available to you cProcProfiler and cProcTimer.

Downloads

These are available at the download page

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'ReillyAmazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.


For help and more information join our forumfollow the blogfollow me on twitter
Subpages (2): Countdown timer proctimer