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:
.Resize(NumArrayRows, NumArrayColumns).Name = "MyNamedRange"
.Value = MyArray
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
.Resize(NumArrayRows, NumArrayColumns).Name = “MyNamedRange”
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.
Great site this newtonexcelbach.wordpress.com and I am really pleased to see you have what I am actually looking for here and this this post is exactly what I am interested in. I shall be pleased to become a regular visitor 🙂
I am facing a problem when using above code to write an array to excel range.
There are formula in some cells. when I am copying whole array to a range(Cell A1 to A10) it is overwrting formula(in cell A5). Although I am not putting any value in array index(myArray(0,4)). By defalut it is setting value blank.
I want to write Values in cell A1to A10
I have Array myArray(0,9)
Cell A5 has formula(=sum(A1+1))
It should show value 112 in cell A5, But it is showing blank in cell A5.
Is there any way to retain formula in cells and copy entire array to a range.
Thanks in advance
Surendra – when you write an array to the spreadsheet it writes over whatever is there, so even if no value has been written to some element of the array, the corresponding cell will have its contents erased.
One workaround would be to split the array into two, so cell A5 doesn’t get written over.
Pingback: Two Years Old Today « Newton Excel Bach, not (just) an Excel Blog
Pingback: More on writing arrays to the worksheet | Newton Excel Bach, not (just) an Excel Blog
Pingback: excel vba write array to sheet? | Askjis