Working with Application Object in Excel VBA – Complete Step by Step Tutorial

Application Object in Excel VBA

The mother of all objects is Excel itself. We call it the Application object. You can use the Application object in Excel VBA to work with various Excel options and tools.

WorksheetFunction

In Excel VBA, WorksheetFunction allows you to use Excel’s built-in functions directly in your code for advanced calculations.
1. Add a command button, then enter this line of code to demonstrate how it works:

Range("A3").Value = Application.WorksheetFunction.Average(Range("A1:A2"))

When the button is clicked, it triggers the attached VBA code to calculate the average of A1 and A2 and finally transfers the value to cell A3.

Application Object in Excel VBA

Note: instead of Application.WorksheetFunction.Average, use WorksheetFunction.Average. If you carefully check the attached picture, you will see the formula bar display the value, not the formula. To insert the formula directly into cell A3, use:

Range("A3").Value = "=AVERAGE(A1:A2)"

ScreenUpdating

To boost performance, disable screen updating so Excel doesn’t refresh visuals during code execution. This prevents flickering and helps your code run faster.
1. For example, insert a command button on your worksheet and enter the code below:
Dim i As Integer

For i = 1 To 10000
Range("A1").Value = i
Next i

When you click the command button on the worksheet, Excel VBA shows each value for a brief moment, which may slow down the process.

2. To speed up the execution process, update the code as follows:

Dim i As Integer

Application.ScreenUpdating = False

For i = 1 To 10000
Range("A1").Value = i
Next i

Application.ScreenUpdating = True

This speeds up your code and displays only the final result, which is 10000.

DisplayAlerts

Excel VBA allows you to turn off alerts while your code runs.

1. For example, insert a command button on the worksheet and type the code line below:

ActiveWorkbook.Close

When the command button is clicked, Excel VBA attempts to close the workbook and prompts you to confirm if you wish to save your work.

2. To disable this warning message during VBA execution, update your code with the line shown below.

Application.DisplayAlerts = False

ActiveWorkbook.Close

Application.DisplayAlerts = True

As a result, the Excel file is closed by VBA without prompting you to save any changes. Any changes are lost.

Calculation

By default, calculation is set to automatic. So, when a value used in a formula changes, Excel automatically recalculates the workbook. To improve the speed of your macro in a workbook with complex formulas, set the calculation mode to manual.

1. For example, add a command button to your worksheet, then type the code below:

Application.Calculation = xlCalculationManual

When you click the command button, Excel VBA changes the worksheet’s calculation mode from automatic to manual to control recalculations.

2. You can confirm this by opening File, choosing Options, and clicking on Formulas.

 

3. Now, changing the value in cell A1 no longer causes cell B1 to update or recalculate automatically as before.

To manually recalculate the workbook in Excel, simply press the F9 key on your keyboard.

4. In most cases, you should turn the calculation back to automatic at the end of your code. To complete this step, add the line of code shown below.

Application.Calculation = xlCalculationAutomatic

1/5 Completed! Explore the application object ➝
Next Chapter: ActiveX Controls