excel - 如何避免vba中的双重打开

Question

目标:
制作一个公共(public)常量以在多个函数中用作工作簿。该常量是通过提示用户打开特定文件来获得的。 (这样做是因为文件每月更新，每个月都有一个指定的名称)
如何在不多次打开的情况下引用打开的工作簿？

细节:
我试图通过删除“path = OpenFile()”来避免多次打开。我还尝试将函数公开，变量仍包含在其原始函数中。我无法跨函数使用定义的变量。
该错误指出:

"Subscript out of Range"

或者

"Object Required"

编辑
错误是指

mvmtqt.Activate

以及对先前函数中声明但未在较新函数中重述的变量的任何其他引用。有几个功能不包括为问题腾出空间。

附加的 openfile1() 和 openfile2() 等是指 openfilee() 的副本。

代码:

Public Function OpenFilee() As String
    On Error GoTo Trap

    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        .Title = "Open Sterling Shipment History" 'Name for file
        .InitialFileName = "Dialog start-up path"
        .Filters.Clear
       ' .Filters.Add "Text Documents (*.txt)", "*.txt", 1
        .ButtonName = " Open "
        .AllowMultiSelect = False
    End With

    If fd.Show <> 0 Then OpenFilee = fd.SelectedItems(1)

Leave:
    Set fd = Nothing
    On Error GoTo 0
Exit Function

Trap:
    MsgBox Err.Description, vbCritical
    Resume Leave

End Function


Public Function ShipmentHistPt2()

    Dim path1 As String
    path1 = OpenFile1()
    If path1 <> vbNullString Then Workbooks.Open (path1)
    Dim mvmtln As Workbook
    Set mvmtln = Workbooks.Open(path1)
    Dim path2 As String
    path2 = OpenFile2()
    If path2 <> vbNullString Then Workbooks.Open (path2)
    Dim mvmtqt As Workbook
    Set mvmtqt = Workbooks.Open(path2)
    mvmtqt.Activate
    Sheets("Sheet1").Select

问题:
每当我尝试激活窗口(mvmtqt.Activate)时，它都会再次开始打开文件(如果我再次在函数中定义变量)，这不仅减慢了进程，而且还消除了所有以前的更改。

Answer 1

原码解释

在我添加到下面代码的注释中，您可以看到从用户(通过 filePicker)获取文件路径、打开该文件、然后打开文件以设置为变量的重复模式。

Public Function ShipmentHistPt2()

    Dim path1 As String
    path1 = OpenFile1()                                    'calls function for filePicker
    If path1 <> vbNullString Then Workbooks.Open (path1)   'If file selected, opens file
    Dim mvmtln As Workbook
    Set mvmtln = Workbooks.Open(path1)                     'Opens file and sets to workbook variable
    Dim path2 As String
    path2 = OpenFile2()                                    'calls function for filePicker
    If path2 <> vbNullString Then Workbooks.Open (path2)   'If file selected, opens file
    Dim mvmtqt As Workbook
    Set mvmtqt = Workbooks.Open(path2)                     'Opens file and sets to workbook
    mvmtqt.Activate
    Sheets("Sheet1").Select

由于我不确定您是否想要两个文件(提示并由用户选择)或一个，我将修改单个提示的代码并打开。

此外，使用 .Activate和 .Select可以减慢你的代码。您可以查看更多信息 here关于使用 .Select 的替代方法，如果你愿意的话。如果你 必须激活工作簿，您应该可以使用此行: Workbooks(mvmtln.Name).Activate
请注意，如果所有 Dim陈述在开头。

Public Function ShipmentHistPt2()
    Dim path1 As String
    Dim mvmtln As Workbook

    path1 = OpenFile1()
    If path1 <> vbNullString Then Set mvmtln = Workbooks.Open(path1)

    If Not mvmtln Is Nothing Then 'verifies that mvmtln has been assigned to a workbook
        Workbooks(mvmtln.Name).Activate 'Can un-comment if needed
        Workbooks(mvmtln.Name).Sheets("Sheet1").Select 'Should select the desired sheet
    End If

编辑(全局变量) :

对于全局变量，您希望有一个通用代码模块(不是工作表或工作簿之一)并在那里创建一些全局变量(不要在子或函数中声明它们，否则它们只会在这些地方具有范围):

Dim globalVar_1 as String 'change as needed
Dim globalVar_2 as Long
Dim globalVar_3 as Workbook
'etc......

然后，在打开所需的工作簿后，将全局变量设置为所需的值:

Public Function ShipmentHistPt2()
    Dim path1 As String
    Dim mvmtln As Workbook

    path1 = OpenFile1()
    If path1 <> vbNullString Then Set mvmtln = Workbooks.Open(path1)

    If Not mvmtln Is Nothing Then 'verifies that mvmtln has been assigned to a workbook
        Workbooks(mvmtln.Name).Activate 'Can un-comment if needed
        Workbooks(mvmtln.Name).Sheets("Sheet1").Select 'Should select the desired sheet

        'Added code
        globalVar_1 = Workbooks(mvmtln.Name).Sheets("Sheet1").Range("A1")
        globalVar_2 = Workbooks(mvmtln.Name).Sheets("Sheet1").Range("A2")
        Set globalVar_3 = mvmtln
    End If