DoEvents Function in Excel VBA
DoEvents in Excel VBA allows us to work with other applications of the system we are working on at the time of running the code. 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. VBA DoEvents not only allows us to work other software’s rather we can also interrupt the running of the code.
DoEvents function passed control to the operating system of the computer we are working on.
How to Use DoEvents in VBA Excel?
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.
The above code 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”.
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 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 vba 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 code to run fast and allows us to fell that the mentioned task is running for itself.
- One of the dangers of the VBA DoEvents function is when we switch worksheet or workbooks it overwrite the active sheet values.
- Another danger with this Do Events is, if we enter any value to the cell the code execution halts and it doesn’t even notify us.
Despite the above dangers of DoEvents, it is still a handy function in VBA Excel. We can use DoEvents as part of the debugging process when we try to fix bugs of the code we have written.
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 –