Often, best performance is achieved by avoiding the use of
Range as much as possible. In this example we read in an entire
Range object into an array, square each number in the array, and then return the array back to the
Range. This accesses
Range only twice, whereas a loop would access it 20 times for the read/writes.
Option Explicit Sub WorkWithArrayExample() Dim DataRange As Variant Dim Irow As Long Dim Icol As Integer DataRange = ActiveSheet.Range("A1:A10").Value ' read all the values at once from the Excel grid, put into an array For Irow = LBound(DataRange,1) To UBound(DataRange, 1) ' Get the number of rows. For Icol = LBound(DataRange,2) To UBound(DataRange, 2) ' Get the number of columns. DataRange(Irow, Icol) = DataRange(Irow, Icol) * DataRange(Irow, Icol) ' cell.value^2 Next Icol Next Irow ActiveSheet.Range("A1:A10").Value = DataRange ' writes all the results back to the range at once End Sub
More tips and info with timed examples can be found in Charles Williams's Writing efficient VBA UDFs (Part 1) and other articles in the series.