Newton Excel Bach, not (just) an Excel Blog

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