WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Screen Updating Property

VBA Screen Updating Property

Excel VBA Screen Updating

VBA Screen Updating is a property used to avoid or prevent distraction flashes while running the code and make it fast by turning off Screen Updating. We can turn off the screen updating by setting this property as false.

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

Screen Updating is something we can notice while the excel 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 the slowdown of the excel program and takes a longer time than usual to complete the task.

In VBA, we have a property called “ScreenUpdating,” 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.

VBA Screen Updating

When to use Screen Updating Feature?

Suppose 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 VBA.
  • Switching between excel workbooks.
  • Opening new workbooks.

How to use Screen Updating Feature in VBA Code?

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

Example #1 – Turn Off Screen Updating

For example, look at the below code.

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 a nested VBA loop to insert serial numbers from the first column to the 50th column and again comes back and insert serial number starting from 51 from the second row to the 50th column.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VBA Screen Update Example 1

Like this, it will insert until it reaches the 50th 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 feature first, we need to access the Application object.

VBA Screen Update Example 1-1

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

Note: You have to apply the Screen Updating feature immediately after the declaration of the variables.

Example 1-2

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

Example 1-3

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

To stop screen updating, set the status to FALSE.

Example 1-4

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.

Example #2 –

Always Set Screen 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

Recommended Articles

This has been a guide to VBA Screen Updating. Here we discuss how to use Application.ScreenUpdating feature to make code run faster and quicker than your usual time along with examples and downloadable excel template. Below are some useful excel articles related to VBA –

  • Solver Function in VBA
  • Use VBA Debug Print
  • Break For Loop in VBA
  • ThisWorkbook Property in VBA
  • VBA FreeFile
4 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA ScreenUpdating Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More