VBA & Macro Problems

How to Update Excel VBA UserForm During a Loop in Real Time

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a way to update and display intermediate results on a VBA UserForm in real-time while a calculation loop is running, instead of waiting for the macro to finish processing.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Running a long-duration iterative calculation loop in Excel VBA that involves visual progress tracking.
Observed behavior: The UserForm freezes and only updates its textboxes or controls after the iterative loop has completely finished.

Before you start

Save a backup of your workbook before modifying and running macro loops, as infinite or resource-heavy loops can sometimes cause the spreadsheet application to stop responding.

Solution 1Recommended

Use a Modeless UserForm and DoEvents

By changing the UserForm properties and telling VBA to periodically yield execution, you can allow the interface to refresh visually during execution.

By default, VBA pauses screen updates and locks the application interface during heavy calculations. To fix this, you must set the UserForm to modeless and instruct the system to process interface events periodically.

To avoid severe performance degradation, it is highly recommended to combine the DoEvents command with a modulo operator so the form only refreshes at set intervals rather than on every single iteration.

Open the VBA Editor

Press ALT + F11 to launch the Visual Basic for Applications window, and locate your UserForm in the Project Explorer.

Set ShowModal to False

Select your UserForm, open the Properties window (press F4), locate the 'ShowModal' property, and change its value from True to False. This allows background processes to continue while the form is visible.

Insert the DoEvents function

Inside your iterative loop code, insert the 'DoEvents' command. This function yields execution so the operating system can process background events like repainting the UserForm textboxes.

Optimize the refresh rate

Wrap the DoEvents function in a modulo operator check (e.g., If i Mod 100 = 0 Then DoEvents) so the screen only refreshes every 100 loops. This maintains optimal macro performance.

Performance Tip: Calling DoEvents on every single loop iteration will significantly slow down your macro's execution speed. Always use a counter or modulo check to refresh periodically.

Advanced Macro Support

Create and Run VBA Macros Seamlessly in WPS Spreadsheets

WPS Office offers robust VBA support, allowing you to write, edit, and execute macros just like in Microsoft Excel. You can easily manage UserForms, handle iterative loops, and use DoEvents to track real-time progress.

1. Open your macro-enabled workbook: Launch WPS Spreadsheets and open your .xlsm file containing the UserForm and loop code.
2. Access the Developer tab: Navigate to the Developer tab on the top ribbon and click on 'Visual Basic' to open the code editor.
3. Modify UserForm properties: Select your UserForm, change the ShowModal property to False in the Properties pane, and ensure DoEvents is implemented in your loop.
4. Run the macro: Execute your macro to observe the UserForm updating in real-time during the calculation loop.

Fully compatible with Microsoft Excel VBA code and macro formats (.xlsm, .xlsb).Lightweight installation ensures fast execution of complex calculation loops.Intuitive Visual Basic Editor to design UserForms and debug code seamlessly.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel macro freeze the screen during a loop?

During a loop, Excel allocates all available system resources to VBA processing. This causes the screen and application interface to freeze until the operation is fully completed, unless explicitly told to yield processing time.

What does the ShowModal property do in a VBA UserForm?

When ShowModal is set to True (the default), the user cannot interact with the rest of the application or run background UI updates until the UserForm is closed. Setting it to False (modeless) allows the code to continue running and the spreadsheet to remain interactive while the form is open.

Does using DoEvents slow down my macro execution?

Yes, calling DoEvents forces the application to pause the macro briefly to process other operating system tasks and interface repaints. Using it too frequently can drastically reduce macro speed, which is why it is best used with a modulo condition to run only periodically.

Excel & Data Problems