The Microsoft Excel blog has some tips for speeding up VBA performance in Excel 2007, which apply equally to earlier versions.

The number 2 tip (which in my opinion should have been the number 1 tip, and probably the number 3 to 10 tip as well) was “Read/Write Large Blocks of Cells in a Single Operation”.   This could be expressed more generally as “minimise the number of data transfer operations between the worksheet and VBA”, which then also covers the benefits of writing VBA code rather than using a .worksheetfunction call for functions that are found in Excel but not VBA.

I covered this subject in one my first posts to this blog Ranges and Arrays-2, but looking back at this post I see that the code did not work as intended.  What I wrote was:

With Range("MyNamedRange")
.Resize(NumArrayRows, NumArrayColumns).Name = "MyNamedRange"
.Value = MyArray
End With

This is intended to resize the worksheet range to the same size as the VBA array, then transfer the contents of the array to the worksheet.  The code does resize the range, but because we are still inside the “with” statement the array is transferred to the original range size. If this is smaller than the array the array will be truncated, and if it is larger the cells outside the array dimensions will be filled with “#/NA” symbols.

What we need to do is close the “with” statement, then transfer the data to the now re-sized array.  While we are at it we will normally want to clear old data from the worksheet range, which can be done with a .ClearContents statement within the with block.

Finally for large blocks of data, and where you don’t need the date or currency data types, there is a speed advantage in using .Value2, rather than .Value.  Look here: Transferring information from Excel Ranges to the UDF for the reason why.

So the final code is:

ReDim myarray(1 To NumArrayRows, 1 To NumArrayColumns)

‘ Fill array

With Range(“MyNamedRange”)
.ClearContents
.Resize(NumArrayRows, NumArrayColumns).Name = “MyNamedRange”
End With
Range(“MyNamedRange”).Value2 = myarray

As usual, if this code is copied and pasted into the VBE you will nead to replace all the “smart-quotes” with proper quotes, and put a proper apostrophe before the comment line.