excel vba performance improvement | Excel & VBA

Excel Slow to Respond – Avoiding Mistakes that Make Excel Slow Down to a Crawl

Everyone hates it when excel becomes slow to respond. So here are some tips to avoid doing things that can slow down excel. Excel 2007 and later versions sport a much bigger grid which tends to amplify poor spreadsheet design. An Excel spreadsheet that seemed a bit slow in Excel 2000 would likely get bogged down to a crawl in Excel 2007 if you, like me, are using the same hardware. The key to understanding ...

Make Mouse Scroll Wheel Work in VBA Editor (IDE)

Posted on 14 July 2009

This ranks right at the top of annoyances when it comes to working with the VBA IDE - the mouse scroll wheel simply does not work. So if you have more lines of code than can fit into a single screen in the VBA IDE, you end up having to drag the scroll bar to up/down to navigate. Jimmy Peña recently posted this very useful VBA tip on his informative blog that provides the solution to this issue. ...

Comments (13)

Fix Runtime Error in VBA

Posted on 24 June 2009

To fix runtime errors is VBA, use the following three statements - On Error, Resume and Debug. The first one traps a runtime VBA error when it occurs, the second one tells the application the point from which to resume code execution when the error has been caught (if you decide to catch the error) and the third one informs the user where (and which) runtime error took place. A runtime Error in VBA can occur for a plethora of reasons. ...

Comments (7)

Make excel calculate faster – Quick tip

Posted on 09 June 2009

If you want to make your large excel workbook calculate faster and have about 10 seconds of time on your hands, use this trick. 1. Open the workbook and select the spreadsheet with the largest number of formulas (or data). 2. While keeping the 'Ctrl' key pressed, press 'End'. 3. Check the cell address that the cursor has moved to. 4. If the cursor is well beyond the last cell that had a formula ...

Comments

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

Posted on 23 November 2008

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 ...

Comments (20)