I'm re-writing my Module of standard methods. These 3 are used to retrieve Workbooks as Workbook Objects. The standard I'm aiming for here is "Third-party Library/Add-in". So:
- Do these functions adhere to the principle of least surprise?
- Have I missed any potential error cases?
As always, general feedback is also welcome.
Public Function GetWorkbook(ByVal strFileName As String, Optional ByVal strFilePath As Variant) As Workbook If Not (IsMissing(strFilePath) Or strFilePath.TypeName = "String") Then PrintErrorMessage "File Path was not supplied as a string", stopExecution:=True End If Dim wbIsOpen As Boolean wbIsOpen = IsWorkbookOpen(strFileName) If wbIsOpen Then Set GetWorkbook = Workbooks(strFileName) Else If Not IsMissing(strFilePath) Then Set GetWorkbook = OpenWorkbook(strFilePath & strFileName) Else PrintErrorMessage "Workbook (" & strFileName & ") is not open, and no filepath was supplied", stopExecution:=True End If End If End Function
Public Function IsWorkbookOpen(ByVal strTargetName As String) As Boolean Dim wbTest As Workbook On Error Resume Next Set wbTest = Workbooks(strTargetName) IsWorkbookOpen = (wbTest.Name = strTargetName) On Error GoTo 0 End Function
Public Function OpenWorkbook(ByVal strFileName As String, strFilePath As String) As Workbook Dim strFullFilename As String strFullFilename = strFilePath & strFileName Dim wbOpenedSuccessfully As Boolean On Error Resume Next Set OpenWorkbook = Workbooks.Open(strFullFilename) wbOpenedSuccessfully = IsWorkbookOpen(strFileName) On Error GoTo 0 If Not wbOpenedSuccessfully Then PrintErrorMessage "Failed to open workbook """ * strFullFilename & """", stopExecution:=True End If End Function