You can prevent Excel from recalculating the workbook by using the statement: An individual item of a collection object may be accessed by either its name or by its index into the collection.
For example, if you have three worksheets ("Sheet1", "Sheet2", and "Sheet3") in a workbook ("My Workbook"), you can reference "Sheet2" with either Whenever you can, declare values as constants, rather than variables.
The following macro lines will, respectively, turn off screen updating and then turn it back on in a VBA macro.
Normally, Excel will recalculate a cell or a range of cells when that cell's or range's precedents have changed.
This may cause your workbook to recalculate too often, which will slow down performance.
If you're going to work with another application, such as Word, declare your OLE object directly, rather than as an Object type variable.
By doing so, a great deal of overhead is done at compile time ("Early Binding") rather than at run time ("Late Binding").
There is a word that you can use with Application that will neutralise all the alerts that Excel can send your way.
Discover this word and many others that you can use in combination with Application in the downloadable course on Excel macros. As you can read: starting in cell A1 a value of "99" will be entered in the selected cell then the cursor will move one cell down to enter "99", repeat the process until the row number of the selected cell is 3000 and come back to cell A1.This means that the Excel screen can look like it has "gone crazy" while the macro is running.One thing you may want to do with your macro to make it run faster and to prevent distracting flashes on the screen is to turn off screen updating while the macro is running.This tip (9151) applies to Microsoft Excel 2007, 2010, and 2013.You can find a version of this tip for the older menu interface of Excel here: Turning Off Screen Updating.For that, I post and update a userform for each step with a text message with the elapsed time, the number and name of each step, and a progress bar. Also, if the program quits, a screen snapshot tells me where the macros quit working on that user's computer. Don't use "Exit Sub", instead use "Go To End Of Macro" and place this code before "End Sub". Screen Updating = True The same goes for all you do with "Application.***" A simple and less involved method of providing progress updates would be to use the following line of code in the macro: Application. Prior to "End Sub", place this last line of code: Application. Screen Updating = False, the statusbar will change.] This does the side effect that it looks as though Excel has "hung" or crashed, especially if the macros is going to take some time to execute.