'**********************************************************************************************************
' 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




'**********************************************************************************************************
' Name:         isDefinedArray
' Author:       mielk | 2012-03-27
'
' Description:  Function to check if the given parameter is an array with dimensions and sizes already
'               declared.
'
' Parameters:
'   arr         Parameter to be tested.
'
' Returns:
'   Boolean     True - if parameter [arr] is an array and its dimensions and sizes has been already
'                      defined.
'               False - if parameter [arr] is not an array or it is declared as a dynamic array but its
'                      dimension and size have not been defined yet.
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-03-27        mielk       Function created.
' 2014-08-10        mielk       Case about arrays with negative up bound instead of being not declared.
'**********************************************************************************************************
Public Function isDefinedArray(arr As Variant) As Boolean
    Const METHOD_NAME As String = "isDefinedArray"
    '------------------------------------------------------------------------------------------------------
    Dim upBound As Long
    Dim lowBound As Long
    '------------------------------------------------------------------------------------------------------
    
    
    'Try to assign bottom and top bound of the given parameter.
    'If it is not an array or is not declared yet, code will move to
    'the label NotArrayException and function will return False.
    On Error GoTo NotArrayException
    upBound = UBound(arr, 1)
    lowBound = LBound(arr, 1)
    
    'In some cases, it is possible to get LBound and UBound of a dynamic array
    'althought it is not declared yet (i.e. arrays returned as a result of VBA
    'built-in Split function if empty string is passed as a parameter),
    'however in this case UBound will be lower than LBound.
    isDefinedArray = (upBound >= lowBound)


'==========================================================================================================
ExitPoint:
    Exit Function

'----------------------------------------------------------------------------------------------------------
NotArrayException:
    GoTo ExitPoint
    
End Function




'**********************************************************************************************************
' Name:         countDimensions
' Author:       mielk | 2012-03-03
'
' Comment:      Returns the number of dimensions of the given VBA array.
'
' Parameters:
'   arr         Array for which number of dimensions is to be returned.
'
' Returns:
'   Integer     The number of dimensions of the given VBA array.
'               If the given value is not an array function returns -1.
'               If the given value is declared as a dynamic array but its dimensions have not been
'               declared yet, 0 is returned.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-03-03        mielk       Function created.
' 2014-06-15        mielk       Returned type changed to Integer to allow -1 to be returned.
'                               For non-arrays values -1 is returned.
'**********************************************************************************************************
Public Function countDimensions(arr As Variant) As Integer
    Const METHOD_NAME As String = "countDimensions"
    '------------------------------------------------------------------------------------------------------
    Dim bound As Long
    '------------------------------------------------------------------------------------------------------


    If VBA.IsArray(arr) Then
        
        On Error GoTo NoMoreDimensions
        Do
            bound = UBound(arr, countDimensions + 1)
            countDimensions = countDimensions + 1
        Loop
        
    Else
        
        countDimensions = -1
        
    End If



'----------------------------------------------------------------------------------------------------------
NoMoreDimensions:

End Function







'**********************************************************************************************************
' Name:                 contains
' Author:               mielk | 2014-09-20
'
' Description:          Function to check if the given string contains substring given as a second
'                       parameter.
'
' Parameters:
'   baseString          Base string that will be searched.
'   lookFor             Substring the function is looking for in the base string.
'   isCaseSensitive     Optional parameter of Boolean type.
'                       It determines if text searching is case sensitive.
'                       * If this value is set to True, searching is case sensitive - a letter in lowercase
'                         is treated as different than the same letter in uppercase (i.e. a ? A).
'                       * If this value is set to False, it doesn't matter if a letter is in lowercase or
'                         in uppercase, since both of them are considered as the same character
'                         (i.e. a = A).
'                       * Default value of this parameter is True.
'
'
' Returns:
'   Boolean             True - if the given substring is contained in the specified base text.
'                       False - otherwise.
'
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-09-20        mielk       Function created.
'**********************************************************************************************************
Public Function contains(baseString As String, lookFor As String, _
                                                    Optional isCaseSensitive As Boolean = False) As Boolean
    Const METHOD_NAME As String = "contains"
    '------------------------------------------------------------------------------------------------------
    Dim uCompareMethod As VBA.VbCompareMethod
    '------------------------------------------------------------------------------------------------------
    
    
    'Convert [isCaseSensitive] parameter of Boolean type to the [VbCompareMethod] enumeration. ----------|
    If isCaseSensitive Then                                                                             '|
        uCompareMethod = VBA.vbBinaryCompare                                                            '|
    Else                                                                                                '|
        uCompareMethod = VBA.vbTextCompare                                                              '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'Check if substring is contained in base string by using VBA built-in function VBA.InStr. -----------|
    contains = (VBA.InStr(1, baseString, lookFor, uCompareMethod) > 0)                                  '|
    '----------------------------------------------------------------------------------------------------|
    
    
End Function