More on writing arrays to the worksheet

Writing a large VBA array to the worksheet in a single operation, rather than cell by cell, can result in a huge improvement of the speed of many macros.  The basic technique was described here.  I recently wanted to modify this technique to write a number of arrays from a loop into adjacent ranges on the same sheet, then adjust the range name to cover all the arrays, so they could be cleared in one operation.  This is the code I came up with:

' Clear all old data
Range("DfileRes").ClearContents
' Reset column offset and maximum number of rows counters, and number of columns per array
DatOff = 0
Maxrows = 0
NumCols = 11

For i = 1 to NumOut
' Fill array "Stressa"
' ...
' Get size of output array; Numrows and reset Maxrows if necessary
' ...
If Numrows > Maxrows then Maxrows = Numrows

' Write array to spreadsheet
Range("DfileRes").Offset(0, DatOff).Resize(NumRows, NumCols).Value = Stressa

' Recalculate column offset value, "DatOff"
DatOff = DatOff + NumCols + 1  ' 1 blank column inserted between each output array

Next i

' Resize output range to cover all data
Range("DfileRes").Resize(Maxrows, DatOff).Name = "DfileRes"

The line that does all the work is:

  • Range(“DfileRes”).Offset(0, DatOff).Resize(NumRows, NumCols).Value = Stressa

This generates a range offset from the top left cell of the range “DFileRes” by zero rows and DatOff columns, with a size of NumRows x NumCols, and writes the contents of the array “StressA” into that range.  Note that a range named “DFileRes” must be created in the spreadsheet  for the macro to work.



About these ads
This entry was posted in Arrays, Excel, VBA and tagged , , . Bookmark the permalink.

3 Responses to More on writing arrays to the worksheet

  1. Not just on Excel, how far away from Excel do you go? DO you go away from science as well? What about topics relating to Apple devices and such?

    Christopher

    Like

    • dougaj4 says:

      Christopher – It’s called not (just) an Excel Blog because it covers science, engineering and music as well, but there may be some stuff on javascript and Google Docs creeping in. There isn’t likely to be anything on Apple because I don’t use them, but I may do something on programming for Android tablets, if I ever have time to get into it.

      Like

  2. Pingback: Writing Arrays to the worksheet – VBA function | Newton Excel Bach, not (just) an Excel Blog

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s