How to improve the runtime performance of Excel Application - VBA

Welcome to Logically Proven blog.

This post demonstrates how to improve the runtime performance of Excel VBA (Visual Basic for Programming).

We all do programming but what makes the difference between good and bad programming. They are many which differentiates good and bad programming but one among them is "How fast your code is executing the given task".
 
Consider a scenario - you have to implement a macro where the functionality is to import the worksheet data from a different workbook.

Please have a look at the picture. when user clicks on  "Import Data" button, the workbook imports the data from the user browsed macro-enabled excel file "D:\PerformanceTest\TrainingPlanner.xlsm".

It is a very simple task and I am sure that almost everyone had worked with this scenario.
But while the code is running background ask these questions to yourself?

1. Is it important to update the screen?
2. Is it required to calculate the complete workbook after every cell modification?
3. Is it required to raise events when you modify a cell?
4. Is it necessary to run the events (workbook open and close) when you open a macro-enabled workbook?
5. Is it required to show alerts or warnings to the user?

If your answer is No. Then add the following sub routine in your code before start of your code.

'Method Overview
'***********************************************************************
'name:      App_state
'params:    Boolean, false - to disable
'                    true - to enable
'returns:   none
'comments:  To speed the performance, disabling events, screenupdating,
'           automatic calculation etc.,
'***********************************************************************

Sub App_state(pblnEnable As Boolean)

    With Application
        'To speed up process, turn off calculation
        .Calculation = IIf(pblnEnable, xlCalculationAutomatic, xlCalculationManual)
        .StatusBar = IIf(pblnEnable, "", "Please wait...")
        .Cursor = IIf(pblnEnable, xlDefault, xlWait)
        .ScreenUpdating = pblnEnable
        .EnableEvents = pblnEnable
        .DisplayAlerts = pblnEnable
    End With
End Sub

What actually this sub routine is doing  -

.Calculation - Setting automatic calculation to OFF before start and setting it to ON at the end. This will avoid unnecessary calculations. Calculate a particular sheet or a range only when required.

.Cursor - Setting the cursor mode to wait. This will avoid the application to register the unnecessary clicks on the workbook when the code is running background.

.StatusBar -In the previous step, the cursor mode is in wait mode. So the user may think the application is got stopped. So we are setting the status bar to please wait. So the user know that the application is still runnung.

.ScreenUpdating - It saves a lot of time in terms of  performance. Consider you are opening a new workbook inside the code, in certain cases it is not required to show the workbook to the user. In another case you are doing a lot of modifications in your workbook, you don't want to show all screen updates to the user (jumping from one sheet to another sheet, scrolling through the worksheet). In this scenarios disable the screen updates.

.EnableEvents - Disable the application events if you don't required. For example you are modifying a cell, the application raises a worksheet change event every-time you modify the cell value. By disabling this you can improve the performance time.

.DisplayAlerts - Disable this if you want to hide the unwanted alerts. For example you are deleting a sheet or rows or columns, then the application alerts you "Do you want to delete?". If you want to ignore such alerts disable alerts which improves the performance of your application.

Usage:

Sub ImportData()
    
    Dim wkb As Workbook
    Dim wkbInput As Workbook
    Dim wks As Worksheet
    Dim wksInput As Worksheet
    
    'error handler
    On Error GoTo err_handler
    
    'Improve the performance
    App_state False
    
    'Do your work
    
    'In case of message box, enable screen updates
    Application.ScreenUpdating = True
    MsgBox "Import is successful", vbOKOnly, "Import"
    Application.ScreenUpdating = False
    
    'Before exit
    App_state True
    
    'release the objects
    
    Exit Sub

err_handler:
     App_state True
     MsgBox Err.Description, vbOKOnly, "Error"
     'release the objects
Exit Sub

Now you got an idea how to improve the performance of your application. But there are some things which you need to understand -

• Don't forget to set App_state to true before closing the workbook or in case of error handler or before exiting the sub routine or function.
• Set only screen updating to true if you are popping up a message box to the user. And set back to false once the user interacted with the message box. If you don't do this, dragging the message box creates cluttering on the monitor. Test it then you will understand what actually it does.
• In case of disable events, the sheet events (e.g. worksheet change) will not be triggered. Decide whether you want to disable events or not with respect to context.
• In case of disable events, if you are opening a macro -enabled workbook through your code, it will not trigger the workbook open and close events. If you want to trigger this events set false to disable events.

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.