vba - 将工作簿声明为全局变量

Question

我开始编写一个适用于多个工作簿的代码，但始终使用相同的引用工作簿。该代码将有许多子项，并且由于我试图避免将每个子项中的引用工作簿的变量变暗，所以我想将它们声明为全局的。

首先我有:

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

这给了我:

"Compile error: Invalid outside procedure"

经过一番谷歌搜索后，我在某处发现了以下代码:

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

这给了我:

"Compile error: Expected: type name"

<小时/>

编辑:

使用:

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

(Workbooks.Open 语句中的单引号)会产生与使用双引号时相同的错误。

谁知道我做错了什么？

编辑2:

我还尝试在“ThisWorkbook”中声明变量，遵循 this answer使用:

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

但随后它返回一个

"Object Required"

在我的模块内。

编辑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

Answer 1

我认为工作簿全局变量最通用的方法是使用 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