EXCELVBA常用代码实战大全.docx
《EXCELVBA常用代码实战大全.docx》由会员分享，可在线阅读，更多相关《EXCELVBA常用代码实战大全.docx（24页珍藏版）》请在冰豆网上搜索。
EXCELVBA常用代码实战大全
VBA常用技巧
VBA常用技巧1
第1章Range（单元格）对象10
技巧1单元格的引用方法10
1-1使用Range属性10
1-2使用Cells属性11
1-3使用快捷记号11
1-4使用Offset属性12
1-5使用Resize属性13
1-6使用Union方法14
1-7使用UsedRange属性14
1-8使用CurrentRegion属性15
技巧2选定单元格区域的方法15
2-1使用Select方法15
2-2使用Activate方法16
2-3使用Goto方法17
技巧3获得指定行、列中的最后一个非空单元格17
技巧4定位单元格20
技巧5查找单元格21
5-1使用Find方法21
5-2使用Like运算符25
技巧6替换单元格内字符串26
技巧7复制单元格区域27
技巧8仅复制数值到另一区域30
8-1使用选择性粘贴30
8-2直接赋值的方法31
技巧9单元格自动进入编辑状态32
技巧10禁用单元格拖放功能32
技巧11单元格格式操作33
11-1单元格字体格式设置33
11-2设置单元格内部格式35
11-3为单元格区域添加边框36
11-4灵活设置单元格的行高列宽38
技巧12单元格中的数据有效性39
12-1在单元格中建立数据有效性39
12-2判断单元格是否存在数据有效性41
12-3动态的数据有效性41
12-4自动展开数据有效性下拉列表43
技巧13单元格中的公式44
13-1在单元格中写入公式44
13-2检查单元格是否含有公式45
13-3判断单元格公式是否存在错误46
13-4取得单元格中公式的引用单元格47
13-5将单元格中的公式转换为数值48
技巧14单元格中的批注49
14-1判断单元格是否存在批注49
14-2为单元格添加批注50
14-3删除单元格中的批注51
技巧15合并单元格操作52
15-1判断单元格区域是否存在合并单元格52
15-2合并单元格时连接每个单元格的文本53
15-3合并内容相同的连续单元格54
15-4取消合并单元格时在每个单元格中保留内容56
技巧16高亮显示单元格区域57
技巧17双击被保护单元格时不显示提示消息框58
技巧18重新计算工作表指定区域60
技巧19录入数据后单元格自动保护60
技巧20工作表事件Target参数的使用方法62
20-1使用单元格的Address属性62
20-2使用Column属性和Row属性63
20-3使用Intersect方法63
第2章Worksheet（工作表）对象65
技巧21引用工作表的方式65
21-1使用工作表的名称65
21-2使用工作表的索引号65
21-3使用工作表的代码名称66
21-4使用ActiveSheet属性引用活动工作表66
技巧22选择工作表的方法67
技巧23遍历工作表的方法68
23-1使用For...Next语句68
23-2使用ForEach...Next语句70
技巧24在工作表中上下翻页71
技巧25工作表的添加与删除72
技巧26禁止删除指定工作表76
技巧27自动建立工作表目录78
技巧28工作表的深度隐藏80
技巧29防止更改工作表的名称82
技巧30工作表中一次插入多行83
技巧31删除工作表中的空行84
技巧32删除工作表的重复行86
技巧33定位删除特定内容所在的行88
技巧34判断是否选中整行89
技巧35限制工作表的滚动区域90
技巧36复制自动筛选后的数据区域91
技巧37使用高级筛选获得不重复记录93
技巧38工作表的保护与解除保护94
技巧39奇偶页打印97
第3章Wordbook（工作簿）对象99
技巧40工作簿的引用方法99
40-1使用工作簿的名称99
40-2使用工作簿的索引号99
40-3使用ThisWorkbook100
40-4使用ActiveWorkbook101
技巧41新建工作簿文件101
技巧42打开指定的工作簿103
技巧43判断指定工作簿是否打开106
43-1遍历Workbooks集合方法106
43-2错误处理方法106
技巧44禁用宏则关闭工作簿107
技巧45关闭工作簿不显示保存对话框111
45-1使用Close方法关闭工作簿111
45-2单击工作簿关闭按钮关闭工作簿113
技巧46禁用工作簿的关闭按钮113
技巧47保存工作簿的方法115
47-1使用Save方法115
47-2直接保存为另一文件名115
47-3保存工作簿副本115
技巧48保存指定工作表为工作簿文件116
技巧49打印预览时不触发事件118
技巧50设置工作簿文档属性信息120
技巧51不打开工作簿取得其他工作簿数据121
51-1使用公式121
51-2使用GetObject函数122
51-3隐藏Application对象123
51-4使用ExecuteExcel4Macro方法124
51-5使用SQL连接125
技巧52返回窗口的可视区域地址126
第4章Shape（图形）、Chart（图表）对象128
技巧53在工作表中添加图形128
技巧54导出工作表中的图片133
技巧55在工作表中添加艺术字135
技巧56遍历工作表中的图形137
技巧57移动、旋转图片139
技巧58工作表中自动插入图片140
技巧59固定工作表中图形的位置143
技巧60使用VBA自动生成图表145
技巧61使用独立窗口显示图表149
技巧62导出工作表中的图表150
技巧63多图表制作151
第5章Application对象155
技巧64取得Excel版本信息155
技巧65取得当前用户名称156
技巧66Excel中的“定时器”156
技巧67设置活动打印机的名称158
技巧68屏蔽、改变组合键的功能159
技巧69设置Excel窗口标题栏160
技巧70自定义Excel状态栏161
技巧71灵活退出Excel162
技巧72隐藏Excel主窗口163
72-1设置Application对象的Visible属性163
72-2将窗口移出屏幕164
72-3设置工作簿作为加载宏运行165
第6章使用对话框167
技巧73使用Msgbox函数167
73-1显示简单的提示信息167
73-2定制个性化的消息框168
73-3获得消息框的返回值169
73-4在消息框中排版171
73-5对齐消息框中显示的信息172
技巧74自动关闭的消息框174
74-1使用WshShell.Popup方法显示消息框174
74-2使用API函数显示消息框175
技巧75使用InputBox函数176
75-1简单的数据输入176
75-2使用对话框输入密码178
技巧76使用InputBox方法180
76-1输入指定类型的数据180
76-2获得单元格区域地址182
技巧77内置对话框183
77-1调用内置的对话框183
77-2获取选定文件的文件名186
77-3使用“另存为”对话框188
技巧78调用操作系统“关于”对话框190
第7章菜单和工具栏192
技巧79在菜单中添加菜单项192
技巧80在菜单栏指定位置添加菜单195
技巧81屏蔽和删除工作表菜单197
技巧82改变系统菜单的操作198
技巧83定制自己的系统菜单199
技巧84改变菜单按钮图标205
技巧85右键快捷菜单增加菜单项206
技巧86自定义右键快捷菜单207
技巧87使用右键菜单制作数据有效性210
技巧88禁用工作表右键菜单212
技巧89创建自定义工具栏213
技巧90自定义工具栏按钮图标216
技巧91自定义工作簿图标217
技巧92移除工作表的最小最大化和关闭按钮218
技巧93在工具栏上添加下拉列表框219
技巧94屏蔽工作表的复制功能221
技巧95禁用工具栏的自定义222
技巧96屏蔽所有的命令栏225
技巧97恢复Excel的命令栏226
第8章控件与用户窗体228
技巧98限制文本框的输入228
技巧99文本框添加右键快捷菜单230
技巧100文本框回车自动输入234
技巧101自动选择文本框内容235
技巧102设置文本框数据格式236
技巧103限制文本框的输入长度238
技巧104将光标返回文本框中239
技巧105文本框的自动换行241
技巧106多个文本框数据相加243
技巧107控件跟随活动单元格244
技巧108高亮显示按钮245
技巧109组合框和列表框添加列表项的方法247
109-1使用RowSource属性添加列表项247
109-2使用List属性添加列表项248
109-3使用AddItem方法添加列表项249
技巧110去除列表框数据源的重复值和空格251
技巧111移动列表框条目253
技巧112允许多项选择的列表框256
技巧113多列组合框和列表框的设置259
113-1多列组合框和列表框添加列表项259
113-2多列列表框写入工作表261
技巧114输入时逐步提示信息263
技巧115二级组合框270
技巧116使用DTP控件输入日期272
技巧117使用RefEdit控件选择区域275
技巧118如何注册控件276
技巧119遍历控件的方法279
119-1使用名称中的变量遍历控件279
119-2使用对象类型遍历控件281
119-3使用程序标识符遍历控件282
119-4使用名称中的变量遍历图形283
119-5使用FormControlType属性遍历图形284
技巧120使微调框最小变动量小于1285
技巧121不打印工作表中的控件287
121-1设置控件格式287
121-2设置控件的printobjcet属性289
技巧122在框架中使用滚动条289
技巧123使用多页控件291
技巧124标签文字垂直居中对齐293
技巧125使用TabStrip控件295
技巧126显示GIF动画图片297
技巧127播放Flash文件300
技巧128在工作表中添加窗体控件302
128-1使用AddFormControl方法303
128-2使用Add方法305
技巧129在工作表中添加ActiveX控件307
129-1使用Add方法308
129-2使用AddOLEObject方法310
技巧130使用spreadsheet控件311
技巧131使用Listview控件314
131-1使用Listview控件显示数据列表314
131-2在Listview控件中使用复选框317
131-3调整Listview控件的行距319
131-4在Listview控件中排序322
131-5Listview控件的图标设置323
技巧132调用非模式窗体326
技巧133进度条的制作328
133-1使用进度条控件328
133-2使用标签控件330
技巧134使用TreeView控件显示层次333
技巧135用户窗体添加图标337
技巧136用户窗体添加最大最小化按纽339
技巧137禁用窗体标题栏的关闭按钮340
技巧138屏蔽窗体标题栏的关闭按钮341
技巧139无标题栏和边框的窗体343
技巧140制作年月选择窗体344
技巧141自定义窗体中的鼠标指针类型347
技巧142调整窗体的显示位置348
技巧143由鼠标确定窗体显示位置350
技巧144用户窗体的打印351
技巧145使用自定义颜色设置窗体颜色353
技巧146在窗体中显示图表354
146-1使用Export方法354
146-2使用API函数356
技巧147窗体运行时调整控件大小357
技巧148在用户窗体上添加菜单360
技巧149在用户窗体上添加工具栏364
技巧150使用代码添加窗体及控件369
技巧151用户窗体的全屏显示375
151-1设置用户窗体为应用程序的大小375
151-2根据屏幕分辨率进行设置376
技巧152在用户窗体上添加状态栏377
第9章函数的使用381
技巧153调用工作表函数求和381
技巧154查找最大、最小值381
技巧155不重复值的录入383
技巧156获得当月的最后一天385
技巧157四舍五入运算386
157-1极小值修正法386
157-2调用工作表函数法387
技巧158使用字符串函数387
技巧159使用日期函数389
技巧160判断是否为数值393
技巧161格式化数值、日期和时间394
技巧162个人所得税自定义函数396
技巧163人民币大写函数398
技巧164列号转换为列标400
技巧165判断工作表是否为空表401
技巧166查找指定工作表402
技巧167查找指定工作簿是否打开404
技巧168取得应用程序的安装路径404
技巧169数组的使用406
169-1代码运行时创建数组406
169-2文本转换为数组407
169-3使用动态数组去除重复值409
第10章文件操作412
技巧170导入文本文件412
170-1使用查询表导入412
170-2使用Open语句导入413
170-3使用OpenText方法415
技巧171将数据写入文本文件416
171-1使用Print#语句416
171-2另存为文本文件418
技巧172文件修改的日期和时间419
技巧173查找文件或文件夹420
技巧174获得当前文件夹的名称422
技巧175创建和删除文件夹422
技巧176重命名文件或文件夹423
技巧177复制指定的文件424
技巧178删除指定的文件425
技巧179搜索特定的文件426
技巧180使用WSH处理文件428
180-1获取文件信息428
180-2查找文件430
180-3移动文件431
180-4复制文件431
180-5删除文件432
180-6创建文件夹433
180-7复制文件夹434
180-8移动文件夹435
180-9删除文件夹435
180-10导入文本文件436
180-11创建文本文件438
第11章其他应用441
技巧181取得电脑名称441
技巧182取得逻辑盘序列号442
技巧183使用API取得硬盘信息443
技巧184使用数字签名444
技巧185暂停代码的运行449
技巧186定时关机450
技巧187打开指定的网页451
技巧188VBE的操作452
188-1添加模块和过程452
188-2建立事件过程454
188-3模块的导入与导出456
188-4删除宏代码457
技巧189保护VBA代码459
189-1设置工程密码459
189-2设置“工程不可查看”460
技巧190优化代码462
190-1关闭屏幕刷新462
190-2使用工作表函数464
190-3使用更快的单元格操作方法465
190-4使用With语句引用对象466
190-5少用激活或选择语句468
技巧191取得文件的基本名称469
技巧192防止用户中断代码运行470
技巧193加班费计算表472
技巧194制作发放条498
技巧195费用统计表501
技巧196职工花名册516
技巧197收据系统529
技巧198职工考勤系统567
第1章Range（单元格）对象
Range对象是Excel应用程序中最常用的对象，一个Range对象代表一个单元格、一行、一列、包含一个或者更多单元格区域（可以是连续的单元格，也可以是不连续的单元格）中选定的单元格，甚至是多个工作表上的一组单元格，在操作Excel内的任何区域之前都需要将其表示为一个Range对象，然后使用该Range对象的方法和属性。
技巧1单元格的引用方法
在VBA中经常需要引用单元格或单元格区域区域，主要有以下几种方法。
1-1使用Range属性
VBA中可以使用Range属性返回单元格或单元格区域，如下面的代码所示。
#001SubRngSelect（）
#002Sheet1.Range（"A3:
F6,B1:
C5"）.Select
#003EndSub
代码解析：
RngSelect过程使用Select方法选中A3:
F6，B1:
C5单元格区域。
Range属性返回一个Range对象，该对象代表一个单元格或单元格区域，语法如下：
Range（Cell1,Cell2）
参数Cell1是必需的，必须为A1样式引用的宏语言，可包括区域操作符（冒号）、相交区域操作符（空格）或合并区域操作符（逗号）。
也可包括美元符号（即绝对地址，如“$A$1”）。
可在区域中任一部分使用局部定义名称，如Range（"B2:
LastCell"），其中LastCell为已定义的单元格区域名称。
参数Cell2是可选的，区域左上角和右下角的单元格。
运行SubRngSelect过程，选中A3:
F6,B1:
C5单元格区域，如图11所示。
图11使用Range属性引用单元格区域
注意如果没有使用对象识别符，Range属性返回活动表的一个区域，如果活动表不是工作表，则该属性无效。
1-2使用Cells属性
使用Cells属性返回一个Range对象，如下面的代码所示。
#001SubCell（）
#002DimicellAsInteger
#003Foricell=1To100
#004Sheet2.Cells（icell,1）.Value=icell
#005Next
#006EndSub
代码解析：
Cell过程使用For...Next语句为工作表中的A1:
A100单元格区域填入序号。
Cells属性指定单元格区域中的单元格，语法如下：
Cells（RowIndex,ColumnIndex）
参数RowIndex是可选的，表示引用区域中的行序号。
参数ColumnIndex是可选的，表示引用区域中的列序号。
如果缺省参数，Cells属性返回引用对象的所有单元格。
Cells属性的参数可以使用变量，因此经常应用于在单元格区域中循环。
1-3使用快捷记号
在VBA中可以将A1引用样式或命名区域名称使用方括号括起来，作为Range属性的快捷方式，这样就不必键入单词“Range”或使用引号，如下面的代码所示。
#001SubFastmark（）
#002[A1：
A5]=2
#003[Fast]=4
#004EndSub
代码解析：
Fastmark过程使用快捷记号为单元格区域赋值。
第2行代码使用快捷记号将活动工作表中的A1：
A5单元格赋值为2。
第3行代码将工作簿中已命名为“Fast”的单元格区域赋值为4。
注意使用快捷记号引用单元格区域时只能使用固定字符串而不能使用变量。
1-4使用Offset属性
可以使用Range对象的Offset属性返回一个基于引用的Range对象的单元格区域，如下面的代码所示。
#001SubOffset（）
#002Sheet3.Range（"A1:
C3"）.Offset（3,3）.Select
#003EndSub
代码解析：
Offset过程使用Range对象的Offset属性选中A1：
A3单元格偏移三行三列后的区域。
应用于Range对象的Offset属性的语法如下：
expression.Offset（RowOffset,ColumnOffset）
参数expression是必需的，该表达式返回一个Range对象。
参数RowOffset是可选的，区域偏移的行数（正值、负值或0（零））。
正值表示向下偏移，负值表示向上偏移，默认值为0。
参数ColumnOffset是可选的，区域偏移的列数（正值、负值或0（零））。
正值表示向右偏移，负值表示向左偏移，默认值为0。
运行Offset过程，选中A1：
A3单元格偏称三行三列后的区域，如图12所示。
图12使用Range对象的Offset属性
1-5使用Resize属性
使用Range对象的Resize属性调整指定区域的大小，并返回调整大小后的单元格区域，如下面的代码所示。
#001SubResize（）
#002Sheet4.Range（"A1"）.Resize（3,3）.Select
#003EndSub
代码解析：
Resize过程使用Range对象的Resize属性选中A1单元格扩展为三行三列后的区域。
Resize属性的语法如下：
expression.Resize（RowSize,ColumnSize）
参数expression是必需的，返回要调整大小的Range对象
参数RowSize是可选的，新区域中的行数。
如果省略该参数，则该区域中的行数保持不变。
参数ColumnSize是可选的，新区域中的列数。
如果省略该参数。
则该区域中的列数保持不变。
运行Resize过程，选中A1单元格扩展为三行三列后的区域，如图13所示。
图13使用Resize属性调整区域大小
1-6使用Union方法
使用Union方法可以将多个非连续区域连接起来成为一个区域，从而可以实现对多个非连续区域一起进行操作，如下面的代码所示。
#001SubUnSelect（）
#002Union（Sheet5.Range（"A1:
D4"）,Sheet5.Range（"E5:
H8"））.Select
#003EndSub
代码解析：
UnSelect过程选择单元格A1:
D4和E5:
H8所组成的区域。
Union方法返回两个或多个区域的合并区域，语法如下：
ex
升级会员 