VBA DoEvents

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.

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

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

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.”

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

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

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.