Since function pasteData uses some non-built VBA functions, they also must be included in your code for the function to work properly.
Otherwise the following error will occur: Compile error: Sub or Function not defined.
Required functions are listed below. You can get to each function's source code by clicking its name:
When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
'**********************************************************************************************************
' Name: pasteData
' Author: mielk | 2013-04-26
'
' Comment: Method to paste the given array into the specified range of the given
' Excel worksheet.
'
' Parameters:
' data Data array to be pasted into Excel worksheet.
' initRange Top-left cell of the range, where the data array is to be pasted.
' transponeData Optional parameter of Boolean type. It defines if data should be transponed
' before being pasted into Excel worksheet.
' clearContents Optional parameter of Boolean type. It determines if the given Excel worksheet
' should be cleared before pasting new data.
'
'
' Exceptions:
' InvalidRangeException Thrown if the range given to the function as the [initRange] parameter
' is invalid - i.e. the workbook it belongs to has been closed.
' InvalidDataFormat Thrown if the data given to this method are not 2D array and cannot be
' converted to 2D array.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-04-26 mielk Function created.
'**********************************************************************************************************
Public Sub pasteData(data As Variant, initRange As Excel.Range, Optional transponeData As Boolean = True, _
Optional clearSheet As Boolean = False)
Const METHOD_NAME As String = "pasteData"
'------------------------------------------------------------------------------------------------------
Dim arr As Variant
Dim wks As Excel.Worksheet
Dim rng As Excel.Range
'------------------------------------------------------------------------------------------------------
Dim firstRow As Long
Dim firstCol As Long
Dim lastRow As Long
Dim lastCol As Long
'------------------------------------------------------------------------------------------------------
'Check if the given initial range is valid. If not, method cannot continue and code jumps to --------|
'the label InvalidRangeException. '|
If Not isRangeValid(initRange) Then GoTo InvalidRangeException '|
'----------------------------------------------------------------------------------------------------|
'Only 2D arrays can be printed by this function, so if the given array has only one dimension -------|
'convert it to 2D. It can be done by using function to2DArray (for 2D arrays this function '|
'made no modifications to the source array). '|
arr = to2DArray(data) '|
'----------------------------------------------------------------------------------------------------|
'Check if the array returned by the function to2DArray has in fact two dimensions. It must be -------|
'checked since this function returns empty value if a source array is not defined yet or has more '|
'than two dimensions. '|
If countDimensions(arr) <> 2 Then GoTo IllegalDataFormat '|
'----------------------------------------------------------------------------------------------------|
'Function reaches this point only if variable [arr] has been successfully converted to 2D array. ----|
With initRange '|
'|
'Set the reference to the Excel worksheet and unprotect it in order to paste data. ----------| '|
Set wks = .parent '| '|
Call wks.Unprotect '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
'Clear the content of the worksheet if [clearSheet] parameter is set to True. ---------------| '|
If clearSheet Then Call wks.Cells.clearContents '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
'Transpone the data array if [transponeData] parameter is set to True. ----------------------| '|
If transponeData Then arr = transposeArray(arr) '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
'Calculate the destination range of the given data ... --------------------------------------| '|
firstRow = .row '| '|
firstCol = .column '| '|
lastRow = .row + arraySize(arr, 1) - 1 '| '|
lastCol = .column + arraySize(arr, 2) - 1 '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
'|
End With '|
'----------------------------------------------------------------------------------------------------|
'... and paste them into this range. ----------------------------------------------------------------|
With wks '|
Set rng = .Range(.Cells(firstRow, firstCol), .Cells(lastRow, lastCol)) '|
End With '|
rng = arr '|
'----------------------------------------------------------------------------------------------------|
'==========================================================================================================
ExitPoint:
Exit Sub
'----------------------------------------------------------------------------------------------------------
InvalidRangeException:
'Error handling for the case if the given range is not valid and cannot be referred.
GoTo ExitPoint
IllegalDataFormat:
'Error handling for the case if the given data array is not 2D array and cannot be converted to 2D
'array.
GoTo ExitPoint
End Sub