|
Range对象是Excel应用程序中最常用的对象，一个Range对象代表一个单元格、一行、一列、包含一个或者更多单元格区域（可以是连续的单元格，也可以是不连续的单元格）中选定的单元格，甚至是多个工作表上的一组单元格，在操作Excel 内的任何区域之前都需要将其表示为一个Range对象，然后使用该Range对象的方法和属性。
技巧1 单元格的引用方法
在VBA中经常需要引用单元格或单元格区域区域，主要有以下几种方法。
1-1 使用Range属性
VBA中可以使用Range属性返回单元格或单元格区域，如下面的代码所示。 #001 Sub RngSelect() #002 Sheet1.Range("A3:F6, B1:C5").Select #003 End Sub 代码解析： RngSelect过程使用Select方法选中A3:F6，B1:C5单元格区域。 Range属性返回一个Range对象，该对象代表一个单元格或单元格区域，语法如下： Range(Cell1, Cell2) 参数Cell1是必需的，必须为 A1 样式引用的宏语言，可包括区域操作符（冒号）、相交区域操作符（空格）或合并区域操作符（逗号）。也可包括美元符号（即绝对地址，如“$A$1”）。可在区域中任一部分使用局部定义名称，如Range("B2:LastCell")，其中LastCell为已定义的单元格区域名称。 参数Cell2是可选的，区域左上角和右下角的单元格。 运行Sub RngSelect过程，选中A3:F6, B1:C5单元格区域，如图 1-1所示。
图 1-1 使用Range属性引用单元格区域 注意 如果没有使用对象识别符，Range属性返回活动表的一个区域，如果活动表不是工作表，则该属性无效。 1-2 使用Cells属性 使用Cells属性返回一个Range对象，如下面的代码所示。 #001 Sub Cell() #002 Dim icell As Integer #003 For icell = 1 To 100 #004 Sheet2.Cells(icell, 1).Value = icell #005 Next #006 End Sub 代码解析： Cell过程使用For...Next语句为工作表中的A1:A100单元格区域填入序号。 Cells属性指定单元格区域中的单元格，语法如下： Cells(RowIndex, ColumnIndex) 参数RowIndex是可选的，表示引用区域中的行序号。 参数ColumnIndex是可选的，表示引用区域中的列序号。 如果缺省参数，Cells属性返回引用对象的所有单元格。 Cells属性的参数可以使用变量，因此经常应用于在单元格区域中循环。
1-3 使用快捷记号
在VBA中可以将A1引用样式或命名区域名称使用方括号括起来，作为Range属性的快捷方式，这样就不必键入单词“Range”或使用引号，如下面的代码所示。 #001 Sub Fastmark() #002 [A1：A5] = 2 #003 [Fast] = 4 #004 End Sub 代码解析： Fastmark过程使用快捷记号为单元格区域赋值。 第2行代码使用快捷记号将活动工作表中的A1：A5单元格赋值为2。 第3行代码将工作簿中已命名为“Fast”的单元格区域赋值为4。 注意 使用快捷记号引用单元格区域时只能使用固定字符串而不能使用变量。
1-4 使用Offset属性
可以使用Range对象的Offset属性返回一个基于引用的Range对象的单元格区域，如下面的代码所示。 #001 Sub Offset() #002 Sheet3.Range("A1:C3").Offset(3, 3).Select #003 End Sub 代码解析： Offset过程使用Range对象的Offset属性选中A1：A3单元格偏移三行三列后的区域。 应用于Range对象的Offset 属性的语法如下： expression.Offset(RowOffset, ColumnOffset) 参数expression是必需的，该表达式返回一个Range对象。 参数RowOffset是可选的，区域偏移的行数（正值、负值或 0（零））。正值表示向下偏移，负值表示向上偏移，默认值为 0。 参数ColumnOffset是可选的，区域偏移的列数（正值、负值或 0（零））。正值表示向右偏移，负值表示向左偏移，默认值为 0。 运行Offset过程，选中A1：A3单元格偏称三行三列后的区域，如图 1-2所示。
图 1-2 使用Range对象的Offset属性
1-5 使用Resize属性
使用Range对象的Resize属性调整指定区域的大小，并返回调整大小后的单元格区域，如下面的代码所示。 #001 Sub Resize() #002 Sheet4.Range("A1").Resize(3, 3).Select #003 End Sub 代码解析： Resize过程使用Range对象的Resize属性选中A1单元格扩展为三行三列后的区域。 Resize属性的语法如下： expression.Resize(RowSize, ColumnSize) 参数expression是必需的，返回要调整大小的Range 对象 参数RowSize是可选的，新区域中的行数。如果省略该参数，则该区域中的行数保持不变。 参数ColumnSize是可选的，新区域中的列数。如果省略该参数。则该区域中的列数保持不变。 运行Resize过程，选中A1单元格扩展为三行三列后的区域，如图 1-3所示。
图 1-3 使用Resize属性调整区域大小
1-6 使用Union方法
使用Union方法可以将多个非连续区域连接起来成为一个区域，从而可以实现对多个非连续区域一起进行操作，如下面的代码所示。 #001 Sub UnSelect() #002 Union(Sheet5.Range("A1:D4"), Sheet5.Range("E5:H8")).Select #003 End Sub 代码解析： UnSelect过程选择单元格A1:D4和E5:H8所组成的区域。Union方法返回两个或多个区域的合并区域，语法如下： expression.Union(Arg1, Arg2, ...) 其中参数expression是可选的，返回一个Application对象。 参数Arg1, Arg2, ...是必需的，至少指定两个Range对象。 运行UnSelect过程，选中单元格A1:D4和E5:H8所组成的区域，如图 1-4所示。
图 1-4 使用Union方法将多个非连续区域连接成一个区域
1-7 使用UsedRange属性
使用UsedRange属性返回指定工作表上已使用单元格组成的区域，如下面的代码所示。 #001 Sub UseSelect() #002 Sheet6.UsedRange.Select #003 End Su 代码解析： UseSelect过程使用UsedRange属性选择工作表上已使用单元格组成的区域，包括空单元格。如工作表中已使用A1单元格和D8单元格，运行UseSelect过程将选择A1到D8单元格区域，如图 1-5所示。
图 1-5 使用UsedRange属性选择已使用区域
1-8 使用CurrentRegion属性
使用CurrentRegion属性返回指定工作表上当前的区域，如下面的代码所示。 #001 Sub CurrentSelect() #002 Sheet7.Range("A5").CurrentRegion.Select #003 End Sub 代码解析： CurrentSelect过程使用CurrentRegion属性选择工作表上A5单元格当前的区域，当前区域是一个边缘是任意空行和空列组合成的范围。 运行CurrentSelect过程将选择A5到B6单元格区域，如图 1-6所示。
图 1-6 CurrentRegion属性选择当前的区域
技巧2 选定单元格区域的方法
2-1 使用Select方法
在VBA中一般使用Select方法选定单元格或单元格区域，如下面的代码所示。 #001 Sub RngSelect() #002 Sheet3.Activate #003 Sheet3.Range("A1：B10").Select #004 End Sub 代码解析： RngSelect过程使用Select方法选定Sheet3中的A1：B10单元格区域，Select方法应用于Range对象时语法如下： expression.Select(Replace) 参数expression是必需的，一个有效的对象。 参数Replace是可选的，要替换的对象。 使用Select方法选定单元格时，单元格所在的工作表必需为活动工作表，所以在第2行代码中先使用Activate方法使Sheet3成为活动工作表，否则Select方法有可能出错，显示如图 2-1所示的错误提示。
图 2-1 Select方法无效提示
2-2 使用Activate方法
还可以使用Activate方法选定单元格或单元格区域，如下面的代码所示。 #001 Sub RngActivate() #002 Sheet3.Activate #003 Sheet3.Range("A1:B10").Activate #004 End Sub 代码解析： RngActivate过程使用Activate方法选定Sheet3中的A1：B10单元格区域，Activate方法应用于Range对象时语法如下： expression.Activate 使用Activate方法选定单元格时，单元格所在的工作表也必需为活动工作表，否则Activate方法有可能出错，显示如图 2-2所示的错误提示。
图 2-2 Activate方法无效提示
2-3 使用Goto方法
使用Goto方法无需使单元格所在的工作表成为活动工作表，如下面的代码所示。 #001 Sub RngGoto() #002 Application.Goto Reference:=Sheet3.Range("A1：B10"), scroll:=True #003 End Sub 代码解析： RngGoto过程使用Goto方法选定Sheet3中的A1：B10单元格区域，并滚动工作表以显示该单元格。 Goto方法选定任意工作簿中的任意区域或任意Visual Basic过程，并且如果该工作簿未处于活动状态，就激活该工作簿，语法如下： expression.Goto(Reference, Scroll) 参数expression是必需的，返回一个Application 对象。 参数Reference是可选的，Variant类型，指定目标。可以是Range对象、包含R1C1-样式记号的单元格引用的字符串或包含 Visual Basic 过程名的字符串。如果省略本参数，目标将是最近一次用Goto方法选定的区域。 参数Scroll是可选的，Variant类型，如果该值为True，则滚动窗口直至目标区域的左上角单元格出现在窗口的左上角。如果该值为False，则不滚动窗口。默认值为False。
技巧3 获得指定行、列中的最后一个非空单元格
使用VBA对工作表进行操作时，经常需要定位到指定行或列中最后一个非空单元格，此时可以使用Range对象的End属性，在取得单元格对象后便能获得该单元格的相关属性，如单元格地址、行列号、数值等，如下面的代码所示。 #001 Sub LastRow() #002 Dim rng As Range #003 Set rng = Sheet1.Range("A65536").End(xlUp) #004 MsgBox "A列中最后一个非空单元格是" & rng.Address(0, 0) _ #005 & ",行号" & rng.Row & ",数值" & rng.Value #006 Set rng = Nothing #007 End Sub 代码解析： LastRow过程使用消息框显示工作表中A列最后非空单元格的地址、行号和数值。 End属性返回一个Range对象，该对象代表包含源区域的区域尾端的单元格。等同于按键、、或，语法如下： expression.End(Direction) 参数expression是必需的，一个有效的对象。 参数Direction是可选的，所要移动的方向，可以为表格 3?1所示的XlDirection 常量之一。
常量 | 值 | 描述 | xlDown | -4121 | 向下 | xlToRight | -4161 | 向右 | xlToLeft | -4159 | 向左 | xlUp | -4162 | 向上 |
表格 3-1 XlDirection 常量 Range对象的End属性返回的是一个Range对象，因此可以直接使用该对象的属性和方法。 运行LastRow过程结果如图 3-1所示。
图 3-1 获得A列最后一个非空单元格 通过修改相应的参数，能够获得指定行中最后一个非空单元格，如下面的代码所示。 #001 Sub LastColumn() #002 Dim rng As Range #003 Set rng = Sheet1.Range("IV1").End(xlToLeft) #004 MsgBox "第一行中最后一个非空单元格是" & rng.Address(0, 0) _ #005 & ",列号" & rng.Column & ",数值" & rng.Value #006 Set rng = Nothing #007 End Sub 代码解析： LastColumn过程使用消息框显示工作表中第一行最后一个非空单元格的地址、列号和数值，如图 3-2所示。
图 3-2 获得第一行最后一个非空单元格
技巧4 定位单元格
在Excel中使用定位对话框可以选中工作表中特定的单元格区域，而在VBA中则使用SpecialCells方法，如下面的代码所示。 #001 Sub SpecialAddress() #002 Dim rng As Range #003 Set rng = Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas) #004 rng.Select #005 MsgBox "工作表中有公式的单元格为： " & rng.Address #006 Set rng = Nothing #007 End Sub 代码解析： SpecialAddress过程使用SpecialCells方法选中工作表中有公式的单元格，并用消息框显示其地址。 SpecialCells方法返回一个Range对象，该对象代表与指定类型及值相匹配的所有单元格，语法如下： expression.SpecialCells(Type, Value) 参数expression是必需的，返回一个有效的对象。 参数Type是必需的，要包含的单元格，可为表格 4?1所列的XlCellType常量之一。
常量 | 值 | 描述 | xlCellTypeAllFormatConditions | -4172 | 任意格式单元格 | xlCellTypeAllValidation | -4174 | 含有验证条件的单元格 | xlCellTypeBlanks | 4 | 空单元格 | xlCellTypeComments | -4144 | 含有注释的单元格 | xlCellTypeConstants | 2 | 含有常量的单元格 | xlCellTypeFormulas | -4123 | 含有公式的单元格 | xlCellTypeLastCell | 11 | 使用区域中最后的单元格 | xlCellTypeSameFormatConditions | -4173 | 含有相同格式的单元格 | xlCellTypeSameValidation | -4175 | 含有相同验证条件的单元格 | xlCellTypeVisible | 12 | 所有可见单元格 |
表格 4?1 XlCellType常量 第3行代码将SpecialCells方法的Type参数设置为xlCellTypeFormulas，返回的是含有公式的单元格，通过修改相应的参数可以返回不同的单元格。 参数Value是可选的，如果Type参数为xlCellTypeConstants或xlCellTypeFormulas， 此参数可用于确定结果中应包含哪几类单元格。将某几个值相加可使此方法返回多种类型的单元格。如果省略将选定所有常量或公式，可为表格 4?2所列的 XlSpecialCellsValue常量之一。
常量 | 值 | 描述 | xlErrors | 16 | 错误 | xlLogical | 4 | 逻辑值 | xlNumbers | 1 | 数字 | xlTextValues | 2 | 文本 |
表格 4?2 XlSpecialCellsValue常量 第5行代码使用消息框显示工作表中含有公式单元格的地址。SpecialCells方法返回的是Range对象，因此可以直接使用该对象的属性和方法。 运行SpecialAddress过程结果如图 4?1所示。
图 4?1 SpecialCells方法
技巧5 查找单元格
5-1 使用Find方法
在Excel中使用查找对话框可以查找工作表中特定内容的单元格，而在VBA中则使用Find方法，如下面的代码所示。 #001 Sub RngFind() #002 Dim StrFind As String #003 Dim Rng As Range #004 StrFind = InputBox("请输入要查找的值:") #005 If Trim(StrFind) <> "" Then #006 With Sheet1.Range("A:A") #007 Set Rng = .Find(What:=StrFind, _ #008 After:=.Cells(.Cells.Count), _ #009 LookIn:=xlValues, _ #010 LookAt:=xlWhole, _ #011 SearchOrder:=xlByRows, _ #012 SearchDirection:=xlNext, _ #013 MatchCase:=False) #014 If Not Rng Is Nothing Then #015 Application.Goto Rng, True #016 Else #017 MsgBox "没有找到该单元格!" #018 End If #019 End With #020 End If #021 End Sub 代码解析： RngFind过程使用Find方法在工作表Sheet1的A列中查找InputBox函数对话框中所输入的值，并查找该值所在的第一个单元格。 第6到第13行代码在工作表Sheet1的A列中查找InputBox函数对话框中所输入的值。应用于Range对象的Find方法在区域中查找特定信息，并返回Range对象，该对象代表用于查找信息的第一个单元格。如果未发现匹配单元格，就返回Nothing，语法如下： expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SerchFormat) 参数expression是必需的，该表达式返回一个Range对象。 参数What是必需的，要搜索的数据，可为字符串或任意数据类型。 参数After是可选的，表示搜索过程将从其之后开始进行的单元格，必须是区域中的单个单元格。查找时是从该单元格之后开始的，直到本方法绕回到指定的单元格时，才对其进行搜索。如果未指定本参数，搜索将从区域的左上角单元格之后开始。 在本例中将After参数设置为A列的最后一个单元格，所以查找时从A1单元格开始搜索。 参数LookIn是可选的，信息类型。 参数LookAt是可选的，可为XlLookAt常量的xlWhole 或xlPart之一。 参数SearchOrder是可选的，可为XlSearchOrder常量的xlByRows或xlByColumns之一。 参数SearchDirection是可选的，搜索的方向，可为XlSearchDirection常量的xlNext或xlPrevious之一。 参数MatchCase是可选的，若为True，则进行区分大小写的查找。默认值为False。 参数MatchByte是可选的，仅在选择或安装了双字节语言支持时使用。若为True，则双字节字符仅匹配双字节字符。若为False，则双字节字符可匹配其等价的单字节字符。 参数SerchFormat是可选的，搜索的格式。 每次使用Find方法后，参数LookIn、LookAt、SearchOrder 和MatchByte的设置将保存。如果下次调用Find方法时不指定这些参数的值，就使用保存的值。因此每次使用该方法时请明确设置这些参数。 如果工作表的A列中存在重复的数值，那么需要使用FindNext方法或FindPrevious方法进行重复搜索，如下面的代码所示。 #001 Sub RngFindNext() #002 Dim StrFind As String #003 Dim Rng As Range #004 Dim FindAddress As String #005 StrFind = InputBox("请输入要查找的值:") #006 If Trim(StrFind) <> "" Then #007 With Sheet1.Range("A:A") #008 Set Rng = .Find(What:=StrFind, _ #009 After:=.Cells(.Cells.Count), _ #010 LookIn:=xlValues, _ #011 LookAt:=xlWhole, _ #012 SearchOrder:=xlByRows, _ #013 SearchDirection:=xlNext, _ #014 MatchCase:=False) #015 If Not Rng Is Nothing Then #016 FindAddress = Rng.Address #017 Do #018 Rng.Interior.ColorIndex = 6 #019 Set Rng = .FindNext(Rng) #020 Loop While Not Rng Is Nothing And Rng.Address <> FindAddress #021 End If #022 End With #023 End If #024 End Sub 代码解析： RngFindNext过程在工作表Sheet1的A列中查找InputBox函数对话框中所输入的值，并将查到单元格底色设置成黄色。 第8行到第17行代码使用Find方法在工作表Sheet1的A列中查找。 第16行代码将查找到的第一个单元格地址赋给字符串变量FindAddress。 第18行代码将查找到的单元格底色设置成黄色。 第19行代码使用FindNext方法进行重复搜索。FindNext方法继续执行用Find方法启动的搜索。查找下一个匹配相同条件的单元格并返回代表单元格的Range对象，语法如下： expression.FindNext(After) 参数expression是必需的，返回一个Range对象。 参数After是可选的，指定一个单元格，查找将从该单元格之后开始。 第20行代码如果查找到的单元格地址等于字符串变量FindAddress所记录的地址，说明A列已搜索完毕，结束查找过程。 运行RngFindNext过程，在InputBox函数输入框中输入“196.01”后结果如图 5?1所示。
图 5?1 使用FindNext方法重复搜索 还可以使用FindPrevious方法进行重复搜索，FindPrevious方法的语法如下： expression.FindPrevious(After) FindPrevious方法和FindNext方法唯一的区别是FindPrevious方法查找匹配相同条件的前一个单元格而FindNext方法是查找匹配相同条件的下一个单元格。
5-2 使用Like运算符
使用Like运算符可以进行更为复杂的模式匹配查找，如下面的代码所示。 #001 Sub RngLike() #002 Dim rng As Range #003 Dim a As Integer #004 a = 1 #005 With Sheet2 #006 .Range("A:A").ClearContents #007 For Each rng In .Range("B1:E1000") #008 If rng.Text Like "*a*" Then #009 .Range("A" & a) = rng.Text #010 a = a + 1 #011 End If #012 Next #013 End With #014 End Sub 代码解析： RngLike过程使用For Each...Next语句和Like运算符在单元格区域B1:E10000中搜索含有“a”字符的单元格，找到匹配单元格以后将单元格的值写入到A列中。 第6行代码使用ClearContents方法清除A列区域的数据。 第7行代码使用For Each...Next语句在单元格区域B1:E10000中循环。 第8行代码使用Like运算符在单元格区域B1:E10000中搜索含有“a”字符的单元格。 Like运算符用来比较两个字符串，语法如下： result = string Like pattern 参数string是必需的，字符串表达式。 参数pattern是必需的，字符串表达式。 如果string与pattern匹配，则result为True；如果不匹配，则result为False。但是如果string或pattern 中有一个为Null，则result 为 Null。 参数pattern可以使用通配符、字符串列表或字符区间的任何组合来匹配字符串。表格 5?1列出pattern中允许的字符以及它们与什么进行匹配。
pattern中的字符 | 符合string中的字符 | ? | 任何单一字符 | * | 零个或多个字符 | # | 任何一个数字 (0–9) | [charlist] | charlist中的任何单一字符 | [!charlist] | 不在charlist中的任何单一字符 |
表格 5?1 pattern中的匹配字符串 第9行代码将找到的匹配单元格的值写入到A列中。 运行RngLike过程结果如图 5?2所示。
图 5?2 使用Like运算符进行模式匹配查找
技巧6 替换单元格内字符串
如果需要替换单元格内指定的字符串，那么使用Range对象的Replace方法，如下面的代码所示。 #001 Sub RngReplace() #002 Range("A1:A5").Replace "通州", "南通" #003 End Sub 代码解析： RngReplace过程将工作表A1:A5单元格中的“通州”字符串替换成“南通”字符串。 应用于Range对象的Replace方法替换指定区域内单元格中的字符，语法如下： expression.Replace(What, Replacement, LookAt, SearchOrder, MatchCase, MatchByte, SearchFormat, ReplaceFormat) 其中参数expression是必需的，返回一个Range对象。 参数What是必需的，要搜索的字符串。 参数Replacement是必需的，替换的字符串。 运行RngReplace过程前工作表如图 6?1所示，运行RngReplace过程后结果如图 6?2所示。
图 6?1 替换前单元格
图 6?2 替换后单元格
技巧7 复制单元格区域
在实际操作中，经常需要复制指定的单元格区域到另外一个单元格区域。要复制指定单元格区域到其他位置，使用Range对象的Copy方法，如下面的代码所示。 #001 Sub RangeCopy() #002 Application.DisplayAlerts = False #003 Sheet1.Range("A1").CurrentRegion.Copy Sheet2.Range("A1") #004 Application.DisplayAlerts = True #005 End Sub 代码解析： RangeCopy过程将如图 7?1所示的Sheet1工作表中A1单元格的当前区域复制到Sheet2工作表中以A1单元格为左上角单元格的区域，如图 7?2所示。
图 7?1 需复制的数据表
图 7?2 复制结果 Range对象的Copy方法的语法如下： Copy(Destination) 参数Destination表示复制单元格区域的目标区域，如果省略该参数，Excel将把该区域复制到剪贴板中。 使用Copy方法复制单元格区域时，也复制了该单元格区域的格式，如图 7?2所示。 复制单元格区域时，如果目标区域为非空单元格区域，Excel将显示如图 7?3所示的消息框提示是否替换单元格内容，可以设置Application.DisplayAlerts属性值为False，使复制时不出现该消息框。
图 7?3 替换对话框 第2行代码通常复制单元格区域的操作不会将单元格区域的列宽大小同时复制，如图 7?2所示。如果希望在复制单元格区域的同时，也复制源区域的列宽大小，可以使用下面的代码。 #001 Sub CopyWithSameColumnWidths() #002 Sheet1.Range("A1").CurrentRegion.Copy #003 With Sheet3.Range("A1") #004 .PasteSpecial xlPasteColumnWidths #005 .PasteSpecial xlPasteAll #006 End With #007 Application.CutCopyMode = False #008 End Sub 代码解析： 第4行代码使用Range对象的PasteSpecial方法选择性粘贴剪贴板中的Range对象的列宽。 第5行代码粘贴剪贴板中的Range对象全部内容。 第7行代码取消应用程序复制模式。 应用于Range对象的PasteSpecial方法将剪贴板中的Range对象粘贴到指定区域，在粘贴时可以有选择的粘贴对象的部分属性。其语法如下： PasteSpecial(Paste, Operation, SkipBlanks, Transpose) 参数Paste指定要粘贴的区域部分，可为表格 7?1所列的XlPasteType常量之一。
[td]常量 | 值 | 描述 | xlPasteAll | -4104 | 全部（默认值） | xlPasteAllExceptBorders | 7 | 边框除外 | xlPasteColumnWidths | 8 | 列宽 | xlPasteComments | -4144 | 批注 | xlPasteFormats | -4122 | 格式 | xlPasteFormulas | -4123 | 公式 | xlPasteFormulasAndNumberFormats | 11 | 公式和数字格式 | xlPasteValidation | 6 | 有效性验证 | xlPasteValues | -4163 | 数值 | xlPasteValuesAndNumberFormats | 12 | 值和数字格式 |
表格 7?1 XlPasteType 常量 参数Operation指定粘贴操作。可为表格 7?2所列的XlPasteSpecialOperation常量之一。
[td]常量 | 值 | 描述 | xlPasteSpecialOperationNone | -4142 | 无（默认值） | xlPasteSpecialOperationAdd | 2 | 加 | xlPasteSpecialOperationSubtract | 3 | 减 | xlPasteSpecialOperationMultiply | 4 | 乘 | xlPasteSpecialOperationDivide | 5 | 除 |
表格 7?2 XlPasteSpecialOperation常量 参数SkipBlanks指示是否跳过空单元格，若参数值为True，则不将剪贴板上区域中的空白单元格粘贴到目标区域中。默认值为False。 参数Transpose指示是否进行转置，若参数值为True，则粘贴区域时转置行和列。默认值为False。 运行CopyWithSameColumnWidths过程后，Sheet3工作表如图 7?4所示，目标区域的各列列宽与源区域一致。
图 7?4 粘贴列宽后的复制结果 注意 使用PasteSpecial方法时指定xlPasteAll(粘贴全部)，不会粘贴列宽。
技巧8 仅复制数值到另一区域
如果在复制单元格区域时，仅希望复制单元格区域的数值，有下面几种方法。
8-1 使用选择性粘贴
使用选择性粘贴功能并指定粘贴数值，如下面的代码所示。 #001 Sub CopyPasteSpecial() #002 Sheet1.Range("A1").CurrentRegion.Copy #003 Sheet2.Range("A1").PasteSpecial Paste:=xlPasteValues #004 Application.CutCopyMode = False #005 End Sub 代码解析： CopyPasteSpecial过程复制工作表Sheet1中A1单元格的当前区域的数值到工作表Sheet2的A1单元格所在区域中。 第2行代码将如图 8?1所示的Sheet1中A1单元格的当前区域进行复制。
图 8?1 需复制的数据表 第3行代码使用选择性粘贴功能并指定粘贴数值，选择性粘贴数值仅复制了单元格区域的数值，单元格区域的格式（背景颜色、字体对齐格式和边框等）不会被复制，复制结果如图 8?2所示。
图 8?2 复制单元格区域数值
8-2 直接赋值的方法
除了使用Copy方法外，还可以使用直接赋值的方法，如下面的代码所示。 #001 Sub GetValueResize() #002 With Sheet1.Range("A1").CurrentRegion #003 Sheet3.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value #004 End With #005 End Sub 代码解析： GetValueResize过程将工作表Sheet1中的A1单元格的当前区域的数值赋予工作表Sheet3的A1单元格所在的单元格区域。 在对单元格区域直接赋值时，应保证源区域大小与目标区域的大小一致，如果源区域为动态的单元格区域，可使用Resize方法确定目标区域。 运行GetValueResize过程，赋值结果如图 8?2所示。
技巧9 单元格自动进入编辑状态
当光标选择单元格时无需双击，自动进入编辑状态，如下面的代码所示。 #001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 If Target.Column = 3 And Target.Count = 1 Then #003 If Target <> "" Then #004 Application.SendKeys "{F2}" #005 End If #006 End If #007 End Sub 代码解析： 工作表的SelectionChange事件过程，当选择工作表C列有数据单元格时自动进入编辑状态。 第2、3行代码设置SelectionChange事件的触发条件，利用Target参数的Column属性和Count属性将事件的触发条件限制在C列并且只有在选择一个单元格时才发生。 第4行代码使用SendKeys方法发送一个F2键到应用程序，等同于选择单元格后按F2键，使单元格进入编辑状态。关于SendKeys方法请参阅技巧12-4。
技巧10 禁用单元格拖放功能
在工作表中可以拖放单元格右下角的小十字对单元格内容进行复制等操作，如果不希望用户进行此操作可以禁用单元格拖放功能，如下面的代码所示。 #001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 If Not Application.Intersect(Target, Range("A1:A15")) Is Nothing Then #003 Application.CellDragAndDrop = False #004 Else #005 Application.CellDragAndDrop = True #006 End If #007 End Sub 代码解析： 工作表的SelectionChange事件过程，在指定的单元格区域禁用单元格的拖放功能。 CellDragAndDrop 属性设置单元格的拖放功能，如果允许使用单元格拖放功能，则该值为True。 为了不影响其他工作表，应在工作表的Deactivate事件中恢复单元格的拖放功能，如下面的代码所示。 #001 Private Sub Worksheet_Deactivate() #002 Application.CellDragAndDrop = True #003 End Sub 代码解析： 工作表的Deactivate事件过程，恢复单元格的拖放功能。 工作表的Deactivate事件当工作表从活动状态转为非活动状态时产生，语法如下： Private Sub object_Deactivate() 参数object代表Worksheet对象。 当选择工作表“Sheet1”的A1：A15单元格时将禁用单元格的拖放功能，如图 10?1所示。
图 10?1 禁用单元格的拖放功能
技巧11 单元格格式操作
11-1 单元格字体格式设置
在VBA中可以对单元格的字体格式进行各种设置，如下面的代码所示。 #001 Public Sub RngFont() #002 With Range("A1").Font #003 .Name = "华文彩云" #004 .FontStyle = "Bold" #005 .Size = 18 #006 .ColorIndex = 3 #007 .Underline = 2 #008 End With #009 End Sub 代码解析： RngFont过程对单元格A1的字体格式进行设置。 其中第3行代码设置字体为“华文彩云”，应用于Font对象的Name属性返回或设置对象的名称。 第4行代码设置字体为加粗，FontStyle属性返回或设置字体样式。设置为“Bold” 加粗字体，设置为“Italic”倾斜字体，也可以设置成“Bold Italic”。 第5行代码设置字体的大小为18磅，Size属性返回或设置字体大小。 第6行代码设置字体的颜色为红色，应用于Font对象的ColorIndex属性返回或设置字体的颜色，该颜色可指定为当前调色板中颜色的编号，如图 11?1所示。
图 11?1 调色板中颜色的编号 第7行代码设置字体为单下划线类型，Underline属性返回或设置应用于字体的下划线类型，可为表格 11?1所列的XlUnderlineStyle常量之一。
常量 | 值 | 描述 | xlUnderlineStyleNone | -4142 | 无 | xlUnderlineStyleSingle | 2 | 单下划线 | xlUnderlineStyleDouble | -4119 | 双下划线 | xlUnderlineStyleSingleAccounting | 4 | 会计用单下划线 | xlUnderlineStyleDoubleAccounting | 5 | 会计用双下划线 |
表格 11?1 XlUnderlineStyle常量 运行RngFont过程结果如图 11?2所示。
图 11?2 单元格字体设置
11-2 设置单元格内部格式
设置单元格的Interior属性可以对单元格的内部格式进行设置，如下面的代码所示。 #001 Sub RngInterior() #002 With Range("A1").Interior #003 .ColorIndex = 3 #004 .Pattern = xlPatternCrissCross #005 .PatternColorIndex = 6 #006 End With #007 End Sub 代码解析： RngInterior过程对A1单元格的内部格式进行设置。 第2行代码使用Interior属性返回单元格对象的内部。 第3行代码设置单元格边框内部的颜色为红色。应用于Interior对象的ColorIndex属性返回或设置边框内部的颜色，可指定为如图 11?1所示的当前调色板中颜色的编号或为XlColorIndex 常量之一：xlColorIndexAutomatic （自动填充）、xlColorIndexNone （无内部填充）。 第4行代码设置单元格设置内部图案为十字图案。应用于Interior对象的Pattern属性返回或者设置内部图案。 第5行代码设置单元格设置内部图案的颜色为黄色。应用于Interior对象的PatternColorIndex属性返回或设置内部图案的颜色，可指定为如图 11?1所示的当前调色板中颜色的编号XlColorIndex常量之一：xlColorIndexAutomatic （自动填充）、xlColorIndexNone （无内部填充）。 运行RngInterior过程结果如图 11?3所示。
图 11?3 设置单元格内部格式
11-3 为单元格区域添加边框
我们为单元格区域添加边框时往往通过录制宏获取代码，但宏录制器生成的代码分别设置单元格区域的每个边框，因此代码多且效率低。使用Range对象的Borders集合可以快速的对单元格区域的每个边框应用相同的格式，而Range对象的BorderAround方法则可以快速地为单元格区域添加一个外边框，如下面的代码所示。 #001 Sub AddBorders() #002 Dim rng As Range #003 Set rng = Range("B4:G10") #004 With rng.Borders #005 .LineStyle = xlContinuous #006 .Weight = xlThin #007 .ColorIndex = 5 #008 End With #009 rng.BorderAround xlContinuous, xlMedium, 5 #010 Set rng = Nothing #011 End Sub 代码解析： AddBorders过程为单元格区域B4:G10设置内部统一边框并添加一个加粗外边框。 第4行到第8行代码使用Borders属性引用单元格区域的Borders集合，其中第5行代码设置其边框样式线条的样式，第6行代码设置边框线条的粗细，第7行代码设置边框的颜色。 应用于Range对象的Borders集合代表Range对象的4个边框（左边框、右边框、顶部边框和底部边框）的4个Border对象组成的集合，这4个边框既可单独返回，也可作为一个组同时返回。 第9行代码使用BorderAround方法为单元格区域添加一个加粗外边框。 应用于Range对象的BorderAround方法向单元格区域添加整个区域的外边框，并设置该边框的相关属性，其语法如下： BorderAround(LineStyle, Weight, ColorIndex, Color) 其中LineStyle参数设置边框线条的样式，Weight参数设置边框线条的粗细，ColorIndex 设置边框颜色，Color参数以RGB值指定边框的颜色。 注意 指定Color参数可以设置颜色为当前调色板之处的其它颜色，不能同时指定ColorIndex参数和Color参数。 运行AddBorders过程，效果如图 11?4所示。
图 11?4 设置单元格区域边框 如果需要在单元格区域中应用多种边框格式，则需分别设置各边框格式，如下面的代码所示。 #001 Sub BordersDemo() #002 Dim rng As Range #003 Set rng = Sheet2.Range("B4:G10") #004 With rng.Borders(xlInsideHorizontal) #005 .LineStyle = xlDot #006 .Weight = xlThin #007 .ColorIndex = 5 #008 End With #009 With rng.Borders(xlInsideVertical) #010 .LineStyle = xlContinuous #011 .Weight = xlThin #012 .ColorIndex = 5 #013 End With #014 rng.BorderAround xlContinuous, xlMedium, 5 #015 Set rng = Nothing #016 End Sub 代码解析： BordersDemo过程代码为单元格区域内部边框在水平和垂直方向上应用不同格式，并为区域添加一个加粗外边框。 Borders(index)属性返回单个Border对象，其Index参数取值可为表格 11?2所列的XlBordersIndex常量之一：
常量 | 值 | 描述 | xlDiagonalDown | 5 | 斜下边框 | xlDiagonalUp | 6 | 斜上边框 | xlEdgeBottom | 9 | 底部边框 | xlEdgeLeft | 7 | 左边框 | xlEdgeRight | 10 | 右边框 | xlEdgeTop | 8 | 顶部边框 | xlInsideHorizontal | 12 | 内部水平 | xlInsideVertical | 11 | 内部垂直 |
表格 11?2 XlBordersIndex常量 运行BordersDemo过程效果如图 1?1所示。
图 11?5 应用不同格式内部边框
11-4 灵活设置单元格的行高列宽
一般情况下单元格的行高列宽都是以磅为单位进行设置的，也可以使用英寸和厘米计量单位设置单元格的行高列宽，如下面的代码 所示。 #001 Sub RngToPoints() #002 With Range("A1") #003 .RowHeight = Application.CentimetersToPoints(2) #004 .ColumnWidth = Application.CentimetersToPoints(1.5) #005 End With #006 With Range("A2") #007 .RowHeight = Application.InchesToPoints(1.2) #008 .ColumnWidth = Application.InchesToPoints(0.3) #009 End With #010 End Sub 代码解析： RngToPoints过程以英寸和厘米计量单位设置单元格的行高列宽。 第3、4行代码使用CentimetersToPoints方法以厘米为计量单位设置A1单元格的行高列宽。CentimetersToPoints方法将计量单位从厘米转换为磅（一磅等于 0.035 厘米），语法如下： expression.CentimetersToPoints(Centimeters) 参数expression是必需的，返回一个Application对象。 参数Centimeters是必需的，指定要转换为磅值的厘米值。 第5、6行代码使用InchesToPoints方法以英寸为计量单位设置A2单元格的行高列宽。InchesToPoints方法将计量单位从英寸转换为磅，语法如下： expression.InchesToPoints(Inches) 参数expression是必需的，返回一个Application对象。 参数Inches是必需的，指定要转换为磅值的英寸值。 运行RngToPoints过程结果如图 11?6所示。
图 11?6 灵活设置单元格的行高列宽
技巧12 单元格中的数据有效性
12-1 在单元格中建立数据有效性
在单元格中建立数据有效性可以使用Add方法，如下面的代码所示。 #001 Sub Validation() #002 With Range("A1:A10").Validation #003 .Delete #004 .Add Type:=xlValidateList, _ #005 AlertStyle:=xlValidAlertStop, _ #006 Operator:=xlBetween, _ #007 Formula1:="1,2,3,4,5,6,7,8" #008 End With #009 End Sub 代码解析： 使用Add方法在A1：A10单元格中建立数据有效性。 第3行代码删除已建立的数据有效性，防止代码运行出错。 第4行到第7行代码使用Add方法建立数据有效性。应用于Validation对象的Add方法的语法如下： expression.Add(Type, AlertStyle, Operator, Formula1, Formula2) 参数expression是必需的，返回一个Validation对象。 参数Type是必需的，数据有效性类型。 参数AlertStyl是可选的，有效性检验警告样式。 参数Operator是可选的，数据有效性运算符。 参数Formula1是可选的，数据有效性公式的第一部分。 参数Formula2是可选的，当Operator为xlBetween或xlNotBetween时，数据有效性公式的第二部分（其他情况下，此参数被忽略）。 Add 方法所要求的参数依有效性检验的类型而定，如表格 12?1所示。
数据有效性类型 | 参数 | xlValidateCustom | Formula1 必需，忽略 Formula2。Formula1 必须包含一个表达式，数据项有效时该表达式取值为 True，而数据项无效时取值为 False。 | xlInputOnly | 能使用 AlertStyle、Formula1 或 Formula2 参数。 | xlValidateList | Formula1 必需，忽略 Formula2。Formula1 必须包含以逗号分隔的取值列表，或引用此列表的工作表。 | xlValidateWholeNumber、xlValidateDate、xlValidateDecimal、xlValidateTextLength 或 xlValidateTime | 必须指定 Formula1 或 Formula2 之一，或两者均指定。 |
表格 12?1 数据有效性类型
12-2 判断单元格是否存在数据有效性
在VBA中没有专门的属性判断单元格是否存在数据有效性设置，可以使用Validation对象的有效性类型和错误陷阱来判断，如下面的代码所示。 #001 Sub Validation() #002 On Error GoTo Line #003 If Range("A2").Validation.Type >= 0 Then #004 MsgBox "单元格有数据有效性!" #005 Exit Sub #006 End If #007 Line: #008 MsgBox "单元格没有数据有效性!" #009 End Sub 代码解析： Validation过程使用Validation对象的有效性类型和错误陷阱来判断A2单元格中是否存在数据有效性。 第6行代码，如果A2单元格中存在数据有效性，Type参数值就会大于等于0，否则就会发生错误，使用On Error GoTo捕捉到错误后转移到第8行代码，显示一个消息框。
12-3 动态的数据有效性
利用VBA可以在单元格中建立动态的数据有效性，如下面的代码所示。 #001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 If Target.Column = 1 And Target.Count = 1 And Target.Row > 1 Then #003 With Target.Validation #004 .Delete #005 .Add Type:=xlValidateList, _ #006 AlertStyle:=xlValidAlertStop, _ #007 Operator:=xlBetween, _ #008 Formula1:="主机,显示器" #009 End With #010 End If #011 End Sub #012 Private Sub Worksheet_Change(ByVal Target As Range) #013 If Target.Column = 1 And Target.Row > 1 And Target.Count = 1 Then #014 With Target.Offset(0, 1).Validation #015 .Delete #016 Select Case Target #017 Case "主机" #018 .Add Type:=xlValidateList, _ #019 AlertStyle:=xlValidAlertStop, _ #020 Operator:=xlBetween, _ #021 Formula1:="Z286,Z386,Z486,Z586" #022 Case "显示器" #023 .Add Type:=xlValidateList, _ #024 AlertStyle:=xlValidAlertStop, _ #025 Operator:=xlBetween, _ #026 Formula1:="三星17,飞利浦15,三星15,飞利浦17" #027 End Select #028 End With #029 End If #030 End Sub 代码解析： 第1行到第11行代码，工作表的SelectionChange事件，当选择工作表的A列单元格时，在A2以下的单元格中建立动态的数据有效性。 其中第2行代码，利用SelectionChange事件的Target参数来限制事件的触发条件。 第3行到第9行代码使用Add方法在A列单元格中建立数据有效性。应用于Validation对象的Add方法请参阅技巧12-1。 第12行到第30行代码，工作表的Change事件，当工作表A列单元格内容改变时，在B列单元格中建立动态的数据有效性。 其中第16行到第27行代码，根据A列单元格的内容在B列对应的单元格中建立数据有效性，其Formula1参数的值根据A列单元格的内容而变化，使之达到动态数据有效性的效果，如图 12?1、图 12?2所示。
图 12?1 动态数据有效性1
图 12?2 动态数据有效性2
12-4 自动展开数据有效性下拉列表
选择工作表单元格时自动展开数据有效性的下拉列表，如下面的代码所示。 #001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 If Target.Column = 5 Then Application.SendKeys "%{down}" #003 End Sub 代码解析： 当选择工作表的E列中有数据有效性的单元格时使用SendKeys方法发送Alt+向下键，打开数据有效性的下拉列表。 应用于Application对象的SendKeys方法将击键发送给活动应用程序，语法如下： expression.SendKeys(Keys, Wait) 参数expression是可选的，该表达式返回一个Application对象。 参数Keys是必需的，要发送的键或者组合键，以文本方式表示。 Keys参数可以指定任何单个键或与Alt、Ctrl 或Shift的组合键（或者这些键的组合）。每个键可用一个或多个字符表示。例如，
"a"
表示字符 a，或者
"{ENTER}"
表示 Enter。 若要指定在按相应键时不会显示的字符（例如，Enter 或 Tab），请使用如表格 12?2所列的代码来表示相应的键，表中的每个代码表示键盘上的一个键。
键 | 代码 | Backspace |
{BACKSPACE}
或
{BS}
| Break |
{BREAK}
| Caps Lock | {
CAPSLOCK}
| Clear | {
CLEAR}
| Delete 或 Del | {
DELETE}
或 {
DEL}
| End |
{END}
| Enter |
~
（波形符）
| Enter（数字小键盘） |
{ENTER}
| Esc | {
ESCAPE}
或 {
ESC}
| F1 到 F15 | {
F1}
到 {
F15}
| Help | {
HELP}
| Home |
{HOME}
| Ins | {
INSERT}
| Num Lock | {
NUMLOCK}
| Page Down | {
PGDN}
| Page Up | {
PGUP}
| Return | {
RETURN}
| Scroll Lock |
{SCROLLLOCK}
| Tab | {
TAB}
| 向上键 | {
UP}
| 向下键 | {
DOWN}
| 向右键 | {
RIGHT}
| 向左键 | {
LEFT}
|
表格 12?2 按键代码
技巧13 单元格中的公式
13-1 在单元格中写入公式
使用Range对象的Formula属性可以在单元格区域中写入公式，如下面的代码所示。 #001 Sub rngFormula() #002 Sheet1.Range("C1:C10").Formula = "=SUM(A1+B1)" #003 End Sub 代码解析： 应用于Range对象的Formula属性返回或设置A1样式表示的Range对象的公式，语法如下： expression.Formula 参数expression是必需的，返回一个Range对象。 还可以使用FormulaR1C1属性返回或设置以R1C1-样式符号表示的公式，如下面的代码所示。 #001 Sub rngFormulaRC() #002 Sheet2.Range("C1:C10").FormulaR1C1 = "=SUM(RC[-2]+RC[-1])" #003 End Sub 如果需要在单元格中写入数组公式则使用Range对象的FormulaArray属性。如下面的代码所示。 #001 Sub RngFormulaArray() #002 Sheet3.Range("C1").FormulaArray = "=A1:A2*B1:B2" #003 End Sub Range对象的FormulaArray属性返回或设置单元格区域的数组公式。
13-2 检查单元格是否含有公式
使用单元格的HasFormula属性检查单元格是否含有公式,如下面的代码所示。 #001 Private Sub CommandButton1_Click() #002 Select Case Selection.HasFormula #003 Case True #004 MsgBox "公式单元格!" #005 Case False #006 MsgBox "非公式单元格!" #007 Case Else #008 MsgBox "公式区域：" & Selection.SpecialCells(xlCellTypeFormulas, 23).Address(0, 0) #009 End Select #010 End Sub 代码解析： 工作表中按钮的单击过程，检查所选择的单元格区域是否含有公式。 第2行代码返回所选择单元格区域的HasFormula属性值。如果区域中所有单元格均包含公式，则该值为True；如果所有单元格均不包含公式，则该值为False。 第3、4行代码，如果返回True，说明区域中所有单元格均包含公式。 第5、6行代码，如果返回False，说明区域中所有单元格均不包含公式。 第7、8行代码，如果是混合区域，则显示包含公式的单元格地址，如图 13?1所示。
图 13?1 显示包含公式的单元格地址
13-3 判断单元格公式是否存在错误
当需要获取的单元格的值由公式返回时，公式返回的结果可能是一个错误文本，包含#NULL!、#DIV/0!、#VALUE!、#REF!、#NAME?、#NUM!、#N/A等。此时，当单元格公式返回结果为错误文本时，如果试图通过Value属性来获得公式的返回结果，将得到类型不匹配的错误信息，如图 13?2所示。
图 13?2 公式错误导致的消息 通过Range对象的Value属性的返回结果是否为错误类型，来判断公式是否存在错误，如下面的代码所示。 #001 Sub FormulaIsError() #002 If VBA.IsError(Range("A1").Value) = True Then #003 MsgBox "A1单元格错误类型为:" & Range("A1").Text #004 Else #005 MsgBox "A1单元格公式结果为" & Range("A1").Value #006 End If #007 End Sub 代码解析“ FormulaIsError过程代码判断单元格A1中公式结果是否为错误，如果为错误则显示该错误类型，否则显示公式的结果，如图 13?3所示。 第2行代码使用IsError函数返回Boolean值，指出表达式是否为一个错误值，如果表达式表示一个错误，则IsError函数返回True，否则返回False。
图 13?3 显示公式错误结果
13-4 取得单元格中公式的引用单元格
如果需要取得单元格中公式的引用单元格对象，可以使用Range对象的Precedents属性，如下面的代码所示。 #001 Sub RngPrecedent() #002 Dim rng As Range #003 Set rng = Sheet1.Range("C1").Precedents #004 MsgBox "公式所引用的单元格有：" & rng.Address #005 Set rng = Nothing #006 End Sub 代码解析： 在工作表的C1单元格中写有公式“SUM(“A1:B1”)”，RngPrecedent过程使用Range对象的Precedents属性取得其引用的单元格A1:B1。 Precedents属性返回一个Range对象，该对象代表单元格的所有引用单元格。如果有若干引用单元格，那么该区域可能是多个的选定区域（Range 对象的联合）。 运行RngPrecedent过程结果如图 13?4所示。
图 13?4 取得引用单元格
13-5 将单元格中的公式转换为数值
工作表中如果存在过多的公式将影响操作速度，将单元格中的函数与公式的结果转换为数值，可以提高工作表运算效率，有下面几种方法可以实现。 使用选择性粘贴的方法可以将函数与公式的结果转换为数值，如下面的代码所示。 #001 Sub SpecialPaste() #002 With Range("A1:A10") #003 .Copy #004 .PasteSpecial Paste:=xlPasteValues #005 End With #006 Application.CutCopyMode = False #007 End Sub 代码解析： SpecialPaste过程使用选择性粘贴方法将单元格区域的公式转换为数值。 第3行代码将单元格区域复制到剪贴板中。 应用于Range对象的Copy方法将单元格区域复制到指定的区域或剪贴板中，语法如下： expression.Copy(Destination) 参数expression是必需的，该表达式返回一个Range对象。 参数Destination是可选的，指定区域要复制到的目标区域。如果省略该参数，Microsoft Excel 将把该区域复制到剪贴板中。 第4行代码将剪贴板中的Range对象仅复制值到单元格区域中。 应用于Range对象的PasteSpecial方法将剪贴板中的Range对象粘贴到指定区域中，语法如下： expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose) 参数expression是必需的，该表达式返回一个Range对象。 参数Paste是可选的，指定要粘贴的区域部分。在本例中设置为xlPasteValues，仅复制值到单元格区域中。 使用Value属性可以将函数与公式的结果转换为数值，如下面的代码所示。 #001 Sub UseValue() #002 Range("A1:A10").Value = Range("A1:A10").Value #003 End Sub 代码解析： UseValue过程使用Value属性将函数与公式的结果转换为数值。 使用Formula属性可以将函数与公式的结果转换为数值，如下面的代码所示。 #001 Sub UseFormula() #002 Range("A1").Formula = Range("A1").Value #003 End Sub 代码解析： UseFormula过程Formula属性将函数与公式的结果转换为数值。当Formula属性值为非公式时，返回的结果与Value属性一致。
技巧14 单元格中的批注
14-1 判断单元格是否存在批注
在VBA中，可以利用Range对象的Comment属性判断单元格是否存在批注，如下面的代码所示。 #001 Sub HasComment() #002 If Range("A1").Comment Is Nothing Then #003 MsgBox "A1单元格中没有批注!" #004 Else #005 MsgBox "A1单元格中批注内容为：" & Chr(13) & Range("A1").Comment.Text #006 End If #007 End Sub 代码解析： HasComment过程判断A1单元格是否存在批注，并用消息框显示批注信息。 Range对象的Comment属性返回一个批注对象，如果指定的单元格不存在批注，该属性返回Nothing。 运行HasComment过程结果如图 14?1所示。
图 14?1 显示批注内容
14-2 为单元格添加批注
如果希望为单元格添加批注，那么可以使用AddComment方法，如下面的代码所示。 #001 Sub Comment_Add() #002 With Range("A1") #003 If .Comment Is Nothing Then #004 .AddComment Text:=.Value #005 .Comment.Visible = True #006 End If #007 End With #008 End Sub 代码解析： Comment_Add判断单元格A1中是否存在批注，如果没有批注则为单元格A1添加批注并将单元格数值作为批注文本，同时显示批注对象。 第4行代码使用Range对象的AddComment方法为单元格添加批注。该方法只有一个参数Text，代表批注文本。如果单元格已经存在批注，则该方法返回一个错误。 第5行代码显示批注对象，Visible属性确定对象是否可视。 当单元格A1中不存在批注时，运行代码后的结果如图 14?2所示。
图 14?2 添加批注
14-3 删除单元格中的批注
如果需要删除单元格中的批注，那么可以使用ClearComments方法、ClearNotes方法或者Delete方法，如下面的代码所示。 #001 Sub Commentdel() #002 On Error Resume Next #003 Range("A1").ClearComments #004 Range("A2").ClearNotes #005 Range("A3").Comment.Delete #006 End Sub 代码解析： Notesdel过程删除单元格中的批注。 第2行代码错误处理语句，如果单元格中没有批注，那么运行第5行代码时会发生错误，所以使用On Error语句来忽略错误 第3行代码使用ClearComments方法删除单元格A1中的批注。ClearComments方法清除指定区域的所有单元格批注，语法如下： expression.ClearComments 第4行代码使用ClearNotes方法删除A2单元格中的批注。ClearNotes方法清除指定区域中所有单元格的附注和语音批注，语法如下： expression.ClearNotes 第5行代码使用Delete方法删除删除A3单元格中的批注.Range对象的Comment属性返回一个Comment对象，该对象代表与该区域左上角单元格相关联的批注。
技巧15 合并单元格操作
15-1 判断单元格区域是否存在合并单元格
Range对象的MergeCells属性可以确定单元格区域是否包含合并单元格，如果该属性返回值为True，则表示区域包含合并单元格。 下面的代码判断单元格 A1是否包含合并单元格，并显示相应的提示信息。 #001 Sub IsMergeCell() #002 If Range("A1").MergeCells = True Then #003 MsgBox "包含合并单元格" #004 Else #005 MsgBox "没有包含合并单元格" #006 End If #007 End Sub 如果在指定区域中存在部分合并的单元格，如图 15?1所示，区域E8:I17中包含合并单元格区域F8:G9，H12:I13。判断这样一个单元格区域中是否包含合并单元格，可以使用下面的代码快速判断单元格区域中是否包含部分合并单元格，而不需要遍历单元格。
图 15?1 包含部分合并单元格的区域 #001 Sub IsMerge() #002 If IsNull(Range("E8:I17").MergeCells) Then #003 MsgBox "包含合并单元格" #004 Else #005 MsgBox "没有包含合并单元格" #006 End If #007 End Sub 代码解析： 当单元格区域中同时包含合并单元格和非合并单元格时，MergeCells属性将返回Null，因此第2行代码通过该返回结果作为判断条件。 运行IsMerge过程结果如图 15?2所示。
图 15?2 提示信息
15-2 合并单元格时连接每个单元格的文本
使用Excel的“合并及居中”按钮合并多个单元格区域时，Excel仅保留区域左上角单元格的内容，如果用户希望在合并如图 15?3所示单元格区域时，将各个单元格的内容连接起来保存在合并后的单元格区域中，则可以使用下面的代码。
图 15?3 合并前单元格区域 #001 Sub Mergerng() #002 Dim StrMerge As String #003 Dim rng As Range #004 If TypeName(Selection) = "Range" Then #005 For Each rng In Selection #006 StrMerge = StrMerge & rng.Value #007 Next #008 Application.DisplayAlerts = False #009 Selection.Merge #010 Selection.Value = StrMerge #011 Application.DisplayAlerts = True #012 End If #013 End Sub 代码解析： Mergerng过程将所选各个单元格的内容连接起来保存在合并后的单元格区域中。 第4行代码使用TypeName函数判断当前选定对象是否为Range对象，若是则继续执行代码。 第5行到第7行代码将当前选中区域的内容连接起来保存在字符串变量StrMerge中。 第8行代码将DisplayAlerts属性设置为False，禁止在合并多重数值区域时，Excel显示的警告信息，如图 15?4所示，以避免中断代码的运行。
图 15?4 合并多重数值区域时警告信息 第9行代码使用Merge方法合并当前选定区域。应用于Range对象的Merge方法通过指定Range对象创建合并单元格，语法如下： expression.Merge(Across) 参数expression是必需的，返回一个Range对象。 参数Across是可选的，如果该值为True，则将指定区域内的每一行合并为一个合并单元格。默认值为False。 第9行也可以使用下面的代码： Selection.MergeCells = True 第10行代码将变量StrMerge的值赋给合并后的单元格。 运行Mergerng过程结果如图 15?5所示。
图 15?5 合并单元格结果
15-3 合并内容相同的连续单元格
如果需要合并如图 15?6所示的工作表中B列中部门相同的连续单元格，可以使用下面的代码。
图 15?6 需合并的工作表 #001 Sub Mergerng() #002 Dim IntRow As Integer #003 Dim i As Integer #004 Application.DisplayAlerts = False #005 With Sheet1 #006 IntRow = .Range("A65536").End(xlUp).Row #007 For i = IntRow To 2 Step -1 #008 If .Cells(i, 2).Value = .Cells(i - 1, 2).Value Then #009 .Range(.Cells(i - 1, 2), .Cells(i, 2)).Merge #010 End If #011 Next #012 End With #013 Application.DisplayAlerts = True #014 End Sub 代码解析： 第7行到第11行代码，从最后一行开始，向上逐个单元格判断连续两个单元格的内容是否相同，如果相同则合并。 运行Mergerng过程后，结果如图 15?7所示。
图 15?7 合并内容相同的连续单元格
15-4 取消合并单元格时在每个单元格中保留内容
如果需要取消如图 15?7所示的工作表中B列“部门”的合并单元格，并且各个单元格均保留原合并单元格的内容，可以使用下面的代码。 #001 Sub UnMerge() #002 Dim StrMer As String #003 Dim IntCot As Integer #004 Dim i As Integer #005 With Sheet1 #006 For i = 2 To .Range("B65536").End(xlUp).Row #007 StrMer = .Cells(i, 2).Value #008 IntCot = .Cells(i, 2).MergeArea.Count #009 .Cells(i, 2).UnMerge #010 .Range(.Cells(i, 2), .Cells(i + IntCot - 1, 2)).Value = StrMer #011 i = i + IntCot - 1 #012 Next #013 End With #014 End Sub 代码解析： UnMerge过程取消工作表中B列中的合并单元格，并且各个单元格均保留原合并单元格的内容。 第7行代码取得B列每个合并单元格的内容。 第8行代码取得合并区域的单元格数量。 第9行代码使用UnMerge方法取消合并单元格。UnMerge方法将合并区域分解为独立的单元格，语法如下： expression.UnMerge 第10行代码将原合并单元格的内容赋值给取消合并单元格后的区域。 第11行代码调整循环变量i的值，使下一次循环从下一个单元格区域开始。 运行UnMerge过程结果如图 15?6所示。
技巧16 高亮显示单元格区域
如果希望以某种方式突出显示活动单元格或者指定的单元格区域，从而一目了然地获得某些信息，那么可以高亮显示活动单元格区域，如下面的代码所示。 #001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 Cells.Interior.ColorIndex = xlColorIndexNone #003 Target.Interior.ColorIndex = 8 #004 End Sub 代码解析： 设置工作表当前选定区域单元格的内部填充颜色，以高亮显示选定区域，如图 16?1所示。
图 16?1 高亮显示选定区域 第2行代码将工作表中所有的单元格的内部填充颜色设置为xlColorIndexNone，即取消单元格的内部填充颜色。 第3行代码将工作表中选定单元格的内部填充颜色设置为8。 应用于Interior对象的ColorIndex属性返回或设置边框内部的颜色。该颜色可指定为当前调色板中颜色的编号（请参阅技巧11-1中的图 11?1）或为 XlColorIndex 常量之一：xlColorIndexAutomatic（指定对图形对象自动填充）、xlColorIndexNone（用于指定无内部填充）。 还可以高亮显示指定区域内的行列，如下面的代码所示。 #001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 Dim rng As Range #003 Cells.Interior.ColorIndex = xlNone #004 Set rng = Application.Union(Target.EntireColumn, Target.EntireRow) #005 rng.Interior.ColorIndex = 24 #006 End Sub 代码解析： 设置工作表当前选定区域单元格内部填充颜色，高亮显示活动单元格所在的行列，如图 16?2所示。 第4行代码使用Union方法将所选单元格所在的行、列连接起来成为一个区域，关于Union方法请参阅技巧1-6。
图 16?2 高亮显示活动单元格所在的行列 注意 使用此方法时，工作表中所有设置的单元格内部填充颜色将会被清除。（不包括通过条件格式设置的单元格内部填充颜色），同时无法在工作表中实现复制粘贴功能。
技巧17 双击被保护单元格时不显示提示消息框
当用户使用鼠标左键双击被保护工作表中锁定的单元格区域时，系统将显示如图 17?1所示的消息框。
图 17?1 系统提示消息框 如果不希望显示该消息框，可以在工作表Worksheet_BeforeDoubleClick事件中进行设置，如下面的代码所示。 #001 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) #002 If Target.Locked = True Then #003 MsgBox "此单元格已保护，不能编辑!" #004 Cancel = True #005 End If #006 End Sub 代码解析： 当用户使用鼠标左键双击工作表单元格时，触发Worksheet_BeforeDoubleClick事件。该事件中的Target参数代表用户双击鼠标左键的单元格区域。 参数Cancel设置是否取消该操作。如果将参数Cancel设置为True，将不进行默认的双击操作。 第2行代码中判断用户双击鼠标左键的单元格区域是否已锁定（Range对象的Locked属性返回或设置Range对象是否锁定），如果单元格区域已锁定，则设置参数Cancel设置为True，不进行默认的双击操作，因而不再显示图 17?1所示的消息框，只显示一个自定义的提示信息，如图 17?2所示。
图 17?2 自定义提示信息
技巧18 重新计算工作表指定区域
如果在工作表中含有大量公式，那么在对工作表执行重新计算操作时，可能需要较长的时间。在实际工作中，有时希望仅对指定的区域进行重新计算，以提高计算效率，那么可以使用下面的代码。 #001 Sub CalculationSpecialRange() #002 Dim OldCalculation As XlCalculation #003 OldCalculation = Application.Calculation #004 Application.Calculation = xlCalculationManual #005 ActiveSheet.Range("A1:D10").Calculate #006 Application.Calculation = OldCalculation #007 End Sub 代码解析： CalculationSpecialRange过程对单元格A1到B10区域进行重新计算。 第3行代码保存当前应用程序的Calculation属性设置。应用于Application对象的Calculation属性返回或设置当前应用程序的计算模式，可为表格 18?1所示的XlCalculation常量之一。
[td]常量 | 值 | 含义 | xlCalculationAutomatic | -4105 | 自动计算 | xlCalculationManual | -4135 | 手动计算 | xlCalculationSemiautomatic | 2 | 除模拟运算表外自动计算 |
表格 18?1 XlCalculation 常量 第4行代码将计算模式设置为手动重算。 第5行代码重新计算活动工作表指定的单元格区域B3:D7。（指定区域之外的公式将不重新计算，但包含易失性函数的公式除外） 第6行代码恢复当前应用程序的Calculation属性设置。
技巧19 录入数据后单元格自动保护
下面的代码可以使用户在单元格录入数据后自动对已录入数据单元格进行保护,防止修改数据。 #001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 On Error Resume Next #003 Sheet1.Unprotect Password:="12345" #004 If Target.Value <> "" Then #005 Target.Locked = True #006 Sheet1.Protect Password:="12345" #007 End If #008 End Sub 代码解析： 工作表的SelectionChange事件，在单元格录入数据后自动对已录入数据单元格进行保护。 第3行代码使用Unprotect方法取消工作表的保护。应用于Worksheet 对象的Unprotect方法取消工作表的保护，如果工作表不是受保护的，则此方法不起作用，语法如下： expression.Unprotect(Password) 参数expression是必需的，该表达式返回一个Worksheet 对象。 参数Password是可选的，指定用于解除工作表的保护的密码，此密码是区分大小写的
。 第4、5行代码单元格录入数据后将Locked属性设置为True。Locked属性应用于Range对象时，如果Range对象被锁定，则该值为True，当工作表有保护时Range对象不可被修改。 第6行代码使用Protect方法保护工作表。应用于Worksheet对象的Protect方法保护工作表使其不至被修改，语法如下： expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables) 其中参数expression是必需的，该表达式返回一个Worksheet对象。 参数Password是可选的，为一个字符串，该字符串为工作表指定区分大小写的密码。 其他参数都是可选参数，其功能等同于如图 19?1所示的工作表保护对话框中的各项选项，具体请参阅VBA帮助。
图 19?1 工作表保护对话框
技巧20 工作表事件Target参数的使用方法
在工作表的SelectionChange事件中，参数Target代表新选定的区域，在工作表的Change事件中参数Target代表更改的区域。在实际应用中可以使用Target参数将触发工作表事件的区域限制在一定的范围内，有以下几种方法：
20-1 使用单元格的Address 属性
使用单元格的Address属性可以将触发条件限制在某一个单元格中，如下面的代码所示。 #001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 If Target.Address(0, 0) = "A1" Then #003 MsgBox "你选择了A1单元格" #004 End If #005 End Sub 代码解析： 当选择工作表A1单元格时显示一个消息框。 第2行代码使用Address属性返回所选单元格的区域引用，当返回的区域引用是“A1”时触发SelectionChange事件，显示一个消息框。 此方法只适用于单个单元格或者加上OR运算符可以适用于几个单元格，多则不方便。
20-2 使用Column属性和Row属性
使用单元格的Column属性和Row属性可以将触发条件限制在某一区域内，如下面的代码所示。 #001 Private Sub Worksheet_Change(ByVal Target As Range) #002 If Target.Column = 1 And Target.Row < 11 Then #003 Target.Offset(, 1) = Val(Target) * 3 #004 End If #005 End Sub 代码解析： 当改变工作表的A1到A10单元格时，如果输入的是数值则将在对应的B列单元格写入乘以3的数值。 第2行代码使用Column属性将触发条件限制在第1列，使用Row属性将触发条件限制在第10行以内，也就是A1到A10的区域范围内。
20-3 使用Intersect方法
使用Intersect方法可以很方便的指定一个或多个区域范围，如下面的代码所示。 #001 Private Sub Worksheet_SelectionChange(ByVal Target As Range) #002 If Not Application.Intersect(Target, Union(Range("A1:A10"), Range("C1:C10"))) Is Nothing Then #003 MsgBox "你选择了" & Target.Address(0, 0) & "单元格" #004 End If #005 End Sub 代码解析： 当选择工作表A1到A10，C1到C10单元格时将所选的单元格地址显示在消息框中。 第2行代码使用Intersect方法判断所选单元格是否与A1到A10，C1到C10单元格重叠，如果重叠说明所选单元格在A1到A10，C1到C10单元格区域内。Intersect方法返回一个Range对象，此对象代表两个或多个范围重叠的矩形区域，语法如下： expression.Intersect(Arg1, Arg2, ...) 参数expression是可选的，返回一个Application对象。 参数Arg1, Arg2, ...是必需的，重叠的区域。必须指定至少两个 Range对象。 | 
发表于 2016-7-11 10:30:19
收藏
顶
踩