excel进阶VBA 全.docx
《excel进阶VBA 全.docx》由会员分享，可在线阅读，更多相关《excel进阶VBA 全.docx（18页珍藏版）》请在冰豆网上搜索。
excel进阶VBA全
第一期：
带你入门VBA
第二期：
进阶操作VBA
第三期：
编制简单的自定义函数
第四期:
如何控制工作薄和工作表
第五期：
如何控制单元格
第一期：
带你入门VBA
1、马上新建一个EXCEL文件，用绘图插入一个矩形，点击右键，在菜单中点击指定宏，再点击新建。
会出现
Sub矩形1_单击（）
EndSub
2、在两句中间加入：
Sheets（"sheet1"）.Range（"a1"）=100
结果如下：
Sub矩形1_单击（）
Sheets（"sheet1"）.Range（"a1"）=100
EndSub
3、再加一句：
Sheets（"sheet1"）.Cells（2,1）=200
Sub矩形1_单击（）
Sheets（"sheet1"）.Range（"a1"）=100
Sheets（"sheet1"）.Cells（2,1）=200
EndSub
这是两种单元格的基本录入方法，都懂吗？
补充：
单元格和工作表在VBA的表示方法
（1）Range（"单元格地址"）如Range（"a1"）即为A1单元格；
（2）CELLS（行，列）如CELLS（1，1）=A1，CELLS（2，1）=A2；
（3）工作表在VBA中表示方法：
sheets（"工作表名"）。
4、再输入下面的循环程序
Sub矩形1_单击（）
DimxAsInteger‘声明x为整数型变量
Forx=1To20‘x的值为从1到20循环
Sheets（"sheet1"）.Cells（x,1）=x‘单元CELLS（X,1）的值班等于x值
Next
EndSub
这个程序的结果A1至A20的值分别为1到20
补充：
“DIM变量名称AS变量类型”这个是声名变量用的，就象是平时别人给你介绍客人：
他是干什么的，只是介绍了我们才能针对性的谈话。
变量也一样，我们给程序介绍：
这个是整数型，你没必要把他当作其他类型对待，这样系统对你声明的变量作整数型对待了，如果不介绍系统还要花费一定的内存去判断新出现的变量是什么？
结果是多占用内存影响运算速度。
5、如果刚才的明白，再加一句，是如何在A21计算出A1：
A20的和
Sub矩形1_单击（）
DimiAsInteger
Fori=1To20
Sheets（"sheet1"）.Cells（i,1）=i
Next
Range（"a21"）.Value=Application.WorksheetFunction.Sum（Range（"a1:
a20"））
EndSub
在VBA不直接支持在EXCEL工作表中的一些函数，如果要调用就必须加上Application.WorksheetFunction，SUM求和语法和工作表中的一样，但表示不能直接SUM（A1：
A20）,要用VBA的表示方法SUM（Range（"a1:
a20"））。
补充：
每个函数前都要加Application.WorksheetFunction.吗？
如果是单个调用必须加；如果是多个调用,就可以用WITH语句省去后面的
Sub矩形1_单击（）
DimiAsInteger
Fori=1To20
Sheets（"sheet1"）.Cells（i,1）=i
Next
WithApplication.WorksheetFunctionRange（"a21"）.Value=.Sum（Range（"a1:
a20"））
Range（"a22"）.Value=.Average（Range（"a1:
a20"））
EndWith
EndSub
注意.Sum和.Average前要有一个英文实心点”.”。
6、布置个习题：
请在工作表SHEET1的D4：
D36单元格中填充4-36的数值，并在D37单元格中求和，并计算处A列存放数据的行数
Sub矩形1_单击（）
DimiAsInteger
Fori=4To36
Sheets（"sheet1"）.Cells（i,4）=i
Next
Range（"d37"）.Value=Application.WorksheetFunction.Sum（Range（"D4:
D36"））
Range（"B1"）.Value=Application.WorksheetFunction.CountA（Columns（"A"））‘Range（"A:
A"）也可表示A列
EndSub
7、即然能求出A列存放数据的行数了,那么如何让程序自动在最好一行填入求得的和呢?
原来是直接range（"a21"）=...现在不这样做，要自动识别最后一行然后在这一行填入求和
（1）如果只是求前20行的和放在最后一行：
Sub矩形1_单击（）
DimiAsInteger
DimYYYAsInteger
Fori=1To20
Sheets（"sheet1"）.Cells（i,1）=i
Next
YYY=Application.WorksheetFunction.CountA（Columns（"A"））
Cells（YYY+1,1）=Application.WorksheetFunction.Sum（Range（"A1:
A20"））
EndSub
‘如果有空行，那么就用RANGE（A65536）.END（XLUP）.ROW来判断最后一行的行号
（2）如果是对A列所有的都求和：
Sub矩形2_单击（）
DimiAsInteger
DimYYYAsInteger
Fori=1To20
Sheets（"sheet1"）.Cells（i,1）=i
Next
YYY=Application.WorksheetFunction.CountA（Columns（"A"））
Cells（YYY+1,1）=Application.WorksheetFunction.Sum（Range（Cells（1,1）,Cells（YYY,1）））
EndSub
在本例中是增加了个整数变量YYY，为什么要命名它为整数，是因为单元格个数是以整数表示的，没有听说过第1.2个单元格吧。
把A列的非空单元格个数赋给YYY，然后在后面的程序就是以调用它了。
补充：
Sum（Range（Cells（1,1）,Cells（YYY,1）））是变动的单元格求和；RANGE（CELLS（），CELLS（））是表示一个区域，比如Range（cells（1,1）,cells（20,1））和Range（"a1:
a20"）是一样的,为什么要这样表示是因为CELLS表示单元格时可以加入变量。
COLUMN是指列,COLUMNS是指列的集合
ROW是表示行,ROWS是行的集合
如果用Sheets（"sheet1"）.columns.select会选定所有列即整个工作表
8、宏的调用
如果是调用本工作薄的宏，直接输入“宏”的本名”
如果是调动其他工作薄的宏，则要用Application.run"123.xls"!
aaa
‘aaa是工作薄123中的宏,只有123.xls工作薄打开时才能调用。
第二期：
进阶操作VBA
1、要求做一个按纽，执行程序后在B1：
B15填入101至115的数值，并在最后一行的单元格求和
Sub按钮1_单击（）
DimiAsInteger
Fori=1To15
Sheets（"Sheet1"）.Cells（i,2）=i+100
Next
Range（"b16"）.Value=Application.WorksheetFunction.Sum（Range（"b1:
b15"））
EndSub
2、在上题的基础上，求B列>106数的个数（包括刚才求单元格B16）,并用对话框的形式显示出来
Sub矩形1_单击（）
Fori=1ToRange（"B65536"）.End（xlUp）.Row
IfCells（i,2）>106Then
K=K+1
EndIf
Next
MsgBox"大于106数值个数有"&K&"个",1+64,"统计信息"
EndSub
补充：
Range（"B65536"）.End（xlUp）.Row是指B列最后一个非空单元格，END（XLUP）是向上数第一个非空单元格，为了找到最下面的非空单元格，当然要从RANGE（"B65536"）开始向上找了。
如选取Sheet1第一行有内容单元格区域（假设A1不为空）:
sheets（"sheet1"）.range（"a1",range（"a1"）.end（xltoright））.select
选取B列有内容单元格区域：
（假设B1不为空）:
sheets（"sheet1"）.range（"B1",range（"B65536"）.end（XLUP））.select
MSGBOX有时带（），比如AAA=MSGBOX（）这种情况下可以取到用户点击对话框按纽的返回值,以确定下一步该怎么做；而不带括号只是提示的作用,不能取得返回的值
MsgBox函数:
MsgBox（"对话框中的提示信息",buttons,title,helpfile,context）
提示语句:
仅有的一个必需的参数。
最大长度大约为1024个字符。
如内容超过一行，则可以在每一行之间用回车符（Chr（13））、换行符（Chr（10））或是回车与换行符的组合（Chr（13）&Chr（10））将各行分隔开来。
Buttons可以是由下列列表中的一个或多个，可将这些数字相加以生成buttons参数值。
省略时值为0。
VbOKOnly=0:
只显示OK按钮
VbOKCancel=1:
显示OK及Cancel按钮
VbAbortRetryIgnore=2:
显示Abort、Retry及Ignore按钮
VbYesNoCancel=3:
显示Yes、No及Cancel按钮
VbYesNo=4:
显示Yes及No按钮
VbRetryCancel=5:
显示Retry及Cancel按钮
VbCritical=16:
显示CriticalMessage图标
VbQuestion=32:
显示WarningQuery图标
VbExclamation=48:
显示WarningMessage图标
VbInformation=64:
InformationMessage图标
vbDefaultButton1=0:
第一个按钮是缺省值
vbDefaultButton2=256:
第二个按钮是缺省值
vbDefaultButton3=512:
第三个按钮是缺省值
vbDefaultButton4=768:
第四个按钮是缺省值
vbApplicationModal=0:
应用程序强制返回；应用程序被挂起，直到用户对消息框作出响应才继续工作。
VbSystemModal=4096:
系统强制返回；全部应用程序都被挂起，直到用户对消息框作出响应才继续工作。
VbMsgBoxHelpButton=16384：
将Help按钮添加到消息框
VbMsgBoxSetForeground=65536：
指定消息框窗口作为前景窗口
VbMsgBoxRight=524288：
文本为右对齐
VbMsgBoxRtlReading=1048576：
指定文本应为在希伯来和阿拉伯语系统中的从右到左显示
返回值：
vbOK=1：
OK
vbCancel=2：
Cancel
vbAbort=3：
Abort
vbRetry=4：
Retry
vbIgnore=5：
Ignore
vbYes=6：
Yes
vbNo=7：
No
helpfile字符串表达式，识别用来向对话框提供上下文相关帮助的帮助文件。
Helpfile与context是相配合的。
Context为数值表达式，由帮助文件的作者指定给适当的帮助主题的帮助上下文编号。
当不需要返回值时,可以这样写（标题和按钮参数可以省略,系统默认用缺省值）:
msgbox"刷新成功!
"
当要判断返回值时,可以这样写（标题和按钮参数一样可以省略,系统默认用缺省值）:
i=msgbox（"需要刷新目录吗?
如果确定请点确定按钮,否则请点取消按钮.",VbOKCancel,"提示窗口"）
ifi=vbcalcelthenexitsub'如果点击了取消,就退出当前事件.
3、把B列>106且<112的数所在单元格填充为红色
Sub矩形1_单击（）
Fori=1ToRange（"B65536"）.End（xlUp）.Row
IfCells（i,2）>106AndCells（i,2）<112Then
Cells（i,2）.Interior.ColorIndex=3
EndIf
Next
EndSub
ColorIndex属性
返回或者设置边框、字体或者内部填充区域的颜色，如下表所示。
该颜色可定义为当前调色板中的索引值，也可使用下列XlColorIndex常量之一：
xlColorIndexAutomatic或xlColorIndexNone。
Variant类型，可读写。
对象
ColorIndex
Border
边框的颜色。
Borders
四条边框的颜色。
如果四条边框使用的不是同一种颜色则返回Null。
Font
字体的颜色。
指定为xlColorIndexAutomatic可自动设置颜色。
Interior
内部填充的颜色。
将本属性指定为xlColorIndexNone可不进行内部填充。
将本属性设为xlColorIndexAutomatic可进行自动填充（对于图形对象）。
说明本属性将一种颜色指定为工作簿调色板的一条索引。
可以使用Colors方法返回当前的调色板。
InputBox函数：
格式如下，第一项为必须外，其除为可选项,可以省略不写，XY坐标为在窗体上的准确位置。
当用户点取消时，返回一个空的字符串（""）。
为了省略某些位置参数，必须加入相应的逗号分界符。
strFillName=InputBox（"对话框中的提示信息","对话框的标题","缺省的返回值"，X坐标，Y坐标）
X坐标和Y坐标当你需要为InputBox窗口指定在屏幕中的位置时用的，单位为象素，一般省略不写。
实例讲解：
（学生成绩排名及汇总
第三讲、编写简单的自定义函数
第一讲链接:
第二讲链接：
第三讲链接：
如何加载自定义函数：
1、现在先从实例开始，新建一个EXCEL工作薄，ART+F11打开VBE编辑器，在工程列表框中单击右键---插入---模块，在右边的代码框中输入：
FunctionPanduan（aaAsRange）
‘panduan是定义的函数名称,aa是该函数的参数,AsRange是定义该参数为单元格
Ifaa.Value>0Then
Panduan="大于零"‘是对引用单元格aa的值进行判断,把判断的结果返回给该函数所在单元格
ElseIfaa.Value=0Then
Panduan="等于零"
Else
Panduan="小于零"
EndIf
EndFunction
还可以用SELECTCASE语句
Functionpanduan（aaAsRange）
SelectCaseaa.Value
CaseIs>0panduan="大于零"‘如果判断的数据必须是范围时,则须在Case语句后加入Is关键词,来表示判断的数据,其实IS是VBA自动加上去的，我们完全可以不管他。
CaseIs=0panduan="等于零"
CaseIs<0panduan="小于零"
EndSelect
EndFunction
在工作表A1输入10，A2输入0，A3输入-10。
在B1单元格设置公式=Panduan（A1），在B2单元格设置公式=Panduan（A2），在B3单元格设置公式=Panduan（A3），看看有什么结果？
出个题练练:
要求编个自定义函数,判断它如果大于100就返回它的值乘于0.1,如果大于200,就返回它的值乘以0.2,如果大于300,就返回它的值乘以0.3,否则返回它的值乘以0.05
PublicFunctionpanduan2（aaAsRange）‘不能将aa定义为Integer（整数）,如果单元格的值为小数,或超过Integer范围就不行了。
SelectCaseaa
CaseIs>300panduan2=aa*0.3
CaseIs>200panduan2=aa*0.2
CaseIs>100panduan2=aa*0.1
CaseElsepanduan2=aa*0.05
EndSelect
EndFunction
或者
FunctionPanduan（aaAsRange）
Ifaa.Value>300Then
Panduan=aa*0.3
ElseIfaa.Value>200Then
Panduan=aa*0.2
ElseIfaa.Value>100Then
Panduan=aa*0.1
ElsePanduan=aa*0.05
EndIf
EndFunction
再出个题，编一个单元区间任一区间求和的函数，比如A1：
A20为一组数，怎样设置一个自定义函数，求出大于100，小于200的所有值的和（最好还能利用这个函数进行相关条件计数）
Functionwbetween（rng,x,y,z）‘rng,x,y,z没定义数据类型就可以为任意类型
Application.Volatile
Ifz=0Then
wbetween=Application.CountIf（rng,"<="&y）-Application.CountIf（rng,"<"&x）
ElseIfz=1Then
wbetween=Application.WorksheetFunction.SumIf（rng,"<="&y,rng）-Application.SumIf（_
rng,"<"&x,rng）‘Application.WorkSheetFunction是在VBA中使用工作表函数定义用的，后面的直接用Application.就可以了。
Else
MsgBox"最后一个参数不能大于1"
EndIf
EndFunction
程序详解:
Functionwbetween（rng,x,y,z）’我们在使用EXCEL工作表函数的时候，都要有函数名称（如IF函数的IF，COUNT函数的COUNT，SUMIF函数的SUMIF），所以我们首先要为函数起个名称（尽量用英文字母，但不要用程序常用的关键字，比如TRUE，FALSE，END等），Functionwbetween为函数起个名称wbetween。
函数名称有了，一般还要有参数，比如COUNT函数=COUNT（A1：
A10），SUMIF（A1:
A10，">0",B1:
B10）等,只有用了这些参数后,我们才能得到想要的结果,所以我们在自定义函数的时候也要为它指定参数,Functionwbetween（rng,x,y,z）,括号中的即为该函数的四个参数,参数的多少要根据自定义函数所要达到的功能而定.比如这个函数wbetween,我要求它求出一单元格区域中一定范围的和或计数,这就需要有四个参数,一个是单元格区域（rng）,一个是上限（x）,一个是下限（y）,另一个就是指定是求和还是计数（z）,这些参数的名称也是自已起的,规则和程序名称类似.
Application.Volatile‘Volatile用于将用户自定义函数标记为易失性函数，无论何时在工作表的任意单元格中进行计算，易失性函数都必须重新进行计算。
非易失性函数只在输入变量改变时才重新计算，若不用于计算工作表单元格的用户自定义函数中，则此方法无效.通俗点,就是让引用单元格改变时,公式也随之更新.
Ifz=0Then'z是用来判断是求和还是计数,在这儿设置的是如果Z=0,就调用工作表函数COUNTIF进行计数
wbetween=Application.CountIf（rng,"<="&y）-Application.CountIf（rng,"<"&x）
‘wbetween=是把计数后的值班返回给该函数,也就是我们想看到的计算结果,COUNIF和下面的SUMIF函数语法和在工作表中使用方法差不多是一样的.不过在调用的时候加上Application.WorksheetFunction（WorksheetFunction可以省略）
ElseIfz=1Then‘如果Z=1,就调动SUMTIF进行求和
wbetween=Application.WorksheetFunction.SumIf（rng,"<="&y,rng）-Application.SumIf（rng,"<"&x,rng）
Else‘如果z是其他值,就执行下一句
MsgBox"最后一个参数不能大于1"‘出现提示框,提示最后一个参数不能大于1
第四讲：
如何控制关于工作薄与工作表
第一期：
第二期：
第三期：
升级会员 