Wednesday, May 25, 2005

Speeding up VBA macros in Excel

Here are some simple methods to speed up how fast macros run.

Application.ScreenUpdating = True
Use this command at the beginning of a macro with True and at the end as False. When the macro runs the screen will stay static. The pc can run the macro faster as it doesn't have to refresh the screen as the macro changes sheets, workbooks or recalculates.

Application.DisplayAlerts = True
Use this command when the macro you are running will require user input that you need the default answer to. Eg if you are saving a file using the ActiveWorkbook.SaveAs command and the file already exists. This means the user won't have to react to the beep and the onscreen dialog box thus shortening the total running time.

Use Arrays instead of operating directly on the spreadsheet.
If you need to manipulate a set of data on a sheet, it is often better to select that data as an array in memory, use if then and for next and do while loop statements to process the information, then when it is complete paste it back into the spreadsheet. On small data sets, say a couple of thousand cells this won't make a hugely noticeable difference, but on a sheet with tens of thousands of cells this will make a huge difference.

References to previous array posts


Posted by: Jethro | 10:30 PM |