使用代码名称引用工作表时出现编译错误。

huangapple go评论57阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年7月4日 21:57:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76613398.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定