Writing Arrays to the worksheet – VBA function

Posted on March 12, 2012 by dougaj4

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

RtnError:
    CopyToRange = 1
End Function



It may also be downloaded from CopyToRange.zip, 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

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

17 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:
    November 25, 2013 at 9:57 am

    So simple. Thank you.

    Like

    Reply
  3. Dennis Green says:
    March 23, 2014 at 9:37 am

    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

    Like

    Reply
  4. Mike says:
    February 15, 2015 at 5:14 am

    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
    Mike

    Like

    Reply
    • Lori says:
      February 15, 2015 at 7:56 pm

      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?

      Like

      Reply
      • dougaj4 says:
        February 16, 2015 at 9:00 am

        Thanks Lori, I’d forgotten about that! The post was:
        https://newtonexcelbach.wordpress.com/2013/06/23/using-index-as-an-array-function/

        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.

        Like

        Reply
  5. dougaj4 says:
    February 15, 2015 at 9:07 am

    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.

    Like

    Reply
  6. Mike says:
    February 15, 2015 at 7:50 pm

    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
    Mike

    Like

    Reply

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

  8. Nigel Heffernan says:
    May 6, 2015 at 2:15 am

    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:

    http://excellerando.blogspot.co.uk/2012/08/a-generic-vba-array-to-range-function.html

    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.

    Like

    Reply
    • dougaj4 says:
      May 10, 2015 at 12:53 pm

      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.

      Like

      Reply
  9. jokaman says:
    August 4, 2021 at 11:14 pm

    Hi, the file CopyToRange.xlsb, is no longer available, can you send it to myphotogp@gmail.com or refresh the link?
    Thanks
    Jorge

    Like

    Reply
    • dougaj4 says:
      August 5, 2021 at 8:21 am

      Chrome now insists that the download links must start with https: rather than http:, which means that most of the old download links on this blog no longer work. I have now updated the link in this post so it should now work with Chrome. For other downloads options are:
      1. Use a browser other than Chrome.
      2. Use the Downloads page (link at the top of the page).
      3. Leave a message and I will update the link.

      Liked by 1 person

      Reply

Leave a comment

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