REAL-TIME

VBA Projects

Full Access with Source Code
  • Designed and Developed by PNRao

  • Full Access with VBA Source Code

  • Well Commented Codes Lines

  • Creative and Professional Design

Effortlessly
Manage Your Projects

120+ Project Management Templates

Seamlessly manage your projects with our powerful & multi-purpose templates for project management.

120+ PM Templates Includes:
  • 50+ Excel Templates

  • 50+ PowerPoint Templates

  • 25+ Word Templates

Share Post

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
Effortlessly Manage Your Projects and Resources
120+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

Save Up to 85% LIMITED TIME OFFER
Project Management Templates

All-in-One Pack
120+ Project Management Templates

Essential Pack
50+ PM Templates

Excel Pack
50+ Excel PM Templates

PowerPoint Pack
50+ Excel PM Templates

MS Word Pack
25+ Word PM Templates

Ultimate Project
Management Template
Ultimate Resource
Management Template
Project Portfolio
Management Templates
Categories: Excel VBATags: Last Updated: February 10, 2013

Leave A Comment