Excel 选项卡工作表名称与 Visual Basic 工作表名称

Question

Visual Basic 似乎无法根据用户修改的工作表名称来引用工作表。工作表选项卡的名称可以更改，但 Visual Basic 似乎仍将工作表名称视为 Sheet1 等，尽管工作簿选项卡已更改为有用的名称。

我有这个：

TABname = rng.Worksheet.Name  ' Excel sheet TAB name, not VSB Sheetx name.

但我想在 Visual Basic 例程中使用工作表名称。到目前为止，我能想到的最好办法是选择工作表选项卡与 Visual Basic 名称的大小写，但这并不让我高兴。

Visual Basic 必须知道 Sheet1、Sheet2 等名称。如何获取与 Excel 选项卡名称关联的这些名称，以便我不必维护一个查找表，该表会随着每个新工作表或工作表选项卡的重命名而变化？

Answer 1

在 Excel 对象模型中，工作表有 2 个不同的名称属性：

Worksheet.Name
Worksheet.CodeName

Name 属性是读/写的，包含出现在工作表选项卡上的名称。用户和 VBA 均可更改

CodeName 属性是只读的

您可以将特定工作表引用为
Worksheets("Fred").Range("A1") 其中 Fred 是 .Name 属性
或作为
Sheet1.Range("A1") 其中 Sheet1 是工作表的代号。

Answer 2

这将更改所有工作表对象的名称（从 VBA 编辑器的角度来看）以匹配其工作表名称（从 Excel 的角度来看）：

Sub ZZ_Reset_Sheet_CodeNames()
'Changes the internal object name (codename) of each sheet to it's conventional name (based on it's sheet name)

    Dim varItem As Variant

    For Each varItem In ThisWorkbook.VBProject.VBComponents
        'Type 100 is a worksheet
        If varItem.Type = 100 And varItem.Name <> "ThisWorkbook" Then
            varItem.Name = varItem.Properties("Name").Value
        End If
    Next
End Sub

需要注意的是，对象名称（代号）“(Name)”是被属性名称“Name”覆盖，因此必须将其作为子属性引用。

Answer 3

实际上“Sheet1”对象/代码名称是可以更改的。在 VBA 中，单击 Excel 对象列表中的 Sheet1。在属性窗口中，您可以将 Sheet1 更改为 rng。

然后您可以将 rng 作为全局对象引用，而无需先创建变量。所以 debug.print rng.name 工作得很好。不再有工作表(“rng”).name。

与选项卡不同，对象名称与其他变量具有相同的限制（即没有空格）。

Answer 4

您应该能够通过用户提供的名称来引用工作表。您确定引用了正确的工作簿吗？如果您在引用一张工作表时打开了多个工作簿，则肯定会导致问题。

如果这是问题所在，使用 ActiveWorkbook （当前活动的工作簿）或 ThisWorkbook （包含宏的工作簿）应该可以解决它。

例如，

Set someSheet = ActiveWorkbook.Sheets("Custom Sheet")

Answer 5

这是一个非常基本的解决方案（也许我错过了问题的全部要点）。 ActiveSheet.Name 将返回当前选项卡名称的字符串（并将反映用户将来的任何更改）。我只是调用活动工作表，设置变量，然后将其用作工作表的对象。在这里，我从表中检索数据以设置部门的报告。该宏适用于我的工作簿中针对同一过滤器（条件和 copytorange）进行格式化的任何工作表 - 每个部门都有自己的工作表，并且可以使用此单个宏更改条件和更新。

Dim currRPT As String
ActiveSheet.Select
currRPT = (ActiveSheet.Name)
Range("A6").Select
Selection.RemoveSubtotal
Selection.AutoFilter
Range("PipeData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
    ("C1:D2"), CopyToRange:=Range("A6:L9"), Unique:=True
Worksheets(currRPT).AutoFilter.Sort.SortFields.Clear
Worksheets(currRPT).AutoFilter.Sort.SortFields.Add Key:= _
    Range("C7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal

Answer 6

（至少）有两种不同的方法可以

• 引用的 Sheets 或 Worksheets 集合来
• 通过 DanM通过不合格对象名称

获取 Worksheet 对象创建包含三个工作表的新工作簿时，将存在四个对象，您可以通过非限定名称访问它们：ThisWorkbook； 工作表1； 工作表2； Sheet3。这可以让您编写如下内容：

Sheet1.Range("A1").Value = "foo"

虽然这看起来像是一个有用的快捷方式，但当工作表被重命名时就会出现问题。即使工作表被重命名为完全不同的名称，非限定对象名称仍为 Sheet1。

这是有一定逻辑的，因为：

• 工作表名称与变量名称不符合相同的规则
• 您可能会意外屏蔽现有变量

例如（在 Excel 2003 中测试），创建一个新的 Workbook，其中包含三个工作表。创建两个模块。在一个模块中声明：

Public Sheet4 As Integer

在另一个模块中输入：

Sub main()

Sheet4 = 4

MsgBox Sheet4

End Sub

运行此命令，消息框应该正确显示。

现在将第四个工作表添加到工作簿中，这将创建一个 Sheet4 对象。尝试再次运行 main，这次您将收到“对象不支持此属性或方法”错误

Answer 7

使用工作表代号也是我需要的答案，以阻止一系列宏失败 - ccampj 在镜子上方的答案 此解决方案（带有屏幕图片）

Answer 8

我不得不求助于这个，但这有维护问题。

Function sheet_match(rng As Range) As String  ' Converts Excel TAB names to the required VSB Sheetx names.
  TABname = rng.Worksheet.Name                ' Excel sheet TAB name, not VSB Sheetx name. Thanks, Bill Gates.
' Next, match this Excel sheet TAB name to the VSB Sheetx name:
   Select Case TABname 'sheet_match
      Case Is = "Sheet1": sheet_match = "Sheet1"  ' You supply these relationships
      Case Is = "Sheet2": sheet_match = "Sheet2"
      Case Is = "TABnamed": sheet_match = "Sheet3" 'Re-named TAB
      Case Is = "Sheet4": sheet_match = "Sheet4"
      Case Is = "Sheet5": sheet_match = "Sheet5"
      Case Is = "Sheet6": sheet_match = "Sheet6"
      Case Is = "Sheet7": sheet_match = "Sheet7"
      Case Is = "Sheet8": sheet_match = "Sheet8"
   End Select
End Function

Answer 9

也许我错了，但您可以打开一个工作簿，然后选择一个工作表并将其属性（名称）更改为您需要的任何内容。这会覆盖“Sheetx”命名约定。这些名称也显示在 VBA 编辑器中。

如何手动执行此操作：
1. 选择工作簿中的工作表（我倾向于创建模板）。
2. 将其选项卡名称设置为您喜欢的任何名称（“foo”）。
3. 单击“开发人员”菜单（您之前已启用，对吗？）。
4. 找到“属性”并单击它，打开该工作表的属性窗口。
5. 按字母顺序排列的列表中的第一项是（名称），（名称）右侧是“Sheetx”。
6. 单击该字段并进行更改（我们使用“MyFav”怎么样）。
7. 关闭属性窗口。
8. 转到 Visual Basic 编辑器。
9. 查看您刚刚修改的工作簿中的工作表。
10. 观察 Microsoft Excel 对象显示您刚刚更改的名称“MyFav”，以及其右侧括号中的工作表选项卡名称（“foo”）。

如果您愿意，可以通过编程方式更改 .CodeName。我使用非工作表名称来方便我的模板操作。
您不必被迫使用“Sheetx”的通用默认值。

Answer 10

我想我可能有一个替代解决方案。它有点难看，但似乎有效。

Function GetAnyNameValue(NameofName) As String
    Dim nm, ws, rng As String
    nm = ActiveWorkbook.Names(NameofName).Value
    ws = CStr(Split(nm, "!")(0))
    ws = Replace(ws, "'", "")
    ws = Replace(ws, "=", "")
    rng = CStr(Split(nm, "!")(1))
    GetAnyNameValue = CStr(Worksheets(ws).Range(rng).Value)
End Function