Convert array to String


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