VBA DoEvents Function

The VBA (Visual Basic for Applications) DoEvents function is a built-in function that allows the execution of other tasks or code while waiting for a specified event to occur. This function is primarily used in long running procedures or processes to prevent any unresponsiveness or freezing of the application.

VBA DoEvents Function – Purpose, Syntax and Arguments

Purpose

The purpose of the DoEvents function is to temporarily pause the execution of the current procedure and let other processes or events occur. This prevents the application from becoming unresponsive and allows the user to perform other tasks while the procedure is running.

Syntax

DoEvents

Arguments

This function does not take any arguments.

Example

Suppose we have a long running procedure in a VBA macro that takes several seconds to complete. During this time, the application becomes unresponsive and the user cannot perform any other tasks. This can be solved by using the DoEvents function. Consider the following code:

For i = 1 to 10000
'long running process
Range("A1").Value = i
'allows other processes to occur
DoEvents
Next i

In the above code, the DoEvents function is used inside the loop which allows the Excel application to refresh and respond to any other events occurring, such as user input or other processes. This prevents the application from becoming unresponsive and allows the user to work on other tasks while the loop is running.

Remarks

• The DoEvents function is typically used in scenarios where a long-running procedure is performing repetitive tasks or calculations. It allows the user to interact with the application while the procedure is still running in the background.
• If the procedure does not contain the DoEvents function, the application will wait for the procedure to complete before responding to any events, making the application unresponsive.
• The DoEvents function cannot be used in a worksheet cell formula. It can only be used in VBA code.
• The DoEvents function will not stop the current procedure, it will only allow other events to occur. Therefore, if there are any errors in the procedure, they will still be raised.
• The use of DoEvents function may slow down the overall performance of the code, as the application needs to switch back and forth between the procedure and other events.
• It is recommended to use the DoEvents function sparingly and only when necessary, as it can lead to unexpected results if not used correctly.

Important Notes

• The DoEvents function is only available in VBA and is not supported in other programming languages.
• The DoEvents function can also be used to release memory during a long-running procedure by allowing VBA to process any events in the background.
• The DoEvents function can be used in combination with other functions, such as ‘Sleep’ or ‘Wait’, to pause code execution for a specific amount of time and allow other events to occur.

Understanding VBA DoEvents Function with Examples

Running a Loop without Freezing the Screen:

Using DoEvents within a loop allows Excel to process other events, like user input or screen updates, preventing the application from appearing frozen.

Sub LoopWithoutFreezing()

Dim i As Long
For i = 1 To 1000000
' Some code here...

DoEvents
Next i

End Sub

Interrupting a Loop with User Input:

You can use DoEvents to allow users to interrupt a loop by, for instance, pressing a button.

Public stopLoop As Boolean

Sub RunLoop()

Dim i As Long
stopLoop = False
For i = 1 To 1000000

' Exit loop if stopLoop is set to True
If stopLoop Then Exit For

' Some code here...

DoEvents
Next i

End Sub

Sub StopTheLoop()
stopLoop = True
End Sub

In this example, if there’s a button assigned to StopTheLoop, pressing that button will change the stopLoop variable to True, allowing you to exit the loop.

Updating Progress Bar:

DoEvents can be used to update a progress bar or a status on the Excel screen to inform the user about the task’s progress.

Sub UpdateProgressBar()

Dim i As Long, totalTasks As Long
Dim progressBar As Shape
totalTasks = 1000

' Assuming you have a rectangle shape named "ProgressBar"
Set progressBar = ThisWorkbook.Sheets("Sheet1").Shapes("ProgressBar")

For i = 1 To totalTasks
progressBar.Width = (i / totalTasks) * 300 ' 300 is the full width of the progress bar

' Some code here...

DoEvents
Next i

End Sub

Preventing Excel from Crashing:

Running intensive tasks for a long duration can sometimes make Excel unresponsive. Using DoEvents helps in yielding control back to the operating system, preventing it from thinking that Excel has crashed.

Sub PreventCrash()

Dim i As Long
For i = 1 To 1000000
' Some intensive task...

If i Mod 100 = 0 Then ' Check every 100 iterations
DoEvents
End If
Next i

End Sub

In this example, DoEvents is triggered every 100 iterations, ensuring that Excel remains responsive without excessively slowing down the main task. Adjusting the frequency of DoEvents is a balance between responsiveness and task efficiency.

Conclusion

The DoEvents function is a very useful tool in VBA when it comes to dealing with long and complex macros, displaying progress to the user, and preventing the screen from freezing. By using this function, the user experience is improved and Excel is less likely to crash. However, it is important to use the function carefully and not rely on it too heavily, as it can also slow down the execution of the code. As always, it is important to thoroughly test and debug any code that uses the DoEvents function to ensure its proper functioning.