Fasten VBA Code – Application Objects Explained with Examples

Home/Excel VBA/Fasten VBA Code – Application Objects Explained with Examples

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.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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
LIMITED TIME OFFER
By |February 10th, 2013|Excel VBA|0 Comments

About the Author:

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

Leave A Comment


Related pages


excel vba timevaluedelete row based on cell value vbavba in excel 2007 tutorial pdfqlikview beginners guideunprotect xlsexcel vba repeatvba cell widthunprotect password protected excelfree excel dashboard templates 2010what is a vlookup used forexcel formula explanationsadoinhow to remove columns in excelmastering excel 2010how can i find duplicates in excelexamples of excel formulasiserror excel functiongantt chart excel 2007 templatelearn excel vbasum if functionhow to find duplicates in excel and delete themexcel vba & operatorvba data validationbutton excel vbaexcel counting functionsvba double variableinsert row excelautomatic hyperlink excelunprotect workbook vbamacro to delete duplicate rows in excelhow to insert multiple columns in excelvba create worksheethow to hide numbers in excelexcel merging columnscase is vbahow to add tick box in excelexcel macro open folderexcel vba to close workbookvba activecell.rowvlookup excel 2007 tutorialcheck for duplicates in excelmacro for beginnersbest excel book for data analysisvba drop down boxbest excel vba bookexcel 2007 autosaveexcel vba lessonsvba programming for beginnerspivot table charts excel 2010workbooks.activatesql vs exceladvanced filter in excel 2010changing row to column in excelsendmail subjectexcel vba cell widthmerge cell in excel 2007how to enable data analysis in excel 2007meaning of hlookup in excelbest excel book for data analysisexcel rgb color codesvba save xlsm as xlsxdelete column vbaproject management timeline templatehow to lock the excel sheetfree download gantt chart excelspecialcells vbaexample of countif in excelvba copy rangehow to protect a workbook in excelexcel vba clearcontentscountif in vbaexcel averageifwhat is the countif function in excelinterior vbasas code tutorialunprotect macroms access vba delete record