| VBA中名稱的使用
分類:ExcelVBA>>技術(shù)技巧
理解工作表中的名稱在Excel工作表中，一般可以通過兩種方式來定義名稱。第一種方式是直接在“名稱框”(如圖1所示)中輸入名字來命名所選定的單元格或單元格區(qū)域，通常，在名稱框中顯示的是所在單元格的行號列標，單擊其右側(cè)的下拉箭頭，可以看到工作簿中的名稱列表。第二種方式是使用“定義名稱”對話框來指定名稱(選取菜單“插入——名稱——定義”命令后，調(diào)出“定義名稱”對話框，如圖2所示)，在該對話框中，可以定義名稱常量和動態(tài)名稱，而這些名稱則不會出現(xiàn)在“名稱框”中。 圖1：名稱框 圖2：定義名稱對話框
名稱又分為全局名稱和局部名稱。全局名稱可以在當前工作簿中的任一工作表中使用，而局部名稱只能在指定的工作表中使用。如圖2中所示，name1為全局名稱，name2為局部名稱，即局部名稱所在的工作表在該名稱的右側(cè)。
此外，正如前所述，還可以在“定義名稱”框中定義名稱常量，如圖2中的x。還可以動態(tài)命名，如圖2中的NameList。它們在引用位置中進行輸入。
通過在工作表中定義名稱，可以更好的管理工作表數(shù)據(jù)，方便地編寫公式和設(shè)置表格。正如在工作表中定義和使用名稱一樣，在VBA中也可以創(chuàng)建和使用名稱，并能利用名稱處理工作表中的數(shù)據(jù)。
在VBA中對名稱的基本操作
(1) 創(chuàng)建名稱
① 可以使用下面的代碼在當前工作簿中創(chuàng)建名稱：
ActiveWorkbook.Names.Add Name:="MyName", RefersToR1C1:="=Sheet1!R2C2:R6C4"或者ActiveWorkbook.Names.Add Name:="MyName", RefersTo:="=Sheet1!$B$2:$D$6"
上面的代碼在當前工作簿中將工作表Sheet1內(nèi)的區(qū)域B2：D6命名為MyName，該名稱為全局名稱。在所命名的名稱中不能出現(xiàn)空格和單元格引用，并且，如果對命名區(qū)域使用A1樣式的引用，則最后使用絕對引用，否則所命名的區(qū)域?qū)淮_定。
在所命名的名稱前加上工作表名，則創(chuàng)建局部名稱，如：
ActiveWorkbook.Names.Add Name:="Sheet1!MyName1", RefersTo:="=Sheet1!$B$2:$D$6"
上面的代碼在工作表Sheet1中命名區(qū)域B2:D6為MyName1，該名稱為局部名稱。
也通過引用指定工作表來創(chuàng)建局部名稱，如：
Worksheets("sheet2").Names.Add Name:="MyName2", RefersTo:="=Sheet2!$A$1:$B$3"
上面的代碼在工作表Sheet2中創(chuàng)建一個局部名稱MyName2，代表Sheet2中的單元格區(qū)域A1：B3。
② 一種簡單的命名方法。例如：
Worksheets("Sheet1").Range("B8:C10").Name = "MyName3"
上面的代碼將工作表Sheet1中的單元格區(qū)域B8:C10命名為MyName3，為全局名稱。
Worksheets("Sheet2").Range("H15:G16").Name = "Sheet2!MyName4"
上面的代碼將工作表Sheet2中的單元格區(qū)域H15:G16命名為MyName4，為局部名稱。
注意，這種方法只能應用于命名單元格區(qū)域，不能夠用來命名公式、數(shù)字等。
③ 在當前工作表中命名局部區(qū)域，該區(qū)域為其它工作表中的單元格區(qū)域。
Worksheets("Sheet1").Range("E6:F8").Name = "Sheet2!MyName5"
或者：
Worksheets("sheet2").Names.Add Name:="MyName5", RefersTo:="=Sheet1!$E$6:$F$8"
上面的代碼在工作表Sheet2中命名工作表Sheet1中單元格區(qū)域E6:F8為MyName5，該名稱為Sheet2中的局部名稱。
④ 命名數(shù)字
Names.Add Name:="NameNumber", RefersTo:=666，將數(shù)字666命名為NameNumber。
⑤ 命名字符串
Names.Add Name:="NameString", RefersTo:="TV"，將字符串TV命名為NameString。
⑥ 命名數(shù)組
Dim MyArray(10)
Dim i As Integer
For i = 1 To 10
MyArray(i) = i
Next i
Names.Add Name:="NameArray", RefersTo:=MyArray
上述代碼先對數(shù)組賦值，然后指定名稱。
⑦ 命名公式
Names.Add Name:="NameFormlas", RefersTo:="=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)"
上面的代碼命名了一個公式，可以用來創(chuàng)建動態(tài)表格或引用動態(tài)的區(qū)域。
使用上面的方法來命名數(shù)字、字符串、數(shù)組或公式存儲經(jīng)常要使用的值，比將該值存放在單元格中更有優(yōu)勢，它可以避免該值被易外修改并減少了對象的引用。
(2) 重命名已有的名稱
Worksheets("Sheet2").Names("MyName5").Name = "MyName6"
上面的代碼將工作表Sheet2中的局部名稱MyName5改名為MyName6。但這種方法只能在單元格或單元格區(qū)域中進行重命名使用，而不能重命名代表公式、數(shù)組和字符串的名稱。
(3) 改變所選區(qū)域所命名的名稱的引用區(qū)域
Worksheets(“Sheet1”).Names.Add Selection.Name.Name,Sheet1.Range(“B3:C4”)
上面的代碼將原來所選區(qū)域的名稱的引用區(qū)域改為單元格區(qū)域B3:C4，即該名稱所代表的區(qū)域已變?yōu)锽3:C4，原來命名區(qū)域名稱被取消。
(4) 提取命名區(qū)域。使用Evaluate方法，例如：
代碼Evaluate("MyName").Interior.ColorIndex = 3將工作表中名稱MyName所代表的單元格區(qū)域的背景設(shè)置為紅色。
(5) 隱藏名稱
Names.Add Name:="HideName", RefersTo:="=$A$1:$C$3", Visible:=False，將隱藏所創(chuàng)建的名稱。注意，如果再創(chuàng)建的名稱與所隱藏的名稱相同，則被隱藏的名稱將被覆蓋。
(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 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]列出當前工作簿中所有名稱的相關(guān)信息
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.Des cription
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
End If
Next Nm
End Sub
[示例6] 判斷某單元格或單元格區(qū)域是否與命名區(qū)域部分重疊(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所代表的單元格或單元格區(qū)域與命名區(qū)域相交叉，則返回命名區(qū)域的名稱，否則返回空。
名稱的高級操作
(下面的內(nèi)容整理自Chip Pearson的文章) [增大名稱框的尺寸]在Excel工作表的名稱框中(如圖1所示)，大約只能顯示16個字符，當超過它所能容納的字符時，后面的字符將會被截取，將不能看到完整的名稱，這對前面的字符相同而區(qū)別在最后幾個字符的名稱來說，很不方便，但是在Excel中沒有改變名稱框尺寸的設(shè)置。這可通過調(diào)用Windows API來解決，通過調(diào)用API來增加下拉框的寬度。 在VBE編輯器中插入一個標準模塊，并輸入以下的代碼： 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 ‘- - - - - - - - - - - - - - - - - - - - - - - - -
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 示例說明：上述代碼運行前后的結(jié)果如圖3和圖4所示。在上面的代碼中，可以通過改變常量cWidth(<<<所示的代碼行)的值來定義下拉框的寬度。 圖3：原名稱框 圖4：修改后的名稱框
[為名稱框定義快捷鍵]
Excel提供的快捷鍵中沒有名稱框的快捷鍵。但是，您能使用VBA代碼設(shè)置快捷鍵，以方便能快速定位到名稱框。 在VBE編輯器中，插入一個標準模塊，并輸入以下代碼： 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 ‘- - - - - - - - - - - - - - - - - - - - - - - - -
Sub SetFocusNameBox() Dim Res As Long Res = SetFocus( _ FindWindowEx( _ FindWindowEx( _ FindWindow("XLMAIN", Application.Caption) _ , 0, "EXCEL;", vbNullString) _ , 0, "combobox", vbNullString)) End Sub 在Excel中，選擇菜單“工具——宏——宏”命令，調(diào)出“宏”對話框，為剛創(chuàng)建的SetFocusNameBox代碼指定快捷鍵，如Ctrl+Shift+N。那么，以后在該工作簿中，按下Ctrl+Shift+N組合鍵，即可定位到名稱對話框。 By fanjy in 2006-11-16
| 
