How to Make VBA Code Run Faster (15 Suitable Ways)

A Visual Basic Applications (VBA) user knows about the importance of why to make VBA code run faster. In this article, I have explained how to make VBA code run faster.

Download Practice Workbook

You can download the practice workbook from here:

15 Ways to Make VBA Code Run Faster

Here, I have described 15 suitable ways about how to make VBA code run faster. The ways are given below.

1. Switch Off Screen Updating to Make VBA Code Run Faster

Keeping screen updates on will make your VBA code slow. Because each time VBA inserts data to the worksheet it reloads the screen image. For that, you have to switch off the screen updates.

You can use the following code for switching off the Screen Updating.

Application.ScreenUpdating = FALSE

Furthermore, you can use the following code for switching on the Screen updating.

Application.ScreenUpdating = TRUE

Read More: How to Make Excel Run Faster with Lots of Data (11 Ways)

2. Shut Off Automatic Spreadsheet Calculation

If you keep the automatic spreadsheet calculation turned on that may impact your VBA code dramatically. While you will change any value in the worksheet then the Excel will recalculate all the cells related to that cell value. This means macro will be inserting those values and VBA will stop until the worksheet will be done.

So, for running faster VBA code this solution is a must. You have to set the calculation as manual.

You can apply the following command to shut off the automatic spreadsheet calculation.

Application.Calculation = xlCalculationManual

In addition, you can 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.

Read More: How to Make Excel Calculate Faster (8 Handy Tips)

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

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

You can use the following code to Copy and Paste by referencing the value itself. By which you can make VBA code run 25 times faster.

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

4. Don’t Complex Formulas to Make VBA Code Run Faster

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

In such a case, there may need to do addition, multiplication, exponentiation, and division. Here, do the exponentiation first then with that result do other operations. Besides, exponentiation takes a long time than other functions.

Basically, the macro can calculate simple formulas faster than complex ones.

5. Disable Events to Make VBA Code Run Faster

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

You can use the corresponding code to disable events.

Application.EnableEvents = False

As well as, you can use the corresponding code to enable events.

Application.EnableEvents = True

Read More: How to Make Excel Open Faster (16 Possible Ways)

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:

• Firstly, you have to go to the top ribbon Developer >> Visual Basic.

• After that, select the tab Insert >> Module.

• Now, write down the corresponding code in 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

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

• Then, go to Developer >> Macros.

• At this time, you have to select Faster_Example from the Macro name.
• Then, press Run.

Finally, you can see the following results. Which will be faster.

Read More: How to Use Select Case Statement in Excel VBA (2 Examples)

Similar Readings

• How to Improve Excel Performance with Large Files (15 Effective Ways)
• Learn Excel VBA Programming & Macros (Free Tutorial – Step by Step)
• How to Stop Calculating 8 Threads in Excel (14 Effective Ways)
• Excel VBA Worksheet Events and Their Uses
• How to Open Large Excel Files Without Crashing (10 Effective Ways)

7. Employ For Each Loop Rather Than For Loop

You can employ the For Each loop. Which is faster than 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

8. Try to Do Less Interact with Worksheet

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

Furthermore, try to not 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 did interact once with the worksheet but still, the following code is the faster one.

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.

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. You must try using the array within the operation in the VBA code.

You can see the following code for a better understanding.

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

Read More: Excel VBA to Populate Array with Cell Values (4 Suitable Examples)

10. Turn Off Alerts to Make VBA Code Run Faster

When you close any workbook without saving it in Excel then, there is an icon box through which you are being alerted whether the workbook is saved or not.

Likewise, 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.

You can use the following command to turn off display alerts.

Application.DisplayAlerts = False

Furthermore, you can use the following command to turn on display alerts.

Application.DisplayAlerts = True

Read More: How to Make Excel Faster on Windows 10 (19 Effective Ways)

11. Avoid Using Variants in Formulas

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

12. Use Boolean Code Instead of Using Strings

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

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

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

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

13. Turn Off Displaying PageBreaks

For making VBA code run faster, you can turn off the displaying pagebreaks. Using this option always may not have an impact but in the case of VBA forcing Excel to count all the page breaks, then this may hamper the speed of VBA code running.

You can employ the following command to turn off displaying page breaks.

ActiveSheet.DisplayPageBreaks = False

Also, you can employ the following command to turn on displaying page breaks.

ActiveSheet.DisplayPageBreaks = True

14. Avoid Selecting Particular Worksheets Unless Important

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

You can use the following code for selection.

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

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.

You can use the following formula to change the setup for displaying status bar updates.

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

Things to Remember

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

Conclusion

I hope you found this article helpful. Here, I have explained the ways of how to make VBA code run faster.

You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Speed up Excel Calculating 4 Processors (16 Effective Ways)
• 22 Macro Examples in Excel VBA
• How to Stop Calculating Threads in Excel (4 Handy Methods)
• List of 10 Mostly Used Excel VBA Objects (Attributes & Examples)
• Open Workbook from Path Using Excel VBA (4 Examples)
• How to Write VBA Code in Excel (With Easy Steps)