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.
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.
For an example look at the below 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. 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 Responding”.
4.6 (247 ratings) 3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
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.
Sub DoEvents_Example1() Dim i As Long For i = 1 To 100000 Range("A1").Value = i DoEvents Next i End Sub
The moment we add the function DoEvents in code we can access Excel worksheet.
From 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 it overwrite 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.
This has been a guide to VBA DoEvents. Here we learn how and when to use DoEvents function in Excel VBA with the help of an example and downloadable excel sheet. You can learn more from the following articles –