英文:
Compile error when referring to sheet using codename
问题
此帖子https://stackoverflow.com/a/65020762/4928763描述了如何使用工作表的代码名称引用工作表,如下所示...
With Sheet1
.[a1] = Sheet1.Name
End With
和...
在Sheet3.Range("A1")中的.CodeName作为Sheet3
所以我希望能够使用以下方式激活工作表...
With Sheet1
.activate
End With
甚至只需使用...
Sheet3.activate
但是尝试时出现编译错误:变量未定义
我做错了什么?
只为明确 - 在我的情况下,工作表的名称为“Monthly Sales”,工作表的Codename为“wsMonthlySales”,在项目资源管理器中,名称显示为wsMonthlySales(Monthly Sales)
我可以使用Sheets("Monthly Sales").Activate
激活工作表,但我想使用像上面描述的wsMonthlySales.Activate
之类的东西,但尝试编译时会出现“变量未定义”的错误。
英文:
This post https://stackoverflow.com/a/65020762/4928763 describes referring to a worksheet using its codename like this...
With Sheet1
.[a1] = Sheet1.Name
End With
and...
.CodeName as Sheet3 in Sheet3.Range("A1")
so I would expect to be able to activate a sheet using...
With Sheet1
.activate
End With
or even just using...
Sheet3.activate
but when trying this I get a compile error: Variable not defined
What am I doing wrong?
Just to be clear - In my case the sheet has Sheet.Name = "Monthly Sales" and Sheet.Codename = "wsMonthlySales" and in the project exporer the name is displayed as wsMonthlySales (Monthly Sales)
I can activate the sheet using Sheets("Monthly Sales").Activate
but I would like to use something like wsMonthlySales.Activate
as descrived above but get "Variable not defined" error when trying to compile
答案1
得分: 1
使用代码名称引用另一个工作簿中的工作表
示例(用法)
Sub RefSheetByCodeNameTEST()
' 定义常量.
Const WORKBOOK_NAME As String = "Monthly.xlsx"
Const WORKSHEET_CODE_NAME As String = "wsMonthlySales"
' 引用工作簿.
Dim wb As Workbook:
On Error Resume Next
Set wb = Workbooks(WORKBOOK_NAME)
On Error GoTo 0
If wb Is Nothing Then
MsgBox "工作簿 " & WORKBOOK_NAME & " 未打开。", _
vbCritical
Exit Sub
End If
' 引用工作表.
Dim ws As Worksheet: ' 或使用 'wsMonthlySales' 代替 'ws'
Set ws = RefWorkSheetByCodeName(wb, WORKSHEET_CODE_NAME)
If ws Is Nothing Then
MsgBox "未找到具有代码名称 " & WORKSHEET_CODE_NAME _
& " 的工作表。", vbCritical
Exit Sub
End If
' 工作表已被引用。可以继续使用 'ws',例如:
MsgBox "工作表的标签名是 " & ws.Name & "。", _
vbInformation
End Sub
函数(工作表)
Function RefWorkSheetByCodeName( _
ByVal wb As Workbook, _
ByVal WorkSheetCodeName As String) _
As Worksheet
Dim ws As Worksheet
For Each ws In wb.Worksheets
If StrComp(ws.CodeName, WorkSheetCodeName, vbTextCompare) = 0 Then
Set RefWorkSheetByCodeName = ws
Exit For
End If
Next ws
End Function
函数(工作表和图表)
Function RefSheetByCodeName( _
ByVal wb As Workbook, _
ByVal SheetCodeName As String) _
As Object
Dim sh As Object
For Each sh In wb.Sheets
If StrComp(sh.CodeName, SheetCodeName, vbTextCompare) = 0 Then
Set RefSheetByCodeName = sh
Exit For
End If
Next sh
End Function
英文:
Use the Code Name to Reference a Worksheet in Another Workbook
An Example (Utilization)
Sub RefSheetByCodeNameTEST()
' Define constants.
Const WORKBOOK_NAME As String = "Monthly.xlsx"
Const WORKSHEET_CODE_NAME As String = "wsMonthlySales"
' Reference the workbook.
Dim wb As Workbook:
On Error Resume Next
Set wb = Workbooks(WORKBOOK_NAME)
On Error GoTo 0
If wb Is Nothing Then
MsgBox "Workbook """ & WORKBOOK_NAME & """ is not open.", _
vbCritical
Exit Sub
End If
' Reference the worksheet.
Dim ws As Worksheet: ' or use 'wsMonthlySales' instead of 'ws'
Set ws = RefWorkSheetByCodeName(wb, WORKSHEET_CODE_NAME)
If ws Is Nothing Then
MsgBox "Worksheet with the code name """ & WORKSHEET_CODE_NAME _
& """ not found.", vbCritical
Exit Sub
End If
' The worksheet is referenced. Continue using 'ws' with e.g.:
MsgBox "The tab name of the worksheet is """ & ws.Name & """.", _
vbInformation
End Sub
The Function (Worksheets)
Function RefWorkSheetByCodeName( _
ByVal wb As Workbook, _
ByVal WorkSheetCodeName As String) _
As Worksheet
Dim ws As Worksheet
For Each ws In wb.Worksheets
If StrComp(ws.CodeName, WorkSheetCodeName, vbTextCompare) = 0 Then
Set RefWorkSheetByCodeName = ws
Exit For
End If
Next ws
End Function
The Function (Sheets (Including Charts))
Function RefSheetByCodeName( _
ByVal wb As Workbook, _
ByVal SheetCodeName As String) _
As Object
Dim sh As Object
For Each sh In wb.Sheets
If StrComp(sh.CodeName, SheetCodeName, vbTextCompare) = 0 Then
Set RefSheetByCodeName = sh
Exit For
End If
Next sh
End Function
答案2
得分: 0
Codename 是一个字符串。但也有一个同名的类对象,它继承自 Worksheet。假设我们有一个名为 ANSWERS.xlsm 的工作簿,项目名称为:“VBAProjectAns”。假设这个工作簿有一个名为 Sheet1 的工作表和 Codename 为 “SHEET01”。我们希望通过另一个工作簿,即 OTHERBOOK.xlsm,使用 Codename SHEET01,该工作簿必须具有不同的项目名称,例如:“VBAProjectOther”。我知道的唯一方法是:我们打开这两个工作簿,然后转到 OTHERBOOK.xlsm,从 VB 中的引用中选择并勾选 VBAProjectAns。然后在代码中,我们可以如下引用 SHEET01:
' 在 OTHERBOOK.xlsm 中的代码
Sub Test()
VBAProjectAns.SHEET01.Activate
End Sub
我们保存这两个工作簿并关闭它们。如果您尝试首先关闭 ANSWERS.xlsm,它不会让您关闭,并显示一个正在使用的消息,因此您首先关闭 OTHERBOOK.xlsm。如果现在再次打开 OTHERBOOK.xlsm,ANSWERS.xlsm 也将自动打开。因此,只要您“连接”这两个工作簿,您就可以以某种方式使用另一个工作簿的工作表的 Codename。
英文:
Codename is a String. But there is also a class object with the same name, which inherits from Worksheet. Suppose we have a workbook named ANSWERS.xlsm that has Project name: "VBAProjectAns". Suppose this workbook has a sheet named Sheet1 and Codename "SHEET01". We want to use the codename SHEET01 through another book, even OTHERBOOK.xlsm which must have a different Project name, even: "VBAProjectOther". So the only way I know is the following: We open both books, go to OTHERBOOK.xlsm and from VB in References we select by checking VBAProjectAns. Then in the code we can refer to SHEET01 as follows:
' code in OTHERBOOK.xlsm
Sub Test()
VBAProjectAns.SHEET01.Activate
End Sub
We store the books and close them. If you try to close ANSWERS.xlsm first, it won't let you with a message that it is in use, so you close OTHERBOOK.xlsm first. If you now open OTHERBOOK.xlsm again, ANSWERS.xlsm will automatically open as well. So you can somehow use the Codename of the sheets of another book, as long as you "marry" the books.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论