Table of Contents

Application Object

The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.

WorksheetFunction

You can use the WorksheetFunction property in Excel VBA to access Excel functions.

1. For example place a command button on your worksheet and add the following code line:

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

When you click the command button on the worksheet Excel VBA calculates the average of the values in cell A1 and cell A2 and places the result into cell A3.

WorksheetFunction in Excel VBA

Note: instead of Application.WorksheetFunction.Average use WorksheetFunction.Average. If you look at the formula bar you can see that the formula itself is not inserted into cell A3. To insert the formula itself into cell A3 use the following code line:

Range(“A3”).Value = “=AVERAGE(A1:A2)”

ScreenUpdating

Sometimes you may find it useful to disable screen updating (to avoid flickering) while executing code. As a result your code will run faster.

1. For example place a command button on your worksheet and add the following code lines:

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 displays each value a tiny fraction of a second and this can take some time.

ScreenUpdating

2. To speed up the 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

As a result your code will run much faster and you will only see the end result (10000).

DisplayAlerts

You can instruct Excel VBA not to display alerts while executing code.

1. For example place a command button on your worksheet and add the following code line:

ActiveWorkbook.Close

When you click the command button on the worksheet Excel VBA closes your Excel file and asks you to save the changes you made.

DisplayAlerts

2. To instruct Excel VBA not to display this alert while executing code update the code as follows.

Application.DisplayAlerts = False

ActiveWorkbook.Close

Application.DisplayAlerts = True

As a result Excel VBA closes your Excel file without asking you to save the changes you made. Any changes are lost.

Calculation

By default calculation is set to automatic. As a result Excel recalculates the workbook automatically each time a value affecting a formula changes. If your workbook contains many complex formulas you can speed up your macro by setting calculation to manual.

1. For example place a command button on your worksheet and add the following code line:

Application.Calculation = xlCalculationManual

When you click the command button on the worksheet Excel VBA sets calculation to manual.

2. You can verify this by clicking on File Options Formulas.

Calculation Options

3. Now when you change the value of cell A1 the value of cell B1 is not recalculated.

Manual Calculation

You can manually recalculate the workbook by pressing F9.

4. In most situations you will set calculation to automatic again at the end of your code. Simply add the following code line to achieve this.

Application.Calculation = xlCalculationAutomatic

Leave A Comment

Excel meets AI – Boost your productivity like never before!

At Formulas HQ, we’ve harnessed the brilliance of AI to turbocharge your Spreadsheet mastery. Say goodbye to the days of grappling with complex formulas, VBA code, and scripts. We’re here to make your work smarter, not harder.

Related Articles

The Latest on Formulas HQ Blog