问题描述
我开始编写一个适用于多个工作簿的代码,但始终使用相同的参考工作簿。代码将有很多代码,因为我想避免将每个子项中的引用工作簿变量变暗,我想声明全局。
有:
全局位置作为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:
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:
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
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
这篇关于将工作簿声明为全局变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!