Since function arrayToString 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
'**********************************************************************************************************
' Name: arrayToString
' Author: mielk | 2013-05-23
'
' Description: Function to convert the given array to String.
'
' Parameters:
' arr Array that String representation is to be returned.
' rowSeparator Optional parameter of a String type.
' It defines a character used to separate rows of the array from each other.
' Default value of this parameter is new line character.
' rowStartTag Optional parameter of a String type.
' It defines a set of characters used to start each row of source array in the
' result string.
' Default value for this parameter is "[".
' rowEndTag Optional parameter of a String type.
' It defines a set of characters used to end each row of source array in the
' result string.
' Default value for this parameter is "]".
' columnSeparator Optional parameter of a String type.
' It defines a character used to separate columns of the array from each other.
' Default value of this parameter is coma (",").
' colStartTag Optional parameter of a String type.
' It defines a set of characters used to start each column of source array in
' the result string.
' Default value for this parameter is "{".
' colEndTag Optional parameter of a String type.
' It defines a set of characters used to end each column of source array in the
' result string.
' Default value for this parameter is "}".
'
'
' Returns:
' String The string representation of the specified array.
'
'
' Exceptions:
' NoArrayException Thrown if parameter [arr] is not an array.
' NotDefinedArrayException Thrown if the given array has not been defined yet.
' TooManyDimensionsException Thrown if the given array has more than 2 dimensions.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-05-23 mielk Function created.
'**********************************************************************************************************
Public Function arrayToString(arr As Variant, _
Optional rowSeparator As String = vbCrLf, _
Optional rowStartTag As String = "[", _
Optional rowEndTag As String = "]", _
Optional columnSeparator As String = ",", _
Optional colStartTag As String = "{", _
Optional colEndTag As String = "}") As String
Const METHOD_NAME As String = "arrayToString"
'------------------------------------------------------------------------------------------------------
'Check if the given parameter [arr] is an array and if it has been already initialized. -------------|
'If it is not an array, code is moved to the label NotArrayException. '|
'If it is an array, but has not been initialized yet, code is moved to the label '|
'NotDefinedArrayException. '|
If Not VBA.IsArray(arr) Then GoTo NotArrayException '|
If Not isDefinedArray(arr) Then GoTo NotDefinedArrayException '|
'----------------------------------------------------------------------------------------------------|
'Function checks the number of dimensions in the given array and call the proper subroutine ---------|
'or move the code to the proper error handler if such array cannot be processed by this function. '|
Select Case countDimensions(arr) '|
'|
Case 1: arrayToString = arrayToString_1D(arr, rowSeparator, rowStartTag, rowEndTag) '|
'|
Case 2: arrayToString = arrayToString_2D(arr, rowSeparator, rowStartTag, rowEndTag, _
columnSeparator, colStartTag, colEndTag) '|
'|
'If the given array has more than two dimensions, it cannot be processed by this function '|
'and the code jumps to error handler TooManyDimensionsException. '|
Case Is > 2: GoTo TooManyDimensionsException '|
'|
End Select '|
'----------------------------------------------------------------------------------------------------|
'==========================================================================================================
ExitPoint:
Exit Function
'----------------------------------------------------------------------------------------------------------
NotArrayException:
'Error handling for the case if the given parameter [arr] is not an array.
GoTo ExitPoint
NotDefinedArrayException:
'Error handling for the case if the given parameter [arr] is an array but it has not been
'initialized yet.
GoTo ExitPoint
TooManyDimensionsException:
'Error-handling for case if the given parameter is an array but it has more than 2 dimensions.
GoTo ExitPoint
End Function
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
'**********************************************************************************************************
' Name: arrayToString_1D
' Author: mielk | 2013-05-23
'
' Comment: Subfunction to convert the given 1D array into String.
'
' Parameters:
' arr Array that String representation is to be returned.
' rowSeparator Defines a character used to separate rows of the array from each other.
' rowStartTag Defines a set of characters used to start each row of source array in the
' result string.
' rowEndTag Defines a set of characters used to end each row of source array in the
' result string.
'
' Returns:
' String The string representation of the specified 1D array.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-05-23 mielk Method created.
'**********************************************************************************************************
Private Function arrayToString_1D(arr As Variant, rowSeparator As String, _
rowStartTag As String, rowEndTag As String) As String
Const METHOD_NAME As String = "arrayToString_1D"
'------------------------------------------------------------------------------------------------------
Dim row As Long
Dim str As String
'------------------------------------------------------------------------------------------------------
'Iterate through each item of the given source array and append it to the final string. -------------|
For row = LBound(arr) To UBound(arr) '|
'|
str = str & rowStartTag & toString(arr(row)) & rowEndTag '|
'|
'If this item is not the last one, the rows separator is being appended. '|
If row < UBound(arr) Then '|
str = str & rowSeparator '|
End If '|
'|
Next row '|
'----------------------------------------------------------------------------------------------------|
'Assign the obtained string to the final result variable.
arrayToString_1D = str
End Function
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
'**********************************************************************************************************
' Name: arrayToString_2D
' Author: mielk | 2013-05-23
'
' Comment: Subfunction to convert the given 2D array to String.
'
' Parameters:
' arr Array that String representation is to be returned.
' rowSeparator Defines a character used to separate rows of the array from each other.
' rowStartTag Defines a set of characters used to start each row of source array in the
' result string.
' rowEndTag Defines a set of characters used to end each row of source array in the
' result string.
' columnSeparator Defines a character used to separate rows of the array from each other.
' colStartTag Defines a set of characters used to start each column of source array in the
' result string.
' colEndTag Defines a set of characters used to end each column of source array in the
' result string.
'
' Returns:
' String The string representation of the specified 2D array.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-05-23 mielk Method created.
'**********************************************************************************************************
Private Function arrayToString_2D(arr As Variant, rowSeparator As String, rowStartTag As String, _
rowEndTag As String, columnSeparator As String, _
colStartTag As String, colEndTag As String) As String
Const METHOD_NAME As String = "arrayToString_2D"
'------------------------------------------------------------------------------------------------------
Dim str As String
Dim strItem As String
'[Iterators]
Dim row As Long
Dim column As Long
'------------------------------------------------------------------------------------------------------
'Iterate through each item of the given source array and append it to the final string. -------------|
For row = LBound(arr, 2) To UBound(arr, 2) '|
'|
'This is the start of new row, so [RowStartTag] is appended. '|
str = str & rowStartTag '|
'|
'Iterate through all the columns in the current row and append their values to the ----------| '|
'final string. '| '|
For column = LBound(arr, 1) To UBound(arr, 1) '| '|
'| '|
str = str & colStartTag & toString(arr(column, row)) & colEndTag '| '|
'| '|
'If this column is not the last one in the current row, the column separator --------| '| '|
'is being appended. '| '| '|
If column < UBound(arr, 1) Then '| '| '|
str = str & columnSeparator '| '| '|
End If '| '| '|
'------------------------------------------------------------------------------------| '| '|
'| '|
Next column '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
'This is the end of the current row, so [RowEndTag] is appended. '|
str = str & rowEndTag '|
'|
'|
'If this column is not the last one in the current row, the column separator is being -------| '|
'appended. '| '|
If row < UBound(arr, 2) Then '| '|
str = str & rowSeparator '| '|
End If '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
Next row '|
'----------------------------------------------------------------------------------------------------|
'Assign the obtained string to the final result variable.
arrayToString_2D = str
End Function
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
'**********************************************************************************************************
' Name: toString
' Author: mielk | 2013-05-23
'
' Comment: Subfunction to get the string representation of the given item.
'
' Parameters:
' item Item which string representation is to be returned.
' It can be any value, no matter if object, array or primitive value.
'
' Returns:
' String String representation of the given object/array/value.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-05-23 mielk Method created.
'**********************************************************************************************************
Private Function toString(item As Variant) As String
Const METHOD_NAME As String = "toString"
'------------------------------------------------------------------------------------------------------
Const OBJECT_LABEL As String = "#Object#"
Const NOTHING_LABEL As String = "#Nothing#"
'------------------------------------------------------------------------------------------------------
'Check if the current item is an object. If it is, try to get its [toString] method. ----------------|
'If this method is inaccessible, use a string defined in OBJECT_LABEL constant. '|
If VBA.IsObject(item) Then '|
'|
'Check if the given object is Nothing. ------------------------------------------------------| '|
If item Is Nothing Then '| '|
'| '|
toString = NOTHING_LABEL '| '|
'| '|
Else '| '|
'| '|
'------------------------------------------------------------------------------------| '| '|
On Error Resume Next '| '| '|
toString = item.toString '| '| '|
On Error GoTo 0 '| '| '|
'------------------------------------------------------------------------------------| '| '|
'| '|
'If string representation has not been assigned above and is still empty, '| '|
'return Object label defined in constant OBJECT_LABEL. '| '|
toString = OBJECT_LABEL '| '|
'| '|
End If '| '|
'--------------------------------------------------------------------------------------------| '|
'|
'|
'If the current item is an array, invoke this function reccurentively to obtain its string '|
'representation. '|
ElseIf VBA.IsArray(item) Then '|
'|
toString = arrayToString(item) '|
'|
Else '|
'|
toString = VBA.CStr(item) '|
'|
End If '|
'----------------------------------------------------------------------------------------------------|
End Function