How to Improve the Speed of Macros in Microsoft Excel
How to Improve the Speed of Macros in Microsoft Excel. This article provides insights on how to enhance the performance of macros in Microsoft Excel.
Let’s consider an example to illustrate the concept. If you’re dealing with lengthy VBA code for calculations or other purposes, you may notice that your macro takes a significant amount of time to execute. The following example demonstrates how you can create macros that run faster compared to regular code.
How to Improve the Speed of Macros in Microsoft Excel
Suppose you need to multiply the numbers in column A by 10% and store the results in column B. Follow these steps to launch the Visual Basic (VB) editor:
- Click on the Developer tab.
- From the Code group, select Visual Basic, or use the shortcut ALT + F11 to open the VB Editor screen.
- Click on Insert, and then select Module to create a new module.
To achieve the desired result, enter the following code within the module:
Sub SlowMacro()
For x = 2 To 50000
Cells(x, 1) = x
Cells(x, 2) = x + (x * 0.1)
Next x
End Sub
To optimize the macro for faster execution, you need to incorporate a specific code segment:
Sub FastMacro()
Application.ScreenUpdating = False
For x = 2 To 50000
Cells(x, 1) = x
Cells(x, 2) = x + (x * 0.1)
Next x
Application.ScreenUpdating = True
End Sub
The magic piece of code is Application.ScreenUpdating = False (before the actions begin) and Application.ScreenUpdating = True (to display the final result). By implementing this code, the macro will run more efficiently.
Upon testing, you will observe that the FastMacro performs significantly faster than the previous code. This approach allows you to enhance the speed and smoothness of macros while working on automation projects in the long run.
