ExcelVBA参考大全 读书笔记ver.docx
- 文档编号:9754127
- 上传时间:2023-02-06
- 格式:DOCX
- 页数:35
- 大小:75.33KB
ExcelVBA参考大全 读书笔记ver.docx
《ExcelVBA参考大全 读书笔记ver.docx》由会员分享,可在线阅读,更多相关《ExcelVBA参考大全 读书笔记ver.docx(35页珍藏版)》请在冰豆网上搜索。
ExcelVBA参考大全读书笔记ver
作者:
郭栋
修改时间:
2011-4-19
版本:
ver1.0
说明:
创建版本
VBA是一种面向对象的程序设计语言,与VisualBasic程序设计语言在结构化和处理对象方面相同。
VBA(VisualBasicforApplication)。
对象模型是可以在应用程序中找到的所有对象的层次结构。
例如,Excel对象模型部分,有一个Application对象,Application对象包含Workbook对象,而Workbook对象包含Worksheet对象,Worksheet对象又包含Range对象。
模块事实上只是一个带有一些特定特性的字处理文档,可用于帮助编写和测试代码。
第一章:
ExcelVBA基础
1录制宏:
通过宏录制器可以生成子过程。
在VBA中,宏被称为过程。
有两种类型的过程:
子过程和函数过程。
2用户自定义函数
FunctionFahrenheit(Centigrade)
Fahrenheit=Centigrade*9/5+32
EndFuntion
3Excel对象模型
集合
Excel自身是一个对象,称为Application对象。
在ExcelApplication对象里,有一个Workbooks集合,包含所有当前打开的Workbook对象。
每个Workbook对象有一个Worksheets集合,包含在该工作薄里的Worksheet对象。
属性
属性是对象的自然特征,可以被度量或量化。
例如:
每个人都有一个身高属性一个年龄属性、一个性别属性、一个名字属性等。
方法
方法是对象或者在对象中可以执行的动作。
事件
对象可以响应事件。
如果希望某个对象响应事件,需要在该对象相应的时间过程里输入VBA代码。
4VBA语言
基本的输入输出
MsgBox输出:
MsgBox(prompt[,buttons][,title][,helpfile,context])
通过位置指定参数:
MsgBox“打印吗?
”,,“警告!
”
通过名称指定参数:
MsgBoxTitle:
=”警告!
”,Prompt:
=”打印吗?
”,Buttons:
=36
指定参数时使用常量:
MsgBoxPrompt:
=”打印吗?
”,Title:
=”警告!
”,Buttons:
=vbYesNo+vbQuestion
返回值:
Answer=MsgBox(Prompt:
=”打印吗?
”,Buttons:
=vbYesNo+vbQuestion)
注意:
如果希望获取函数的返回值,需要在括号里放置所有的参数。
如果无需使用返回值,则不应该使用括号。
此规则也应用于对象的方法。
InputBox输入:
UserName=InputBox(Prompt:
=“请输入您的名字:
”)
调用函数和子过程
OptionExplicit
'Start——函数、子过程调用
SubMaster()
DimSalesDataAsString
SalesData=GetInput(Message:
="输入销售数据")
IfSalesData=FalseThenExitSub
PostInputInputData:
=SalesData,Target:
="B3"
EndSub
FunctionGetInput(MessageAsString)AsString
DimDataAsString
Data=InputBox(Message)
IfData=""ThenGetInput=FalseElseGetInput=Data
EndFunction
SubPostInput(InputDataAsString,TargetAsString)
Range(Target).Value=InputData
EndSub
'End——函数、子过程调用
括号和参数列表
不使用Call语句:
A.仅当调用函数过程并使用该函数过程的返回值时,将参数放置在括号内;
B.当调用函数过程但不使用该函数过程的返回值时,不需要在参数周围放置括号
C.当调用子过程时,不在参数周围放置括号
关于括号,重要而细微的区别
A.MsgBox(“插入磁盘”)‘MsgBox与(“插入磁盘”)之间有一个空格
Response=MsgBox(“插入磁盘”)‘MsgBox与(“插入磁盘”)之间无空格
注意:
在MsgBox与(“插入磁盘”)之间插入一个空格额外的空格表明括号里面是参数而非参数列表。
使用Call语句:
A.如果使用Call语句,则必须在传递给被调用过程的参数两边放置括号
变量声明
注意:
OptionExplicit仅应用于其出现处的模块。
需要强制变量声明的每个模块必须在其声明部分重复该语句
变量的作用域和生存期
变量的作用域定义了哪些过程可以使用该变量。
(在VBA中,宏被称为过程。
有两种类型的过程:
子过程和函数过程。
)
变量的生存期定义了变量保存所赋的值多长时间。
变量类型
声明变量类型
DimSalesDataAsDouble,IndexAsInteger,StartDateAsDate
声明函数和参数类型
FunctionIsHoliday(WhichDayAsDate)AsBoolean
SubMarine(CrewSizeAsInteger,FuelCapacityAsDouble)
常量
ConstVersionAsString=“Release3.9a”
对象变量
创建对象变量引用对象
A.Set语句用于将一个对象引用赋值给一个对象变量。
'Start——对象变量
SubObjectVariable()
DimrngAsRange
Setrng=ThisWorkbook.Worksheets("Sheet1").Range("C10")
rng.Value=InputBox("输入一月的销售量")
rng.Offset(-1,0).Value="一月销售量"
EndSub
'End——对象变量
做出判断
If语句
If语句提供三种形式:
IIf函数、单行的If语句以及If结构。
A.IIf函数
FunctiondTax(dProfitBeforeTaxAsDouble)AsDouble
dTax=IIf(dProfitBefore>0,0.3*dProfitBeforeTax,0)
EndFunction
B.单行的If语句
FunctiondTax2(dProfitBeforTaxAsDouble)AsDouble
IfdProfitBeforTax>0ThendTax2=0.3*dProfitBeforeTaxElsedTax2=0
EndFunction
C.If结构
FunctiondTax3(dProfitBeforTaxAsDouble)AsDouble
IfdProfitBeforTax>0Then
dTax3=0.3*dProfitBeforeTax
Else
dTax3=0
EndIf
EndFunction
SelectCase语句
FunctionvPrice1(sProductAsstirng)AsVariant
SelectCasesProduct
Case"苹果"
vPrice1=12.5
Case"桔子"
vPrice1=15
Case"梨子"
vPrice1=18
CaseElse
vPrice1=CVErr(xlErrNA)
EndSelect
EndFunction
FunctionvPrice2(sProductAsstirng)AsVariant
SelectCasesProduct
Case"苹果":
vPrice2=12.5
Case"桔子":
vPrice2=15
Case"梨子":
vPrice2=18
CaseElse:
vPrice2=CVErr(xlErrNA)
EndSelect
FunctionvFare(iAgeAsInteger)AsVariant
SelectCaseiAge
Case0To3,Is>65
vFare=0
Case4To15
vFare=10
Case16To65
vFare=20
CaseElse
vFare=CVErr(xlErrNA)
EndSelect
EndFunction
循环
Do…Loop
例子省略
For…Next
例子省略
数组
数组
A.数组是可以包含一个以上数据项的VBA变量,通过在名称后面包含括号声明数组。
在括号里放置整数,定义数组中元素的个数。
B.数组的定义
DimavData
(2)AsInteger‘一共有avData(0),avData
(1),avData
(2)三个元素
DimavData(1To2)AsInteger‘一共有avData
(1),avData
(2)两个元素
C.数组元素赋值
avData(0)=1
avData
(1)=10
avData
(2)=100
DimavDataAsVariant
avData=Array("North","South","East","West")
D.数组函数
LBound,UBound
SubArray1()
DimaiData(10)AsInteger
DimsMessageAsString,iAsInteger
Fori=LBound(aiData)ToUBound(aiData)
aiData(i)=i
Nexti
sMessage="ÏÂÏÞ="&LBound(aiData)&vbCr
sMessage=sMessage&"ÉÏÏÞ="&UBound(aiData)&vbCr
sMessage=sMessage&"ÔªËØÊý="&WorksheetFunction.Count(aiData)&vbCr
sMessage=sMessage&"ÔªËغÍ="&WorksheetFunction.Sum(aiData)
MsgBoxsMessage
EndSub
多维数组
A.多维数组定义
DimavData(10,20)AsVariant
DimavData(1To10,1To20)
B.多维数组函数
DimavDataAsVariant
LBound(aiData,1)ToUBound(aiData,2)
动态数组
A.通过忽略数组维数声明一个动态数组
DimasData()AsString
B.可以在运行时使用ReDim语句声明所需大小,因而可以使用变量定义索引值的范围:
ReDimavData(iRows,iColumns)
ReDimavData(iminRowToiMaxRow,iminColToimaxCol)
运行时错误处理
捕获错误
OnErrorGotoLineLabel
获取错误信息
Err对象可以获取到错误信息。
Err对象的Number属性返回错误号,Description
属性返回相关的报错信息
忽略错误
Resume语句表现为三种形式:
A.Resume执行导致错误的语句
B.ResumeNext返回导致错误语句的下一条语句并执行该语句,从而跳过了有问题的语句;
C.ResumeLineLabel跳转到代码中指定的行标签处,选择从何处恢复执行
恢复错误处理
OnErrorGoTo0
第二章:
Application对象
1全局:
Application对象是全局对象,它的许多属性和方法也是全局的成员。
2Active属性:
下面的Application属性是全局的属性,允许引用活动的对象:
ActiveCell、ActiveChart、ActivePrinter、ActiveSheet、ActiveWindow、ActiveWorkbook、Selection。
3显示警告:
设置DisplayAlerts属性为False,可以屏蔽掉大多数警告。
当屏蔽一个警告对话框时,自动执行该对话框中默认的按钮相关联的操作。
例如:
Application.DisplayAlerts=False
ActiveSheet.Delete
Application.DisplayAlerts=True
4屏幕刷新:
Application.ScreenUpdating=False
5Application的Evaluate方法:
将一个MicrosoftExcel名称转换为一个对象或者一个值。
语法
表达式.Evaluate(Name)
表达式 一个代表Application对象的变量。
参数
名称
必选/可选
数据类型
描述
Name
必选
Variant
使用MicrosoftExcel命名约定的对象名称。
返回值
Variant
说明
该方法可使用下列MicrosoftExcel名称类型:
A1格式引用。
可以通过A1格式表示法引用单个单元格。
所有引用均视为绝对引用。
区域。
在引用中可以使用区域、交集和联合运算符(分别为冒号、空格和逗号)。
定义的名称。
可用宏语言指定任何名称。
外部引用。
可以使用!
运算符引用另一工作簿中的单元格或已定义的名称,例如,Evaluate("[BOOK1.XLS]Sheet1!
A1")。
图表对象。
可以指定任何图表对象名称(如“Legend”、“PlotArea”或“Series1”),以访问该对象的属性和方法。
例如,Charts("Chart1").Evaluate("Legend").Font.Name返回图例中所用字体的名称。
6InputBox:
Setrng=Application.InputBox(prompt:
="请输入单元格区域",Type:
=8)
7状态栏:
允许为StatusBar属性赋一个文本字符串,并将该字符串显示在Excel屏幕底部状态栏的左侧。
SubShowMessage()
DimlCounterAsLong
ForlCounter=0To100000000
IflCounterMod1000000=0Then
Application.StatusBar="ProcessingRecord"&lCounter
EndIf
NextlCounter
Application.StatusBar=False
EndSub
注意:
在过程的结尾,必须将StatusBar属性值设置为False,返回状态栏默认操作。
否则,最后的消息将会一直停留在状态栏上。
8SendKeys:
SendKeys允许发送按键到当前活动窗口,用来控制不支持任何其他交互形式的应用程序,例如DDE(DynamicDataExchange)或OLE。
9OnTime:
使用OnTime方法安排在将来某个时刻运行宏,需要指定该宏运行的日期和时间以及宏的名称。
如果使用Application对象的Wait方法暂停某宏,所有的Excel行为,包括手工交互操作,都将挂起。
使用OnTime的优势在于,当等待运行预设的宏时,允许返回正常的Excel交互操作,包括运行其他的宏。
10OnKey:
使用OnKey方法将一个宏过程赋给单个按键或任意组合键。
也可以使用该方法禁用组合键。
SubAssignDown()
Application.OnKey"{Down}","DownTen"
EndSub
SubDownTen()
ActiveCell.Offset(10,0).Select
EndSub
11工作表函数:
在Excel中可以使用两种内置函数,一组函数是VBA语言的组成部分,另一组函数是Excel工作表函数的子集(WorksheetFunction对象用作可从VisualBasic中调用的MicrosoftExcel工作表函数的容器。
)。
一般情况下,如果一个VBA函数与一个Excel函数有着相同的用途,那么该Excel函数就不能直接用于VBA宏(但可以使用Evaluate方法访问任何Excel函数)。
12Caller:
Application对象的Caller属性返回调用或执行宏过程的对象的引用,
SelectCaseTypeName(Application.Caller)
Case"Range"
v=Application.Caller.Address
Case"String"
v=Application.Caller
Case"Error"
v="Error"
CaseElse
v="unknown"
EndSelect
MsgBox"caller="&v
第三章:
工作薄和工作表
1Workbooks集合:
Workbooks集合由当前所有在内存里打开的Workbook对象组成。
添加Workbook对象
向Workbooks集合中添加对象的方式有很多种,可以基于Workbook对象的默认属性创建新的空工作薄,或基于模板文件创建新工作薄,还可以打开一个现有的工作薄文件。
A.基于默认的工作薄创建新的空工作薄,使用Workbooks集合的Add方法
B.Add方法允许为新工作薄指定模板
C.用Open方法可以向Workbooks集合中添加现有的工作薄文件。
从路径中获取文件名
当在VBA中处理工作薄时,经常需要指定目录路径和文件名称。
某些任务只需要知道路径,例如已设置了默认的目录。
某些任务只需要知道文件名称,例如希望激活某个已打开的工作薄。
而另一些任务中,既需要路径也需要文件名,例如希望打开已存在但不在活动目录中的工作薄文件。
Setwkb=Workbooks.Open(FileName:
=”D:
\Project\VBAStudy\Capture3\test.xlsx”)
MsgBoxwkb.Name‘返回test.xlsx
MsgBoxwkb.Path‘返回D:
\Project\VBAStudy\Capture3\
MsgBoxwkb.FullName‘返回D:
\Project\VBAStudy\Capture3\test.xlsx
在相同目录中的文件
ThisWorkbook是对包含该代码的工作薄的引用。
无论该工作薄位于哪儿,ThisWorkbook的Path属性都将提供必需的路径以定位相关的文件。
覆盖现有的工作薄
FunctionbFileIsExists(sFileAsString)AsBoolean
IfDir(sFile)<>""ThenbFileIsExists=True
EndFunction
SubCreateNextFileName()
DimwkbAsWorkbook
DimiAsInteger
DimsFNameAsString
Setwkb=Workbooks.Add(Template:
="D:
\Project\VBAStudy\Capture3\Test.xlsx")
i=0
Do
i=i+1
sFName="D:
\Project\VBAStudy\Capture3\Test"&i&".xlsx"
LoopWhilebFileIsExists(sFName)
Application.DisplayAlerts=False
wkb.SaveAsFilename:
=sFName
Application.DisplayAlerts=True
EndSub
保存改变
SubCloseWorkbook()
DimwkbAsWorkbook
Setwkb=Workbooks.Open(Filename:
="D:
\Project\VBAStudy\Capture3\Test.xlsx")
Range("A1").Value=Format(Date,"dddmmmdd,yyyy")
Range("A1").EntireColumn.AutoFit
wkb.CloseSaveChanges:
=True
EndSub
2Sheets集合:
在Workbook对象里,有一个Sheets集合,其成员是Worksheet对象或Chart对象。
Worksheet对象与Chart对象也分别属于他们自己的集合——Worksheets集合和Charts集合。
Charts集合仅包括图表工作表。
嵌入在工作表中的图表不是Charts集合的成员,而是包含在ChartObject对象中,是工作表的ChartObjects集合中的成员。
工作表(Worksheet)
可通过在Sheets集合和Worksheets集合里的名称或索引值引用工作表。
如果已知要处理的工作表的名称,可以通过名称在Worksheets集合里指定工作表。
注意:
Worksheet对象的Index属性返回的是Sheets集合中的索引值,不是Worksheets集合中的索引值。
(Sheets集合中的索引值和Worksheets集合中的索引值是不同的。
因为,Sheets集合中也包括Chart对象即图表工作表)
工作表的复制和移动
Worksheet对象的Copy方法和Move方法允许每次复制或移动一个或者多个工作表。
他们都提供了两个可选参数,允许指定该操作的目的位置,即某指定的工作表之前或之后。
如果不使用任何参数,那么将复制或者移动工作表到一个新工作薄中。
注意:
Copy方法和Move方法不返回任何值或引用
组合工作表
在VBA中,可以使用Worksheets集合的Select方法并联合Array函数来组合工作表。
此外,也可以使用Worksheet对象的Select方法创建工作表组。
按正常的方式选择第1个工作表,通过使用Select方法并将其Replace参数设置为False将其他工作表添加到组中。
3Windows对象:
Window对象
代表窗口。
说明
许多工作表特征(如滚动条和标尺)实际上是窗口的属性。
Window对象是Windows集合的成员。
Application
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ExcelVBA参考大全 读书笔记ver ExcelVBA 参考 大全 读书笔记 ver