VBA中名称的使用

1、excelvba 技术技巧http:/ - 1 -vba 中名称的使用理解工作表中的名称在 excel 工作表中， 一般可以通过两种方式来定义名称。第一种方式是直接在“名称框” (如图 1 所示 )中输入名字来命名所选定的单元格或单元格区域，通常，在名称框中显示的是所在单元格的行号列标，单击其右侧的下拉箭头，可以看到工作簿中的名称列表。第二种方式是使用“定义名称”对话框来指定名称(选取菜单“插入名称定义”命令后，调出“定义名称”对话框，如图2 所示 )，在该对话框中，可以定义名称常量和动态名称，而这些名称则不会出现在“名称框”中。图 1：名称框图 2：定义名称对话框名称又分为全 局名称和 局部

2、 名称。全局 名称可以在 当前 工作簿中的 任一工作表中使用， 而 局部名称 只能 在指定的工作表中使用。如图2 中所示， name1 为全 局名称， name2 为局部 名称， 即局部 名称所在的工作表在该名称的右侧。此外 ，正如前所述，还可以在“定义名称”框中定义名称常量，如图2 中的 x。还可以动态命名，如图2 中的 namelist 。它们 在引用位置 中进行输入。通过在工作表中定义名称，可以 更好 的 管理 工作表 数据 ，方便地编写公 式和 设置 表格。 正 如在工作表中定义和使用名称一样，在 vba 中也可以 创建 和使用名称，并能利 用名称 处理 工作表中的 数据 。在 vba

3、 中对名称的基本操作(1) 创建 名称 可以使用下 面的代码 在 当前 工作簿中 创建 名称：activeworkbook.names.add name:=myname, referstor1c1:=sheet1!r2c2:r6c4excelvba 技术技巧http:/ - 2 -或者 activeworkbook.names.add name:=myname, refersto:=sheet1!$b$2:$d$6 上面 的代码 在当前 工作簿中 将工作表 sheet1 内的区域 b2：d6 命名为 myname ，该名称为全局 名称。在所命名的名称中不能 出现 空格和单元格 引用， 并且 ，

4、如 果对命名区域使用a1样式的 引用，则 最 后使用 绝对引用， 否则所命名的区域将会不 确定。在所命名的名称前加上 工作表名，则创建局部 名称，如：active workbook.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 上面 的代码

5、 在工作表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 ，为 局部 名称。注意 ，这种方 法只能应 用

6、于命名单元格区域，不能够 用来命名 公式、数 字等。 在当前 工作表中命名 局部 区域，该区域为其它工作表中的单元格区域。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, r

7、efersto:=666 ，将数 字 666 命名为 namenumber 。 命名字 符串names.add name:=namestring, refersto:=tv ，将字符串 tv 命名为 namestring 。 命名 数组dim myarray(10) dim i as integer for i = 1 t o 10 myarray(i) = i next i names.add name:=namearray, refersto:=myarray 上述代码先 对数组赋值 ，然后指定名称。 命名 公式names.add name:=nameformlas, refersto:=

8、offset(sheet1!$a$1,0,0,counta(sheet1!$a:$a),1) 上面 的代码 命名 了 一个公 式，可以用来 创建 动态表格或 引用动态的区域。使用 上面 的方 法来命名 数字、字符串、 数组 或公式存储经 常要使用的 值，比将 该值存放 在单元格中 更有优势 ， 它可以 避免 该值被易外修改并减少了对象的引用。(2) 重命名 已有的名称worksheets(sheet2).names(myname5).name = myname6 上面 的代码将 工作表 sheet2 中的 局部 名称 myname5 改 名为 myname6 。 但这种方 法只能excelvb

9、a 技术技巧http:/ - 3 -在单元格或单元格区域中进行重命名使用，而不能 重命名 代表公式 、数组 和字 符串 的名称。(3) 改变 所选区域所命名的名称的引用区域worksheets( “ sheet1 ” ).names.add selection.name.name,sheet1.range(“ b3:c4 ” ) 上面 的代码将 原来所选区域的名称的引用区域 改为单元格区域b3:c4 ， 即 该名称所 代表的区域已变 为 b3:c4 ， 原来命名区域名称被取消。(4) 提取命名区域。使用evaluate 方法，例如：代码 evaluate(myname).interior.co

10、lorindex = 3将工作表中名称myname 所代表的单元格区域的 背景 设置 为红色 。(5) 隐藏 名称names.add name:=hidename, refersto:=$a$1:$c$3, visible:=false， 将隐藏 所创建的名称。 注意 ，如 果再创建 的名称 与所隐藏 的名称 相同 ，则 被隐藏 的名称 将被 覆盖 。(6) 删除 名称names(myname3).delete 上面 的代码 删除 当前 工作簿中的名称myname3 。注意 ， 当前 工作簿中 重 命名 已有名称和 删除 名称 时，要注意 所要操作的名称是全 局名称 还 是局部 名称。如 果为

11、 局部 名称，则 必须 在代码 中加上 该名称所在工作表的引用。命名名称注意事项(1) 名称的 最大长度 为 255 个字符。(2) 名称可以字 母、空 格或下 划线开 头。(3) 名称中不 能包含 空格 、连字线等字符 。(4) 避免 在名称中使用 与 单元格 引用相同 的字 符，如 a1、g11 等。(5) 避免 在名称中使用excel的保留 字，如： criteria 、database 、extract 、print_area 、print_titles等。一些示例示例 1 检查 当前 工作簿中 某名称是 否存 在sub test() dim str as boolean str =

12、nameexists(myname) if str = true then msgbox 该名称 存在于当前 工作簿中 . else msgbox 该名称不 存在. end if end sub - - - - - - - - - - - - - - - - - - - - - function nameexists(findname as string) as boolean excelvba 技术技巧http:/ - 4 -dim rng as range dim myname as string on error resume next myname = activeworkbook.n

13、ames(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 = tru

14、e next end sub 示例 3列出 当前 工作簿中所 有 名称的 相关信息sub shownames() dim n as integer for n = 1 t o 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).short

15、cutkey 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 & - &am

16、p; err.description end select end sub excelvba 技术技巧http:/ - 5 -示例 说明 ：如 果要 获取指定单元格所定义的名称，可以使用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 判断某 单元格或单元格区域是否与命名区域 部分重叠 (by c

17、hip 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

18、nameofparentrange = end function 示例 说明 ：如 果 rng 所代 表的单元格或单元格区域与命名区域 相交叉 ，则 返回 命名区域的名称， 否则返回 空 。名称的高级操作(下面的内容整理自chip pearson的文章 ) 增大名称框的尺寸在 excel 工作表的名称框中(如图 1 所示 )，大约 只能 显示 16 个字符，当超 过它所能容纳的字 符时，后面的字 符将 会被截取，将 不能看到 完整 的名称， 这对 前面 的字 符相同 而区别 在最后几个字 符的名称来 说，很不方 便，但是在 excel 中没有改 变名称框 尺寸 的设置。这可通过调用window

19、s api来解决 ，通过调用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 _ excelvba 技术技巧http:/ - 6 -(byval hwnd as long, byval wmsg as long, _ byval wparam as long, lparam as any) as long - - - - - - - - -