VBA常用代码解析（第一讲）

正文开始前，先插播一条广告：复制下面黄色内容后打开手机淘宝即可查看！感谢各位的支持！

全国包邮！iPhone6/plus/iPhone5/5s4/4s钢化玻璃膜，0.25mm高清！￥变身钢铁侠手机！￥

下面应以为粉丝的要求，开始把我以前的一些学习资料整理了一下，然后分20期发给各位有需要的朋友，您也可以索取源文件，邮箱：[e-mail]！

EXCEL VBA常用代码解析

Range对象是Excel应用程序中最常用的对象，一个Range对象代表一个单元格、一行、一列、包含一个或者更多单元格区域(可以是连续的单元格，也可以是不连续的单元格)中选定的单元格，甚至是多个工作表上的一组单元格，在操作Excel 内的任何区域之前都需要将其表示为一个Range对象，然后使用该Range对象的方法和属性。

▲001 单元格的引用方法

在VBA中经常需要引用单元格或单元格区域区域，主要有以下几种方法。

001-1 使用Range属性

VBA中可以使用Range属性返回单元格或单元格区域，如下面的代码所示。

Sub RngSelect()

Sheet1.Range('A3:F6，B1:C5').Select

End Sub

代码解析：

RangeSelect过程使用Select方法选中A3:F6，B1:C5单元格区域。

Range属性返回一个Range对象，该对象代表一个单元格或单元格区域，语法如下：

Range(Cell1，Cell2)

参数Cell1是必需的，必须为 A1 样式引用的宏语言，可包括区域操作符(冒号)、相交区域操作符(空格)或合并区域操作符(逗号)。也可包括美元符号(即绝对地址，如“$A$1”)。可在区域中任一部分使用局部定义名称，如Range('B2:LastCell')，其中LastCell为已定义的单元格区域名称。

参数Cell2是可选的，区域左上角和右下角的单元格。

运行RangeSelect过程，选中A3:F6，B1:C5单元格区域。

001-2 使用Cells属性

使用Cells属性返回一个Range对象，如下面的代码所示。

Sub Cell()

Dimicell As Integer

For icell = 1 To 100

Sheet2.Cells(icell，1).Value = icell

Next

End Sub

代码解析：

Cell过程使用For...Next语句为工作表中的A1:A100单元格区域填入序号。

Cells属性指定单元格区域中的单元格，语法如下：

Cells(RowIndex，ColumnIndex)

参数RowIndex是可选的，表示引用区域中的行序号。

参数ColumnIndex是可选的，表示引用区域中的列序号。

如果缺省参数，Cells属性返回引用对象的所有单元格。

Cells属性的参数可以使用变量，因此经常应用于在单元格区域中循环。

001-3 使用快捷记号

在VBA中可以将A1引用样式或命名区域名称使用方括号括起来，作为Range属性的快捷方式，这样就不必键入单词“Range”或使用引号，如下面的代码所示。

Sub Fastmark()

[A1：A5] = 2

[Fast]= 4

End Sub

代码解析：

Fastmark过程使用快捷记号为单元格区域赋值。

第2行代码使用快捷记号将活动工作表中的A1：A5单元格赋值为2。

第3行代码将工作簿中已命名为“Fast”的单元格区域赋值为4。

注意使用快捷记号引用单元格区域时只能使用固定字符串而不能使用变量。

001-4 使用Offset属性

可以使用Range对象的Offset属性返回一个基于引用的Range对象的单元格区域，如下面的代码所示。

Sub Offset()

Sheet3.Range('A1:C3').Offset(3，3).Select

End Sub

代码解析：

Offset过程使用Range对象的Offset属性选中A1：A3单元格偏移三行三列后的区域。

应用于Range对象的Offset 属性的语法如下：

expression.Offset(RowOffset，ColumnOffset)

参数expression是必需的，该表达式返回一个Range对象。

参数RowOffset是可选的，区域偏移的行数(正值、负值或 0(零))。正值表示向下偏移，负值表示向上偏移，默认值为 0。

参数ColumnOffset是可选的，区域偏移的列数(正值、负值或 0(零))。正值表示向右偏移，负值表示向左偏移，默认值为 0。

运行Offset过程，选中A1：A3单元格偏称三行三列后的区域。

001-5 使用Resize属性

使用Range对象的Resize属性调整指定区域的大小，并返回调整大小后的单元格区域，如下面的代码所示。

Sub Resize()

Sheet4.Range('A1').Resize(3，3).Select

End Sub

代码解析：

Resize过程使用Range对象的Resize属性选中A1单元格扩展为三行三列后的区域。

Resize属性的语法如下：

expression.Resize(RowSize，ColumnSize)

参数expression是必需的，返回要调整大小的Range对象

参数RowSize是可选的，新区域中的行数。如果省略该参数，则该区域中的行数保持不变。

参数ColumnSize是可选的，新区域中的列数。如果省略该参数。则该区域中的列数保持不变。

运行Resize过程，选中A1单元格扩展为三行三列后的区域。

001-6 使用Union方法

使用Union方法可以将多个非连续区域连接起来成为一个区域，从而可以实现对多个非连续区域一起进行操作，如下面的代码所示。

Sub UnSelect()

Union(Sheet5.Range('A1:D4')，Sheet5.Range('E5:H8')).Select

End Sub

代码解析：

UnSelect过程选择单元格A1:D4和E5:H8所组成的区域。Union方法返回两个或多个区域的合并区域，语法如下：

expression.Union(Arg1，Arg2，...)

其中参数expression是可选的，返回一个Application对象。

参数Arg1，Arg2，...是必需的，至少指定两个Range对象。

运行UnSelect过程，选中单元格A1:D4和E5:H8所组成的区域。

001-7 使用UsedRange属性

使用UsedRange属性返回指定工作表上已使用单元格组成的区域，如下面的代码所示。

Sub UseSelect()

Sheet6.UsedRange.Select

End Sub

代码解析：

UseSelect过程使用UsedRange属性选择工作表上已使用单元格组成的区域，包括空单元格。如工作表中已使用A1单元格和D8单元格，运行UseSelect过程将选择A1到D8单元格区域。

001-8 使用CurrentRegion属性

使用CurrentRegion属性返回指定工作表上当前的区域，如下面的代码所示。

Sub CurrentSelect()

Sheet7.Range('A5').CurrentRegion.Select

End Sub

代码解析：

CurrentSelect过程使用CurrentRegion属性选择工作表上A5单元格当前的区域，当前区域是一个边缘是任意空行和空列组合成的范围。

运行CurrentSelect过程将选择A5到B6单元格区域。

▲002 选定单元格区域的方法

002-1 使用Select方法

在VBA中一般使用Select方法选定单元格或单元格区域，如下面的代码所示。

Sub RngSelect()

Sheet3.Activate

Sheet3.Range('A1：B10').Select

End Sub

代码解析：

RngSelect过程使用Select方法选定Sheet3中的A1：B10单元格区域，Select方法应用于Range对象时语法如下：

expression.Select(Replace)

参数expression是必需的，一个有效的对象。

参数Replace是可选的，要替换的对象。

使用Select方法选定单元格时，单元格所在的工作表必需为活动工作表，所以先使用Activate方法使Sheet3成为活动工作表，否则Select方法有可能出错，显示错误提示。

002-2 使用Activate方法

还可以使用Activate方法选定单元格或单元格区域，如下面的代码所示。

Sub RngActivate()

Sheet3.Activate

Sheet3.Range('A1:B10').Activate

End Sub

代码解析：

RngActivate过程使用Activate方法选定Sheet3中的A1：B10单元格区域，Activate方法应用于Range对象时语法如下：

expression.Activate

使用Activate方法选定单元格时，单元格所在的工作表也必需为活动工作表，否则Activate方法有可能出错，显示错误提示。

002-3 使用Goto方法

使用Goto方法无需使单元格所在的工作表成为活动工作表，如下面的代码所示。

Sub RngGoto()

Application.GotoReference:=Sheet3.Range('A1：B10')，scroll:=True

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。

▲003 获得指定行、列中的最后一个非空单元格

使用VBA对工作表进行操作时，经常需要定位到指定行或列中最后一个非空单元格，此时可以使用Range对象的End属性，在取得单元格对象后便能获得该单元格的相关属性，如单元格地址、行列号、数值等，如下面的代码所示。

Sub LastRow()

Dimrng As Range

Setrng = Sheet1.Range('A65536').End(xlUp)

MsgBox'A列中最后一个非空单元格是' & rng.Address(0，0) _

& '，行号' & rng.Row & '，数值' & rng.Value

Setrng = Nothing

End Sub

代码解析：

LastRow过程使用消息框显示工作表中A列最后非空单元格的地址、行号和数值。

End属性返回一个Range对象，该对象代表包含源区域的区域尾端的单元格。等同于按键<End+向上键>、<End+向下键>、<End+向左键>或<End+向右键>，语法如下：

expression.End(Direction)

参数expression是必需的，一个有效的对象。

参数Direction是可选的，所要移动的方向，可以为XlDirection 常量之一。

Range对象的End属性返回的是一个Range对象，因此可以直接使用该对象的属性和方法。

通过修改相应的参数，能够获得指定行中最后一个非空单元格，如下面的代码所示。

Sub LastColumn()

Dimrng As Range

Setrng = Sheet1.Range('IV1').End(xlToLeft)

MsgBox'第一行中最后一个非空单元格是' & rng.Address(0，0) _

& '，列号' & rng.Column & '，数值' & rng.Value

Setrng = Nothing

End Sub

代码解析：

LastColumn过程使用消息框显示工作表中第一行最后一个非空单元格的地址、列号和数值。

▲004 定位单元格

在Excel中使用定位对话框可以选中工作表中特定的单元格区域，而在VBA中则使用SpecialCells方法，如下面的代码所示。

Sub SpecialAddress()

Dimrng As Range

Setrng = Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas)

rng.Select

MsgBox'工作表中有公式的单元格为：' & rng.Address

Setrng = Nothing

End Sub

代码解析：

SpecialAddress过程使用SpecialCells方法选中工作表中有公式的单元格，并用消息框显示其地址。

SpecialCells方法返回一个Range对象，该对象代表与指定类型及值相匹配的所有单元格，语法如下：

expression.SpecialCells(Type，Value)

参数expression是必需的，返回一个有效的对象。

参数Type是必需的，要包含的单元格，可为表格所列的XlCellType常量之一。

第3行代码将SpecialCells方法的Type参数设置为xlCellTypeFormulas，返回的是含有公式的单元格，通过修改相应的参数可以返回不同的单元格。

参数Value是可选的，如果Type参数为xlCellTypeConstants或xlCellTypeFormulas，此参数可用于确定结果中应包含哪几类单元格。将某几个值相加可使此方法返回多种类型的单元格。如果省略将选定所有常量或公式，可为表格所列的 XlSpecialCellsValue常量之一。

第5行代码使用消息框显示工作表中含有公式单元格的地址。SpecialCells方法返回的是Range对象，因此可以直接使用该对象的属性和方法。

▲005 查找单元格

005-1 使用Find方法

在Excel中使用查找对话框可以查找工作表中特定内容的单元格，而在VBA中则使用Find方法，如下面的代码所示。

Sub RngFind()

DimStrFind As String

DimRng As Range

StrFind= InputBox('请输入要查找的值:')

If Trim(StrFind)<> ““ Then

With Sheet1.Range('A:A')

Set Rng = .Find(What:=StrFind，_

After:=.Cells(.Cells.Count)，_

LookIn:=xlValues，_

LookAt:=xlWhole，_

SearchOrder:=xlByRows，_

SearchDirection:=xlNext，_

MatchCase:=False)

If Not Rng Is Nothing Then

Application.Goto Rng，True

Else

MsgBox '没有找到该单元格!'

End If

End With

EndIf

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方法进行重复搜索，如下面的代码所示。

Sub RngFindNext()

DimStrFind As String

DimRng As Range

DimFindAddress As String

StrFind= InputBox('请输入要查找的值:')

If Trim(StrFind)<> ““ Then

With Sheet1.Range('A:A')

Set Rng = .Find(What:=StrFind，_

After:=.Cells(.Cells.Count)，_

LookIn:=xlValues，_

LookAt:=xlWhole，_

SearchOrder:=xlByRows，_

SearchDirection:=xlNext，_

MatchCase:=False)

If Not Rng Is Nothing Then

FindAddress = Rng.Address

Do

Rng.Interior.ColorIndex = 6

Set Rng = .FindNext(Rng)

Loop While Not Rng Is Nothing AndRng.Address <> FindAddress

End If

End With

EndIf

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”后结果如所示。还可以使用FindPrevious方法进行重复搜索，FindPrevious方法的语法如下：

expression.FindPrevious(After)

FindPrevious方法和FindNext方法唯一的区别是FindPrevious方法查找匹配相同条件的前一个单元格而FindNext方法是查找匹配相同条件的下一个单元格。

005-2 使用Like运算符

使用Like运算符可以进行更为复杂的模式匹配查找，如下面的代码所示。

Sub RngLike()

Dimrng As Range

Dima As Integer

a =1

WithSheet2

.Range('A:A').ClearContents

For Each rng In .Range('B1:E1000')

If rng.Text Like '*a*' Then

.Range('A' & a) =rng.Text

a = a + 1

End If

Next

EndWith

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 Likepattern

参数string是必需的，字符串表达式。

参数pattern是必需的，字符串表达式。

如果string与pattern匹配，则result为True；如果不匹配，则result为False。但是如果string或pattern 中有一个为Null，则result 为 Null。

参数pattern可以使用通配符、字符串列表或字符区间的任何组合来匹配字符串。表格中列出pattern中允许的字符以及它们与什么进行匹配。

第9行代码将找到的匹配单元格的值写入到A列中。

▲006 替换单元格内字符串

如果需要替换单元格内指定的字符串，那么使用Range对象的Replace方法，如下面的代码所示。

Sub RngReplace()

Range('A1:A5').Replace'通州'，'南通'

End Sub

代码解析：

RngReplace过程将工作表A1:A5单元格中的“通州”字符串替换成“南通”字符串。

应用于Range对象的Replace方法替换指定区域内单元格中的字符，语法如下：

expression.Replace(What，Replacement，LookAt，SearchOrder，MatchCase，MatchByte，SearchFormat，ReplaceFormat)

其中参数expression是必需的，返回一个Range对象。

参数What是必需的，要搜索的字符串。

参数Replacement是必需的，替换的字符串。

运行RngReplace过程前工作表。

▲007 复制单元格区域

在实际操作中，经常需要复制指定的单元格区域到另外一个单元格区域。要复制指定单元格区域到其他位置，使用Range对象的Copy方法，如下面的代码所示。

Sub RangeCopy()

Application.DisplayAlerts= False

Sheet1.Range('A1').CurrentRegion.CopySheet2.Range('A1')

Application.DisplayAlerts= True

End Sub

代码解析：

RangeCopy过程将Sheet1工作表中A1单元格的当前区域复制到Sheet2工作表中以A1单元格为左上角单元格的区域。

Sheet2工作表复制后。

Range对象的Copy方法的语法如下：

Copy(Destination)

参数Destination表示复制单元格区域的目标区域，如果省略该参数，Excel将把该区域复制到剪贴板中。使用Copy方法复制单元格区域时，也复制了该单元格区域的格式。

复制单元格区域时，如果目标区域为非空单元格区域，Excel将显示消息框提示是否替换单元格内容，可以设置Application.DisplayAlerts属性值为False，使复制时不出现该消息框。

第2行代码通常复制单元格区域的操作不会将单元格区域的列宽大小同时复制，如果希望在复制单元格区域的同时，也复制源区域的列宽大小，可以使用下面的代码。

Sub CopyWithSameColumnWidths()

Sheet1.Range('A1').CurrentRegion.Copy

WithSheet3.Range('A1')

.PasteSpecial xlPasteColumnWidths

.PasteSpecial xlPasteAll

EndWith

Application.CutCopyMode= False

End Sub

代码解析：

第4行代码使用Range对象的PasteSpecial方法选择性粘贴剪贴板中的Range对象的列宽。

第5行代码粘贴剪贴板中的Range对象全部内容。

第7行代码取消应用程序复制模式。

应用于Range对象的PasteSpecial方法将剪贴板中的Range对象粘贴到指定区域，在粘贴时可以有选择的粘贴对象的部分属性。其语法如下：

PasteSpecial(Paste，Operation，SkipBlanks，Transpose)

参数Paste指定要粘贴的区域部分，可为表格所列的XlPasteType常量之一。

参数Operation指定粘贴操作。可为表格所列的XlPasteSpecialOperation常量之一。

参数SkipBlanks指示是否跳过空单元格，若参数值为True，则不将剪贴板上区域中的空白单元格粘贴到目标区域中。默认值为False。

参数Transpose指示是否进行转置，若参数值为True，则粘贴区域时转置行和列。默认值为False。

运行CopyWithSameColumnWidths过程后，Sheet3工作表，目标区域的各列列宽与源区域一致。

注意使用PasteSpecial方法时指定xlPasteAll(粘贴全部)，不会粘贴列宽。

▲008 仅复制数值到另一区域

如果在复制单元格区域时，仅希望复制单元格区域的数值，有下面几种方法。

008-1 使用选择性粘贴

使用选择性粘贴功能并指定粘贴数值，如下面的代码所示。

Sub CopyPasteSpecial()

Sheet1.Range('A1').CurrentRegion.Copy

Sheet2.Range('A1').PasteSpecialPaste:=xlPasteValues

Application.CutCopyMode= False

End Sub

代码解析：

CopyPasteSpecial过程复制工作表Sheet1中A1单元格的当前区域的数值到工作表Sheet2的A1单元格所在区域中。

第2行代码将Sheet1中A1单元格的当前区域进行复制。

第3行代码使用选择性粘贴功能并指定粘贴数值，选择性粘贴数值仅复制了单元格区域的数值，单元格区域的格式(背景颜色、字体对齐格式和边框等)不会被复制，复制结果。

008-2 直接赋值的方法

除了使用Copy方法外，还可以使用直接赋值的方法，如下面的代码所示。

Sub GetValueResize()

WithSheet1.Range('A1').CurrentRegion

Sheet3.Range('A1').Resize(.Rows.Count，.Columns.Count).Value = .Value

EndWith

End Sub

代码解析：

GetValueResize过程将工作表Sheet1中的A1单元格的当前区域的数值赋予工作表Sheet3的A1单元格所在的单元格区域。

在对单元格区域直接赋值时，应保证源区域大小与目标区域的大小一致，如果源区域为动态的单元格区域，可使用Resize方法确定目标区域。

▲009 单元格自动进入编辑状态

当光标选择单元格时无需双击，自动进入编辑状态，如下面的代码所示。

Private Sub Worksheet_SelectionChange(ByVal TargetAs Range)

If Target.Column= 3 And Target.Count = 1 Then

If Target <> ““ Then

Application.SendKeys '{F2}'

End If

EndIf

End Sub

代码解析：

工作表的SelectionChange事件过程，当选择工作表C列有数据单元格时自动进入编辑状态。

第2、3行代码设置SelectionChange事件的触发条件，利用Target参数的Column属性和Count属性将事件的触发条件限制在C列并且只有在选择一个单元格时才发生。

第4行代码使用SendKeys方法发送一个F2键到应用程序，等同于选择单元格后按F2键，使单元格进入编辑状态。

▲010 禁用单元格拖放功能

在工作表中可以拖放单元格右下角的小十字对单元格内容进行复制等操作，如果不希望用户进行此操作可以禁用单元格拖放功能，如下面的代码所示。

Private Sub Worksheet_SelectionChange(ByVal TargetAs Range)

If NotApplication.Intersect(Target，Range('A1:A15')) Is Nothing Then

Application.CellDragAndDrop = False

Else

Application.CellDragAndDrop = True

EndIf

End Sub

代码解析：

工作表的SelectionChange事件过程，在指定的单元格区域禁用单元格的拖放功能。

CellDragAndDrop 属性设置单元格的拖放功能，如果允许使用单元格拖放功能，则该值为True。

为了不影响其他工作表，应在工作表的Deactivate事件中恢复单元格的拖放功能，如下面的代码所示。

Private Sub Worksheet_Deactivate()

Application.CellDragAndDrop= True

End Sub

代码解析：

工作表的Deactivate事件过程，恢复单元格的拖放功能。

工作表的Deactivate事件当工作表从活动状态转为非活动状态时产生，语法如下：

Private Sub object_Deactivate()

参数object代表Worksheet对象。

当选择工作表“Sheet1”的A1：A15单元格时将禁用单元格的拖放功能，