excel进阶VBA 全.docx
- 文档编号:5957269
- 上传时间:2023-01-02
- 格式:DOCX
- 页数:18
- 大小:48.75KB
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
第四讲:
如何控制关于工作薄与工作表
第一期:
第二期:
第三期:
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel进阶VBA excel 进阶 VBA
![提示](https://static.bdocx.com/images/bang_tan.gif)