VBA Screen Updating | Use Application.ScreenUpdating in Excel VBA

Screen Updating in Excel VBA

One of the frustrating thing I hear from new learners of VBA is when they are executing large code they frustrated by the fact that it slows down the excel and takes much time to complete the task. I am not a stranger to slow down of code when it comes to looping through the cells and perform a specific set of task. Then how do we speed up our code? This is the question many of you has and there is no doubt in that. In this article, we will show you one of the important methods to speed up our code i.e. Screen Updating in Excel VBA.

What is Screen Updating in VBA?

VBA Screen Updating is something we can notice while the macro is running. When the task is executing we can notice our screen is updating the values until the macro finishes its assigned task.

As our screen flickering or refreshing it leads to slow down of the excel program and takes longer time than usual to complete the task.

Often times we can feel the excel screen goes crazy while the macro is running and we almost frustrated by that. But how do we deal with these situations and make the code run faster than the usual slow thing?

One of the things we can do avoid or prevent distraction flashes of screen updating and make it fast we can turn off Screen Updating.

In VBA we have a property called “Screen Updating” and we set this property to FALSE so that it will eliminate the process of screen updating while the code is running.

In this article, we will say goodbye to the watching of on-screen action drama while the code is running. Today you will make your code run faster and quicker than your usual time.

How to Set Not to Update the Screen Updating in VBA?

For an example look at the below code.

You can download this VBA ScreenUpdating Excel Template here – VBA ScreenUpdating Excel Template

Code:

Sub Screen_Updating()

 Dim RowCount As Long
 Dim ColumnCount As Long
 Dim MyNumber As Long

 MyNumber = 0

 For RowCount = 1 To 50

 For ColumnCount = 1 To 50
   MyNumber = MyNumber + 1
   Cells(RowCount, ColumnCount).Select
   Cells(RowCount, ColumnCount).Value = MyNumber
 Next ColumnCount

 Next RowCount

End Sub

The above has nested VBA loops to insert serial numbers from the first column to 50^th column and again comes back and insert serial number starting from 51 from the second row to the 50^th column.

Like this, it will insert until it reaches the 50^th row.

While this code is running you can notice your screen flickering and you cannot do anything apart from watching this crazy moment.

To avoid all of these we can add Screen Updating to FALSE.

To access the Screen Updating in Excel VBA feature first we need to access the Application object.

As we can see with the Application object we have many properties and methods. So, select VBA Screen Updating from the IntelliSense list.

Note: If you have where to apply Screen Updating in excel VBA, “you have to apply this immediately after the declaration of the variables”

After selecting the Screen Updating property put an equal sign (=).

As we can two Boolean values i.e. FALSE & TRUE.

To stop screen updating set the status to FALSE.

Now, as the macro starts running first it will update the screen updating status to FALSE and proceed to the next line.

Since macro executed Screen Updating to FALSE it will not allow the screen to update while the code is executing its task.

Always Set this Updating to TRUE at the End

I have seen many people set the Screen Updating to FALSE but forgot to set it back to TRUE at the end of the macro.

Always set the Screen Updating back to TRUE at the end of the macro.

Code:

Sub Screen_Updating()

 Dim RowCount As Long
 Dim ColumnCount As Long
 Dim MyNumber As Long

 Application.ScreenUpdating = False
 MyNumber = 0

 For RowCount = 1 To 50

 For ColumnCount = 1 To 50
   MyNumber = MyNumber + 1
   Cells(RowCount, ColumnCount).Select
   Cells(RowCount, ColumnCount).Value = MyNumber
 Next ColumnCount

 Next RowCount
 Application.ScreenUpdating = True

End Sub

When to Use Screen Updating?

If you have any doubt when to use this technique. Look into the below points.

When you are looping through a large number of cells.
Sending emails from Excel.
Switching between excel workbooks.
Opening new workbooks.

Excel

Financial Functions Excel

Lookup Functions Excel

Excel Charts

Others

Certification Courses

Excel VBA All in One Bundle

Excel Data Analysis Course

VBA Macros Course