Excel VBA DoEvents Function
When we write huge codes, we always fall into the trap of VBA. When the code is big, VBA takes its own time to finish the running and executing process. Which may take time from a minute to several minutes. This is the time frame when everyone gets irritated. And we cannot do anything else in this time period. But to avoid this, we have a function in VBA called DoEvents. By the help of DoEvents, we can run the code in the background and simultaneously we can do other kinds of stuff as well. We cannot only work on other tool or software but also we can interrupt the code or stop in between the process of execution.
Once we enter DoEvents in the code, then apart from VBA, we will also be having the control of work we want to do. The good thing about DoEvents is it doesn’t have any kind of syntax to write. We can directly place Doevents where we want to take control of VBA and perform the tasks as per our need.
How to Use DoEvents Function in Excel VBA?
Below are the different examples to use DoEvents function in excel using VBA code.
Excel VBA DoEvents – Example #1
Although to execute DoEvents we need a huge set of data where we can see how the code is getting interrupted. So, we will use a loop, where the numbers will get applied until the code is completed. For this, follow the below steps:
Step 1: Open a module in VBA from Insert menu tab as shown below.
Step 2: Write the subprocedure of VBA DoEvents or we can choose any name to define the code as per our need.
Code:
Sub VBA_DoEvents() End Sub
Step 3: Now define a variable in any name with data types Long. Purpose of considering the data type as Long is because we will be taking any length of data we want which is not possible if we consider Integer.
Code:
Sub VBA_DoEvents() Dim A As Long End Sub
Step 4: Now open a For-Next loop where we will put the conditions.
Code:
Sub VBA_DoEvents() Dim A As Long For Next A End Sub
Step 5: Consider the range of a number of your choice which can be seen getting implemented. Here we are taking the range 1 to 2000 in defined variable A.
Code:
Sub VBA_DoEvents() Dim A As Long For A = 1 To 20000 Next A End Sub
Step 6: Once we define the range of numbers, we will select the range of cells where we will see the changes in numbers which we chose above. Let’s say the range of cells is A1 to A5. This could be a single cell as well.
Code:
Sub VBA_DoEvents() Dim A As Long For A = 1 To 20000 Range("A1:A5").Value = A Next A End Sub
Step 7: Now run the code by clicking on the Play button located below the menu bar.
We will see, the code has completed by printing the numbers from 1 to 20000 but we couldn’t stop it.
Step 8: To get full control of the code, apply DoEvents after the loop conditions as shown below.
Code:
Sub VBA_DoEvents() Dim A As Long For A = 1 To 20000 Range("A1:A5").Value = A DoEvents Next A End Sub
Step 9: Now again run the code. And at this time we try to stop the code by clicking the stop button, we will notice that code will get stopped between the processes before completing the trial of 20000.
Excel VBA DoEvents – Example #2
There is another type of process for implementing DoEvents. But for this also we will need huge code as seen in example-1. For this, follow the below steps:
Step 1: Write the subprocedure of VBA DoEvents as shown below.
Code:
Sub VBA_DoEvents2() End Sub
Step 2: Directly open a For-next loop without defining a variable.
Code:
Sub VBA_DoEvents2() For Next End Sub
Step 3: Choose a number range we will see running. Let the range be 1 to 20000 same as example-1.
Code:
Sub VBA_DoEvents2() For A = 1 To 20000 Next End Sub
Step 4: Now directly put any work for output, let’s say the output is as shown below. And the value range will be +1 of actual value.
Code:
Sub VBA_DoEvents2() For A = 1 To 20000 Output = Output + 1 Next End Sub
Step 5: Now select the range where we want to see the output. Let the cell be A1.
Code:
Sub VBA_DoEvents2() For A = 1 To 20000 Output = Output + 1 Range("A1").Value = Output Next End Sub
Step 6: And at last we will put DoEvents at the end of For loop condition.
Code:
Sub VBA_DoEvents2() For A = 1 To 20000 Output = Output + 1 Range("A1").Value = Output DoEvents Next End Sub
Step 7: And then run the code by pressing F5 key. We will see, we can do anything even when the code is running. For demonstration, I have selected the cells B1 and C1. The numbers reached at 4348 only which is quite less than the limit we set.
Step 8: If we remove the DoEvents and run the code, we will be not able to do anything until the code gets completes its process. Let’s see that also.
Code:
Sub VBA_DoEvents2() For A = 1 To 20000 Output = Output + 1 Range("A1").Value = Output Next End Sub
Step 9: Now run the code again. As we can see, while we tried to click on Stop, the code didn’t stop. And the Play feature was still ON.
Pros and Cons of Excel VBA DoEvents
- We can work on any tool or file in the background while the VBA code will be running in parallel.
- Using DoEvents doesn’t stop us doing the work we want when the code is running.
- It overwrites the worksheet or workbook when we switch between the different worksheets or workbook.
- The code stops when we write or change the cell for a moment and we don’t get notified.
Things to Remember
- If we do anything without applying DoEvents, then there is a major possibility that the code makes the system and file freezed. And that we will either resume the files once the code is completely run or crash the file on multiple clicks.
- We can see the change in the process while working on the other work simultaneously.
- Once we complete the code, save the file in macro-enabled excel format to avoid losing code.
- Try applying the code in a huge and big set of coding, there we will see the actual implementation and use.
Recommended Articles
This is a guide to VBA DoEvents. Here we discuss how to use DoEvents function in Excel using VBA code along with practical examples and downloadable excel template. You can also go through our other suggested articles –
