在 Excel 的中名称是一个使用很频繁的东西，通过在Excel定义和使用名称，可以更好的管理工作表数据，方便地编写公式和设置表格。正如在工作表中定义和使用名称一样，在VBA中也可以创建和使用名称，并能利用名称处理工作表中的数据，这里我们就来具体的讲一讲Excel 名称及其 VBA 中的使用。
一、认识和理解名称1、什么是名称：所谓名称就是给单元格引用、常量、公式或者表格取一个有意义的名字，便于你了解和记忆这些对象，比如像下面的这些例子所表现的那样：
Excel 的可以创建和使用名称可以分为以下两种类型
已定义的名称:代表单元格、单元格区域、公式或常量值的名称。您可以创建自己的已定义名称，有时 Excel 也会为您创建已定义名称，例如当您设置打印区域时。
表名称:Excel 中表格的名称，每次插入 Excel 表时，Excel 都会创建如 表1、表2 等默认 Excel 表名称，如果有需要您也可以修改这些默认名称。
3、名称的有效范围名称的适用范围可以分为工作表级和工作簿级，其主要的区别如下：
工作表级(局部名称): 此类名称的适用范围为定义其的工作表。比如在 Sheet1 中定义一个名称叫 "销售"，这个名称在没有限定的情况下只能在Sheet1中被识别，如果要在其他的表格中使用这个名称，就必须在名称前加上定义工作表的名字来限定他。比如：Sheet1!销售
工作簿级(全局名称): 此类名称的适用范围为工作簿。工作簿中的所有工作表而言都可以识别并使用这个名称。但其他的工作簿是不能识别和使用的。
注意： 名称的名字在其的适用范围必须是唯一的。但是你可以在不同的范围定义名字相同的名称，比如您可以为 Sheet1,Sheet2和Sheet3都定义一个名叫 "销售" 的名称。你甚至还可以定义一个工作簿级的名叫 "销售" 名称。但工作表和工作簿同时存在一个名字相同的名称时就会导致名称冲突。Excel 为解决此类冲突，默认情况下会使用工作表级的名称，因为局部工作表级的名称优先于全局工作簿级的名称。如果要使用工作簿级的名称的话，那就必须为此名称添加前缀来消除歧义，比如：Book1!销售
4、创建名称在 Excel 中一般可以通过三种方式来创建名称，如下所述：
编辑栏上的 "名称框": 我们可以直接在“名称框”中输入名字来命名所选定的单元格或单元格区域，通常，在名称框中显示的是所在单元格的行号列标，单击其右侧的下拉箭头，可以看到工作簿中的名称列表：
根据所选内容创建: 根据工作表中选定的单元格区域很方便的基于现有的行和列标签来创建名称：
使用“定义名称”对话框: 使用这种方式可以更加灵活的创建、编辑及使用名称(例如指定局部工作表级别适用范围或创建名称批注),在该对话框中，可以定义常量名称和动态名称:
名称主要通过以下几种方式来输入:
直接键入: 直接在单元格等中输入名称。
使用"公式记忆式键入" :使用"公式记忆式键入"下拉列表，其中自动为您列出了有效名称。
使用 "用于公式" 命令项: 从“公式”选项卡“定义的名称”组中“用于公式”菜单的下拉列表中选择已定义名称。
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样式的引用，则最后使用绝对引用，否则所命名的区域将会不确定。
在所命名的名称前加上工作表名，则创建局部名称，如：
强国说https://www.qiangguoshuo.com
ActiveWorkbook.Names.Add Name:="Sheet1!MyName1", RefersTo:="=Sheet1!$B$2:$D$6" |
Worksheets("sheet2").Names.Add Name:="MyName2", RefersTo:="=Sheet2!$A$1:$B$3" |
Worksheets("Sheet1").Range("B8:C10").Name = "MyName3" |
Worksheets("Sheet2").Range("H15:G16").Name = "Sheet2!MyName4" |
Worksheets("Sheet1").Range("E6:F8").Name = "Sheet2!MyName5" |
Worksheets("sheet2").Names.Add Name:="MyName5", RefersTo:="=Sheet1!$E$6:$F$8" |
Names.Add Name:="NameNumber", RefersTo:=666 |
Names.Add Name:="NameString", RefersTo:="TV" |
Dim MyArray(10)Dim i As IntegerFor i = 1 To 10 MyArray(i) = iNext iNames.Add Name:="NameArray", RefersTo:=MyArray |
Names.Add Name:="NameFormlas", RefersTo:="=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)" |
Worksheets("Sheet2").Names("MyName5").Name = "MyName6" |
Worksheets(“Sheet1”).Names.Add Selection.Name.Name,Sheet1.Range(“B3:C4”) |
使用Evaluate方法，例如：代码
Evaluate("MyName").Interior.ColorIndex = 3 |
Evaluate("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 IfEnd 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 = TrueEnd Function |
Function NameExists(TheName As String) As Boolean On Error Resume Next NameExists = Len(ThisWorkbook.Names(TheName).Name) <> 0End Function |
Sub UnHideName() Dim Nm As Name For Each Nm In Names Nm.Visible = True NextEnd Sub |
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 NextEnd Sub |
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 SelectEnd Sub |
Sub DeleteName() Dim Nm As Name For Each Nm In ActiveWorkbook.Names If Nm.Name Like "*name*" Then Nm.Delete Next NmEnd Sub |
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 |
在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 IfPublic Const CB_SETDROPPEDWIDTH = &H160Sub 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)：
#If Win64 Then Public Declare PtrSafe Function SetFocus Lib "user32" (ByVal hwnd As LongPtr) As LongPtr 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#Else Public Declare Function SetFocus Lib "user32" (ByVal hwnd As Long) As Long 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#End IfSub SetFocusNameBox() Call SetFocus( _ FindWindowEx( _ FindWindowEx( _ FindWindow("XLMAIN", Application.Caption), _ 0, "EXCEL;", vbNullString), _ 0, "combobox", vbNullString))End Sub |
转载声明：本站发布文章及版权归原作者所有，转载本站文章请注明文章来源！
