Writing Arrays to the worksheet – VBA function

Previous posts have looked at the VBA code for efficiently writing a VBA array to the spreadsheet as quickly as possible, most recently here.

I have now put this code in a function that can be conveniently called from any other VBA routine, with several optional features:

  • Optional “NumRows” and “NumCols” parameters, allowing part of the array to be written to the spreadsheet (default is write the whole array)
  • Optional “ClearRange” parameter, allowing the data in the named range to be cleared before writing the new data.  This may be useful if the range to be written is smaller than the old range, and you wish to keep the old data outside the limits of the new array.  Default is True, i.e. the old data will be cleared.
  • Optional Rowoff and Coloff parameters, allowing the data to be offset from the top-left corner of the specified range.  Default offset values are zero.

Another new feature is that the array to be written may be a worksheet range, providing a quick and convenient way of copying the data in any range as values to another location.

The code is shown below:

Function CopyToRange(VBAArray As Variant, RangeName As String, Optional NumRows As Long = 0, _
                    Optional NumCols As Long = 0, Optional ClearRange As Boolean = True, _
                    Optional RowOff As Long = 0, Optional ColOff As Long = 0) As Long
Dim DataRange As Range

    On Error GoTo RtnError
    If TypeName(VBAArray) = "Range" Then VBAArray = VBAArray.Value2
    If NumRows = 0 Then NumRows = UBound(VBAArray)
    If NumCols = 0 Then NumCols = UBound(VBAArray, 2)
    Set DataRange = Range(RangeName)
    If ClearRange = True Then DataRange.Offset(RowOff, ColOff).ClearContents
    DataRange.Resize(NumRows, NumCols).Name = RangeName
    Range(RangeName).Offset(RowOff, ColOff).Value = VBAArray
    Set DataRange = Nothing
    CopyToRange = 0
    Exit Function

    CopyToRange = 1
End Function

It may also be downloaded from CopyToRange.xlsb, which includes the code plus a short sample code that generates a 4×4 array, writes it to the spreadsheet, then copies that range to another named range on the spreadsheet, producing the output below:

CopyToRange Function

Note that this function must be called from a VBA subroutine, since functions called from the spreadsheet, as a User Defined Function, cannot write to other parts of the spreadsheet.

Finally, another short but useful function is included in the download file, which clears a named range. After clearing the named range is re-sized to 1 row, or optionally to any specified number of rows:

Function ClearRange(RangeName As String, Optional NumRows As Long = 0, _
        Optional RowOff As Long = 0, Optional ColOff As Long = 0) As Long
Dim DataRange As Range, NumCols As Long

    On Error GoTo RtnError

    Set DataRange = Range(RangeName)
    If NumRows = 0 Then NumRows = 1
    NumCols = DataRange.Columns.Count

    DataRange.Offset(RowOff, ColOff).ClearContents
    DataRange.Resize(NumRows, NumCols).Name = RangeName
    Set DataRange = Nothing
    ClearRange = 0
    Exit Function

    ClearRange = 1
End Function
This entry was posted in Arrays, Excel, VBA and tagged , , , , . Bookmark the permalink.

12 Responses to Writing Arrays to the worksheet – VBA function

  1. Pingback: Daily Download 30: Data Transfer, to and from VBA | Newton Excel Bach, not (just) an Excel Blog

  2. Tom says:

    So simple. Thank you.


  3. Dennis Green says:

    This is exactly what I am looking for. I have built an array of ranges from spreadsheet a. The values of the ranges must be placed in spreadsheet b, which is grouped. So first I will need to clear the group – deleting all current values, and I need to keep 1 row, which contains formula, or not, I can save the formulas and paste them back after pasting the pertinent data. I have not tried the code, but I am really excited about just the possibility that you have provided what I am sure will be of tremendous value in accomplishing my goal. Thanks So Much, Dennis Green


  4. Mike says:

    Hello and thanks first of all for the very useful code snippet!
    I am currently trying to enhance my VBA code using Arrays instead of processing ranges. However, I am currently stuck on the following issue:

    How about if only want to write part of an array to a range? I know you can reduce the number of rows and columns by specifying the parameters “NumRows” and “NumCols” but it will always count from the top left corner of the array. As an example, imagine an array of 5×5. How can I write only the the “inner square” of 3×3 of that array into a range?

    Here is what I see as possible solutions:

    1) Use one or two loops to write each cell of the range individually.
    (Probably very inefficient and slow, correct?)

    2) Copy the desired sub-array into another array.
    (This seems to be tricky as ReDim can only alter the right and lower boundary of the array, correct? One could however try to use a loop to copy item by item into the new array. The question is, how efficient is that?)

    3) Use the coordinates of the desired sub-array to access the original source range and then copy to the desired destination range.
    (At least the searching and processing of data was done in the array.)

    4) The “ideal solution”: directly address the sub-array and write it to the desired range. Similar to Range(CellTopLeft,CellBottomRight) just for arrays not for ranges…

    So here we are. I just don’t know how to get a sub-array which is not stuck in the top left corner of the source array…

    I would appreciate your thoughts on this as I am still rather limited in my knowledge of VBA.

    Thanks and best regards


    • Lori says:

      Mike: One other possibility to return the middle square from your 5×5 array (v, say):
      Application.Index(v, Array(Array(2), Array(3), Array(4)), Array(2, 3, 4))
      To make this approach generic you could add a function that returns an array of consecutive integers.

      Doug: Seem to remember there was a post related to this sometime ago?


      • dougaj4 says:

        Thanks Lori, I’d forgotten about that! The post was:

        Based on your one-liner I wrote:

        Function SliceArray(DatRange As Variant, TLR As Long, TLC As Long, NumR As Long, NumC As Long) As Variant
        Dim i As Long, RowA() As Variant, ColA() As Variant

        If TypeName(DatRange) = “Range” Then DatRange = DatRange.Value2

        ReDim RowA(1 To NumR)
        ReDim ColA(1 To NumC)

        For i = 1 To NumR
        RowA(i) = Array(TLR + i – 1)
        Next i

        For i = 1 To NumC
        ColA(i) = TLC + i – 1
        Next i

        SliceArray = Application.Index(DatRange, RowA, ColA)
        End Function

        which seems to work. I’ll elaborate that a little (with some defaults for the return array position and size arguments) and put it in my next post.


  5. dougaj4 says:

    Mike, – good question, I think I’ll do a post on that, but in the mean time probably the simplest way is to create a new empty array of the required size, using Redim, then copy the part of the big array you want with a double loop. That sounds like it should be slow but actually it isn’t, certainly it’s much faster than copying to the spreadsheet one cell at a time.

    An alternative would be to copy the big array to a range object in VBA, then you can use the Offset and Resize methods on this range to copy the bits you want to the spreadsheet.


  6. Mike says:

    Hello again,

    thanks for the interesting thoughts on that. One more idea I had was maybe making use of Worksheetfunction.Index() in a way. Would that be possible in order to grab more of an array than just one item? But then again, how efficient is the use of Worksheetfunction?
    I really think it would be worth having a post on this as the use of arrays is quite important in VBA programming since it can give a plus in performance. The only thing is to consider the all the possibilities how to flexibly handle arrays and then decide which one is the most efficient. Once that has been investigated it would be possible to implement that in a generic subroutine (very similar to the original post). If the most efficient solution was looping through the array item by item in order to copy it to another array before writing it to range then so be it.

    I am really eager to see more on this topic as I certainly will implement the findings in my future programming. 🙂

    Thanks and kind regards


  7. Pingback: Slicing Arrays with VBA | Newton Excel Bach, not (just) an Excel Blog

  8. A very concise solution to a common problem…

    Have you tested it with arrays containing text exceeding 255 chars, and arrays picked up from a range containing an error?

    Also: a hidden ‘gotcha’ in Excel VBA is that you (or other developers using your code) can send a Range reference to your VBAArray parameter, expecting it to pick up the default property of the object (Range.Value, which is usually a variant array). It doesn’t: your explicitly-specified VBAArray variant has been ‘set’ to an object reference, and subsequent code will throw type errors.

    I think that most Excel developers have written their own generic ArrayToRange function to deal with this stuff: see what searching StackOverflow gives you. Most of the answers will look like the code on Excellerando, which I last updated in 2012:


    Beware of older code that uses API calls to check the dimensions of an array: the API function declarations need to go in conditional-compilation blocks to handle ptrSafe and Long declarations in differing 32- and 64-bit environments, and I think that some of the documentation for that is incorrect. I’ve been guilty of worse: I used to call Kernel32 RtlMoveMemory to pick out the dimensions from a Variant’s header bytes for over a decade, and I now regard that as a bad idea.


    • dougaj4 says:

      Nigel – thanks for the comments.

      I hadn’t checked it with errors or long strings, but I have now. Errors seem to be passed OK, and strings up to 32767 characters are OK. After that they get truncated. Obviously the array passed to the CopyToRange function has to be a variant, or something capable of holding error values and/or strings, rather than a double as used in the original test routines.

      I’m afraid I don’t get your point about VBAArray being set to an object reference. If VBAArray is passed as a range it is converted to a variant array (using Value2). Datarange is set to a range, but this is only used to pass the array values to the worksheet, then set to nothing. Am I missing something?

      I will certainly follow up your comments about checking the array dimensions. Catering for 64 bit Excel is something I definitely need to work on.


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