Excel 名称(Name)及其 VBA 中的使用详解教程 |
您所在的位置：网站首页 › 如何在excel中创建一个表名称 › Excel 名称(Name)及其 VBA 中的使用详解教程 |
Excel 名称(Name)及其 VBA 中的使用详解教程
|
在 Excel 的中名称是一个使用很频繁的东西，通过在 Excel 定义和使用名称，可以更好的管理工作表数据，方便地编写公式和设置表格。正如在工作表中定义和使用名称一样，在VBA中也可以创建和使用名称，并能利用名称处理工作表中的数据，这里我们就来具体的讲一讲Excel 名称及其 VBA 中的使用。 一、认识和理解名称 1、什么是名称：所谓名称就是给单元格引用、常量、公式或者表格取一个有意义的名字，便于你了解和记忆这些对象，比如像下面的这些例子所表现的那样：
2、名称的类型： Excel 的可以创建和使用名称可以分为以下两种类型 已定义的名称: 代表单元格、单元格区域、公式或常量值的名称。您可以创建自己的已定义名称，有时 Excel 也会为您创建已定义名称，例如当您设置打印区域时。 表名称: Excel 中表格的名称，每次插入 Excel 表时，Excel 都会创建如 表1、表2 等默认 Excel 表名称，如果有需要您也可以修改这些默认名称。 3、名称的有效范围名称的适用范围可以分为工作表级和工作簿级，其主要的区别如下： 工作表级(局部名称): 此类名称的适用范围为定义其的工作表。比如在 Sheet1 中定义一个名称叫 "销售"，这个名称在没有限定的情况下只能在 Sheet1 中被识别，如果要在其他的表格中使用这个名称，就必须在名称前加上定义工作表的名字来限定他。比如：Sheet1!销售 工作簿级(全局名称): 此类名称的适用范围为工作簿。工作簿中的所有工作表而言都可以识别并使用这个名称。但其他的工作簿是不能识别和使用的。 注意： 名称的名字在其的适用范围必须是唯一的。但是你可以在不同的范围定义名字相同的名称，比如您可以为 Sheet1, Sheet2和 Sheet3都定义一个名叫 "销售" 的名称。你甚至还可以定义一个工作簿级的名叫 "销售" 名称。但工作表和工作簿同时存在一个名字相同的名称时就会导致名称冲突。Excel 为解决此类冲突，默认情况下会使用工作表级的名称，因为局部工作表级的名称优先于全局工作簿级的名称。如果要使用工作簿级的名称的话，那就必须为此名称添加前缀来消除歧义，比如：Book1!销售 4、创建名称在 Excel 中一般可以通过三种方式来创建名称，如下所述： 编辑栏上的 "名称框": 我们可以直接在“名称框”中输入名字来命名所选定的单元格或单元格区域，通常，在名称框中显示的是所在单元格的行号列标，单击其右侧的下拉箭头，可以看到工作簿中的名称列表：
根据所选内容创建: 根据工作表中选定的单元格区域很方便的基于现有的行和列标签来创建名称：
使用“定义名称”对话框: 使用这种方式可以更加灵活的创建、编辑及使用名称(例如指定局部工作表级别适用范围或创建名称批注), 在该对话框中，可以定义常量名称和动态名称:
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 = 3Evaluate("MyName").Interior.ColorIndex = 3 将工作表中名称MyName所代表的单元格区域的背景设置为红色。 5、隐藏名称 Names.Add Name:="HideName", RefersTo:="=$A$1:$C$3", Visible:=False Names.Add Name:="HideName", RefersTo:="=$A$1:$C$3", Visible:=False 将隐藏所创建的名称。注意，如果再创建的名称与所隐藏的名称相同，则被隐藏的名称将被覆盖。 6、删除名称 Names("MyName3").Delete Names("MyName3").Delete 上面的代码删除当前工作簿中的名称MyName3。 注意，当前工作簿中重命名已有名称和删除名称时，要注意所要操作的名称是全局名称还是局部名称。如果为局部名称，则必须在代码中加上该名称所在工作表的引用。 三、VBA 中对名称的使用实例我们在第一部分了解和认识了名称的初步概念，在第二部分又学习了 VBA 中名称一些基本的操作，比如 添加和删除等，下面我们用一些具体的例子来进一步学习 VBA 中名称的运用 1、检查当前工作簿中某名称是否存在 Sub test() Dim str As Boolean str = NameExists("myName") If str = True Then MsgBox "该名称存在于当前工作簿中." Else MsgBox "该名称不存在." End If End Sub ‘- - - - - - - - - - - - - - - - - - - - - Function NameExists(FindName As String) As Boolean Dim rng As Range Dim myName As String On Error Resume Next myName = ActiveWorkbook.Names(FindName).Name If Err.Number = 0 Then NameExists = True End Function 或者： Function NameExists(TheName As String) As Boolean On Error Resume Next NameExists = Len(ThisWorkbook.Names(TheName).Name) 0 End Function 2、工作簿中的所有名称可见 Sub UnHideName() Dim Nm As Name For Each Nm In Names Nm.Visible = True Next End Sub 3、列出当前工作簿中所有名称的相关信息 Sub ShowNames() Dim N As Integer For N = 1 To ActiveWorkbook.Names.Count On Error Resume Next Cells(N, 1) = "'" & ActiveWorkbook.Names(N).Name Cells(N, 2) = "'" & ActiveWorkbook.Names(N).RefersToRange.Address Cells(N, 3) = "'" & ActiveWorkbook.Names(N).ShortcutKey Cells(N, 4) = "'" & ActiveWorkbook.Names(N).Visible Next End Sub 4、显示当前单元格所命名的名称 Sub ShowNames_activecell() On Error Resume Next MsgBox ActiveCell.Name.Name Select Case Err.Number Case 0 Case 1004 MsgBox "单元格" & ActiveCell.Address(4) & "没有命名。" Case Else MsgBox Err.Number & " -- " & Err.Description End Select End Sub 示例说明：如果要获取指定单元格所定义的名称，可以使用Name属性两次。5、删除当前工作簿中含有“name”字符的名称 Sub DeleteName() Dim Nm As Name For Each Nm In ActiveWorkbook.Names If Nm.Name Like "*name*" Then Nm.Delete Next Nm End Sub 6、判断某单元格或单元格区域是否与命名区域部分重叠 Function NameOfParentRange(Rng As Range) As String Dim Nm As Name For Each Nm In ThisWorkbook.Names If Rng.Parent.Name = Nm.RefersToRange.Parent.Name Then If Not Application.Intersect(Rng, Nm.RefersToRange) Is Nothing Then NameOfParentRange = Nm.Name Exit Function End If End If Next Nm NameOfParentRange = "" End Function 示例说明：如果Rng所代表的单元格或单元格区域与命名区域相交叉，则返回命名区域的名称，否则返回空。 四、有关名称的部分技巧 1、加大名称框的宽度 在Excel工作表的名称框中(如图1所示)，大约只能显示16个字符，当超过它所能容纳的字符时，后面的字符将会被截取，将不能看到完整的名称，这对前面的字符相同而区别在最后几个字符的名称来说，很不方便，但是在Excel中没有改变名称框尺寸的设置。这可通过调用 Windows API 来解决，通过调用API来增加下拉框的宽度。在VBE编辑器中插入一个标准模块，并输入以下的代码(代码可用于 32 位和64位 Excel)： #If Win64 Then Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr Public Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _ (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, _ ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr Public Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _ (ByVal hwnd As LongPtr, ByVal wMsg As Long, _ ByVal wParam As LongPtr, lParam As Any) As LongPtr #Else Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _ (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _ ByVal lpsz1 As String, ByVal lpsz2 As String) As Long Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _ (ByVal hwnd As Long, ByVal wMsg As Long, _ ByVal wParam As Long, lParam As Any) As Long #End If Public Const CB_SETDROPPEDWIDTH = &H160 Sub SetNameBoxDropWidth() Const xWidth = 600 '这里设置为你需要的宽度 Call SendMessage( _ FindWindowEx( _ FindWindowEx( _ FindWindow("XLMAIN", Application.Caption), _ 0, "EXCEL;", vbNullString), _ 0, "combobox", vbNullString), _ CB_SETDROPPEDWIDTH, xWidth, 0) End Sub效果如图：
示例说明：上述代码运行前后的结果如图3和图4所示。在上面的代码中，可以通过改变常量 xWidth 的值来定义下拉框的宽度。 2、为名称框定义快捷键Excel提供的快捷键中没有名称框的快捷键。但是，您能使用VBA代码设置快捷键，以方便能快速定位到名称框。 在VBE编辑器中，插入一个标准模块，并输入以下代码(代码可用于 32 位和64位 Excel)：
|
【本文地址】
今日新闻 |
推荐新闻 |