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>