Writing an array to a worksheet range – correction

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”)
.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.

This entry was posted in Excel, VBA. Bookmark the permalink.

6 Responses to Writing an array to a worksheet range – correction

  1. Gaggriema says:

    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 🙂


    • Surendra says:

      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


  2. dougaj4 says:

    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.


  3. Pingback: Two Years Old Today « Newton Excel Bach, not (just) an Excel Blog

  4. Pingback: More on writing arrays to the worksheet | Newton Excel Bach, not (just) an Excel Blog

  5. Pingback: excel vba write array to sheet? | Askjis

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.