VBA Screen Update

What is Excel VBA Screen Updating Property?

You must have noticed that when you run a macro in Excel VBA, you can see the changes on the screen as the code executes. It is due to the VBA Screen Updating property. The Screen Updating Property dictates whether the screen is updated in real-time while the code runs.

By default, Excel has screen updating enabled, which means that as the code executes, the screen continuously updates, causing flickering and a brief delay before displaying the output.

However, we can easily address this by adding a few lines of VBA code to disable the screen updating feature, i.e., using VBA Screen Updating False property. This simple adjustment ensures that the output is generated without any flickering, providing a smoother user experience. However, once the code is executed, it is not turned on automatically. For this, you should turn it ON from your end as shown below.

When to Use Screen Updating Feature?

VBA Screen Updating Property is beneficial when executing complex or lengthy VBA macros that involve multiple calculations, data manipulations, or UI updates. By turning off Screen Updating, unnecessary flickering and delays are eliminated, providing a seamless and efficient user experience. Therefore, it is recommended to disable VBA Screen Updating Property during the execution of time-consuming tasks and re-enable it once the macro has completed its execution.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use Screen Updating Feature in VBA Code?

Examples

To illustrate the usage of Screen Updating in VBA, let’s consider the following examples:

Example #1 – Turn Off Screen Updating

Let us enhance the initially mentioned example by utilizing the VBA Screen Updating Property to prevent screen flickering and ensure a smoother operation.

• Step 1: Open the Visual Basic Editor by pressing ALT + F11.

• Step 2: Insert a new module by clicking “Insert” from the top menu and selecting “Module.”

• Step 3: In the new module, first, let’s create a subroutine “Sub DoubleCellValue()” and then disable screen updating by setting “Application.ScreenUpdating” to “False.” It prevents the screen from flickering during the code execution.

• Step 4: Now, let us clear the cells A1 to F100 contents. The contents of cells A1 to F100 are cleared using the “Range(“A1:F100″).ClearContents” statement. It ensures that the cells are empty before performing any calculations.

• Step 5: The starting values for cells A1 to F1 are set to 1, 2, 3, 4, 5, and 6 using the “Range(“A1:F1”).Value = Array(1, 2, 3, 4, 5, 6)” statement. It initializes the first row with these values.

• Step 6: A VBA loop is set up to iterate through cells A2 to F100 using the loop variable “i.”

The loop starts from row 2 (i = 2) and continues until row 100 (i <= 100).

• Step 7: Within the loop, the code doubles the value of each cell based on the value in the previous cell.

For each cell A2 to F100, the value is calculated by multiplying the value in the previous row (i – 1) by 2 and assigning it to the current cell.

This is done using statements like “Range(“A” & i).Value = Range(“A” & i – 1).Value * 2”, where the value in cell A(i) is calculated based on the value in cell A(i-1), and so on for cells B to F.

• Step 8: Once the loop completes, screen updating is re-enabled by setting “Application.ScreenUpdating” to “True.” It ensures that any subsequent changes made in the code are immediately visible to the user.

• Step 9: To execute the macro and turn off screen updating, save the workbook and close the Visual Basic Editor. Press ALT + F8 to open the “Macro” dialog box. Select the “DoubleCellValue” macro from the list and click “Run.”

• Step 10: This will run the code and create data in Cells A2 to F100. The code displays a message box with the text “Cell values updated!” using the MsgBox statement to notify the user that the cell values have been updated.

You will notice that the values are updated in the cells (A2 to F100) smoothly without flickering or delay. This is achieved VBA Screen Updating Disable property, which prevents the screen from updating after each cell modification.

Here is the full code:

Sub DoubleCellValue()
‘ Disable Screen Updating
Application.ScreenUpdating = False
‘ Clear the contents of cells A1 to F100
Range(“A1:F100”).ClearContents
‘ Initialize the starting values in cells A1 to F1
Range(“A1:F1”).Value = Array(1, 2, 3, 4, 5, 6)
‘ Loop through cells A2 to F100
Dim i As Long
For i = 2 To 100
‘ Double the previous cell value and put it in the current cell
Range(“A” & i).Value = Range(“A” & i – 1).Value * 2
Range(“B” & i).Value = Range(“B” & i – 1).Value * 2
Range(“C” & i).Value = Range(“C” & i – 1).Value * 2
Range(“D” & i).Value = Range(“D” & i – 1).Value * 2
Range(“E” & i).Value = Range(“E” & i – 1).Value * 2
Range(“F” & i).Value = Range(“F” & i – 1).Value * 2
Next i
‘ Re-enable Screen Updating
Application.ScreenUpdating = True
‘ Show Message
MsgBox “Cell values updated!”
End Sub

Example #2

In this example, we will learn how to create a smoother user experience while performing a simple calculation using VBA Screen updating property.

• Step 1: In the new module, start by assigning a subroutine “Sub CalculateAndDisplayResult()” and Set “Application.ScreenUpdating” property to “False” to disable screen updating. It prevents any visual updates on the screen during the code execution.

• Step 2: Now let us perform a simple calculation, such as adding two numbers; in this example, let us add 2 and 3 and store the result in a variable.

• Step 3: Assign the calculated result to a specific cell on the worksheet using the “Range(“A1”).Value” notation.

• Step 4: Return the “Application.ScreenUpdating” property to “True” to re-enable screen updating. It allows the changes to the worksheet, including the calculated result, to be displayed on the screen.

• Step 5: Now, let us show a message box to inform the user that the calculation process has been completed. It provides a visual indication that the code execution has finished. The message box will appear with the text “Calculation completed!” when this step is reached in the code.

• Step 6: To execute the macro, save the workbook and close the Visual Basic Editor. Press ALT + F8 to open the “Macro” dialog box. Select the “CalculateAndDisplayResult” macro from the list and click “Run”.

• Step 7: Once you run the macro, you will observe that the calculated result is instantly displayed in cell A1 with a message displayed in the message box.

Here is the full code:

Sub CalculateAndDisplayResult()
‘ Disable Screen Updating
Application.ScreenUpdating = False
‘ Perform a calculation
Dim result As Double
result = 2 + 3
‘ Show the result on the worksheet
Range(“A1”).Value = result
‘ Enable Screen Updating
Application.ScreenUpdating = True
‘ Show a message to indicate completion
MsgBox “Calculation completed!”
End Sub

Important Things to Note

• While Screen Updating is disabled, any changes made to the workbook will not be visible until Screen Updating is re-enabled.
• Be cautious when disabling VBA Screen Updating Property if your code relies on user interaction, as the user may not see any feedback until it is re-enabled.
• It’s important to place the VBA Screen Updating Code at the right locations in your VBA code. Typically, you would disable VBA Screen Updating Property at the beginning and re-enable it at the end to ensure the entire code runs without unnecessary screen updates.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand VBA Screen Updating Property with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA Screen Updating Property. Here we learn how to use screen updating feature in VBA code, examples & downloadable excel template. You can learn more from the following articles –

• VBA SendKeys
• VBA Functions
• VBA Return Function