现在的位置: 首页 > Excel 杂记 > 正文

Excel 名称(Name)及其 VBA 中的使用(1)

2014年07月17日 Excel 杂记 ⁄ 共 3905字 ⁄ 字号 暂无评论 ⁄ 阅读 8,655 次
本文由网上资料整理而成

 

在 Excel 的中名称是一个使用很频繁的东西,通过在 Excel 定义和使用名称,可以更好的管理工作表数据,方便地编写公式和设置表格。正如在工作表中定义和使用名称一样,在VBA中也可以创建和使用名称,并能利用名称处理工作表中的数据,这里我们就来具体的讲一讲Excel 名称及其 VBA 中的使用。

 

一、认识和理解名称

 

1、什么是名称:

所谓名称就是给单元格引用、常量、公式或者表格取一个有意义的名字,便于你了解和记忆这些对象,比如像下面的这些例子所表现的那样:

20140717100000

  

2、名称的类型:

Excel 的可以创建和使用名称可以分为以下两种类型

已定义的名称:  代表单元格、单元格区域、公式或常量值的名称。您可以创建自己的已定义名称,有时 Excel 也会为您创建已定义名称,例如当您设置打印区域时。

表名称:  Excel 中表格的名称,每次插入 Excel 表时,Excel 都会创建如 表1、表2 等默认 Excel 表名称,如果有需要您也可以修改这些默认名称。

 

3、名称的有效范围

名称的适用范围可以分为工作表级和工作簿级,其主要的区别如下:

工作表级(局部名称):  此类名称的适用范围为定义其的工作表。比如在 Sheet1 中定义一个名称叫 "销售",这个名称在没有限定的情况下只能在 Sheet1 中被识别,如果要在其他的表格中使用这个名称,就必须在名称前加上定义工作表的名字来限定他。比如:Sheet1!销售

工作簿级(全局名称):  此类名称的适用范围为工作簿。工作簿中的所有工作表而言都可以识别并使用这个名称。但其他的工作簿是不能识别和使用的。

注意: 名称的名字在其的适用范围必须是唯一的。但是你可以在不同的范围定义名字相同的名称,比如您可以为 Sheet1, Sheet2和 Sheet3都定义一个名叫 "销售" 的名称。你甚至还可以定义一个工作簿级的名叫 "销售" 名称。但工作表和工作簿同时存在一个名字相同的名称时就会导致名称冲突。Excel 为解决此类冲突,默认情况下会使用工作表级的名称,因为局部工作表级的名称优先于全局工作簿级的名称。如果要使用工作簿级的名称的话,那就必须为此名称添加前缀来消除歧义,比如:Book1!销售

 

4、创建名称

在 Excel 中一般可以通过三种方式来创建名称,如下所述:

编辑栏上的 "名称框":  我们可以直接在“名称框”中输入名字来命名所选定的单元格或单元格区域,通常,在名称框中显示的是所在单元格的行号列标,单击其右侧的下拉箭头,可以看到工作簿中的名称列表:

20140717101826

 

根据所选内容创建:  根据工作表中选定的单元格区域很方便的基于现有的行和列标签来创建名称:

20140717102243

  

使用“定义名称”对话框:  使用这种方式可以更加灵活的创建、编辑及使用名称(例如指定局部工作表级别适用范围或创建名称批注), 在该对话框中,可以定义常量名称和动态名称:

20140717102805

  

5、输入名称

名称主要通过以下几种方式来输入:

直接键入:  直接在单元格等中输入名称。

使用 "公式记忆式键入" :  使用 "公式记忆式键入" 下拉列表,其中自动为您列出了有效名称。

使用 "用于公式" 命令项:  从公式选项卡定义的名称组中用于公式菜单的下拉列表中选择已定义名称。

 

6、命名名称注意事项

下面是创建和编辑名称时需要注意的语法规则。

有效字符:  名称中的第一个字符必须是字母、下划线 (_) 或反斜杠 (\)。名称中的其余字符可以是字母、数字、句点和下划线。注意: 不能将字母“C”、“c”、“R”或“r”用作已定义名称,因为当在名称定位文本框中输入这些字母中的两个时,会将它们用作为当前选定的单元格选择行或列的简略表示法。

不允许的单元格引用:  名称不能与单元格引用(例如 Z$100 或 R1C1)相同。

空格无效:  不允许使用空格。请使用下划线 (_) 和句点 (.) 作为单词分隔符,例如 Sales_Tax 或 First.Quarter。

名称长度:  一个名称最多可以包含 255 个字符。

区分大小写:  名称可以包含大写字母和小写字母。Excel 在名称中不区分大写字符和小写字符。例如,如果创建了名称 Sales,接着又在同一工作簿中创建另一个名称 SALES,则 Excel 会提示您选择一个唯一的名称

 

二、VBA 中对名称的基本操作

  
1、 创建名称

  • 可以使用下面的代码在当前工作簿中创建名称
ActiveWorkbook.Names.Add Name:="MyName", RefersToR1C1:="=Sheet1!R2C2:R6C4"
'或者
ActiveWorkbook.Names.Add Name:="MyName", RefersTo:="=Sheet1!$B$2:$D$6"

上面的代码在当前工作簿中将工作表Sheet1内的区域B2:D6命名为MyName,该名称为全局名称。在所命名的名称中不能出现空格和单元格引用,并且,如果对命名区域使用A1样式的引用,则最后使用绝对引用,否则所命名的区域将会不确定。

在所命名的名称前加上工作表名,则创建局部名称,如:

ActiveWorkbook.Names.Add Name:="Sheet1!MyName1", RefersTo:="=Sheet1!$B$2:$D$6"

上面的代码在工作表Sheet1中命名区域B2:D6为MyName1,该名称为局部名称。
也通过引用指定工作表来创建局部名称,如:

Worksheets("sheet2").Names.Add Name:="MyName2", RefersTo:="=Sheet2!$A$1:$B$3"

上面的代码在工作表Sheet2中创建一个局部名称MyName2,代表Sheet2中的单元格区域A1:B3。

  • 一种简单的命名方法。例如
Worksheets("Sheet1").Range("B8:C10").Name = "MyName3"

上面的代码将工作表Sheet1中的单元格区域B8:C10命名为MyName3,为全局名称。

Worksheets("Sheet2").Range("H15:G16").Name = "Sheet2!MyName4"

上面的代码将工作表Sheet2中的单元格区域H15:G16命名为MyName4,为局部名称。
注意,这种方法只能应用于命名单元格区域,不能够用来命名公式、数字等。

  • 在当前工作表中命名局部区域,该区域为其它工作表中的单元格区域
Worksheets("Sheet1").Range("E6:F8").Name = "Sheet2!MyName5"

或者:

Worksheets("sheet2").Names.Add Name:="MyName5", RefersTo:="=Sheet1!$E$6:$F$8"

上面的代码在工作表Sheet2中命名工作表Sheet1中单元格区域E6:F8为MyName5,该名称为Sheet2中的局部名称。

  • 命名数字
Names.Add Name:="NameNumber", RefersTo:=666

将数字666命名为NameNumber

  • 命名字符串
Names.Add Name:="NameString", RefersTo:="TV"

将字符串TV命名为NameString。

  • 命名数组
Dim MyArray(10)
Dim i As Integer
For i = 1 To 10
 MyArray(i) = i
Next i
Names.Add Name:="NameArray", RefersTo:=MyArray

上述代码先对数组赋值,然后指定名称。

  • 命名公式
Names.Add Name:="NameFormlas", RefersTo:="=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)"

上面的代码命名了一个公式,可以用来创建动态表格或引用动态的区域。
使用上面的方法来命名数字、字符串、数组或公式存储经常要使用的值,比将该值存放在单元格中更有优势,它可以避免该值被易外修改并减少了对象的引用。

 

2、重命名已有的名称

Worksheets("Sheet2").Names("MyName5").Name = "MyName6"

上面的代码将工作表Sheet2中的局部名称MyName5改名为MyName6。但这种方法只能在单元格或单元格区域中进行重命名使用,而不能重命名代表公式、数组和字符串的名称。

 
3、改变所选区域所命名的名称的引用区域

Worksheets(“Sheet1”).Names.Add Selection.Name.Name,Sheet1.Range(“B3:C4”)

上面的代码将原来所选区域的名称的引用区域改为单元格区域B3:C4,即该名称所代表的区域已变为B3:C4,原来命名区域名称被取消。

 
4、提取命名区域。

使用Evaluate方法,例如:代码

Evaluate("MyName").Interior.ColorIndex = 3

将工作表中名称MyName所代表的单元格区域的背景设置为红色。

 

5、隐藏名称

Names.Add Name:="HideName", RefersTo:="=$A$1:$C$3", Visible:=False

将隐藏所创建的名称。注意,如果再创建的名称与所隐藏的名称相同,则被隐藏的名称将被覆盖。

 

6、删除名称

Names("MyName3").Delete

上面的代码删除当前工作簿中的名称MyName3。

注意,当前工作簿中重命名已有名称和删除名称时,要注意所要操作的名称是全局名称还是局部名称。如果为局部名称,则必须在代码中加上该名称所在工作表的引用。

 

下接:Excel 名称(Name)及其 VBA 中的使用(2)
 

给我留言

您必须 [ 登录 ] 才能发表留言!