Here are the example codes to control the Events, Screen Updating, Progress Bar, Display Alerts and other Application Objects. This will help you to write Optimized VBA Code to fasten your code and build better VBA Macro Applications.
Stop Screen Updating
You can fasten your code by stopping your screen updating. You can use the following syntax to disable Screen Updating.
To Disable Screen Updating:
Application.ScreenUpdating = False
To Enable Screen Updating:
Application.ScreenUpdating = True
Note: All ways it is a better practice to enable the screen updating once you done with your task.
Example: To print 50,000 numbers
Sub PrintNos() Dim i As Long Application.ScreenUpdating = False For i = 1 To 50000 Sheets("Sheet1").Cells(i, 1) = i Next Application.ScreenUpdating = True End Sub
Stop Events in Excel VBA – Disable Enable
You can disable the events by using Application.EnableEvents = False
Example: The following code will stop the events and change the value of combo box
Sub sbStopEvents() 'Diable Events Application.EnableEvents = False 'Do your Task 'changing combo box combo1.Value = "VBA Tutorials" 'Make sure that you are enabling the events once you are done with your task Application.StatusBar = "" End Sub
Stop Application Alerts in Excel VBA – Disable Enable
You can suppress the Waning alerts by disabling the display alerts.
Situation 1: When you are deleting a sheet using VBA, Excel popup a warning message.
Situation 2: When you are saving a file with already existing file name, Excel popup a warning message.
To avoid this, here is the solution:
To Enable display alerts:
Application.DisplayAlerts = True
To Disable display alerts:
Application.DisplayAlerts = False
Note: All ways it is a better practice to Enable the alerts once you are done with the suppressing the alerts.
Example 1: When saving a workbook
Sub ExampleSaveWorkbook() Application.DisplayAlerts = False ActiveWorkbook.SaveAs ThisWorkbook.Path & "Output.xls" Application.DisplayAlerts = True End Sub
Example 2: When deleting a worksheet
Sub ExampleDeleteSheet() Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub
Display Progress on Statusbar in VBA Excel
Solution: You can display the progress on the stausbar using Application.Stausbar property
Example: The following code will print the 5000 numbers and show you the progress on the statusbar
Sub sbStatusBar() 'Pring some text on statusbar Application.StatusBar = "Start Printing the Numbers" 'Print numbers For icntr = 1 To 5000 Cells(icntr, 1) = icntr 'Printing the Progress Application.StatusBar = " Please wait while printing the numbers " & round((icntr / 5000 * 100),0) & "%" Next 'Make sure that you are clearing the Status bar once you are done with your task Application.StatusBar = "" End Sub
Set Windows State in Excel VBA – Minimize Maximize Normal
Sub sbWindowState() 'Maximized State Application.WindowState = xlMaximized 'Minimized State Application.WindowState = xlMinimized 'Normal State Application.WindowState = xlNormal End Sub
Toggle Full Screen in Excel VBA
Sub sbFullScreen() 'Full Screen Mode Application.DisplayFullScreen = True 'Normal Mode Application.DisplayFullScreen = False End Sub
Get User Name in VBA Excel
Sub sbGetUserName() MsgBox Application.UserName End Sub
Stop Calculations in Excel VBA – Manual Automatic
Sub sbStopCalculations() Application.Calculation = xlCalculationManual 'Your code here. Application.Calculation = xlCalculationAutomatic End Sub
Open Visual Basic Editor (VBE) – Open Module with VBA
You can use Goto method
'Open Visual Basic Editor Sub sbOpenVBE() With Application 'To Open a sub procedure 'sbOpenVBE' .Goto "sbOpenVBE" End With End Sub
VBA to Exit from Procedure or Function
Example: The following code will Exit from the code based on a condition
Sub sbTerminateProc() 'Terminate the procedure if the a cell value is equals to 155.5 For iCntr=1 to 10000 If Cells(iCntr,1)=155.5 then Exit Sub Next End Sub