How to speed up calculation and Improve performance of Excel and VBA




TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

The key to increasing speed of calculation and improving performance of your excel worksheet and VBA code lies in understanding how Excel calculates (and re-calculates) each cell and resolves dependencies. Whenever an event occurs that causes Excel to recalculate (for example a cell is changed, a VBA code calls for re calculation or anything else), the entire calculation process is carried out roughly as follows:

1. Load workbook in memory when the workbook is first opened.

2. Track all dependencies and build an initial “calculation chain”. As a part of building this chain, it also tries to sequence all the calculations so that those cells which are the ‘least’ dependent on other cells are stacked at the beginning of the chain and the ones that are ‘most’ dependent are stacked towards the end of the chain.

3. Calculate the cells that are marked in the chain one by one, starting from the top and proceeding along to the bottom of the chain. If it tries to calculate a cell and finds that it is dependent on some other cell lower down the chain, it rearranges the chain to move that cell downwards.

As an exception to the above rule though, and if you are using version prior to Excel 2007, if the number of dependencies go beyond 65536, Excel will shift onto the ‘brute’ mode where it will force recalculate all cells. Quite often in practice, you may actually surpass that limit while working with large datasets.

Now lets look at some ways in which we can speed up the execution of excel and VBA code in our spreadsheets:

Tip #1 Isolate repeated formulae and move them to single cells

If possible, avoid the use of the same formula repeatedly in multiple cells by taking in out and using in a separate cell. You can then link all those cells which need to use the formula, to that single cell. So look at your spreadsheet, if you have a vlookup() that gets repeated over a 1000 cells and essentially all of them return the same value, take it out and put it in a single cell.

Tip #2 Reduce dependence on volatile functions
Volatile functions get calculated every time there is a change, even though their own output may not change. Some of the most frequently used volatile functions are : OFFSET(), INFO(), RAND(), NOW(), TODAY(), CELL(), INDIRECT() etc.

A point to note is that a cell will be marked for recalculation if it has a volatile function, even though you may be nested deep within a conditional formula and you expect is to be called rarely. For example, if you have a formula like =if(value1 > value2, rand(),0), the cell will be recalculated even though value1 and value2 may not change. So, wherever possible avoid using volatile functions in your spreadsheets and while writing VBA code, avoid the generous use of Application.Volatile command.

Tip #3 Try placing everything within a single worksheet.
Use references within the worksheet, then within various worksheets of the same workbook and only then to other workbooks. That will reduce the overhead on Excel and allow faster execution of calculations.

Tip #4 Nest if conditions in the order of frequency of occurrence

You may want to visit the IF() conditions and see if they are nested in the order in which they are most likely to be encountered. Nested IF() conditions should be like:
= IF ( most_likely_condition , IF(the_second_most_likely_condition , IF(the_third_most_likely_condition,….), ), )
Why, because the moment the first condition is met, the function will stop further evaluation and promptly return a value rather than having to go on checking if any of the other IF conditions are met. In some cases, an OR condition can be
Fastest finger first you see…

Tip #5 Keep the total area under reference to a minimum
Imagine for a minute that a single formula, say a simple SUM(“range”) function. Expectedly, it references a number of cells to generate an output. If each cell that it references can be given a weight of one, the total weight of the formula would be the number of cells it references. Now imagine a vlookup(). It’s total weight would be the number of cells that are specified as the lookup range in the argument. If you could sum up all the weights of all the formulae in your worksheet, you can get an idea of the amount of resources Excel will have to marshal to keep track of changes. Imagine using an entire column (sum(A:A)) as an argument. On the other hand, if you keep the area under references, like limiting the lookup range for a vlookup or a sum function, to only those many rows or columns that are ever expected to contain data, now or in the future, you’d do a world of good. I was once puzzled by the slow response of one of the dashboards that I was working on which didn’t seem to have too many formulae, only to realize that I had a single formula copied not to 1000 rows (as was needed), but to all the 65536 rows in the spreadsheet. No wonder the spreadsheet responded to my commands with utmost disdain!

Tip #6 Use simple formulae in multiple cells rather than a complicated formula in a single cell
Having said it, in some cases, this may not be advisable, especially when you utilize a large number of such complicated formulae – you may bloat out the spreadsheet and prevent others from understanding what you did once you’re gone!!! But in many other cases, especially when you have volatile functions in your complicated formula, by virtue of point 2 above, you may want to simplify things for Excel by breaking things down into smaller easily digestible pieces for it. That way, Excel may have to perform a much smaller number of calculations.

Tip #7 Use Application.ScreenUpdating in VBA
Use Application.ScreenUpdating = False at the beginning of your VBA code and end it with Application.ScreenUpdating = True. It relieves Excel of it’s duty to update the screen till the time the code is getting executed.

Tip #8 Weigh the use of VBA against using inbuilt Excel formulae
Carefully weigh using VBA code vs. using multiple formulae in excel. Sometimes the VBA code can do the job much faster, on others it can slow thing down. As rule of thumb, if you use VBA – optimize. Like any other language, reuse variables and define data and return types so that you don’t keep VBA from guessing an object’s type at run time.

Additionally if you write your custom VBA functions (also called the UDF or User Defined Function), until unless you are a coding God, there is a strong chance that your function is going to consume more system resources than a comparable in built VBA function. So weigh the trade-off between usability and performance carefully. Given that, also ensure that your own function is called last in a nested IF/OR condition.

Tip #9 Reduce Used Range
The used range can roughly be described as the area that Excel may want to run recalculations over. Now one would rarely a situation where every cell in the spreadsheet contains some value, Excel tries to run calculations only over the area which contains some value and then ignores the rest of the cells. So when resolving a very large set of dependencies, Excel tries to determine which cell may be the last one after which no other cells may have been touched or ‘dirtied’ so that it can stop its recalculations after reaching that cell. Press CTRL+END on your workbook and you will be taken to the last cell of the used range. If you find that you’ve just landed at row 50,000 and column GS, and you know that your data set does not expand that far, go back to the last cell in your data set and delete all the cells to the right and below it. Then save the workbook. That will reduce the used range and should add that extra boost.

Tip #10 Sort Final and Static Data
Often times we shut shop the moment we think we have the formulas working and VBA code running properly. But lookups and many other functions work a lot faster if they have to run on data that has been properly sorted. So before you close that worksheet, take a few seconds to sort that data if it’s not going to change further.

Tip #11 Sort worksheets
.Excel recalculates worksheets in alphabetical order. To improve performance, you can rename the sheets in your workbook in such a manner that the sheet containing only data is the first one, the one with intermediate formulae in the middle and the ones with the final presentation in the last. Use f4 or View->Properties window in VBA editor to change sheet names.

Also if Excel has to resolve dependencies using ‘brute’ force, it will start from the first cell and proceed right downwards. So if everything is in the same sheet, place data first, then intermediate formulas to the right or downwards and the final presentation towards the rightmost bottom.

Tip #12 Use conditional formatting with care

Conditional formats may seem a good choice to present data in a user friendly manner but remember that they are going to be recalculated if the input values change, just like any other formulae in the worksheet. So if you have to use conditional formatting, save it for the dessert – when showing the final result to the user. When using VBA you can use Worksheet.EnableFormatConditionsCalculation =TRUE/FALSE to temporarily turn conditional formatting On and Off.

Tip #13 When using VBA, don’t read/write cell by cell – Read/write in bulk
(Thanks for Jon for the input)
When writing VBA code, reading values cell by cell from a spreadsheet can bog down the speed of execution of the code. A faster way of carrying out processing is to simply read the entire row/column/range into memory by assigning it to a Variant object. You can then carry out operations on the variant object and once done, write it back to the spreadsheet. Here are two examples:

The Wrong Way

The RightWay

The later executes much faster, especially if you, like me, work using an old war horse of a pc :-)

And while at that, another tip – you don’t have to select cells to access and modify them them. (Range(“A1″).Select is not a necessary step to read value from that cell. (However, when you use the macro recorder, you will often see lines of codes like these.)

Tip #14 If everything fails – Increase RAM and chip power
Sometimes it is better to go back and ask for greater resources. The productivity improvement may well worth the investment.

Recommended readings in this regard are:

http://msdn.microsoft.com/en-us/library/aa730921.aspx
http://www.decisionmodels.com
http://blogs.msdn.com/excel/archive/2008/10/03/what-is-the-fastest-way-to-scan-a-large-range-in-excel.aspx
http://msdn.microsoft.com/en-us/library/aa140058.aspx
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm
http://www.mvps.org/dmcritchie/excel/slowresp.htm

TumblrStumbleUponRedditLinkedInAIMDiggEvernoteWhatsAppMySpaceKindle It

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>


Comments and Trackbacks

  1. Jon Peltier wrote:

    Good list.

    Re #5: It is my understanding that Excel looks at the intersection of the worksheet’s used range and the specified large range, and ignores cells outside the used range. However, if you have not followed #9, you still impose a huge penalty.

    In addition to #7, Application.ScreenUpdating, you should also change calculation to manual, run the code, then turn it back to automatic at the end. This saves you all of the intermediate recalcs.

    Re #8 and using Integers instead of Longs (and Singles instead of Doubles), these are fallacies. VB converts to the larger capacity type, does its work, and converts back. Any overflow occurs not during calculation but during the final conversion.

    Another thing you should list before “If all else fails” is proper transfer of data from Excel to VBA and back. Don’t read and write one cell at a time. That one access operation per range is probably the slowest part of the VBA code. Read the entire range you need in one step into a VB array, process the data, and dump a new array into the worksheet.

  2. Ajay wrote:

    Jon
    I would think that turning manual update on at the beginning of the code and turning it off at the end of the code may not always be a good idea. If the code needs to read recalculated intermediate values, the results may not come out as we would expect. Though I agree with you in that it may pay rich dividends to turn it on and off to isolate pieces of code that do not need to reference intermediate values. That way, if needed, after the executing of an isolated piece of code, one can always force a Application.Calculate to force a calculation.

    Point 8 has been duly corrected. I tested the following function using both the integer and the long data types :

    Sub try()
    Dim i As Integer
    Dim t As Integer
    Dim s As Long
    s = Timer
    For t = 1 To 4000
    For i = 1 To 32000
    i = i + 1
    Next i
    i = 1
    Next
    MsgBox Timer – s
    End Sub

    and to my surprise the code executed much faster using the long data type. However, in most programming languages, its better not to use objects with a larger scope than necessary. However evidence points out to that not being the case with the VBA integer data type.

    (As a request – would you have access to any specific references on the www that deal with this topic.)

  3. serdarb wrote:

    very nice post…

  4. pravin wrote:

    Sub try()
    Dim i As Integer
    Dim t As Integer
    Dim s As Long
    s = Timer
    For t = 1 To 4000
    For i = 1 To 32000
    i = i + 1
    Next i
    i = 1
    Next
    MsgBox Timer – s
    End Sub

  5. Sebastien wrote:

    I’m interested in # 13 solution but I’m wondering if you can apply the to a 2 dimensional range? and how big of a data range you can use in the temp range?

  6. sunil jadhav wrote:

    I found my technical problems……! I will try to improve my sheets as per d suggestions! Thanks!

  7. benishiryo wrote:

    hi there Ajay. i’ve been trying to research on whole column recently. i found this microsoft source that says:
    “For functions like SUM, SUMIF, and SUMIFS that handle ranges, the calculation time is proportional to the number of used cells you are summing or counting. Unused cells are not examined, so whole column references are relatively efficient, but it is better to ensure you do not include more used cells than you need.”

    http://msdn.microsoft.com/en-us/library/office/aa730921(v=office.12).aspx

    Jon Peltier also commented here regarding point 5 & 9 with a similar context, but you didn’t respond. would you care to give your views?

  8. Fergal wrote:

    great article – thanks a lot for that, I”m pretty sure it’s going to be a great help in optimisation of my Excel custom utility

  1. da TaB is On » Excel VBA For Loop, Do While Loop and Do Until Loop wrote:

    [...] it to utilize all the resources towards the execution of the code. You may also want to my post on how to speed up and improve the execution of vba code in excel. Related ArticlesExcel Waterfall Chart Template with Negative ValuesData Forms in ExcelData [...]

  2. da TaB is On » How to Create a Dashboard in Excel wrote:

    [...] way can help improve speed of calculation of formulas in your Excel dashboard. To know more about ways to improve the speed of calculation in excel read this [...]

  3. Make excel calculate faster - Quick tip | da TaB is On wrote:

    [...] that’s it. Read more about speeding up calculation and performance of you excel spreadsheets here. A Few More Interesting ArticlesRetirement Planner in ExcelExcel Chart Tip – Insert Chart [...]

  4. Excel Macro | da TaB is On wrote:

    [...] That would take some time. If the macro that you wrote runs into hundreds of lines of code, you can improve the speed of execution by cutting down on all the other processes that are not required when the code is [...]

  5. Is There a Dinosaur Sitting on Your Excel File? | SukhbinderSingh.com wrote:

    [...] How to speed up calculation and Improve performance of Excel and VBA These are excellent tips and must visit to know more about the calculations steps followed by Excel. Share this:ShareTwitterLinkedInFacebookGoogleEmailPrintLike this:Like Loading… [...]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagDutch flagHindi flagPolish flagSwedish flagFilipino flagHebrew flagIndonesian flagUkrainian flagThai flagTurkish flag
treeemap software for excel