Application Object in Excel VBA

Application Object in Excel VBA

In terms of VBA, MS Excel itself is an object. In fact, it is the master object that provides application level controls. That is why it is called as Application Object.

WorksheetFunction

In MS Excel VBA, WorksheetFunction property can be used to access MS Excel functions.

To have a better understanding, insert a Command Button on a worksheet and add following lines of code:

Range(“A6”).Value = Application.WorksheetFunction.Average(Range(“A1:A5”))

On clicking the command button on the worksheet, MS Excel VBA calculates the average of the values in cell A1 and cell A5 and places the result into cell A6.

ScreenUpdating

ScreenUpdating is used to disable the actions of VBA code execution from displaying on screen. While ScreenUpdating is off, you will observe flickering on the main screen (i.e. Worksheet) as the code will execute which also slows down the processing. By using ScreenUpdating, process speed also extends.

To have a better understanding of this concept, insert a command button on worksheet and add following lines of code:

Dim intCnt As Integer
For intCnt = 1 To 20000

    Range(“A1”).Value = intCnt

Next intCnt

When you click the command button on the worksheet, Excel VBA displays each value a tiny fraction of a second and this can take some time.

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

Dim intCnt As Integer

Application.ScreenUpdating = False

For intCnt = 1 To 20000

    Range(“A1”).Value = intCnt

Next intCnt

The addition of “Application.ScreenUpdating = False” line of code will make the code run faster and you will only see the final result (20000).

DisplayAlerts

DisplayAlerts function will allow you to instruct MS Excel VBA to stop the display of alerts while code execution.

To have a better understanding of this concept, insert a command button on worksheet and add following lines of code:

ActiveWorkbook.Close

On clicking the command button on the worksheet, MS Excel VBA closes your Excel file and pops the dialog box whether you want to save the changes or not.

If you want to disable the display of alert while code execution, add the following code.

Application.DisplayAlerts = False

ActiveWorkbook.Close
Application.DisplayAlerts = True

As a result of the above code, MS Excel VBA will close the Excel file, without showing any dialog box and any changes made will be discarded.

Calculation

It is a default behavior of MS Excel that it calculates formulas automatically. Each time when a value changes, all the relevant formulas update themselves automatically. This doesn’t cause any issues in a general usage however, when it comes to complex calculations and implementation of complex formulas, it is better to use manual updating of formula. This approach speed up the process and code execution.

To have a better understanding of this concept, insert a command button on worksheet and add following line of code:

Application.Calculation = xlCalculationManual

On clicking the command button on the worksheet, MS Excel VBA sets calculation to manual.

To verify this, click on File > Options > Formulas.

Workbook can be manually recalculated by pressing F9 Key.