将工作簿声明为全局变量 [英] Declare a Workbook as a Global variable

问题描述

我开始编写一个适用于多个工作簿的代码，但始终使用相同的参考工作簿。代码将有很多代码，因为我想避免将每个子项中的引用工作簿变量变暗，我想声明全局。

有：

 全局位置作为Excel.Workbook 
设置位置= Workbooks.Open（M：\My Documents \MSC Thesis\Italy\Merged\locXws.xlsx）
  

哪个给了我：

编译错误：外部程序无效

经过一些搜索之后，我发现以下代码位于某个地方：

  Public Const位置作为Excel.Workbook =工作簿。打开（M：\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx）
  

其中给了我：

编译错误：预期：类型名称

---

编辑：

使用：

  P公共Const位置作为Excel.Workbook =Workbooks.Open（'M：\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx'）
  / pre> 
 
 
（Workbooks.Open语句中的单引号）结果与使用双引号时的错误相同。
 
 
 
谁知道我在做错什么？
 
 
 
 Edit2：
 
 
 
我也试图申报ThisWorkbook中的变量，这个答案使用：
  Private Sub Workbook_Open（）
 Dim Locations作为Excel.Workbook 
 Dim MergeBook作为Excel.Workbook 
 Dim TotalRowsMerged As String 
 
 
位置= Workbooks.Open（M：\My Documents\MSC论文\Italy\Merged\locXws.xlsx）
 MergeBook = Workbooks.Open（M：\My Documents\MSC Thesis\Italy\M erged\DURUM IT产生merged.xlsm）
 TotalRowsMerged = MergeBook.Worksheets（Sheet1）。UsedRange.Rows.Count 
 End Sub 
  
但是，它返回一个
 
 
需要对象
 
在我的模块内。
 
 
 
 Edit3：
 
 
 
我现在有这个工作，但是不得不将SET行复制到每个Sub，这样做有一个更好的方法？
 全局位置作为工作簿
全局MergeBook作为工作簿
全局TotalRowsMerged As String 
 
 Sub Fill_CZ_Array（）
设置位置= Application.Workbooks（locXws.xlsx）
设置MergeBook = Application.Workbooks（DURUM IT yield merged.xlsm）
 TotalRowsMerged = MergeBook.Worksheets（Sheet1）。UsedRange.Rows .Count 
  
 
 
解决方案
我认为工作簿全球最通用的方式变量将创建一个mo dule与公共属性获取程序。您可以先引用它，而不需要调用任何代码，而不用担心文件是否打开。
 
 
 
这是一个示例模块代码的变量：
 私有wLocations作为工作簿
 
公共属性获取位置（）作为工作簿
 Const sPath As String =M：\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx
 Dim sFile As String 
 
如果wLocations不是然后
'从完整路径提取文件名
 sFile = Dir（sPath）
 
错误恢复下一步
 
'检查文件是否已经打开
设置wLocations =工作簿（sFile）
 
如果wLocations没有，然后
设置wLocations = Workbooks.Open（sPath）
如果
 
错误GoTo 0 
如果
设置位置= wLocations 
结束属性
  
您可以在代码中的任何位置将其用作全局变量le：
  Sub Test（）
 Debug.Print Locations.Worksheets.Count 
 End Sub 
  
 
I am starting to write a code that will become applicable to multiple workbooks, but always uses the same reference workbook. The code will have many subs, and as I am trying to avoid to dim a variable to the reference workbook in every sub I would like to declare them Global.

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

  
"Compile error: Invalid outside procedure"
After some googling I found the following bit of code somewhere:
Public Const Locations As Excel.Workbook = "Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx")"
Which gave me: 

  
"Compile error: Expected: type name"




Edit:

Using:
Public Const Locations As Excel.Workbook = "Workbooks.Open('M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx')"
(Single quotation marks within the Workbooks.Open statement) results as the same error as when using double quotation marks.

Who knows what I am doing wrong?

Edit2:

I also tried to declare the variables in the "ThisWorkbook", following this answer using: 
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
But then it returns an 

  
"Object Required" 
within my  module.

Edit3:

I now have this which works, but has the downside of having to copy the SET lines into every Sub, there has to be a better way to do this?
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

解决方案
I think the most universal way for workbook global variable would be creating a module with a Public Property Get procedure. You can refer to it without calling any code first, and you don't have to worry if the file is open or not.

Here is the sample module code for one of the variables:
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
You can use it anywhere in the code as a global variable:
Sub Test()
  Debug.Print Locations.Worksheets.Count
End Sub


                        
这篇关于将工作簿声明为全局变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！