Excel 选项卡工作表名称与 Visual Basic 工作表名称 [英] Excel tab sheet names vs. Visual Basic sheet names

问题描述

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

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.

我有这个:

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

但我想在 Visual Basic 例程中使用工作表名称.到目前为止，我能想到的最好方法是选择大小写工作表选项卡与 Visual Basic 名称，这对我来说并不重要.

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 必须知道 Sheet1、Sheet2 等名称.如何将这些与 Excel 选项卡名称相关联，以便我不必维护一个查找表，该表会随着每个新工作表或工作表选项卡的重命名而发生变化?

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?

推荐答案

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

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

工作表.名称
工作表.代码名称

Worksheet.Name
Worksheet.CodeName

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

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

CodeName 属性是只读的

the CodeName property is read-only

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

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.

这篇关于Excel 选项卡工作表名称与 Visual Basic 工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！