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

Excel tab sheet names vs. Visual Basic sheet names

提问by SteveNeedsSheetNames

It seems that Visual Basic can not reference sheets according to user-modified sheet names. The worksheet tabs can have their names changed, but it seems that Visual Basic still thinks of the worksheet names as Sheet1, etc., despite the workbook tab having been changed to something useful.

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

I have this:

我有这个：

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

but I would like to use sheet names in Visual Basic routines. The best I could come up so far is to Select Case the Worksheet Tab vs. Visual Basic names, which doesn't make my day. Visual Basic must know the Sheet1, Sheet2, etc., names. How can I get these associated with the Excel tab names so that I don't have to maintain a look-up table which changes with each new sheet or sheet tab re-naming? Thanks in advance for your replies.

但我想在 Visual Basic 例程中使用工作表名称。到目前为止我能想到的最好的方法是 Select Case the Worksheet Tab vs. Visual Basic 名称，这对我来说并不重要。Visual Basic 必须知道 Sheet1、Sheet2 等名称。如何将这些与 Excel 选项卡名称相关联，以便我不必维护随每个新工作表或工作表选项卡重命名而变化的查找表？提前感谢您的回复。

回答by Charles Williams

In the Excel object model a Worksheet has 2 different name properties:

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

Worksheet.Name
Worksheet.CodeName

Worksheet.Name
Worksheet.CodeName

the Name property is read/write and contains the name that appears on the sheet tab. It is user and VBA changeable

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

the CodeName property is read-only

CodeName 属性是只读的

You can reference a particular sheet as Worksheets("Fred").Range("A1") where Fred is the .Name property or as Sheet1.Range("A1") where Sheet1 is the codename of the worksheet.

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

回答by ark565

This will change all worksheet objects' names (from the perspective of the VBA editor) to match that of their sheet names (from the perspective of Excel):

这将更改所有工作表对象的名称（从 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

It is important to note that the object name (codename) "(Name)" is being overridden by the property name "Name", and so it must be referenced as a sub-property.

重要的是要注意对象名称（代号）“（名称）”被属性名称“名称”覆盖，因此它必须作为子属性引用。

回答by ccampj

Actually "Sheet1" object / code name can be changed. In VBA, click on Sheet1 in Excel Objects list. In the properties window, you can change Sheet1 to say rng.

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

Then you can reference rng as a global object without having to create a variable first. So debug.print rng.name works just fine. No more Worksheets("rng").name.

然后您可以将 rng 引用为全局对象，而无需先创建变量。所以 debug.print rng.name 工作得很好。没有更多的工作表（“rng”）。名称。

Unlike the tab, the object name has same restrictions as other variables (i.e. no spaces).

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

回答by devuxer

You should be able to reference sheets by the user-supplied name. Are you sure you're referencing the correct Workbook? If you have more than one workbook open at the time you refer to a sheet, that could definitely cause the problem.

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

If this is the problem, using ActiveWorkbook(the currently active workbook) or ThisWorkbook(the workbook that contains the macro) should solve it.

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

For example,

例如，

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

回答by Ottobahn

This a very basic solution (maybe I'm missing the full point of the question). ActiveSheet.Name will RETURN the string of the current tab name (and will reflect any future changes by the user). I just call the active sheet, set the variable and then use it as the Worksheets' object. Here I'm retrieving data from a table to set up a report for a division. This macro will work on any sheet in my workbook that is formatted for the same filter (criteria and copytorange) - each division gets their own sheet and can alter the criteria and update using this single macro.

这是一个非常基本的解决方案（也许我错过了问题的全部内容）。ActiveSheet.Name 将返回当前选项卡名称的字符串（并将反映用户未来的任何更改）。我只是调用活动工作表，设置变量，然后将其用作工作表的对象。在这里，我正在从表中检索数据以设置部门的报告。这个宏可以在我的工作簿中为相同过滤器（标准和复制范围）格式化的任何工作表上工作 - 每个部门都有自己的工作表，可以使用这个宏改变标准和更新。

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

回答by barrowc

There are (at least) two different ways to get to theWorksheetobject

有（至少）两种不同的方法可以到达Worksheet对象

• via the Sheetsor Worksheetscollections as referenced by DanM
• by unqualified object names

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

When a new workbook with three worksheets is created there will exist four objects which you can access via unqualified names: ThisWorkbook; Sheet1; Sheet2; Sheet3. This lets you write things like this:

当三个工作表创建将存在，你可以通过不合格的名称访问四个对象一个新的工作簿：ThisWorkbook; Sheet1; Sheet2; Sheet3. 这使您可以编写如下内容：

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

Although this may seem like a useful shortcut, the problem comes when the worksheets are renamed. The unqualified object name remains as Sheet1even if the worksheet is renamed to something totally different.

尽管这似乎是一个有用的快捷方式，但在重命名工作表时会出现问题。Sheet1即使工作表被重命名为完全不同的名称，未限定的对象名称仍然存在。

There is some logic to this because:

这有一些逻辑，因为：

• worksheet names don't conform to the same rules as variable names
• you might accidentally mask an existing variable

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

For example (tested in Excel 2003), create a new Workbookwith three worksheets. Create two modules. In one module declare this:

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

Public Sheet4 As Integer

In the other module put:

在另一个模块中：

Sub main()

Sheet4 = 4

MsgBox Sheet4

End Sub

Run this and the message box should appear correctly.

运行此程序，消息框应正确显示。

Now add a fourth worksheet to the workbook which will create a Sheet4object. Try running main again and this time you will get an "Object does not support this property or method" error

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

回答by SteveNeedsSheetNames

I have had to resort to this, but this has issues with upkeep.

我不得不求助于这个，但这在维护方面存在问题。

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

回答by MickeyBlueOwl

Using the sheet codename was the answer I needed too to stop a series of macros falling over - ccampj's answer above mirrors this solution(with screen pics)

使用工作表代号也是我需要的答案，以阻止一系列宏翻倒 - 上面 ccampj 的答案反映了这个解决方案（带有屏幕图片）

回答by BjH

Perhaps I am wrong, but you can open a workbook, and select a worksheet and change its property (Name) to whatever you need it to be. This overrides the "Sheetx" naming convention. These names are also displayed in the VBA Editor.

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

How to do this manually: 1. Select the sheet in a workbook (I tend to create templates). 2. Set its tab name to whatever you like ("foo"). 3. Click on the Developer menu (which you previously enabled, right?). 4. Locate "Properties" and click on it, bringing up that worksheet's properties window. 5. The very first item in the Alphabetic listing is (Name) and at the right of (Name) is "Sheetx".
6. Click on that field and change it (how about we use "MyFav"). 7. Close the properties window. 8. Go to the Visual Basic editor. 9. Review the sheets in the workbook you just modified. 10. Observe that the MicroSoft Excel Objects shows the name you just changed "MyFav", and to the right of that, in parenthesis, the worksheet tab name ("foo").

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

You can change the .CodeName programmatically if you would rather. I use non-Sheet names to facilitate my template manipulation. You are not forced to use the generic default of "Sheetx".

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

回答by LasrinPrime

I think I may have an alternative solution. It's a little ugly, but it seems to work.

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

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