How Macros Can Run Faster in Microsoft Excel

In this article, you will learn how macros can run faster.

 

Let us understand with an example:

If you are working on long VBA codes for calculation or any other purpose; your macro is taking time to execute. The below example will show you how you can make macros that will run faster than the normal codes.

In case you are required to multiply the number by 10% in column A to its respective cell in column B

You need to follow the steps to launch VB editor

 

Click on Developer tab

From Code group, select Visual Basic

img1

or press ALT + F11 shortcut key to launch VB Editor screen.

Click on Insert, and then on Module

img2

This will create new module.

To get result; enter the following code in the Module

 

Sub SlowMacro()

    For x = 2 To 50000

        Cells(x, 1) = x

        Cells(x, 2) = x + (x * 0.1)

    Next x

End Sub

img3

img4

To make the macro run faster, you need the magic piece of code i.e.

Application.ScreenUpdating = False (Before action begins)

Application.ScreenUpdating = True (to show the final result)

The below code will work like a charm

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

img5

After testing you will find the FastMacro runs really faster than the previous code.

In this way, in longer run while working on automation you can make the macros to work faster & smoother.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube