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