[SOLVED] Excel – slow VBA execution of simple loop

Issue

I just want to make sure that text inside a column is changed into uppercase.

For this I am using the following code

Private Sub Worksheet_Activate()

Dim cell As Range

For Each cell In Range("$G$6:$G$200")
    cell.Value = UCase(cell.Value)
Next cell

End Sub

In this case the loop runs over ~200 cells but it already takes about 15 seconds to execute. Any ideas?

Solution

This will copy the values into an array, do the ucase and write it back. In such a way you reduce the number of read and write accesses to the sheet which usually slows down the code as every write access might trigger events, recalculations or cause a screen updating.

Private Sub Worksheet_Activate()

    On Error GoTo EH
    Application.EnableEvents = False
    
    Dim rg As Range
    Set rg = Range("$G$6:$G$200")
    
    Dim vdat As Variant
    vdat = rg.Value
    
    Dim i As Long, j As Long
    For i = LBound(vdat, 1) To UBound(vdat, 1)
        For j = LBound(vdat, 2) To UBound(vdat, 2)
            vdat(i, j) = UCase(vdat(i, j))
        Next
    Next
    
    rg.Value = vdat

EH:
    Application.EnableEvents = True
    
End Sub

Answered By – Storax

Answer Checked By – Willingham (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *