VBA: How to prevent Excel from freezing when using a large loop to update a worksheet?
I'm refactoring my project and I'm trying to implement I am encountering an issue where my Excel application freezes when I run a VBA script to update a worksheet with a large amount of data... The script iterates over a range of approximately 10,000 cells to apply some calculations and formatting, and it causes Excel to become unresponsive. I've tried using `DoEvents` to allow Excel to process other events, but it doesn't seem to help. Here is a simplified version of the code I've been using: ```vba Sub UpdateData() Dim i As Long Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False For i = 1 To 10000 ws.Cells(i, 1).Value = i * 2 ' Some calculation ws.Cells(i, 1).Interior.Color = RGB(255, 255, 255) ' Formatting ' Intentionally slow it down for testing ' Application.Wait (Now + TimeValue("0:00:01")) Next i Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True End Sub ``` I have also considered breaking the loop into smaller chunks or using an array to store the results before writing them to the worksheet all at once, but I am not sure how to implement that effectively. The freezing issue is particularly frustrating because it disrupts user experience. Any guidance on how to make this process more efficient or handle the large dataset without freezing Excel would be greatly appreciated. I've been using Vba for about a year now. Any advice would be much appreciated. What are your experiences with this? I'm working on a CLI tool that needs to handle this. I'd love to hear your thoughts on this.