[SOLVED] Excel – slow VBA execution of simple loop


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?


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))
    rg.Value = vdat

    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 *