Elite Membership

VBA DoEvents

Written by Jeevan A Y Jeevan A Y Freelance Writer Jeevan, a seasoned data expert with 7 years in MIS reporting, excels in Advanced Excel, VBA, Power BI, and SQL. Currently an Assistant Manager MIS, his insightful data storytelling drives swift decision-making. 7+ years of experience MBA (Finance & Marketing) MIS Reporting View Full Profile
Reviewed by Dheeraj Vaidya, CFA, FRM Dheeraj Vaidya, CFA, FRM Content Reviewer & Course Director A former J.P.Morgan and CLSA Equity Analyst, Dheeraj specializes in financial modeling, AI, forecasting, and valuations. In his career spanning almost two decades, he has trained and mentored more than 100,000 students and professionals on a range of topics. 20+ years of experience CFA, FRM, IIT Delhi, IIM Lucknow Financial Modeling View Full Profile
Updated Dec 23, 2024
Read Time 3 min

Excel VBA DoEvents Function

With the help of VBA DoEvents, we can make the code run in the background and simultaneously allow us to work with excel and other application software. Furthermore, DoEvents allows us to work with other software and interrupt the running of the code.

Download FREE VBA DoEvents Excel Template and Follow Along!
Download Excel Template

The DoEvents function passes control to the operating system of the computer we are working on.

VBA DoEvents

How to use DoEvents Function?

A large amount of VBA code is required when the requirement is huge. In those cases, Excel hangs and stops for some time, sometimes becoming unresponsive.

For example, look at the below code.

Code:

Sub DoEvents_Example1()

    Dim i As Long

    For i = 1 To 100000

        Range("A1").Value = i

    Next i

End Sub
VBA DoEvents Example 1.1

The above code will insert serial numbers from 1 to 100000. Therefore, executing the task will take more than a minute. During the execution, excel hangs for a considerable time to complete the task. During this time, Excel shows the message “Excel Not Responding.”

VBA DoEvents Example 1.3

Moreover, we cannot access the worksheet we are working on. It is a frustrating thing. So how do we make the Excel worksheet available to work while the code is running behind the screen?

We can achieve it by adding a VBA DoEvents function.

Code:

Sub DoEvents_Example1()

     Dim i As Long

     For i = 1 To 100000

          Range("A1").Value = i
          DoEvents

    Next i

End Sub
Example 1.2

When we add the function DoEvents in the code, we can access the Excel worksheet.

GIF

The above shows that the code is running, but we can access the worksheet.

Interrupt the Code Running

When the code runs behind the screen, we can add rows and columns, delete the same, rename the sheet, and do many other things. The moment we add DoEvents, it makes the VBA code run fast and allows us to feel that the mentioned task is running for itself.

  • One of the dangers of the DoEvents function is when we switch worksheets or workbooks, which overwrites the active sheet values.
  • Another danger is if we enter any value to the cell, the code execution halts, and it doesn’t even notify us.

Note: Despite the above dangers of DoEvents, it is still a handy function. We can use DoEvents as part of the debugging process when we try to fix bugs in the code we have written.

Recommended Articles

This article has been a guide to VBA DoEvents. Here, we learn how and when to use the DoEvents function in Excel VBA with the help of an example and a downloadable Excel sheet. You can learn more from the following articles: –