How to Make VBA Code Run Faster (15 Ways)

Method 1 – Switch Off Screen Updating to Make VBA Code Run Faster

Keeping screen updates on will slow your VBA code. Each time VBA inserts data into the worksheet, it reloads the screen image. To do so, you have to switch off the screen updates.

You can use the following code to switch off the Screen Updating:

Application.ScreenUpdating = FALSE

You can use the following code to switch on the Screen Updating:

Application.ScreenUpdating = TRUE

Method 2 – Shut Off Automatic Spreadsheet Calculation

If you keep the automatic spreadsheet calculation turned on, it may dramatically impact your VBA code. If you change any value in the worksheet, Excel will recalculate all the cells related to that cell value. This means macros will insert those values, and VBA will stop until the worksheet is done.

This solution is a must for running faster VBA code.

You have to set the calculation manually.

Apply the following command to shut off the automatic spreadsheet calculation:

Application.Calculation = xlCalculationManual

You can also apply the following command to turn on the automatic spreadsheet calculation:

Application.Calculation = xlCalculationAutomatic

You can also use Excel to set the Calculation Options.

Go to the top ribbon Formulas >> from Calculation Options >> Choose Automatic.

How to Make VBA Code Run Faster

Method 3 – Avoid Using the VBA Copy and Paste Method to Make VBA Code Run Faster

The built-in copy-paste method can make VBA code run slower.

Use the following code to Copy and Paste by referencing the value itself. This will make a VBA code run 25 times faster.

Range("B1:D100").value = Range("F1:H100").value

Method 4 – Don’t Use Complex Formulas to Make VBA Code Run Faster

A complex formula can make VBA code run slower. When complex calculations are needed, you can break the formula into different parts. Suppose you want to know your monthly loan payments using VBA. The macro will calculate the loan’s monthly payment at 5% annual interest for 4 years.

In such a case, addition, multiplication, exponentiation, and division may be needed. Here, exponentiation is performed first, and then, with that result, other operations are performed. Besides, exponentiation takes longer than other functions.

The macro can calculate simple formulas faster than complex ones.

Method 5 – Disable Events to Make VBA Code Run Faster

Events can be triggered when you change any cell in a worksheet when you activate any worksheet or when you open any workbook. In such cases, events can lead to unwanted results or can make the macro run slower.

Use the corresponding code to disable events:

Application.EnableEvents = False

You can also use the corresponding code to enable events:

Application.EnableEvents = True
Read More: How to Open Large Excel Files Without Crashing

Method 6 – Apply “With…..End With” Statements in VBA

Applying with statements can make the VBA code run faster.

An example is given below:

Suppose you have the following dataset with blank rows.

Steps:

Go to the top ribbon Developer >> Visual Basic.

Select the tab Insert >> Module.

How to Make VBA Code Run Faster

Enter the corresponding code in the Module:

Sub Faster_Example()
    With Sheets("using with-end with statement")
        .Range("B4").FormulaR1C1 = "Exceldemy"
        .Range("B5").FormulaR1C1 = "VBA code"
        .Range("B4").Font.Bold = True
        .Range("B5").Font.Bold = True
    End With
End Sub

How to make VBA code run faster

With statement denotes that you can execute many statements on a particular object without mentioning the object name repeatedly. By doing this, you can make the VBA code more readable.

Go to Developer >> Macros.

Select Faster_Example from the Macro name.
Press Run.

You can see the following results.

How to make VBA code run faster

Method 7 – Employ the ‘For Each’ Loop Rather Than the ‘For’ Loop

You can employ the For Each loop. Which is faster than the For loop. For…. Each loop executes each element of a group or an array.

An example is given below.

Sub Loop1()
    Dim cell As Range
    For Each cell In Range("b4:b50")
        cell.Value = 100
    Next cell
End Sub

Method 8 – Try to Interact Less with the Worksheet

VBA gets slower when it needs to interact with worksheets. If possible, put all the formulas in VBA. Calling the formulas from macros makes the VBA code run slower. Because of this, VBA has to re-read the data each time.

Furthermore, try not to insert worksheet data within the loops. You may call the data first and then insert the formulas into the loops.

An example is given below:

For Time = 1 To 24
Worked_Hour = Range("Salary_Hour") + Time
Next Time

In this code, VBA interacted once with the worksheet, but the following code is still the fastest.

Salary_Hour = Range("Salary_Hour")
For Time = 1 to 24
Worked_Hour = Salary_Hour + Time
Next Time

It can be more fruitful to conduct all the numerical calculations in VBA.

Read More: How to Make Excel Open Faster

Method 9 – Using a Single Operation for Reading and Writing Blocks of Data

“Array” can hold multiple variables easily. So, you should not initialize the array at the beginning. Try using the array within the operation in the VBA code.

Use the following code:

With Worksheets("Exceldemy1")
.Range("B4:L20").Value = myArray
End With

Instead of the following:

myArray= Worksheets("Exceldemy1").Range("B4:L20").value

Method 10 – Turn Off Alerts to Make VBA Code Run Faster

When you close any workbook without saving it in Excel, there is an icon box that alerts you whether the workbook is saved or not.

Likewise, an Alert is that thing through which you are being alerted to do something.

This may make VBA code run a little slower. So, you can turn off alerts to make VBA code run faster.

Use the following command to turn off display alerts:

Application.DisplayAlerts = False

Use the following command to turn on display alerts:

Application.DisplayAlerts = True

Method 11 – Avoid Using Variants in Formulas

If you don’t use “Option Explicit” at the beginning of the code, anything undefined will act as a variant. So, try to declare “Option Explicit” at the beginning of the code, as VBA is very sensitive to variables. Moreover, variables are slow to process in VBA code. That is why try not to use variables as formula variants until necessary.

Method 12 – Use Boolean Code Instead of Using Strings

Strings are simple text that is slow to read in the macro. In this case, you can use enumeration to assign numerical values as variants. Macro can read numerical values faster.

Moreover, you can assign certain numerical values according to your consideration in enumeration.

Apart from this, you can use boolean code. Boolean code acts as a switch like TRUE/FALSE, which is a very fast procedure.

Using Strings Enumeration Boolean Code

Using Strings	Enumeration	Boolean Code
`Select Case Marrital_status Case "Married" (insert code here)... Case "Unmarried" (insert code here)... Case "Widow" (insert code here)... End Select`	`Public Enum enumMarrital_status Married = 0 Unmarried = 1 Widow = 2 End Enum Dim Marrital_status as enumMarrital_status Select Case Marrital_status Case Married (insert code here)... Case Unmarried (insert code here)... Case Widow (insert code here)... End Select`	`If bMarried Then (insert code here)... ElseIf bUnmarried Then (insert code here)... ElseIf bWidow Then (insert code here)... End If`

Select Case Marrital_status
Case "Married"
(insert code here)...
Case "Unmarried"
(insert code here)...
Case "Widow"
(insert code here)...
End Select

Public Enum enumMarrital_status
Married = 0
Unmarried = 1
Widow = 2
End Enum
Dim Marrital_status as enumMarrital_status

Select Case Marrital_status
Case Married
(insert code here)...
Case Unmarried
(insert code here)...
Case Widow
(insert code here)...
End Select

If bMarried Then
(insert code here)...
ElseIf bUnmarried Then
(insert code here)...
ElseIf bWidow Then
(insert code here)...
End If

Method 13 – Turn Off Displaying PageBreaks

To make VBA code run faster, you can turn off the display of page breaks. Using this option may not always have an impact, but if VBA forces Excel to count all the page breaks, this may hamper the speed of VBA code running.

Use the following command to turn off displaying page breaks:

ActiveSheet.DisplayPageBreaks = False

Use the following command to turn on displaying page breaks:

ActiveSheet.DisplayPageBreaks = True

Method 14 – Avoid Selecting Particular Worksheets Unless Important

Try not to use the “Select” command. This could make VBA code run faster.

Use the following code to select:

Salary1 = Worksheets("Income").Cells(4,4)

Method 15 – Don’t Overuse StatusBar Updates

VBA code does calculations faster than displaying the result. So, avoiding the overuse of status bar updates may make the VBA code run faster. When there are lots of operations, you can use status bar updates after doing some operations instead of after doing each operation.

Use the following formula to change the setup to display status bar updates:

For Calculation = 1 To 100 (insert code here)... If Calculation mod 10 = 0 Then Application.StatusBar = Calculation Next Calculatio

Read More: How to Make Excel Calculate Faster

Things to Remember

When using those given codes in Excel, you have to complete the full steps of the VBA coding format.