Logo
×

VBA Screen Updating

What is VBA Screen Updating property?

Screen Updating is a VBA property which is used to display the output generation while running the code. If this property is enabled, we could see the output (numbers, text, alphabets, or pattern) getting generated on the Excel sheet. Screen updating property can be turned ON/Off at any point in time. If it is set to Boolean true, it continues the screen updating else if set to Boolean False; it disables this property. By default, this property is set to Boolean True. The users usually turn it off to avoid the screen flickering, distractions and mostly to reduce the background processing time used to run or wait for the pointer. Thereby increasing the speed of the code and saving time.

Screen Updating is more bane than a boon. This property continues to update the screen until VBA finishes its assigned job. Imagine if you have VBA code with a massive set of code, it would be too difficult as the screen flickering and refreshing will continue till the end of the code. Thus, it leads to the slow down of the code or sometimes the hangs the system, which becomes frustrating while working with the large set if macro code, it is always advisable to set off the screen property at the beginning of the code and set back to Boolean TRUE at the end of the code.

Note: Always remember to Turn on the Screen updating property when you set it off else, the screen for the sheet will not get updated, and the result will not be displayed.

Syntax

Application. ScreenUpdating

Where the screen updating refers to Boolean data type and can be set to TRUE or FALSE

Examples: Steps to Turn Off the Screen Updating Property

Code:

Option Explicit
Sub ScreenUpdating_Example()
'Declaring the variable
Dim iCounter As Long
'disabling the Screen updating by setting it boolean True
Application.ScreenUpdating = False
'work with the logic
For iCounter = 1 To 10
    Cells(iCounter, 1).Value = iCounter
Next iCounter
'Again turning on the Screen updating property so as the output could get updated
Application.ScreenUpdating = True
End Sub

Let’s work with the step by step analysis of the above code:

Step 1: Open the VBA developer tab either by using the shortcut keywords Alt +F11 or click on developer window -> visual basic editor.

Step 2: Visual Basic Editor will open. The next step is to create a module. Right-clicking on the VBA Project-> Click on Insert-> Click on Module.

'Declaring the variables
Dim timeStart As Double, timeStop As Double
Dim totalTime As Double
Dim i As Long
'setting the Screen updating property to Boolean False
Application.ScreenUpdating = False
'starting the time
timeStart = Timer
'enabling a for next loop to print the values 1 to 1000 in the excel sheet
For i = 1 To 1000
    Cells(i, 1).Value = i
Next i
'stoping the time
timeStop = Timer
'Calculating the Total time and rounding it off to 2 values.
totalTime = Round(timeStart - timeStop, 2)
'Displaying the total time to run the program.
MsgBox totalTime
'enabling the Screen updating property by setting it to Boolean True
Application.ScreenUpdating = True
End Sub

Step 3: Introduce the subcategory following with your macro name and declare your variable with the Integer Data type.

VBA Screen Updating

Step 4: To disable the screen updating property, firstly we need to access the application object. Next, we will click ctrl +space to access all its properties. Screen Updating is one of the Application object’s properties.

VBA Screen Updating

Step 5: Select the Screen updating property from the IntelliSense list and put an equal sign. You will notice that the Booleans True and False are displayed.

VBA Screen Updating

Step 6: Select the Boolean False. Thus, Screen Updating property has been disabled.

VBA Screen Updating

Step 7: Now, write the logic which you want to perform. Unlike, here we have written the logic to display a range of values numbering from 1 to 10.

VBA Screen Updating

Step 8: Before ending the sub-procedure always remember to turn on the Screen updating property so as the output could get updated on the sheet.  Again, access the application object and select its screen updating property and set it to Boolean True.

VBA Screen Updating

Output

Step 9: Press the F5 key to run the output. You will notice in no time, VBB has displayed the output.

VBA Screen Updating

Example 2: Comparing Screen Updating on and Off Property

Let’s see the practical effects for turning off the screen updating property in our VBA code. Firstly, we will write the macro by enabling the screen updating note.

  1. By turning on screen updating

Follow the below steps:

  • Introduce the subcategory following with your macro name and declare the five variables which are as follows:
    • timeStart (Double data type): It is used to calculate the timer’s starting point
    • timeStop (Double data type): It calculates the timer’s ending point
    • totalTime (Double data type): It variable will find the total time to run the code. It subtracts the timeStop with the timeStart.
    • i (Long data type): It will be used for looping and indexing purpose.
  • Access the application object and select its screen updating property and set it to Boolean True.
  • Call the timer method and store its value in the timeStart variable
  • Using the For Next loop, print 1 to 1000-digit numbers in your Excel sheet.  
  • Again, call the timer method to pause the time count. Store the returned value in the timeStop variable.
  • Next, we will calculate the total time for running the code. For this subtract the values of timeStop and timeStart variables.
  • Display the totalTime with the help of MsgBox.

Code:

Sub ScreeUpdating_SetToTrue()
'Declaring the variables
Dim timeStart As Double, timeStop As Double
Dim totalTime As Double
Dim i As Long
'setting the Screen updating property to Boolean True
Application.ScreenUpdating = True
'starting the time
timeStart = Timer
'enabling a for next loop to print the values 1 to 1000 in the excel sheet
For i = 1 To 1000
    Cells(i, 1).Value = i
Next i
'stoping the time
timeStop = Timer
'Calculating the Total time and rounding it off to 2 values.
totalTime = Round(timeStart - timeStop, 2)
'Displaying the total time to run the program.
MsgBox totalTime
End Sub
VBA Screen Updating

Output

VBA Screen Updating

The time taken to run the code is -0.1s.

  •  When the Screen Update is set to False

Follow the below steps:

  • Introduce the subcategory following with your macro name and declare the variables i.e., timeStart (to calculate the timer’s starting point), timeStop (to calculate the timer’s ending point), totalTime (subtracting timeStop with timeStart) with Double Data type and i (for looping and indexing) with long data type.
  • Access the application object and select its screen updating property and set it to Boolean True.
  • Call the timer method and store its value in the timeStart variable
  • Using for-next loop, print 1 to 1000-digit numbers in your Excel sheet.  
  • Again, call the timer method to pause the time count. Store the returned value in the timeStop variable.
  • Next, we will calculate the total time for running the code. For this subtract the values of timeStop and timeStart variables.
  • Display the totalTime with the help of MsgBox.
  • Before ending the sub procedure, again call the application object and set its screen updating property to Boolean so as the data in the Excel sheet should get updated.

Code:

Sub ScreeUpdating_SetToFalse()
'Declaring the variables
Dim timeStart As Double, timeStop As Double
Dim totalTime As Double
Dim i As Long
'setting the Screen updating property to Boolean False
Application.ScreenUpdating = False
'starting the time
timeStart = Timer
'enabling a for next loop to print the values 1 to 1000 in the excel sheet
For i = 1 To 1000
    Cells(i, 1).Value = i
Next i
'stoping the time
timeStop = Timer
'Calculating the Total time and rounding it off to 2 values.
totalTime = Round(timeStart - timeStop, 2)
'Displaying the total time to run the program.
MsgBox totalTime
'enabling the Screen updating property by setting it to Boolean True
Application.ScreenUpdating = True
End Sub
VBA Screen Updating

Output

VBA Screen Updating

The time taken to run the code by disabling the screen updating is only -0.09 where while enabling the screen updating property the total time taken to run the similar code was -0.1.

Related Topics

Excel VBA AutoFilter

One of the reasons for Excel VBA’s popularity is its capability to filter and analyze data from huge database with the help of a method known as AutoFilter. This method permits a...

4 minutes read.

Excel VBA Str Function

VBA Str Function: The Str function in VBA converts the given number into a string representation of that number. Syntax Str (Number) Parameter Number (required) – This parameter represents the numeric value that you want...

1 minute read.

Excel VBA Left Function

VBA Left Function: The Left function in VBA returns a substring from the start of the specified string. Syntax Left (Str, Length) Parameter Str (required) – This parameter represents the string that you want to extract...

1 minute read.

Debugging in Excel VBA

Debugging in VBA: Debugging is a technique used to fix errors in programming languages. In Excel VBA, we have different ways by which you can identify the error in the...

2 minutes read.

Excel VBA UBound Function

Excel VBA UBound Function: The UBound function in VBA returns the highest subscript for the specified dimension in the given array. Syntax UBound (ArrayName, [Dimension]) Parameter ArrayName (required) – This parameter represents an array for which...

1 minute read.

VBA ActiveCell Property

What is the ActiveCell Property? The active cell signifies the active selected cell in the current worksheet. The Active property acts as a reference point and is used to move the cell cursor...

5 minutes read.

Excel VBA Format Function

VBA Format Function The format function in VBA applies a specified format to an expression and returns the result as a string. Syntax Format (Expression, [Format], [FirstDayOfWeek] , [FirstWeekOfYear] ) Parameter Expression (required)- This parameter represents the expression that you want to format. Format...

3 minutes read.

VBA Updating Pivot Table

VBA- Updating Pivot Table The pivot table is an important feature to explore, summarize, and interpret the bulk amount of data. It helps data evaluating, reviewing, as well as making useful...

3 minutes read.

Excel VBA Right Function

VBA Right Function: The Right function in VBA returns a substring from the end of the given string. Syntax Right (Str, Length) Parameter Str (required) – This parameter represents the string from which you want to...

1 minute read.

Excel VBA CDec Function

VBA CDec Function: The CDec function in VBA converts an expression into a Decimal data type. Syntax CDec (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to a...

1 minute read.

Excel VBA DateSerial Function

The DateSerial function in VBA returns a Date from a supplied year, month, and day number. Syntax DateSerial (Year, Month, Day) Parameter Year (required) – This parameter represents an integer signifying the year. Month (required) – This parameter...

2 minutes read.

Excel VBA IsNumeric Function

VBA IsNumeric Function: The IsNumeric function in VBA returns a Boolean value showing whether the specified Expression contains a numeric value or not. Syntax IsNumeric (Expression) Parameter Expression (required)- This parameter represents the variant that...

1 minute read.

Excel VBA InStr Function

VBA InStr Function The InStr function in VBA searches for a substring inside the given string. It returns the position of a substring within a string, as an integer if the substring is found...

1 minute read.

Excel VBA INT Function

VBA INT Function: The INT function in VBA rounds the given supplied number down and returns an integer value. The positive numbers are rounded to zero, and the negative numbers are rounded away from zero. Syntax Int...

1 minute read.

Excel VBA StrReverse Function

The StrReverse function returns a String after reversing the given String. Syntax StrReverse (Expression) Parameter Expression (required) – This parameter represents the String that you want to reverse. Return This function returns a String after reversing the given String. Example...

1 minute read.

Excel VBA Year Function

Excel VBA Year Function: The Year function in VBA returns the four-digit year for the specified date. Syntax Year (Date) Parameter Date (required) – This parameter represents the significant date. Return This function returns the four-digit year...

1 minute read.

Excel VBA Sgn Function

VBA Sgn Function: The Sgn function in VBA returns an integer (+1, 0, or -1), stating the arithmetic sign for the specified number. Syntax Sgn (Number) Parameter Number (required) –This parameter represents the number that...

1 minute read.

Excel VBA Trim Function

The Trim function in VBA removes the leading and trailing spaces from the specified string. Syntax Trim (String) Parameter String (required) – This parameter represents the string from which you want to remove the leading and...

1 minute read.

Excel VBA Minute Function

The Minute function in VBA returns the minute component for the specified time. Syntax Minute (Time) Parameter Time (required) – This parameter represents the time. Return This function returns the minute component for the specified time. Example 1 Sub MinteFunction_Example1() ...

1 minute read.

Excel VBA FormatPercent Function

VBA FormatPercent Function: The FormatPercent function in VBA is used to apply a percent format to a numeric expression, and it returns the result as a string. Syntax FormatPercent (Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits]) Parameter Expression (required)...

2 minutes read.