The way out of this frozen state is simple: Go back to the VBE, and execute the following statement in the Immediate window: If you have a worksheet with many complex formulas, you may find that you can speed things considerably by setting the calculation mode to manual while your macro is executing.
When the macro finishes, set the calculation mode back to automatic.
You can use the Worksheet Function property in Excel VBA to access Excel functions. For example, place a command button on your worksheet and add the following code line: When you click the command button on the worksheet, Excel VBA calculates the average of the values in cell A1 and cell A2 and places the result into cell A3. You can manually recalculate the workbook by pressing F9. In most situations, you will set calculation to automatic again at the end of your code. To insert the formula itself into cell A3, use the following code line: Sometimes you may find it useful to disable screen updating (to avoid flickering) while executing code. You can instruct Excel VBA not to display alerts while executing code. For example, place a command button on your worksheet and add the following code line: When you click the command button on the worksheet, Excel VBA closes your Excel file and asks you to save the changes you made. To instruct Excel VBA not to display this alert while executing code, update the code as follows. As a result, Excel recalculates the workbook automatically each time a value affecting a formula changes.You won't be able to see what the macro is doing, but it will run faster.Remember to set the Screen Updating property back to True when your macro ends.This example demonstrates how turning off screen updating can make your code run faster.
The example hides every other column on Sheet1, while keeping track of the time it takes to do so.Screen Updating = False start Time = Time Worksheets("Sheet1"). Worksheet Function | Screen Updating | Display Alerts | Calculation The mother of all objects is Excel itself. The application object gives access to a lot of Excel related options. As a result, Excel VBA closes your Excel file, without asking you to save the changes you made. If your workbook contains many complex formulas, you can speed up your macro by setting calculation to manual. For example, place a command button on your worksheet and add the following code line: When you click the command button on the worksheet, Excel VBA sets calculation to manual. You can verify this by clicking on File, Options, Formulas. Now when you change the value of cell A1, the value of cell B1 is not recalculated.The following statement sets the Excel calculation mode to manual: If your code uses cells with formula results, turning off calculation means that the cells will not be recalculated unless you explicitly tell Excel to do so!As you know, a macro can automatically perform a series of actions.These types of messages mean that you can’t leave Excel unattended while it executes your macro — unless you know the secret trick.