Excel - slow VBA execution of simple loop-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

Iterating between cells take times. Using an array and dropping the result at the end, iteration will be much faster and writing in each iteration makes the code slower. But Evaluate is more compact and has a similar efficiency:

  Dim rng As Range: Set rng = Range("$G$6:$G$200")
  rng.value = Evaluate("Upper(" & rng.Address & ")")