将工作簿声明为全局变量

Question

将工作簿声明为全局变量

17

我正在编写一个通用于多个工作簿的代码，但始终使用相同的参考工作簿。该代码将有许多子程序，因此我尝试避免在每个子程序中为参考工作簿定义变量，我希望将它们声明为全局变量。

最初我是这样写的：

Global Locations As Excel.Workbook
Set Locations = Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx")

这给了我如下提示：

"编译错误：过程外无效"

在进行一些搜索后，我在某个地方发现了以下代码：

Public Const Locations As Excel.Workbook = "Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx")"

这给了我：

"编译错误：期望：类型名称"

编辑：

使用：

Public Const Locations As Excel.Workbook = "Workbooks.Open('M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx')"

(在 Workbooks.Open 语句中使用单引号和双引号会产生相同的错误。)谁知道我做错了什么？

编辑2：

我还尝试在“ThisWorkbook”中声明变量，按照此答案使用：

Private Sub Workbook_Open()
Dim Locations As Excel.Workbook
Dim MergeBook As Excel.Workbook
Dim TotalRowsMerged As String


Locations = Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx")
MergeBook = Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\DURUM IT yields merged.xlsm")
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count
End Sub

但是然后它会返回一个

"缺少对象"

在我的模块中。

编辑3：

现在我有了这个可以运行的代码，但是缺点是需要将SET行复制到每一个Sub中，肯定有更好的方法来实现吧？

Global Locations As Workbook
Global MergeBook As Workbook
Global TotalRowsMerged As String

Sub Fill_CZ_Array()
Set Locations = Application.Workbooks("locXws.xlsx")
Set MergeBook = Application.Workbooks("DURUM IT yields merged.xlsm")
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count

- Luuklag

你不能使用某种个人工作簿吗？然后这样的工作簿在所有其他工作簿中都可用，例如：Debug.Print Workbooks("PERSONAL.XLSB").Sheets(1).Name。 - Daniel Dušek

你将使用“Locations”中的多少个不同工作表？ - ManishChristian

位置有多个正在使用的工作表。我希望有一个能够处理“无限”数量的工作表的解决方案，以便在各种情况和/或问题下可以重复使用，其他成员也可以使用。 - Luuklag

你的带有代码的工作簿需要可见吗？换句话说，你会使用工作簿中的表格，还是只使用其中的代码？如果只使用代码，则将工作簿保存为插件。 - Excel Developers

11个回答

6

你的问题意味着你想要一个全局工作簿常量，而不是一个变量。因为VBA不允许在过程外初始化对象，所以你不能拥有一个对象常量。最好的方法是在事件中初始化一个公共工作簿变量。

你可以声明一个全局变量，但不能在过程外执行代码来赋值：

Public myBook As Excel.Workbook

Sub AssignWorkbook()
    Set myBook = Workbooks.Open("C:\SomeBook.xlsx") '// <~~ valid, inside sub
End Sub

Sub TestItWorked()
    MsgBox myBook.Name
End Sub

在一个普通模块中，您可以有以下内容：

Public myBook As Excel.Workbook

在你的Workbook_Open()事件中：

Private Sub Workbook_Open()
    Set myBook = Workbooks.Open("C:\SomeOtherBook.xlsx")
End Sub

这样你就可以在代码的其他地方使用 myBook，而不必重新分配它。

也许值得看一看Chip Pearson关于VBA变量作用域的文章，请点击此处

- SierraOscar

是的，我们已经知道了，但是还没有解决如何执行这段代码的问题。你应该在任何想要运行的子程序中引用这个 AssignWorkbook() 子程序，或者在打开时运行这个子程序。 - Luuklag

请参见编辑，了解如何在打开事件上分配值。这意味着您只需分配一次即可。 - SierraOscar

是的，但我一个小时前也与user3964075讨论过这种情况。这只是一个权宜之计。我正在寻找一个“干净”的解决方案。 - Luuklag

1

就VBA而言，这就是清晰的解决方案。工作簿是一个对象类，直到使用“Set”关键字进行赋值，它才没有任何值。这是在Excel中将工作簿分配给变量的唯一方法-你无法拥有一个工作簿常量。 - SierraOscar

6

您需要的是具有静态属性的工厂，例如在一个单独的模块中。 mFactoryWkbs

Private m_WkbLocations           As Workbook
Private m_WkbMergeBook           As Workbook

Public Property Get LOCATIONS() As Workbook
    If m_WkbLocations Is Nothing Then
        Set m_WkbLocations= Workbooks.Open("wherever")
    End If
    Set LOCATIONS = m_WkbLocations
End Property

Public Property Get MERGEBOOK () As Workbook
    If m_WkbMergeBook Is Nothing Then
        Set m_WkbMergeBook = Workbooks.Open("wherever")
    End If
    Set MERGEBOOK = m_WkbMergeBook 
End Property

只需在需要的位置调用属性即可，无需额外变量（或为其设置）。

TotalRowsMerged = MERGEBOOK.Worksheets("Sheet1").UsedRange.Rows.Count

- Berryl

5

这是我目前能想到的最好方法。结果是现在只有一个地方可以更改文件名，但我仍需要在每个子程序中复制SET函数。虽然还不完美，但比没有好。

Public Const DESTBOOK = "DURUM IT yields merged.xlsm"

Global Locations As Workbook
Global MergeBook As Workbook
Global TotalRowsMerged As String

Sub Fill_CZ_Array()
Set Locations = Application.Workbooks("locXws.xlsx")
Set MergeBook = Application.Workbooks(DESTBOOK)
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count

- Luuklag

我仍然需要在每个子程序中复制SET函数。这是什么意思？如果你的变量是全局或公共的，放置在模块中并且只初始化一次，那么你可以从任何其他模块访问这些变量。 - BrakNicku

你首先必须在子程序中设置它们，这是唯一可以做到的。因此，您将不得不将其放置在每个子程序中，以便运行该子程序时发生的第一件事情是设置参数。或者通过调用第二个子程序来启动您的子程序，在其中设置参数。现在我正在寻找一种方法，使这些设置全局化，因此无需在每个子程序中调用它们。 - Luuklag

1

在 Workbook_Open 中调用 Fill_CZ_Array 一次，就不需要在其他地方再次调用它。 - BrakNicku

那肯定是一个解决方法。但是是否还有一种“干净”的方式来做到这一点呢？ - Luuklag

我所能想到的唯一“更清晰”的初始化全局工作簿变量的方法是创建一个公共属性，其中包含一个常量字符串。它将检查工作簿是否已经打开，如果需要则打开并返回工作簿。 - BrakNicku

好的，直到悬赏结束你仍有7天的时间来考虑一个答案 ;) - Luuklag

4

每当我遇到这种情况时，我就将wb声明为一个公共常量字符串：

public wb as string = "c:\location"

接下来，在项目代码中，您可以引用

workbooks(wb).anything

- user3476534

这其实并不疯狂！虽然不够优雅，但它能正常工作。 - Jean-François Corbett

3

只有当你知道引用工作簿中所有工作表的编号和名称时，本解决方案才会起作用。

在你的模块中，为所有工作表声明公共变量，如下所示：

Public sht1 As Worksheet
Public sht2 As Worksheet
Public sht3 As Worksheet
...

在应用程序加载事件中实例化这些公共变量。

Sub Workbook_Open()

    Workbooks.Open ("your referenced workbook")

    'Instantiate the public variables
    Set sht1 = Workbooks("Test.xlsm").Sheets("Sheet1")
    Set sht2 = Workbooks("Test.xlsm").Sheets("Sheet2")
    Set sht3 = Workbooks("Test.xlsm").Sheets("Sheet3")

End Sub

现在你可以在子程序中引用这些全局工作表。例如：

Sub test()
    MsgBox sht1.Range("A1").Value
    MsgBox sht2.Range("A1").Value
    MsgBox sht3.Range("A1").Value
End Sub

- ManishChristian

没有必要全局定义工作表，只需要定义工作簿就足够了。因为有了工作簿，我可以链接到其中的工作表。 - Luuklag

那么，在这种情况下，Macro Man的答案就是你需要的。我提供了这个选项，这样你就不需要实例化所有的工作表，可以直接使用它们。 - ManishChristian

3

如果您创建了一个名为ExcelMod的模块，并在该模块内有一个名为Initialize()和Terminiate()的公共函数或子程序，那么您可以使用这些例程来初始化和终止模块级变量。例如，我以前使用过这个方法：（请注意，模块变量是在模块顶部声明的第一件事。）

Dim excelApp As Object, wb As Workbook, ws As Worksheet

Sub Initialize()
    Set excelApp = CreateObject("Excel.Application")
    Set wb = Workbooks.Open("C:\SomeOtherBook.xlsx")
End Sub

Sub Terminate()
    Set excelApp = Nothing
    Set wb = Nothing
End Sub

变量是整个模块的一部分，只能在这些子程序中初始化和终止。您可以随意将变量传入和传出模块，并在所有此模块的子程序中使用它们，无需重新设置。如果您需要在另一个模块中使用它，则需要像通常一样将其传递给该模块。

另外，正如其他人所提到的，您可以使用workbook_Open事件调用初始化子程序，仅在需要时创建对象并设置它们一次。

这符合您的要求吗？

- Clint Street

3

你可以使用类模块来完成此操作，并依赖于类初始化器在模块中使用时为您完成工作：

名为 cLocations 的类模块：

Public Workbook As Workbook

Private Sub Class_Initialize()
    Set Workbook = Workbooks.Open("C:\Temp\temp.xlsx")
End Sub

在你的模块中喜欢的任何地方，或者无论在哪里：

Dim Locations As New cLocations

Sub dosomething()
    Locations.Workbook.Sheets(1).Cells(1, 1).Value = "Hello World"
End Sub

然后，您可以使用Locations.Workbook来引用位置工作簿，ThisWorkbook来引用正在运行代码的工作簿，ActiveWorkbook来引用拥有焦点的工作簿。这样，您就可以从一个工作簿（ThisWorkbook）运行代码，使用位置工作簿（Locations.Workbook）作为参考，并迭代其他工作簿（ActiveWorkbook）以添加另一层自动化。

如果您逐步执行代码，您会发现只有在需要它的代码行时才初始化该类，而不是在加载工作簿时。

我必须补充说明的是，在这种情况下，如果您稍微介绍一下您想要实现的目标，我们可能能够为您提供解决编码过程中遇到的问题更好的解决方案。

您还可以更进一步，抽象到应用程序级别，将位置工作簿隐藏起来，甚至为已命名的工作表提供智能感知，如果您明确知道它们的位置或名称：

类模块：

Private App As Application
Public Workbook As Workbook
Public NamedSheet As Worksheet

Private Sub Class_Initialize()
    Set App = New Application
    App.Visible = False
    App.DisplayAlerts = False
    Set Workbook = App.Workbooks.Open("C:\Temp\temp.xlsx") 'maybe open read only too?
    Set NamedSheet = Workbook.Sheets("SomethingIKnowTheNameOfExplicitly")
End Sub

Public Sub DoSomeWork()
    'ThisWorkbook refers to the one the code is running in, not the one we opened in the initialise
    ThisWorkbook.Sheets(1).Cells(1, 1).Value = Wb.Sheets(1).Cells(1, 1).Value
End Sub

Public Function GetSomeInfo() As String
    GetSomeInfo = NamedSheet.Range("RangeIKnowTheNameOfExplicitly")
End Function

然后在你的模块中，第一次使用变量时，可以通过一行代码进行初始化：

Dim Locations As New cLocations
Dim SomeInfo

Sub DoSomething()
    SomeInfo = Locations.GetSomeInfo 'Initialised here, other subs wont re-initialise

    Locations.Workbook.Sheets(1).Cells(1, 1).Value = _ 
        ThisWorkbook.Sheets(1).Cells(1, 1).Value

    Locations.NamedSheet.Cells(1,1).Value = "Hello World!"

    Locations.Workbook.Save
End Sub

- user1641172

3

这是我通常在需要正确初始化全局变量时所做的事情:

在一个普通的代码模块中加入以下代码:

Public Initialized As Boolean
Public Locations As Workbook

Sub Initialize()
    If Initialized Then Exit Sub
    Const fname As String = "M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx"
    On Error Resume Next
        Set Locations = Workbooks(Dir(fname))
    On Error GoTo 0
    If Locations Is Nothing Then
        Set Locations = Workbooks.Open(fname)
    End If
    Initialized = True
End Sub

然后在工作簿的代码模块中输入：

Private Sub Workbook_Open()
    Initialize
End Sub

此外，在任何可能启动您的代码的“网关”子程序或函数（例如事件处理程序、UDF等）中，将 Initialize （或者可能是： 如果未初始化则初始化）放在第一行。通常大多数子程序不会直接启动，可以依赖于调用者正确设置 Locations 。如果您需要测试某些东西，而该变量未设置时无法正常运行，则可以直接在即时窗口中键入 initialize 。

- John Coleman

2

如果我理解你的问题正确，你正在创建一个应该在应用程序级别而不是工作簿级别上运行的代码。在这种情况下，为什么不创建一个插件？

插件中的所有代码都将访问应用程序级别的所有打开的工作簿。

- cyboashu

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- BrakNicku · Accepted Answer

我认为工作簿全局变量最通用的方式是创建一个具有 Public Property Get 过程的模块。您可以在不调用任何代码的情况下引用它，并且无需担心文件是否已打开。

以下是一个变量示例模块代码：

Private wLocations As Workbook

Public Property Get Locations() As Workbook
  Const sPath As String = "M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx"
  Dim sFile As String

  If wLocations Is Nothing Then
      'extract file name from full path
      sFile = Dir(sPath)

      On Error Resume Next

      'check if the file is already open    
      Set wLocations = Workbooks(sFile)

      If wLocations Is Nothing Then
        Set wLocations = Workbooks.Open(sPath)
      End If

      On Error GoTo 0
  End If
  Set Locations = wLocations
End Property

您可以将其用作全局变量，在代码的任何地方使用它：

Sub Test()
  Debug.Print Locations.Worksheets.Count
End Sub