Excel VBA DoEvents Function

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

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

How to use DoEvents Function?

A large amount of VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more 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 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.”

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.

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.

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: –

  • Double Data Type in VBADeclare Array in VBAVBA FreeFileVBA Split