Since function convertToStringArray 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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
'**********************************************************************************************************
' Name: convertToStringArray
' Author: mielk | 2014-09-10
'
' Comment: Function to convert the given array of Variant type into the array of String type
' containing the same values.
' It is an array equivalent of the VBA built-in function CStr.
'
' Parameters:
' arr Array to be converted into String type.
' ignoreErrors Optional parameter of Boolean type.
' If this function is set to True, function will ignore all the errors (e.g. if any
' objects are stored in the source array that cannot be converted into string) and
' simply leave empty value instead of that.
' If this function is set to False, function will raise an error if it find any value
' in the source array that cannot be converted into String.
'
' Default value of this parameter is True.
'
'
' Returns:
' String() Array of the String type containing the same values as source array (except for
' all the values that couldn't been converted into String type).
'
'
' Exceptions:
' NotArrayException Thrown if the given parameter [arr] is not an array.
' UninitializedArrayException Thrown if the given parameter [arr] is an array but it has not been
' initialized yet.
' TooManyDimensionsException Thrown if the given array has more than two dimensions.
' IllegalTypeException Thrown if any of the array items cannot be converted to String type
' (unless ignoreErrors parameter is set to True, in such case this item
' is just ignored without raising any errors).
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-09-10 mielk Function created.
'**********************************************************************************************************
Public Function convertToStringArray(arr As Variant, Optional ignoreErrors As Boolean = True) As String()
Const METHOD_NAME As String = "convertToStringArray"
'------------------------------------------------------------------------------------------------------
Dim result() As String
Dim row As Long
Dim col As Long
'------------------------------------------------------------------------------------------------------
'Check if the given parameter [arr] is an array and if it has been already initialized. -------------|
If Not VBA.IsArray(arr) Then GoTo NotArrayException '|
If Not isDefinedArray(arr) Then GoTo UninitializedArrayException '|
'----------------------------------------------------------------------------------------------------|
'Iterate through all the values of the source array and convert them to String type. ----------------|
'There is different iterating logic for 1D and 2D arrays, that is why the function first '|
'how many dimensions the source array has. '|
Select Case countDimensions(arr) '|
'|
'|
'|
'--- Converting 1D arrays. ------------------------------------------------------------------| '|
Case 1: '| '|
'| '|
'Create result array with the proper size. '| '|
ReDim result(LBound(arr) To UBound(arr)) '| '|
'| '|
'Iterate through all the rows in the source array, convert the values to String -----| '| '|
'type and insert them into result array. '| '| '|
For row = LBound(arr) To UBound(arr) '| '| '|
'| '| '|
'Check if the current value is not array or object - in such case it --------| '| '| '|
'cannot be converted into String and added to result array. '| '| '| '|
If VBA.IsObject(arr(row)) Or VBA.IsArray(arr(row)) Then '| '| '| '|
'| '| '| '|
'If [ignoreErrors] parameter is set to False, error will be ---------| '| '| '| '|
'raised. Otherwise this value will be simply ignored. '| '| '| '| '|
If Not ignoreErrors Then '| '| '| '| '|
GoTo IllegalTypeException '| '| '| '| '|
End If '| '| '| '| '|
'--- [If Not ignoreErrors Then] -------------------------------------| '| '| '| '|
'| '| '| '|
Else '| '| '| '|
'| '| '| '|
result(row) = VBA.CStr(arr(row)) '| '| '| '|
'| '| '| '|
End If '| '| '| '|
'------- [If VBA.IsObject(arr(row)) Or VBA.IsArray(arr(row)) Then] ----------| '| '| '|
'| '| '|
Next row '| '| '|
'----------- [For row = LBound(arr) To UBound(arr)] ---------------------------------| '| '|
'| '|
'--------------- [Case 1:] ------------------------------------------------------------------| '|
'|
'|
'|
'|
'--- Converting 2D arrays. ------------------------------------------------------------------| '|
Case 2: '| '|
'| '|
'Create result array with the proper size. '| '|
ReDim result(LBound(arr, 1) To UBound(arr, 1), LBound(arr, 2) To UBound(arr, 2)) '| '|
'| '|
'Iterate through all the columns in the source array --------------------------------| '| '|
For col = LBound(arr, 1) To UBound(arr, 1) '| '| '|
'| '| '|
'Iterate through all the rows in the source array ---------------------------| '| '| '|
For row = LBound(arr, 2) To UBound(arr, 2) '| '| '| '|
'| '| '| '|
'Check if the current value is not array or object - in such -------| '| '| '| '|
'case it cannot be converted into String and added to result array. '| '| '| '| '|
If VBA.IsObject(arr(col, row)) Or VBA.IsArray(arr(col, row)) Then '| '| '| '| '|
'| '| '| '| '|
'If [ignoreErrors] parameter is set to False, error will ----| '| '| '| '| '|
'be raised. Otherwise this value will be simply ignored. '| '| '| '| '| '|
If Not ignoreErrors Then '| '| '| '| '| '|
GoTo IllegalTypeException '| '| '| '| '| '|
End If '| '| '| '| '| '|
'--- [If Not ignoreErrors Then] -----------------------------| '| '| '| '| '|
'| '| '| '| '|
Else '| '| '| '| '|
'| '| '| '| '|
result(col, row) = VBA.CStr(arr(col, row)) '| '| '| '| '|
'| '| '| '| '|
End If '| '| '| '| '|
'------- [If VBA.IsObject(arr(col, row)) Or VBA.IsArray ...] --------| '| '| '| '|
'| '| '| '|
'| '| '| '|
Next row '| '| '| '|
'------- For row = LBound(arr, 2) To UBound(arr, 2)] ------------------------| '| '| '|
'| '| '|
'| '| '|
Next col '| '| '|
'----------- [For col = LBound(arr) To UBound(arr)] ---------------------------------| '| '|
'| '|
'--------------- [Case 2:] ------------------------------------------------------------------| '|
'|
'|
'|
'|
'--- Arrays with more than two dimensions - they cannot be converted. -----------------------| '|
Case Else: '| '|
'If the source array has more than two dimensions, it cannot be converted and '| '|
'TooManyDimensionsException is raised. '| '|
GoTo TooManyDimensionsException '| '|
'--------------- [Case Else:] ---------------------------------------------------------------| '|
'|
'|
'|
End Select '|
'----------------------------------------------------------------------------------------------------|
'Assign the final array to the function's result.
convertToStringArray = result
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
NotArrayException:
'Error handler for the case if the given parameter [arr] is not an array.
GoTo ExitPoint
UninitializedArrayException:
'Error handler for the case if the given array has not been initialized.
GoTo ExitPoint
TooManyDimensionsException:
'Error handler for the case if the given array has more than one dimension.
GoTo ExitPoint
IllegalTypeException:
'Error handler for the case if the given array is static and cannot be resized to add new item.
GoTo ExitPoint
End Function