如何用数组VBA为单元格批量添加批注以提升处理速度？

Absolutely! Using arrays to minimize worksheet interactions is exactly the fix you need here—direct worksheet I/O (reading/writing cells, adding comments one-by-one) is the biggest bottleneck in your current approach. Shifting most work to memory will make processing your 390,000-cell dataset feel nearly instantaneous compared to your original loop.

Let me walk you through a tailored solution:

Sub SplitValuesAndComments()
    Dim ws As Worksheet
    Dim targetRange As Range
    Dim valueArray As Variant
    Dim commentTextArray As Variant
    Dim rowIdx As Long, colIdx As Long
    Dim tildePosition As Integer
    Dim rawText As String
    Dim commentContent As String
    
    ' Set your target worksheet (update "DataSheet" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("DataSheet")
    ' Define the exact range you need to process (adjust to your 30000x13 range)
    Set targetRange = ws.Range("A1:M30000") ' Or use ws.UsedRange if preferred
    
    ' Read entire range into a memory array (single worksheet read operation)
    valueArray = targetRange.Value
    ' Initialize a matching array to store comment text
    ReDim commentTextArray(1 To UBound(valueArray, 1), 1 To UBound(valueArray, 2))
    
    ' Process all data in memory (no worksheet interactions here!)
    For rowIdx = 1 To UBound(valueArray, 1)
        For colIdx = 1 To UBound(valueArray, 2)
            rawText = CStr(valueArray(rowIdx, colIdx))
            tildePosition = InStr(rawText, "~")
            
            If tildePosition > 0 Then
                ' Extract the left part as the new cell value
                valueArray(rowIdx, colIdx) = Left(rawText, tildePosition - 1)
                ' Extract the right part as comment content
                commentTextArray(rowIdx, colIdx) = Mid(rawText, tildePosition + 1)
            Else
                ' No tilde found? Keep original value, skip comment
                commentTextArray(rowIdx, colIdx) = ""
            End If
        Next colIdx
    Next rowIdx
    
    ' Write processed values back to the sheet in one go (single worksheet write)
    targetRange.Value = valueArray
    
    ' Handle comments (we still loop cells here, but it's far faster now)
    ' First clear existing comments to avoid duplicates
    targetRange.ClearComments
    
    For rowIdx = 1 To UBound(commentTextArray, 1)
        For colIdx = 1 To UBound(commentTextArray, 2)
            commentContent = commentTextArray(rowIdx, colIdx)
            If commentContent <> "" Then
                With targetRange.Cells(rowIdx, colIdx)
                    .AddComment
                    .Comment.Text Text:=commentContent
                    ' Optional: Auto-size comment for better readability
                    .Comment.Shape.TextFrame.AutoSize = True
                End With
            End If
        Next colIdx
    Next rowIdx
    
    MsgBox "Processing finished!", vbInformation
End Sub

Key Speed Boosts Explained: #

• Batch I/O Operations: We read the entire dataset into an array in one step, and write the processed values back in another. This eliminates 390,000 individual cell read/write calls, which are the slowest part of VBA worksheet interactions.
• In-Memory String Splitting: All the ~ splitting logic happens in the array, which is orders of magnitude faster than working directly with worksheet cells.
• Streamlined Comment Handling: While we still loop to add comments, we’ve prepped all comment text in memory first, and cleared existing comments in a single batch to avoid redundant checks.

Extra Performance Tweaks: #

To squeeze out even more speed, add these lines at the start of the sub (and restore them at the end, even if an error occurs):

' Disable slow worksheet features during processing
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

' Add this at the end to restore settings
Cleanup:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description, vbCritical
    Resume Cleanup

内容的提问来源于stack exchange，提问作者F. Valle