'**********************************************************************************************************
' Name:                 getVBProject
' Author:               mielk | 2015-03-01
'
' Description:          Function to return Visual Basic Project defined by the given parameter [book].
'
' Parameters:
'   book                Parameter defining what VB project should be returned.
'                       This parameter can take several types of values:
'                       *   object of Excel.Workbook type - in this case VB project assigned to this file
'                           is returned.
'                       **  the name of workbook - in this case the function tries to find a workbook with
'                           such a name among all the open workbooks. If the workbook is found, VB project
'                           assigned to this workbook is returned, otherwise a proper exception is thrown
'                           and the function returns Nothing.
'                       *** the path of the workbook - in this case the function checks if the given path
'                           is correct and tries to open this Excel file. If this workbook could be opened
'                           VBProject assigned to this workbook is returned. Otherwise a proper exception
'                           is thrown and function returns Nothing.
'
'
' Returns:
'   VBProject           Object of VB project type, representing Visual Basic project assigned to the Excel
'                       workbook defined by the input parameter [book].
'
'
' Exceptions:
'   IllegalTypeException            Thrown if the parameter [book] has other type than any of available
'                                   types described above.
'   IllegalWorkbookException        Thrown if the parameter [book] is of Excel.Workbook type, but it cannot
'                                   be used for any reason (i.e. it has been already closed or deleted).
'   WorkbookNotExistException       Thrown if the parameter [book] is a string defining the path or the
'                                   name of a workbook, but the function could not find such a file.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2015-03-01        mielk       Function created.
'**********************************************************************************************************
Public Function getVBProject(book As Variant) As vbProject
    Const METHOD_NAME As String = "getVBProject"
    '------------------------------------------------------------------------------------------------------
    Const STRING_TYPE As String = "String"
    '------------------------------------------------------------------------------------------------------
    Dim wkb As Excel.Workbook
    Dim path As String
    '------------------------------------------------------------------------------------------------------


    'Check the type of the input parameter [wkb] and select the proper logic depending on this type.
    If TypeOf book Is vbProject Then
    
        Set getVBProject = book
    
    ElseIf TypeOf book Is Excel.Workbook Then
        
        'Assign the given parameter [book] to variable [wkb] for data-type compatibility.
        Set wkb = book
        
        'Check if the given workbook is a valid one. If not, throw IllegalWorkbookException. ------------|
        If Not isBookValid(wkb) Then                                                                    '|
            Set wkb = Nothing                                                                           '|
            GoTo IllegalWorkbookException                                                               '|
        Else                                                                                            '|
            Set getVBProject = wkb.vbProject                                                            '|
        End If                                                                                          '|
        '------------------------------------------------------------------------------------------------|
       
    ElseIf VBA.TypeName(book) = STRING_TYPE Then
        
        'Assign the given parameter [book] to variable [path] for data-type compatibility.
        path = stringify(book)
        
        '------------------------------------------------------------------------------------------------|
        If fileExists(path) Then                                                                        '|
                                                                                                        '|
            'Try to open a workbook with the given path. If the path is not found and exception is      '|
            'raised by the function [openFile], catch this exception and go to                          '|
            '[WorkbookNotExistException] where this function's own exception is thrown.                 '|
            On Error GoTo WorkbookNotExistException                                                     '|
            Set wkb = openWorkbook(path, False)                                                         '|
                                                                                                        '|
        Else                                                                                            '|
                                                                                                        '|
            'Try to find workbook as if the given parameter [book] was its name only.                   '|
            On Error GoTo WorkbookNotExistException                                                     '|
            Set wkb = Excel.Workbooks(path)                                                             '|
                                                                                                        '|
        End If                                                                                          '|
        '------------------------------------------------------------------------------------------------|
       
       
        'Check if a workbook has been successfully assigned to variable [wkb]. If it has, ---------------|
        'assign its VBA project to the result of the function.                                          '|
        If Not wkb Is Nothing Then                                                                      '|
                                                                                                        '|
            Set getVBProject = wkb.vbProject                                                            '|
                                                                                                        '|
        Else                                                                                            '|
                                                                                                        '|
            'If workbook with such filepath cannot be opened WorkbookNotExistException                  '|
            'exception is thrown.                                                                       '|
            On Error GoTo WorkbookNotExistException                                                     '|
                                                                                                        '|
        End If                                                                                          '|
        '------------------------------------------------------------------------------------------------|
        
        
    Else
        
        'If function reaches this point, it means that the parameter [book] was of illegal type and
        'exception IllegalTypeException is thrown.
        GoTo IllegalTypeException
        
    End If



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


'----------------------------------------------------------------------------------------------------------
IllegalTypeException:
    '(...)
    'Error handling for a case if parameter [book] is of other type than Excel.Workbook or String.
    
    GoTo ExitPoint


IllegalWorkbookException:
    '(...)
    'Error handling for a case if parameter [book] is of Excel.Workbook type but it cannot be
    'referred (i.e. it has been closed or deleted).
    
    GoTo ExitPoint


WorkbookNotExistException:
    '(...)
    'Error handling for a case if parameter [book] is of String type, but function could not find
    'any workbook with such name or file path.
    
    GoTo ExitPoint


End Function




'**********************************************************************************************************
' Name:                 isBookValid
' Author:               mielk | 2013-04-25
'
' Comment:              Function to check if the given Excel workbook is valid and you can refer to its
'                       properties and methods without errors.
'
'                       Using this function is very helpful since it lets you avoid
'                       Run-time error '-2147221080 (800401a8)': Automation error.
'                       This error is generated, when the code tries to refer to a property or a method
'                       of an Excel workbook that had been already closed.
'
' Parameters:
'   wkb                 The Excel workbook to be checked.
'
' Returns:
'   Boolean             True - if the given workbook is valid and you can refer to it without any errors.
'                       False - if the given workbook is invalid, that means it is corrupted or had been
'                               closed or deleted before this method has been called and referring to it
'                               will raise an error.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-04-25        mielk       Function created.
'**********************************************************************************************************
Public Function isBookValid(wkb As Excel.Workbook) As Boolean
    Const METHOD_NAME As String = "isBookValid"
    '------------------------------------------------------------------------------------------------------
    Dim strBookName As String
    '------------------------------------------------------------------------------------------------------
    
    On Error Resume Next
    strBookName = wkb.name
    
    'Check method is very easy - if the name of the given workbook has been assigned to the variable
    '[strBookName], this workbook is valid and you can refer to it. Otherwise, error would be generated
    'and step [strBookName = wkb.name] would be skipped because of [On Error Resume Next] statement above.
    isBookValid = VBA.Len(strBookName)
    
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






'**********************************************************************************************************
' Name:                 fileExists
' Author:               mielk | 2012-12-02
'
' Comment:              Function to check if file with the given filepath exists in the file system.
'                       This is a wrapping function for method .fileExists of FileSystemObject class that
'                       lets a user avoid declaring a new instance of FileSystemObject class when invoking
'                       this function.
'
' Parameters:
'   folderPath          The path of a file to be checked.
'
'
' Returns:
'   Boolean             True - if file with the given filepath exists.
'                       False - otherwise.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-12-02        mielk           Function created.
'**********************************************************************************************************

Public Function fileExists(filepath As String) As Boolean
    Const METHOD_NAME As String = "fileExists"
    '------------------------------------------------------------------------------------------------------
    Static objFSO As Object         '(Late binding that allows to use the function, even if
                                    'Microsoft Scripting Runtime library is not loaded)
    '------------------------------------------------------------------------------------------------------

    
    'Create FileSystemObject instance if it hasn't been created yet. ------------------------------------|
    If objFSO Is Nothing Then                                                                           '|
        Set objFSO = VBA.CreateObject("Scripting.FileSystemObject")                                     '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|


    fileExists = objFSO.fileExists(filepath)
    

End Function





'**********************************************************************************************************
' Name:                 openWorkbook
' Author:               mielk | 2014-02-26
'
' Comment:              Function to open the Excel file with the given filepath in the specified Excel
'                       application instance.
'
' Parameters:
'   filePath            The path of a file to be opened.
'   readOnly            Optional parameter of Boolean type.
'                       It determines if the file should be open in read-only mode.
'                       Default value for this parameter is False, so file is open in write-read mode.
'   excelInstance       Excel instance to be used when opening the specified file.
'   createIfNotExists   Optional parameter of Boolean type.
'                       It determines if the file should be created if it not exists yet.
'
'
' Returns:
'   Excel.Workbook      Reference to the Excel file with the specified filepath.
'
'
' Exceptions:
'   PathNotFoundException           Thrown if the given path has not been found.
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-02-26        mielk       Function created.
'**********************************************************************************************************
Public Function openWorkbook(filepath As String, Optional readOnly As Boolean = False, _
                         Optional excelInstance As Excel.Application, _
                         Optional createIfNotExists As Boolean = False) As Excel.Workbook
    Const METHOD_NAME As String = "openWorkbook"
    '------------------------------------------------------------------------------------------------------
    Dim xls As Excel.Application
    Dim bAlerts As Boolean
    '------------------------------------------------------------------------------------------------------
    
        
    'Find out what Excel application instance should be used for checking. ------------------------------|
    If excelInstance Is Nothing Then                                                                    '|
        Set xls = Excel.ThisWorkbook.Application                                                        '|
    Else                                                                                                '|
        Set xls = excelInstance                                                                         '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|


    'Switch off the alerts when opening workbook. Alerts settings will be restored at the end of --------|
    'this function.                                                                                     '|
    With xls                                                                                            '|
        bAlerts = .DisplayAlerts                                                                        '|
        .DisplayAlerts = False                                                                          '|
    End With                                                                                            '|
    '----------------------------------------------------------------------------------------------------|


    If isFileOpen(filepath, xls) Then
        Set openWorkbook = xls.Workbooks(getFileName(filepath))
    Else
        
        If fileExists(filepath) Then
            Set openWorkbook = xls.Workbooks.Open(filepath, readOnly:=readOnly, UpdateLinks:=False)
        End If
        
    End If
  
  
    'Restoring [DisplayAlerts] setting. -----------------------------------------------------------------|
    'It is not critical, so if any error occurred just skip it and continue.                            '|
    On Error Resume Next                                                                                '|
    xls.DisplayAlerts = bAlerts                                                                         '|
    On Error GoTo 0                                                                                     '|
    '----------------------------------------------------------------------------------------------------|
  
  
End Function




'**********************************************************************************************************
' Name:                 isFileOpen
' Author:               mielk | 2014-02-26
'
' Comment:              Function checks if the given file is open in the specified Excel instance.
'
' Parameters:
'   filePath            The path of a file to be checked.
'   excelInstance       Excel instance to be searched through for the given file.
'                       If this parameter is ommitted, the current Excel instance is used.
'
' Returns:
'   Boolean             True - if the given Excel file is open in the specified Excel instance.
'                       False - otherwise.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-02-26        mielk       Function created.
'**********************************************************************************************************
Public Function isFileOpen(filepath As String, Optional excelInstance As Excel.Application) As Boolean
    Const METHOD_NAME As String = "isFileOpen"
    '------------------------------------------------------------------------------------------------------
    Dim xls As Excel.Application
    Dim fileName As String:             fileName = getFileName(filepath)
    Dim wkb As Excel.Workbook
    '------------------------------------------------------------------------------------------------------
    
    
    'Find out what Excel application instance should be used for checking. ------------------------------|
    If excelInstance Is Nothing Then                                                                    '|
        Set xls = Excel.ThisWorkbook.Application                                                        '|
    Else                                                                                                '|
        Set xls = excelInstance                                                                         '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'Try to get the reference to the specified file from the given Excel instance. If nothing -----------|
    'is assigned, it means there is no such file open in this Excel instance.                           '|
    On Error Resume Next                                                                                '|
    Set wkb = xls.Workbooks(fileName)                                                                   '|
    On Error GoTo 0                                                                                     '|
    '----------------------------------------------------------------------------------------------------|

    
    '----------------------------------------------------------------------------------------------------|
    If wkb Is Nothing Then                                                                              '|
        isFileOpen = False                                                                              '|
    Else                                                                                                '|
        isFileOpen = compareString(wkb.FullName, filepath)                                              '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|
    
    
End Function





'**********************************************************************************************************
' Name:                 getFileName
' Author:               mielk | 2014-02-26
'
' Comment:              Function returns the name and extension of the given full filepath.
'
' Parameters:
'   filepath            The path of a file which name is to be returned.
'
' Returns:
'   String              The name of the given file (including extension).
'                       If the given parameter is not a proper file path, an empty String is returned.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-03-26        mielk       Function created.
'**********************************************************************************************************
Public Function getFileName(filepath As String) As String
    Const METHOD_NAME As String = "getFileName"
    '------------------------------------------------------------------------------------------------------
    Dim slashPosition As Integer
    '------------------------------------------------------------------------------------------------------
    
    slashPosition = VBA.InStrRev(filepath, "\")
    getFileName = VBA.Mid(filepath, slashPosition + 1)
    
End Function






'**********************************************************************************************************
' Name:                 compareString
' Author:               mielk | 2013-05-15
'
' Comment:              Function compares two strings and returns True if they are equal.
'                       The difference between this function and function compareStrings is that this
'                       function can compare only two strings but it is much faster, so it is recommended
'                       to use this function instead of [compareStrings] if there is no need to compare
'                       more than two strings to each other.
'
'
' Parameters:
'   baseString          Base string.
'   comparedString      String to be compared to the base string.
'   isCaseSensitive     Optional parameter of Boolean type.
'                       It defines if comparing is case-sensitive.
'                       Default value of this parameter is True, that means comparing is case-insensitive.
'   trimmed             Optional parameter of Boolean type.
'                       It defines if white spaces at the beginning and at the end of strings should be
'                       ignored.
'                       Default value of this parameter is True.
'
'
' Returns
'   Boolean             True - if both given strings are equal (excluding white spaces if [trimmed]
'                               parameter is set to True).
'                       False - if given strings differ from each other or if any of string is null.
'
'
' Exceptions:
'   IllegalObjectException          Thrown if parameter [baseString] or [comparedString] passed to this
'                                   functions are objects, arrays or other values that cannot be converted
'                                   to String.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-05-15        mielk           Method created.
'**********************************************************************************************************
Public Function compareString(baseString As Variant, comparedString As Variant, _
                Optional isCaseSensitive As Boolean = False, Optional trimmed As Boolean = True) As Boolean
    Const METHOD_NAME As String = "compareString"
    '------------------------------------------------------------------------------------------------------
    Dim compareMethod As VBA.VbCompareMethod
    Dim base_ As String
    Dim compared_ As String
    '------------------------------------------------------------------------------------------------------
    
    
    'Fast check if both strings are exactly the same. In such case True is returned without -------------|
    'taking any further actions.                                                                        '|
    If baseString = comparedString Then GoTo EqualStrings                                               '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'Check if any given string is Null. In such case False is returned (even if both strings are null). -|
    If VBA.isNull(baseString) Or VBA.isNull(comparedString) Then GoTo NullStrings                       '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'Check if both given strings can be converted into Strings. If at least one of them cannot be -------|
    'converted, IllegalObjectException is thrown.                                                       '|
    On Error GoTo IllegalObjectException                                                                '|
    base_ = VBA.CStr(baseString)                                                                        '|
    compared_ = VBA.CStr(comparedString)                                                                '|
    On Error GoTo 0                                                                                     '|
    '----------------------------------------------------------------------------------------------------|
   
    'Convert parameter [isCaseSensitive] of Boolean type to VbCompareMethod type. -----------------------|
    compareMethod = VBA.IIf(isCaseSensitive, VBA.vbBinaryCompare, VBA.vbTextCompare)                    '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'If the [trimmed] parameter is set to True, remove all white spaces at the beginning and at the -----|
    'end of the input strings.                                                                          '|
    If trimmed Then                                                                                     '|
        base_ = VBA.Trim$(base_)                                                                        '|
        compared_ = VBA.Trim$(compared_)                                                                '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|


    
    'Check if strings are the same, by using VBA built-in function StrComp.
    compareString = (VBA.StrComp(base_, compared_, compareMethod) = 0)
    


'==========================================================================================================


ExitPoint:
    Exit Function


'----------------------------------------------------------------------------------------------------------
EqualStrings:
    compareString = True
    GoTo ExitPoint


NullStrings:
    compareString = False
    GoTo ExitPoint


IllegalObjectException:
    'Error handler for the case if objects, arrays or any other values that cannot be converted to
    'String have been passed as an input parameters.
    GoTo ExitPoint


End Function