VBA学习记录.docx
《VBA学习记录.docx》由会员分享，可在线阅读，更多相关《VBA学习记录.docx（17页珍藏版）》请在冰豆网上搜索。
VBA学习记录
1.统计A1:
A10单元格内底色为黄色的个数
Functioncountcolor
Dimrngasrange
Foreachrnginrange（“A1:
A10”）
Ifrng.interior.color=RGB（255,255,0）Then
Countcolor=Countcolor+1
Endif
Nextrng
EndFunction
2.用颜色索引号来引用颜色
Range（“A1”）.interior.ColorIndex=6黄色
3.要统计的单元格不是固定的，用参数指定计算区域
FunctionCountColor（arrasRange）
Dimrngasrange
ForEachrnginarr
Ifrng.interior.color=RGB（255,255,0）Then
Countcolor=countcolor+1
Endif
Nextrng
Endfunction
4.给自定义函数指定第2个参数
FunctionCountColor（arrasRange，casrange）
Dimrngasrange
Foreachrnginarr
Ifrng.interior.color=c.inerior.colorThen
Countcolor=countcolor+1
Endif
Nextrng
EndFunction
5.设置自定义函数为易失性函数:
在函数开始添加
Application.volatileTrueF9键重算工作表
6.只显示最终运行结果，而不显示运行过程
Subinputtest（）
Cells.Clearcontents‘清除表中所有数据
Application.ScreenUpdating=Flase
Range（“A1:
A10”）=100
Range（“B:
:
B10”）=200
Application.ScreenUpdating=true
Endsub
7.取消显示警告对话框
SubDelSht（）
Dimshtasworksheet
Application.DisplayAlerts=False
Foreachshtinworksheets
Ifsht.name<>activesheet.NameThen
Sht.delete
Endif
Next
Application.DisplayAlerts=true‘displayalerts是要加s的。
Endsub
8.自动写入单元格地址
PrivateSubWorksheet_SelectionChange（ByValtargetAsRange）
target.Value=target.Address
EndSub
9.禁用事件
（1）PrivateSubWorksheet_SelectionChange（ByValtargetAsRange）
‘选中一个单元格后就显示该单元格的地址
target.Value=target.Address
target.Offset（1,0）.Select’程序结束前，选中新的单元格。
选中新的单元格会再次运行这个过程，因此造成了死循环。
EndSub
（2）虽然执行了第四行，但并没有触发重新执行该过程，而通过第五行得到的结果是在执行了第四行后，只是选择了下一个单元格，而没有重新执行该过程。
——禁用整个过程。
PrivateSubWorksheet_SelectionChange（ByValtargetAsRange）’在工作表中的自动生成的句子。
target.Value=target.Address
application.enableEvents=false
target.Offset（1,0）.Select
application.enableEvents=true
Endsub
10.在VBA里面没有countif函数，可以使用Application对象WorksheetFunction属性调用工作表函数。
A1:
B50单元格中数值大于1000的单元格有多少个。
SubCountest（）
Dimmycountasinteger
Mycount=application.worksheetfunction.countif（Range（A1:
B50）范围,”>1000”条件）
Endsub
如果VBA里有相同的函数，不能再引用工作表里的函数，否则会出错。
如Len（“ABCD”）
11.在选择的单元格输入300
Application.Selection.value=300,Selection代表所选当前选中对象，省略了工作薄和工作表对象。
12.引用工作表
1.Worksheets.item（3）‘用索引号来指明位置
2.Workbooks（“Book1.xls”）
13.Thisworkbook:
代码所在的工作薄，如Range（“A1”）=thisworkbook.name/.path/.FullName
14.创建，打开，关闭工作薄
1.创建一个工作薄并命名保存到一个指定文件夹。
Workbooks.Add
ActiveWorkbook.saveasFilename:
=“D:
\我的文档\javalearning\表格.xls”
2.打开工作薄
Workbooks.open“D:
\我的文档\javalearning\表格.xls”
3.激活某个工作薄
Workbooks（“表格.xls”）.activate
4.继续保留源文件不打开新文件
Thisworkbook.SaveCopyAsFilename:
=“D:
\我的文档\javalearning\表格.xls”
5.关闭工作薄
Workbooks.close‘关闭所有打开的工作薄
Workbooks（“表格.xls”）.close‘关闭表格工作表
关闭而不显示保存对话框
Workbooks（“表格.xls”）.closesavechanges:
=true‘关闭并保存修改，参数名称和参数之间用“:
=”连接。
Thisworkbook:
当前工作薄；activeworkbook:
活动工作薄
6.工作表操作
1.新建工作表
Worksheets.add
Worksheets.Addafter:
=Worksheets
（1）,Count:
=3编程时，可以使用多个参数，不同参数之间用英文逗号隔开。
在第一张工作表后插入3张工作表。
◆在当前工作薄最后一张工作表前插入两张工作表：
Worksheets.addbefore:
=worksheets（woksheets.Count）,Count=2
2.更改工作表标签名称
Woksheets
（2）.Name=“工资表”
Activesheet.Name=“工资表”，新建的工作表总是活动工作表。
Worksheets.add（before:
=Worksheets
（1））.Name=“工资表”‘add方法是有参数的
3.Delete删除工作表
删除工作表，不提示删除警告
Application.DisplayAlerts=False
Worksheets（“工资表”）.delete
Application.DisplayAlerts=true
4.激活工作表
Worksheets
（1）.Activate或Worksheets
（1）.Select‘当工作表隐藏时，调用它的Select方法会出错，用activate方法不能同时选中多张工作表。
5.复制工作表
Worksheets（“工资表”）.copybefore:
=Worksheets（“出勤登记表”）/把复制得到的表放在哪张表前面，会出现“工资表
（2）”。
不使用参数，默认将工作表复制到新工作薄中，也就是新建了一个工作薄。
6.移动工作表——Move方法
Worksheets（“工资表”）.Movebefore:
=Worksheets（“出勤登记表”）
Move告诉VBA执行的是移动操作，不保留原工作表，不不指定参数，将把工作表移动到新工作表中。
7.隐藏或显示工作表
Worksheets（“工资表”）.Visible=False
=xlSheetHidden
=0
这三句代码作用是一样的，这样隐藏的表示不能再菜单里取消隐藏的，只能用VBA代码或属性窗口中显示它。
Worksheets（“工资表”）.Visible=xlSheetVeryHidden‘取消隐藏
=2
显示工作表：
Worksheets（“工资表”）.Visible=True/xlsheetVisible/1/-1四种方法来显示它。
8.获取工作表的数目
Worksheets.Count
15.Sheets与Worksheets
Sheets比Worksheets包含的方法和属性多。
16.核心，至关重要的Range对象
1.Range（“A1:
A10”）.Value=200;如果单元格被定义为名称，如date,可写成Range（“date”）.Value=100;如果引用多个区间，在各区间添加逗号Range（“A1:
A10,A4:
A6,C3:
D9”）.select
2.引用相交区域（公共区域），在多个区域之间添加空格。
Range（“B1:
B10A4:
D6”）.select空格而不是逗号
3.两个区域围城的矩形区域Range（“B6:
B10”,“D2:
D8”）.select相当于数学里的最小公倍数。
4.Worksheet/Range对象的Cells属性,cells是复数形式
两种等效的写法：
ActiveSheet.Cells（3,4）.value=20;ActiveSheet.Cells（3,”D”）.value=20。
3是行号，只能是数字，4和D是列标，可以是数字，字母。
Range对象的Cell：
Range（“B3:
F9”）.Cells（2,3）=100。
Cells属性还可以用作Range属性的参数：
Range（Cells（1,1）,Cells（10,5））.select‘选中活动工作表中的A1:
E10单元格。
Range（“A1”,”E10”）.select
Cells可以只使用一个参数：
ActiveSheet.Cells
（2）.Value=200也就是在B1单元格内输入200.
在B3:
F9的第8个单元格输入100：
Range（“B3:
F9”）.Cells（8）.Value=100
如果不指定任何参数，Cells属性将返回指定对象中的所有单元格：
ActiveSheet.Cells.Select;Range（“B3：
F9”）.Cells.Select
●更加简洁的方式：
将单元格地址写在中括号里。
不用引号。
[B2]‘B2单元格
[A1:
D10]‘A1:
D10单元格
[A1:
A10,C1:
C10,E1:
E10]‘3个单元格区域合并区域
[B1:
B10A5:
D5]‘两个单元格区域的公共区域
[n]单元格区域被定义的名称‘名称n所代表的单元格。
[]是Application对象的Evaluate方法的简写形式，非常适合引用一个固定的Range对象，里面不能使用变量，缺少灵活性。
5.其它单元格使用方式
1）引用整行：
ActiveSheet.Rows（“3:
5”）.select‘选中活动工作表中的第3行到第5行。
不带参数则选中所有的行。
Range对象的Rows属性：
Rows（“3:
10”）.Rows（“1:
1”）.Select
2）引用整列：
ActiveSheet.Columns（“F:
G”）
ActiveSheet.Columns（6）
ActiveSheet.Columns
Columns（“B:
G”）.Columns（“B:
B”）.select‘选中B：
G列区域中的第2列。
3）Application对象的Union方法
Union方法可将不连续的多个单元格区域粘在一起，可以同时对其进行操作
Application.Union（Range（“A1:
A10”））,Range（“D1:
D5”））.select‘Application可以不写，不同参数之间用逗号隔开。
4）Range对象的Offset属性
Range（“A1”）.offset（2,3）.value=500‘A1单元格出发向下移动2行，向右移动3列到达D3单元格并写入500值。
Range（“C5：
D6”）.offset（-3,0）.Select‘向上移动3个单元格。
5）Range对象的Resize属性
使用Resize属性扩大或缩小指定的单元格区域，得到一个新的单元格区域。
Range（“B2”）.Resize（5,4）.select‘将B2单元格扩大到B2:
E6
第一个区域告诉VBA新区域有5行，第二个参数告诉VBA新区域有4行。
两个参数都是正数。
Range（“B2:
E6”）.Resize（2,1）.Select‘将B2：
E6单元格区域缩小为B2:
B3
6）Worksheet对象的UseRange属性
UsedRange是Worksheet对象的属性，返回
工作表中已使用的单元格围城的矩形区域。
ActiveSheet.UsedRange.Select‘选中活动工作表中已使用的单元格区域，
7）CurrentRegion属性返回当前区域，即以空行和空列的组合为边界的区域。
Range（“B5”）.CurrentResion.Select相当于选中B5后，按F5键，选中B5所在的区域。
8）Range对象的End属性，End属性返回当前区域结尾处的单元格。
Range（“C5”）.End（xlUp）.Select等同于在源单元格按得到的单元格。
有四个参数可以使用：
xlToLeft,xlToRight,xlUp,xlwhiwn
等同于在源单元格<按Ctrl+方向键>书上写的是Ctrl
在A列最后一个单元格按上方向键得到A列最后一个非空单元格：
ActiveSheet.Range（“A65536”）.End（xlup）.offset（1,0）.value=“张青”
9）Value属性是Range对象的默认属性。
Range对象的Count属性返回指定的单元格区域中包含的单元格数。
Range（“B4:
F10”）.Count
ActiveSheet.UsedRange.Rows.count
ActiveSheet.UsedRange.Columns.Count
Selection.Address‘对活动工作表中当前选中对象的引用
10）选择性清除单元格
Range（“B2:
B15”）.Clear
Range（“B2:
B15”）.ClearComments
Range（“B2:
B15”）.ClearContents
Range（“B2:
B15”）.ClearFormats
11）复制单元格区域
Range（“A1”）.copyRange（“C1”）‘宏代码告诉我们，复制单元格的语句永远是：
源单元格区域.Copy目标单元格。
其中省略了“Destination:
=”
Range（“A1”）.CurrentRegion.Copyrange（“G1”）
只粘贴数值，而不张贴格式：
宏录制方法：
Range（“A1:
A10”）.copy
Range（“F1:
F10”）.pasteSpecialPaste:
=xlPasteValues
或者使用：
Range（“F1:
F10”）.Value=Range（“A1:
A10”）.Value
12）剪切单元格
Range（“F1:
F10”）.cutDestination:
=Range（“G1”）
Range（“F1:
F10”）.cutRange（“G6”）
13）删除单元格
不会提供删除对话框供你选择。
Range（“B5”）.DeleteShift:
=xlToleft
Range（“B5”）.DeleteShift:
=xlup（如果不使用参数的话等同于这一句）
Range（“B5”）.EntireRow.Delete
Range（“B5”）.entireColum.Delete
17.其他常用对象
1.新建名字：
ActiveWorkbook.Names.AddName:
=“date”,RefersToR1C1:
=“Sheet1!
R5C[-2]”‘add,新建名称的方法，R代表row，C代表Column[]表示相对引用。
更简单的方式：
Range（“A1:
C10”）.Name=“date”
2.给单元格添加批注
Range（“B5”）.AddCommentText:
=“我用VBA新建的批注”
判断是否有批注：
IfRange（“B5”）.CommentIsNothingThen
Range（“B5”）.Comment.Delete
Range（“B5”）.Comment.Visible=False
3.单元格化妆
withRange（“A1:
L1”）.Font
.Name=“宋体”
.Size=12
.Color=RGB（255,0,0）‘字体颜色
.Bold=True
.Italic=true斜体
.Underline=xlUnderlineStyleDouble‘给文字加双划线
Endwith
4.单元格添加底纹
Range（“A1:
A11”）.Interior.color=RGB（255,255,0）也可以用ColorIndex属性
5.给表格设置边框
WithRange（“A1:
L1”）.CurrentRegion.Borders
.LineStyle=xlContinuous
.color=RGB（0,0,225）
.Weight=xlHairline
Endwith
18.典型的技巧和示例
（1）创建一个新工作薄，并保存到指定文件夹中
Dimwbasworkbook,shtasworksheet
Setwb=workbooks.add
Setsht=worksheets
（1）
Withsht
.name=”花名册”
.Range（“A1:
F1”）=Array（“序号”，”姓名”…）
Endwith
wb.Saveasthisworkbook.Path&“\员工花名册xls”
Activeworkbook.close
（2）判断“员工花名册.xls”工作薄是否存在
Dimfilasstring
Fil=thisworkbook.path&“\员工花名册.xls”
Iflen（Dir（fil））>0then
Msgbox“工作薄已存在”
Else
Msgbox“工作薄不存在”
Endif
Dir函数返回文件名，否则返回空字符串””
（3）按不同的班级将成绩表分类
DimIasLong,bjasstring,rngasrange
i=2
bj=cells（I,”C”）.Value
DoWhilebj<>“”
Setrng=Worksheets（bj）.Range（“A65536“）.End（xlUp）.Offset（1,0）
Cells（i,”A”）.Resize（1,7）.copyrng
i=i+1
bj=cells（I,”C”）.Value
loop
（4）将工作表保存为新文件薄
Application.screenUpdating=False
Dimfolderasstring
Folder=thisworkbook.path&“\班级成绩表”
IfLen（dir（folder,vbdirectory））=0ThenMKDirfolder
Dimshtasworksheet
Foreachshtinworksheets
Sht.Copy
Activeworkbook.saveasfolder&“\”&sht.name&“.xls”
Activeworkbook.close
Next
Application.ScreenUpdating=true
（5）把各个班级的成绩表汇总到一张工作表中
Rows（"2:
65536”）.Clear‘删除所有记录
Dimshtasworksheet,xrowasinteger,rngasrange
Foreachshtinworksheets
Ifsht.name<>activesheet.namethen
Setrng=range（“A65536”）.End（xlup）.offset（1,0）
Xrow=sht.range（“A1”）.Currentregion.rows.count-1
Sht.range（“A2”）.resize（xrow,7）.copyrng
Endif
Next
（6）将文件夹下的多工作薄数据汇总到另一个工作薄的同一张工作表里。
Dimraslong,caslong
r=1
c=8
range（cells（r+1）,”A”）,cells（65536,c））.clearContents
application.screenupdating=False
dimfilenameasstring,wbasworkbook,shtasworksheet,erowaslong,_
fnasstring,arrasvariant
filename=Dir（Thisworkbook.path&“\*.xls”）
dowhilefilename<>””
iffilename<>thisworkbook.namethen
erow=range（“A1”）.currentregion.rows.count+1
fn=thisworkbook.path&“\”&filename
setwb=getobject（fn）
setsht=wb.worksheets
（1）‘汇总的是第一张工作表
‘将数组arr中的记录保存在arr数组里
arr=sht.range（sht.cells（r+1,“A”））,sht.cells（65536,”B”）.end（xlup）.offset（0,
升级会员 