第9章 批注功能的使用
工作表中某些内容不便放在单元格中,但有存在的必要性,或者需要对某单元格数据做补充说明时,一般都采用对单元格添加批注的方式处理。本章讲解VBA在批注中的运用技巧。
● 实例65快捷键添加日期批注并自动缩放
● 实例66批量添加批注
● 实例67批量删除批注
● 实例68为指定列数据添加批注
● 实例69添加图片背景批注
● 实例70添加个性化批注
● 实例71批量修改批注外观
● 实例72为公式添加标注
实例65 快捷键添加日期批注并自动缩放
【技巧说明】 使用快捷键添加日期批注并使批注框缩放为适应日期数据大小。
【案例介绍】 如图2.66所示。出货表中的各种产品每月发一次货,日期不固定。现需输入送货数量时标示送货日期,且批注框刚好适应日期数据之大小。
【案例实现】 参见以下步骤:
[1] 使用快捷键Alt+F11进入VBE(Visual Basic Editor)环境。
[2] 单击菜单【插入】\【模块】,打开模块代码窗口。
[3] 在右边代码窗口输入以下代码:
Sub添加批注() Application.OnKey "^{q}", "添加批注" '设定快捷键 ActiveCell.AddComment '添加批注 With ActiveCell.Comment .Visible=False '批注不可见 .Text Text:=WorksheetFunction.Text(Date, "YYYY-MM-DD") '批注文本 为日期 .Shape.TextFrame.AutoSize=True '批注大小自动缩放 End With End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中单元格C4,利用快捷键Alt+F8调出运行宏窗口,然后单击“执行”按钮,将本程序设为快捷键Ctrl+Q,同时在C4已建立内容为今日日期之批注,如图2.67所示。以后单击任意单元格后,只需要利用快捷键Ctrl+Q即可建立批注。
图2.66 待添加日期批注的出货表
图2.67 利用快捷键产生的批注
提示
本实例参见光盘样本:..\第2部分\实例65.xlsm。
【相关知识说明】
(1)Application.OnKey:当按特定键或特定的组合键时运行指定的过程,可用此功能设定程序的快捷键。本例中^{q}即表示快捷键Ctrl+Q。OnKey的使用语法为:表达式.OnKey(Key, Procedure)。其中第一个参数可以使用特定的功能键。功能键与代码的对应列表见表2.4。
(2)AddComment:为区域添加批注。
(3)Shape.TextFrame.AutoSize=True:此处表示批注的外框自动缩放,以适应其中文字的宽度和高度。
表2.4 功能键与代码的对应列表
实例66 批量添加批注
【技巧说明】 批量对选定区域添加批注。
【案例介绍】 以实例65数据为例,当今日有多个产品出货时,不需要每个单元格使用快捷键产生批注,可以批量给区域添加批注以提升效率。数据如图2.68所示。
【案例实现】 参见以下步骤:
[1] 使用快捷键Alt+F11进入VBE(Visual Basic Editor)环境。
[2] 单击菜单【插入】\【模块】,打开模块代码窗口。
[3] 在右边代码窗口输入以下代码:
Sub批量添加批注() For Each Rng In Selection If Rng <> "" Then Rng.AddComment With Rng.Comment .Visible=False .Text Text:=WorksheetFunction.Text(Date, "YYYY-MM-DD") .Shape.TextFrame.AutoSize=True End With End If Next End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中单元格区域C3∶D9,然后利用快捷键Alt+F8调出运行宏窗口,然后单击“执行”按钮。
图2.68 待添加批注数据
图2.69 批量添加批注后的数据
提示
本实例参见光盘样本:..\第2部分\实例66.xlsm。
实例67 批量删除批注
【技巧说明】 批量删除选区内的批注。
【案例介绍】 以实例66数据为例,将实例66在区域中添加的批注一次性删除。
【案例实现】 参见以下步骤:
[1] 使用快捷键Alt+F11进入VBE(Visual Basic Editor)环境。
[2] 单击菜单【插入】\【模块】,打开模块代码窗口。
[3] 在右边代码窗口输入以下代码:
Sub批量删除批注() For Each Rng In Selection Rng.ClearComments Next End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中单元格区域C3∶D9,然后利用快捷键Alt+F8调出运行宏窗口,单击“执行”按钮,选区的批注将瞬间删除。
提示
本实例参见光盘样本:..\第2部分\实例67.xlsm
【相关知识说明】
ClearComments:删除区域中的批注。
实例68 为指定列数据添加批注
【技巧说明】 为指定列数据添加批注。
【案例介绍】 如图2.70所示,社保人员列表中无“工号”列,现需要鼠标指向姓名时显示员工工号。
【案例实现】 参见以下步骤:
[1] 使用快捷键Alt+F11进入VBE(Visual Basic Editor)环境。
[2] 单击菜单【插入】\【模块】,打开模块代码窗口。
[3] 在右边代码窗口输入以下代码:
Sub批量添加批注() For Each Rng In Selection If Rng <> "" Then Rng.AddComment With Rng.Comment .Visible=False .Text Text:="此员工号:"&WorksheetFunction.VLookup(Rng,Range("I2:J47"),2,0) .Shape.TextFrame.AutoSize=True End With End If Next End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中单元格区域B3∶B10,然后利用快捷键Alt+F8调出运行宏窗口,单击“执行”按钮,选区中员工姓名处自动添加其工号,工号与员工工号表中一致,如图2.71所示。
图2.70 待添加工号数据表
图2.71 已添加工号批注的数据表
提示
1.本实例参见光盘样本:..\第2部分\实例68.xlsm。
2.单元格中已有批注时不可用AddComment方法再添加批注,否则会出错。
【相关知识说明】
VLookup:在表格数组的首列查找值,并由此返回表格数组当前行中其他列的值。
实例69 添加图片背景批注
【技巧说明】 添加批注框背景为图片的批注。
【案例介绍】 人事资料表(见图2.72)直接存放员工照片将使工作表显得不美观,可以将照片存在批注中,鼠标移过时可以查看。
图2.72 待加照片的人事资料表
【案例实现】 参见以下步骤:
[1] 使用快捷键Alt+F11进入VBE(Visual Basic Editor)环境。
[2] 单击菜单【插入】\【模块】,打开模块代码窗口。
[3] 在右边代码窗口输入以下代码:
Sub插入图片标注() Dim pizhu, pic As String Set pizhu=ActiveCell.Comment If Not pizhu Is Nothing Then ActiveCell.Comment.Delete '已有批注则删除批注 Set pizhu=Nothing '释放变量 pic=Application.GetOpenFilename(ImgFileformat) '提取图片文件 If pic="False" Then End '选择取消则退出程序 With ActiveCell .AddComment '添加批注 .Comment.Visible=False '批注不可见 .Comment.Shape.Fill.Transparency=0# '批注不透明 .Comment.Shape.Fill.UserPicture pic '填充图片 .Comment.Shape.LockAspectRatio=msoTrue '设定图片透明度 .Comment.Shape.Height=30# '设定批注高度为30 End With ActiveCell.Select '设置图片比例,高与宽之比为3∶4 ActiveCell.Comment.Shape.ScaleWidth 3, msoFalse, msoScaleFromTopLeft ActiveCell.Comment.Shape.ScaleHeight 4, msoFalse, msoScaleFromTopLeft End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中单元格A3,利用快捷键Alt+F8调出运行宏窗口,然后单击“执行”按钮,将弹出“请选择员工照片”对话框,如图2.73所示。
[6] 选择照片,并单击“确定”按钮返回工作表,单元格A3即已完成图片批注的插入,如图2.74所示。
图2.73 “请选择员工照片”对话框
图2.74 图片背景的批注
提示
本实例参见光盘样本:..\第2部分\实例69.xlsm。
【相关知识说明】
GetOpenFilename:显示标准的“打开”对话框,并获取用户文件名。语法如下:
表达式.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)
各参数含义见表2.5。
表2.5 GetOpenFilename参数列表
实例70 添加个性化批注
【技巧说明】 添加边框更具个性化的批注。
【案例介绍】 为单元格添加批注,其边框不限于常规的四方形。
【案例实现】 参见以下步骤:
[1] 使用快捷键Alt+F11进入VBE(Visual Basic Editor)环境。
[2] 单击菜单【插入】\【模块】,打开模块代码窗口。
[3] 在右边代码窗口输入以下代码:
Sub添加个性化批注() Dim mystr As String, mystr2 As String, Comment ActiveCell.ClearComments mystr=InputBox("输入批注内容", "批注", Application.UserName, 10, 10) mystr2=InputBox("输入批注外型" & Chr(10) & "1为口哨型,2为书卷型,3为箭头型" _ & Chr(10) & "4为圆角矩形,5为缺角矩形,6为菱型,7为五角星.", "批注外型", 1, 10, 10) If mystr <> "" And mystr2 <> "" Then Set Comment=ActiveCell.AddComment With Comment .Visible=False .Text Text:=mystr .Visible=True .Shape.Select End With With Selection.ShapeRange If mystr2=1 Then .AutoShapeType= msoShapeFlowchartSequential AccessStorage If mystr2=2 Then .AutoShapeType=msoShapeFoldedCorner If mystr2=3 Then .AutoShapeType=msoShapeRightArrow If mystr2=4 Then .AutoShapeType= msoShapeRoundedRectangular Callout If mystr2=5 Then .AutoShapeType=msoShapePlaque If mystr2=6 Then .AutoShapeType=msoShapeDiamond If mystr2=7 Then .AutoShapeType=msoShape5pointStar End With ActiveCell.Select: Exit Sub End If End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中任意单元格,然后利用快捷键Alt+F8调出运行宏窗口,单击“执行”按钮,将弹出输入批注内容对话框,如图2.75所示。
[6] 输入内容后再弹出选择批注外型的对话框,可以根据提示输入相应的数字编号,如图2.76所示。
图2.75 输入批注内容
图2.76 选择批注外型
[7] 选择外型编号并确定后,单元格将产生对应外型的批注,如图2.77所示。
图2.77 生成个性化的批注
提示
本实例参见光盘样本:..\第2部分\实例70.xlsm。
【相关知识说明】
ShapeRange.AutoShapeType:返回或设置指定的Shape或ShapeRange对象的形状类型,该对象必须代表自选图形,而不能代表直线、任意多边形或连接符,可以用名称表示类型,也可以使用数值。AutoShapType类型表(名称和数值的对应关系)见表2.6。
表2.6 AutoShapeType类型表
续表
续表
续表
实例71 批量修改批注外观
【技巧说明】 批量修改批注外观。
【案例介绍】 一次性修改选区中所有批注的外观样式。
【案例实现】 参见以下步骤:
[1] 使用快捷键Alt+F11进入VBE(Visual Basic Editor)环境。
[2] 单击菜单【插入】\【模块】,打开模块代码窗口。
[3] 在右边代码窗口输入以下代码:
Sub批量修改批注外型() Dim i As Integer, cell As Range, mystr As String If TypeName(Selection) <> "Range" Then MsgBox "请选择单元格!", 64, " 【友情提示】": Exit Sub '未选中单元格则退出 On Error GoTo err '排错,当工作表中没有批注时会出错 mystr=InputBox("输入批注外型" & Chr(10) & "1为口哨型,2为书卷型,3为箭头型" _ & Chr(10) & "4为圆角矩形,5为缺角矩形,6为菱型,7为五角星.", "批注外型", 1, 10, 10) Application.ScreenUpdating=False For Each cell In Selection If Not Application.Intersect(cell, Cells.SpecialCells(xlCellTypeComments)) Is Nothing Then With cell.Comment .Visible=True .Shape.Select True End With With Selection.ShapeRange If mystr=1 Then .AutoShapeType=msoShapeFlowchartSequentialAccessStorage If mystr=2 Then .AutoShapeType=msoShapeFoldedCorner If mystr=3 Then .AutoShapeType=msoShapeRightArrow If mystr=4 Then .AutoShapeType=msoShapeRoundedRectangularCallout If mystr=5 Then .AutoShapeType=msoShapePlaque If mystr=6 Then .AutoShapeType=msoShapeDiamond If mystr=7 Then .AutoShapeType=msoShape5pointStar cell.Comment.Visible=False End With Else i=i+1 End If Next Application.ScreenUpdating=True If i=Selection.Count Then MsgBox "选区中没有批注!", 64, "提示" ActiveCell.Select: Exit Sub err: MsgBox "本工作表中没有批注!", 64, "提示" End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中任意区域,然后利用快捷键Alt+F8调出运行宏窗口,单击“执行”按钮,程序弹出输入对话框等待输入新批注样式编号(对话框和实例70中图2.76一样)。
[6] 在对话框中输入批注编号后,如果工作表中没有批注,则弹出提示如图2.78所示;如果工作表中有批注,但选区中没有批注,则弹出提示如图7.79所示。如果选区中有批注,则程序一次性将所有批注的样式修改为用户指定的样式。
图2.78 工作表中无批注的提示
图2.79 选区中无批注的提示
提示
本实例参见光盘样本:..\第2部分\实例71.xlsm。
实例72 为公式添加标注
【技巧说明】 为公式添加标注。
【案例介绍】 只要单元格格式不是文本,公式都会显示出最后结果,而非公式本身。本例将公式添加到批注中,指向单元格时就提示单元格公式。
【案例实现】 参见以下步骤:
[1] 使用快捷键Alt+F11进入VBE(Visual Basic Editor)环境。
[2] 单击菜单【插入】\【模块】,打开模块代码窗口。
[3] 在右边代码窗口输入以下代码:
Sub为公式添加标注() On Error GoTo endd Set Rng=Selection.Cells.SpecialCells(xlCellTypeFormulas) For Each cell In Rng cell.ClearComments With cell.AddComment .Text Text:=cell.Formula With .Shape .TextFrame.AutoSize=True .Fill.ForeColor.SchemeColor=44 .AutoShapeType=msoShapePlaque If Len(cell.Formula) > 50 And Len(cell.Formula) < 100 Then .Width=300 .Height=32 ElseIf Len(cell.Formula)>=100 And Len(cell.Formula)<150 Then .Width=300 .Height=48 ElseIf Len(cell.Formula)>=150 And Len(cell.Formula)<300 Then .Width=300 .Height=95 ElseIf Len(cell.Formula)>=300 And Len(cell.Formula)<1024 Then .Width=300 .Height=150 End If .Visible=False End With End With Next cell endd: End Sub
[4] 关闭VBE窗口返回到工作表。
[5] 选中单元格区域A1∶C8,然后利用快捷键Alt+F8调出运行宏窗口,并单击“执行”按钮,所有公式的单元格都已建立批注,其余单元格忽略,如图2.80所示。
图2.80 为公式添加批注
提示
本实例参见光盘样本:..\第2部分\实例72.xlsm。
【相关知识说明】
(1)Cells.SpecialCells(xlCellTypeFormulas):表示有公式的单元格。
(2)Shape:代表绘图层中的对象,如自选图形、任意多边形、OLE对象或图片。本例中用Shape.Width和Shape.Heigh来设置图形的宽和高。注意批注框也是一种图形对象。