VBA DoEvents

Excel VBA DoEvents Function

With the help of VBA DoEvents, we can make the code run in the background and simultaneously allows us to work with excel and other application software’s as well. DoEvents not only allows us to work with other software’s rather we can also interrupt the running of the code.

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

VBA-DoEvents

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA DoEvents (wallstreetmojo.com)

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, and even sometimes, it becomes unresponsive.

You can download this VBA DoEvents Excel Template here – VBA DoEvents Excel Template

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. It will easily take more than a minute to execute the task. During the execution, excel hangs for a considerable amount of time to complete the task. During this time, excel shows the message as “Excel Not RespondingExcel Not RespondingWhen the Excel software hangs or freezes in the middle of a task, the "Excel no responding" message pops. There are multiple reasons which leads to such errors and there are multiple ways to troubleshoot his problem as well.read more.”

VBA DoEvents Example 1.3

Moreover, we cannot access the worksheet we are working on. This is a frustrating thing, then how do we make the excel worksheet to be available to work while the code is running behind the screen.

This can be achieved 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

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

GIF

From the above, we can see that the code is running, but we can access the worksheet.

Interrupt the Code Running

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

  • One of the dangers of the DoEvents function is when we switch worksheets or workbooks, and it 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 of the code we have written.

Recommended Articles

This 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 downloadable excel sheet. You can learn more from the following articles –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>