【Excel】VBA中名称的使用.doc

【Excel】VBA中名称的使用理解工作表中的名称在Excel工作表中，一般可以通过两种方式来定义名称。第一种方式是直接在“名称框”(如图1所示)中输入名字来命名所选定的单元格或单元格区域，通常，在名称框中显示的是所在单元格的行号列标，单击其右侧的下拉箭头，可以看到工作簿中的名称列表。第二种方式是使用“定义名称”对话框来指定名称(选取菜单“插入名称定义”命令后，调出“定义名称”对话框，如图2所示)，在该对话框中，可以定义名称常量和动态名称，而这些名称则不会出现在“名称框”中。名称又分为全局名称和局部名称。全局名称可以在当前工作簿中的任一工作表中使用，而局部名称只能在指定的工作表中使用。如图2中所示，name1为全局名称，name2为局部名称，即局部名称所在的工作表在该名称的右侧。此外，正如前所述，还可以在“定义名称”框中定义名称常量，如图2中的x。还可以动态命名，如图2中的NameList。它们在引用位置中进行输入。通过在工作表中定义名称，可以更好的管理工作表数据，方便地编写公式和设置表格。正如在工作表中定义和使用名称一样，在VBA中也可以创建和使用名称，并能利用名称处理工作表中的数据。在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 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)上面的代码命名了一个公式，可以用来创建动态表格或引用动态的区域。使用上面的方法来命名数字、字符串、数组或公式存储经常要使用的值，比将该值存放在单元格中更有优势，它可以避免该值被易外修改并减少了对象的引用。(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。注意，当前工作簿中重命名已有名称和删除名称时，要注意所要操作的名称是全局名称还是局部名称。如果为局部名称，则必须在代码中加上该名称所在工作表的引用。命名名称注意事项(1) 名称的最大长度为255个字符。(2) 名称可以字母、空格或下划线开头。(3) 名称中不能包含空格、连字线等字符。(4) 避免在名称中使用与单元格引用相同的字符，如A1、G11等。(5) 避免在名称中使用Excel的保留字，如：Criteria、Database、Extract、Print_Area、Print_Titles等。一些示例示例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示例2工作簿中的所有名称可见Sub UnHideName() Dim Nm As Name For Each Nm In Names Nm.Visible = True NextEnd 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 NextEnd 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 SelectEnd Sub示例说明：如果要获取指定单元格所定义的名称，可以使用Name属性两次。示例5删除当前工作簿中含有“name”字符的名称Sub DeleteName() Dim Nm As Name For Each Nm In ActiveWorkbook.Names If Nm.Name Like *name* Then Nm.Delete End If Next NmEnd Sub示例6 判断某单元格或单元格区域是否与命名区域部分重叠(By Chip Pearson)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所代表的单元格或单元格区域与命名区域相交叉，则返回命名区域的名称，否则返回空。名称的高级操作(下面的内容整理自Chip Pearson的文章)增大名称框的尺寸在Excel工作表的名称框中(如图1所示)，大约只能显示16个字符，当超过它所能容纳的字符时，后面的字符将会被截取，将不能看到完整的名称，这对前面的字符相同而区别在最后几个字符的名称来说，很不方便，但是在Excel中没有改变名称框尺寸的设置。这可通过调用Windows API来解决，通过调用API来增加下拉框的宽度。在VBE编辑器中插入一个标准模块，并输入以下的代码：Public Declare Function FindWindow Lib user32 Alias FindWindowA _ (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPublic Declare Function FindWindowEx Lib user32 Alias FindWindowExA _ (ByVal hWnd1 As Long, ByVal hWnd2 As Long, _ ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPublic Declare Function SendMessage Lib user32 Alias SendMessageA _ (ByVal hwnd As Long, ByVal wMsg As Long, _ ByVal wParam As Long, lParam As Any) As Long- - - - - - - - - - - - - - - - - - - - - - - - -Sub WidenNameBoxDrop2() Dim Res As Long Const CB_SETDROPPEDWIDTH = &H160 Const cWidth = 400 Res = SendMessage( _ FindWindowEx( _ FindWindowEx( _ FindWindow(XLMAIN, Application.Caption) _ , 0, EXCEL;, vbNullString) _ , 0, combobox, vbNullString), _ CB_SETDROPPEDWIDTH, cWidth, 0)End Sub示例说明：上述代码运行前后的结果如图3和图4所示。在上面的代码中，可以通过改变常量cWidth(所示的代码行)的值来定义下拉框的宽度。为名称框定义快捷键Excel提供的快捷键中没有名称框的快捷键。但是，您能使用VBA代码设置快捷键，以方便能快速定位到名称框。在VBE编辑器中，插入一个标准模块，并输入以下代码：Public Declare Function SetFocus Lib user32 (ByVal hwnd As Long) As LongPublic Declare Function F