Excel VBA：显式引用当前应用实例及工作簿处理函数问题

Here's a robust VBA function that does exactly what you're asking for—check if a target workbook is already open in a specified Excel instance (or the current one if none is provided), return it if it exists, and open it if it doesn't. I've added some quality-of-life improvements like case-insensitive filename checks and basic error handling to make it more reliable.

Function GetOrOpenWorkbook(targetName As String, Optional app As Excel.Application) As Excel.Workbook
    Dim wb As Excel.Workbook
    
    ' Fall back to the current Excel instance if no app is provided
    If app Is Nothing Then
        Set app = Excel.Application
    End If
    
    ' Loop through all open workbooks in the specified instance
    For Each wb In app.Workbooks
        ' Case-insensitive comparison matches Windows filename behavior
        If StrComp(wb.Name, targetName, vbTextCompare) = 0 Then
            Set GetOrOpenWorkbook = wb
            Exit Function ' Exit early once we find a match
        End If
    Next wb
    
    ' If workbook isn't open, attempt to open it
    On Error Resume Next ' Temporarily suppress errors for file access issues
    Set GetOrOpenWorkbook = app.Workbooks.Open(targetName)
    On Error GoTo 0 ' Reset error handling
    
    ' Optional: Notify user if the workbook couldn't be opened
    If GetOrOpenWorkbook Is Nothing Then
        MsgBox "Failed to open workbook: " & targetName & vbNewLine & "Check if the file exists or you have permission to access it.", vbExclamation
    End If
End Function

Key Features Explained #

• Optional app Parameter: If you don't pass an Excel instance, the function automatically uses the current active Excel application. This is perfect for most scenarios where only one Excel instance is running.
• Case-Insensitive Check: Uses StrComp with vbTextCompare to match filenames regardless of case (since Windows treats "MyFile.xlsx" and "myfile.XLSX" as the same file).
• Early Exit: Stops looping through workbooks as soon as it finds a match, which is more efficient than checking every workbook even after finding the target.
• Basic Error Handling: Catches cases where the file might be missing, locked, or inaccessible, and shows a helpful message to the user.

How to Use It #

Here are a couple of common usage examples:

' Example 1: Use the default (current) Excel instance
Dim myDataWB As Excel.Workbook
Set myDataWB = GetOrOpenWorkbook("SalesData.xlsx")

' Example 2: Specify a specific Excel instance (for multi-instance scenarios)
Dim separateExcelApp As Excel.Application
' Get an existing instance or create a new one
Set separateExcelApp = GetObject(, "Excel.Application") ' Or Set separateExcelApp = New Excel.Application
Set myDataWB = GetOrOpenWorkbook("Inventory.xlsx", separateExcelApp)

Additional Notes #

• Full File Paths: If your target workbook isn't in Excel's default save/load directory, make sure to pass the full file path (e.g., "C:\Documents\SalesData.xlsx") instead of just the filename.
• Error Handling Customization: You can expand the error handling to catch specific errors (like error 1004 for file not found) and handle them differently if needed.
• Multi-Instance Scenarios: If you frequently work with multiple Excel instances, explicitly passing the app parameter ensures you're checking the correct set of open workbooks.

内容的提问来源于stack exchange，提问作者Michael James