'**********************************************************************************************************
' Name:                 removeEmptyRows
' Author:               mielk | 2014-09-20
'
' Description:          Function to remove all the empty rows from the given 2D array.
'
' Parameters:
'   arr                 Array to be cleared from empty rows.
'                       It must have two dimensions. If the given array has more or less dimensions, or
'                       if it is not an array at all, exception will be thrown.
'
'
' Returns:
'   Variant()           The source 2D array without empty rows.
'
'
' 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 -----------------------------------------------------------------------------------------
' 2014-09-20        mielk       Function created.
'**********************************************************************************************************
Public Function removeEmptyRows(arr As Variant) As Variant
    Const METHOD_NAME As String = "removeEmptyRows"
    '------------------------------------------------------------------------------------------------------
    Dim results() As Variant
    Dim col As Long
    Dim row As Long
    Dim resultRow As Long
    Dim isEmpty As Boolean
    '------------------------------------------------------------------------------------------------------
    
    
    'Check if the given parameter [arr] is the proper array, that can be processed by this function. ----|
    '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 jumps to NotDefinedArrayException label. '|
    'If it is an array, but has less or more than two dimensions, code is moved to the label            '|
    'TooManyDimensionsException.                                                                        '|
    If Not VBA.IsArray(arr) Then GoTo NotArrayException                                                 '|
    If Not isDefinedArray(arr) Then GoTo NotDefinedArrayException                                       '|
    If countDimensions(arr) <> 2 Then GoTo TooManyDimensionsException                                   '|
    '----------------------------------------------------------------------------------------------------|
    
    
    
    'Resize the final array to the same size as the original array. It will be trimmed at the end -------|
    'of this function.                                                                                  '|
    ReDim results(LBound(arr, 1) To UBound(arr, 1), LBound(arr, 2) To UBound(arr, 2))                   '|
    resultRow = LBound(arr, 2) - 1                                                                      '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'Iterate through all the rows from the source array. ------------------------------------------------|
    For row = LBound(arr, 2) To UBound(arr, 2)                                                          '|
                                                                                                        '|
        'Check if current row has any non-empty cell. -----------------------------------------------|  '|
        isEmpty = True                                                                              '|  '|
                                                                                                    '|  '|
        'Iterate through all the cells in the current row. --------------------------------------|  '|  '|
        For col = LBound(arr, 1) To UBound(arr, 1)                                              '|  '|  '|
                                                                                                '|  '|  '|
            'If there is at least one non-empty cell, this row is not empty and there is ----|  '|  '|  '|
            'no point in checking rest of cells in this row.                                '|  '|  '|  '|
            If isNonEmptyString(arr(col, row)) Then                                         '|  '|  '|  '|
                isEmpty = False                                                             '|  '|  '|  '|
                Exit For                                                                    '|  '|  '|  '|
            End If                                                                          '|  '|  '|  '|
            '----------- [If isNonEmptyString(arr(col, row)) Then] --------------------------|  '|  '|  '|
                                                                                                '|  '|  '|
        Next col                                                                                '|  '|  '|
        '--------------- [For col = LBound(arr, 1) To UBound(arr, 1)] ---------------------------|  '|  '|
                                                                                                    '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
                                                                                                        '|
        'If this row is not marked as empty, add it to the final array. -----------------------------|  '|
        If Not isEmpty Then                                                                         '|  '|
                                                                                                    '|  '|
            resultRow = resultRow + 1                                                               '|  '|
                                                                                                    '|  '|
            'Iterate through all the columns in the current row and add their values to the -----|  '|  '|
            'final array.                                                                       '|  '|  '|
            For col = LBound(arr, 1) To UBound(arr, 1)                                          '|  '|  '|
                                                                                                '|  '|  '|
                'Before adding value to the array, it has to be checked if the value is -----|  '|  '|  '|
                'an object or a primitive value, because there is a difference in           '|  '|  '|  '|
                'syntex when appending objects and non-objects.                             '|  '|  '|  '|
                If VBA.IsObject(arr(col, row)) Then                                         '|  '|  '|  '|
                    Set results(col, resultRow) = arr(col, row)                             '|  '|  '|  '|
                Else                                                                        '|  '|  '|  '|
                    results(col, resultRow) = arr(col, row)                                 '|  '|  '|  '|
                End If                                                                      '|  '|  '|  '|
                '------- [If VBA.IsObject(value) Then] --------------------------------------|  '|  '|  '|
                                                                                                '|  '|  '|
            Next col                                                                            '|  '|  '|
            '----------- [For col = LBound(arr, 1) To UBound(arr, 1)] ---------------------------|  '|  '|
                                                                                                    '|  '|
                                                                                                    '|  '|
        End If                                                                                      '|  '|
        '--------------- [If Not isEmpty Then] ------------------------------------------------------|  '|
                                                                                                        '|
                                                                                                        '|
    Next row                                                                                            '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'If there are any items in the final array, trim this array to remove empty rows. -------------------|
    If resultRow Then                                                                                   '|
        ReDim Preserve results(LBound(results, 1) To UBound(results, 1), _
                               LBound(results, 2) To resultRow)                                         '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|

    
'==========================================================================================================
ExitPoint:
    
    
    'Assign final array to the result of this function.
    removeEmptyRows = results
    
    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




'**********************************************************************************************************
' 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:                 isNonEmptyString
' Author:               mielk | 2014-09-20
'
' Description:          Function to check if the given value is a non-empty string.
'                       Note that strings consisting only of blank characters (i.e. spaces) are also
'                       considered to be an empty string.
'
' Parameters:
'   value               Value to be checked.
'
'
' Returns:
'   Boolean             True is returned only if the given value or its text representation is a non-empty
'                           string.
'                       False value is returned in a few cases:
'                           * the source parameter [value] is an empty string,
'                           * the source parameter [value] is a string consisting only of blank characters,
'                           * the source parameter [value] is not a string and cannot be converted into
'                             string (i.e it is object or array).
'
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-09-20        mielk       Function created.
'**********************************************************************************************************
Public Function isNonEmptyString(ByVal value As Variant) As Boolean
    Const METHOD_NAME As String = "isNonEmptyString"
    '------------------------------------------------------------------------------------------------------
    
    If Not VBA.IsObject(value) Then
        isNonEmptyString = VBA.Len(removeSpaces(stringify(value))) > 0
    End If
    
End Function




'**********************************************************************************************************
' Name:                 removeSpaces
' Author:               mielk | 2012-11-18
'
' Comment:              Function to remove all the blank characters from the given string.
'
' Parameters:
'   text                String to be cleaned of blank characters.
'                       The given value must be of a String type or of any other type that can be
'                       converted to String.
'                       The following characters are considered by this function as blank:
'
'                                   Character               |  ASCII code
'                                   ------------------------|--------------
'                                   Horizontal Tab          |        9
'                                   Line Feed               |       10
'                                   Carriage Return         |       13
'                                   Space                   |       32
'                                   No-Break Space          |      160
'
' Returns:
'   String              The original text cleaned of blank characters.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-11-18        mielk           Function created.
'**********************************************************************************************************
Public Function removeSpaces(ByVal text As String) As String
    Const METHOD_NAME As String = "removeSpaces"
    '------------------------------------------------------------------------------------------------------

    removeSpaces = VBA.Replace(text, VBA.Chr$(9), "")               '..........................tab
    removeSpaces = VBA.Replace(removeSpaces, VBA.Chr$(10), "")      '.................... linefeed
    removeSpaces = VBA.Replace(removeSpaces, VBA.Chr$(13), "")      '.................... carriage
    removeSpaces = VBA.Replace(removeSpaces, VBA.Chr$(32), "")      '....................... space
    removeSpaces = VBA.Replace(removeSpaces, VBA.Chr$(160), "")     '....................... space

End Function




'**********************************************************************************************************
' Name:                 stringify
' Author:               mielk | 2014-09-20
'
' Description:          Function to convert the given parameter into String.
'                       This function works similar to VBA built-in function VBA.CStr but it doesn't throw
'                       an error if value passed as a input parameter cannot be converted to String.
'
'
' Parameters:
'   value               Value to be converted into String.
'
'
' Returns:
'   String              The string representation of the given parameter.
'                       * For primitives value, function returns the same value as VBA built-in function
'                         VBA.CStr.
'                       * For arrays, function returns all its values converted to String.
'                       * For object, function check if it contains function toString and returns its
'                         result. If there is no such method for this object, value defined in OBJECT_TAG
'                         constant is returned.
'
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-09-20        mielk       Function created.
'**********************************************************************************************************
Public Function stringify(value As Variant) As String
    Const METHOD_NAME As String = "stringify"
    '------------------------------------------------------------------------------------------------------
    Const OBJECT_TAG = "[Object]"
    '------------------------------------------------------------------------------------------------------
   
    On Error Resume Next
    
    'For missing and empty parameters, empty String is returned. ----------------------------------------|
    If Not VBA.IsMissing(value) And Not VBA.isEmpty(value) Then                                         '|
                                                                                                        '|
        'There is different logic for obtaining String representation of object and -----------------|  '|
        'primitive value, so function needs to check which one is the given parameter.              '|  '|
        If VBA.IsObject(value) Then                                                                 '|  '|
            stringify = value.toString                                                              '|  '|
            If VBA.Len(stringify) = 0 Then stringify = OBJECT_TAG                                   '|  '|
        Else                                                                                        '|  '|
            stringify = VBA.CStr(value)                                                             '|  '|
        End If                                                                                      '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|
    
    
End Function