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.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:
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