DoEvents is used to achieve a pseudo-multi-threading in VBA. Microsoft explains DoEvents as follows:
The DoEvents function surrenders execution of the macro so that the operating system can process other events.
But this is difficult to understand. Actually I do not understand what it is meaning. Now I will explain in better way.
Look at the code below:
Sub test()
Dim i As Long
Dim j As Long
For i = 0 To 100000
j = i
If i = j Then
Range("A1") = i
End If
Next
MsgBox ("Finished")
End Sub
This code takes a lot of time until it finishes executing. This is because comparison of two variables is time-consuming work.
If this code takes 20 seconds until finishing executing, the user of the program needs to wait for 20 seconds without doing nothing. This is ultimately frustrating, isn’t it?
You can solve this problem by using “DoEvents”. See the code below:
In this code, you can see DoEvents is inserted inside the For loop.
Sub test()
Dim i As Long
Dim j As Long
For i = 0 To 100000
j = i
If i = j Then
Range("A1") = i
End If
DoEvents
Next
MsgBox ("Finished")
End Sub
What happens if we execute the code?
If we execute this code, the excel accepts our operation during the execution.The reason why it accepts operation is the For loop has DoEvents inside.If For loop doesn’t have DoEvents inside, the For loop just execute the code without doing anything else.
(For loop 1) -> (For loop 2) -> (For loop 3) -> … -> For loop finishes!
During this process, OS doesn’t accept any other operation.
Meanwhile, by exercuting “DoEvents”, the For loop is interrupted and OS accepts other operation during the interruption. Just after the interruption, another loop is started, so the whole process will be:
(For loop 1) -> (OS accepts other operation) -> (For loop 2) -> (OS accepts other operation) -> … -> For loop finishes!
This loop is repeated very quickly, so this can be interpreted as if the program is accepting other operations even during the For loop process. This is the pseudo-multi-threading in VBA.
So, if the For loop process is time consuming, you can make the program accept other operation made from users by inserting “DoEvents” inside the For loop.
