Excel高级应用Word下载.docx
《Excel高级应用Word下载.docx》由会员分享，可在线阅读，更多相关《Excel高级应用Word下载.docx（25页珍藏版）》请在冰点文库上搜索。
当前文档、所有文档（normal.dot）。
normal.dot中的宏在别的文档中也可以运行。
1.5EXCEL中将宏指定给按钮或图片
VBA主要的目标是为自动化提供一个易于操作的界面。
“按钮”是最常见的界面组成元素之一。
通过使用“窗体”工具栏，可以为工作簿中的工作表添加按钮。
在创建完一个按钮后，可以为它指定宏，然后就可以通过单击按钮来执行宏了。
此后，单击按钮就可以运行该宏。
在按钮上右击鼠标，可改变大小或标题。
指定宏到图片十分简单，用“插入|图片”菜单或其他方法在当前工作表放置图片后，右击图片，在快捷菜单中选“指定宏”即可进行设置。
1.6将宏指定给工具栏按钮
将宏指定给“工具栏按钮”，可按如下步骤进行：
①在Excel中，选择“工具|自定义”菜单，显示“自定义”对话框。
②在“命令”卡中，从“类别”列表框中选择“宏”，从“命令”列表框中将“自定义按钮”拖动到任意一个工具栏上。
③左击该按钮，选择“指定宏”，显示“指定宏”对话框。
④选择需要的宏名并单击“确定”按钮。
⑤单击“关闭”按钮，关闭“自定义”对话框。
2控件与用户窗体
2.1控件（EXCEL）
在Excel工具栏上单击鼠标右键，从快捷菜单中选择“窗体”，显示出“窗体”工具栏，其中有16个控件，9个可放到工作表上。
1．标签：
用于表现静态文本。
2．分组框：
用于组合其它控件。
3．按钮：
用于执行宏命令。
4．复选框：
它是一个选择控件，通过单击可以选择和取消选择，可以多项选择。
5．选项按钮：
通常几个选项按钮组合在一起使用，在一组中只能选择一个选项按钮。
6．列表框：
用于显示多个选项并从中选择。
7．组合框：
可以选择其中的项目或者输入一个其它值。
8．滚动条：
是一种选择控制机制。
包括水平滚动条和垂直滚动条。
9．微调控件：
是一种数值选择机制，通过单击控件的箭头来选择数值。
要将控件添加到工作表上，可以在“窗体”工具栏中单击需要的控件，此时鼠标变成十字形状，在当前工作表的适当位置按下鼠标左键并拖动鼠标画出一个矩形，这个矩形代表了控件的大小，对大小满意后放开鼠标左键，这样一个控件就添加到工作表上了。
2.2用户窗体
用户窗体。
用户窗体可以作为程序的对话框和窗口。
向用户窗体添加控件类似于向工作表添加控件。
在VBA编辑器中出现一个名为“UserForm1”的窗体,“控件工具箱”同时出现，在其中有许多已经熟悉的控件，另外还有一些新的控件。
这些新的控件是：
1．切换按钮：
该控件如果被选中，那么会保持被按下的状态。
如果再次单击它就恢复为没有按下的状态。
2．选项卡条（TabStrip）：
它是包含多个选项卡的控件。
通常用来对相关的信息进行组织或分类。
3．多页：
外观类似选项卡条，是包含一页或多页的控件。
选项卡条给人相似的外观，而多页控件的各页包含各自不同的控件，有各自不同的布局。
如果每一页具有相同布局,则应选择选项卡条，否则应该选择多页。
4．图像控件：
它允许向窗体上放置图片。
5．RefEdit：
它外观象文本框，通过这个控件可以将用户窗体折叠起来，以便选择单元格区域。
3对象、属性、方法和事件
同其它任何面向对象的编程语言一样，VBA里也有对象、属性、方法和事件。
所谓对象，就是代码和数据的组合。
如表、窗体或文本框等都是对象。
所谓属性，是指对象的特性，像大小、颜色、状态等。
所谓方法指的是对象能执行的动作，例如，清除活动单元格的内容。
事件是一个对象可以辨认的动作，并且可以写某些代码针对此动作来做响应。
例如Worksheet的selectionchange事件和change事件。
Selectionchange事件是在选区发生变化时出发，target返回选择的区域的值。
change事件是在单元格内容发生变化的时候触发，target返回变化的内容。
示例：
自动签到，自动求和。
自动签到代码：
PrivateSubWorksheet_SelectionChange（ByValTargetAsRange）
straddr=Target.Address
col_s=Mid（straddr,2,1）
row_n=Mid（straddr,4）
Ifcol_s="
B"
Androw_n>
1Then
Target.Value=Format（Now,"
yyyy-mm-ddhh:
mm:
ss"
）
EndIf
EndSub
Mid（string,start[,length]）
Mid函数的语法具有下面的命名参数：
说明
string必要参数。
字符串表达式，从中返回字符。
如果string包含Null，将返回Null。
start必要参数。
为Long。
string中被取出部分的字符的起始位置。
如果start超过string的字符数，Mid返回零长度字符串（"
"
）。
length可选参数；
为Variant（Long）。
要返回的字符数。
如果省略或length超过文本的字符数（包括start处的字符），将返回字符串中从start到尾端的所有字符。
Format（expression[,format[,firstdayofweek[,firstweekofyear]]]）
Format函数的语法具有下面几个部分：
部分说明
expression必要参数。
任何有效的表达式。
format可选参数。
有效的命名表达式或用户自定义格式表达式。
（#代表任意的多个字符，0代表任意的一个字符）。
firstdayofweek可选参数。
常数，表示一星期的第一天。
firstweekofyear可选参数。
常数，表示一年的第一周。
自动求和：
在A列和B列输入数值，在C列自动显示和。
代码如下：
PrivateSubWorksheet_Change（ByValTargetAsRange）
DimaddrAsString,col_letterAsString,row_noAsString
Dimsum_addrAsString,A_addrAsString,B_addrAsString,A_valAsString
DimB_valAsString
addr=Target.Address'
求当前单元格地址
col_letter=Mid（addr,2,1）'
求地址中列标
row_no=Mid（addr,4）'
求地址中行号
Ifcol_letter="
A"
Orcol_letter="
Then
sum_addr="
C"
&
row_no
A_val=Target.Value
B_addr="
B_val=Range（B_addr）.Value
Else
B_val=Target.Value
A_addr="
A_val=Range（A_addr）.Value
Range（sum_addr）.Value="
="
Str（Val（A_val））&
"
+"
Str（Val（B_val））'
针对0的情况，需要将A_val和B_val先转换成数值类型再转换成字符串类型
‘str函数转换过来的数值前面虽然有空格，但可以正确进行运算
Str（number）
必要的number参数为任何有效的数值表达式。
当一数字转成字符串时，总会在前头保留一空位来表示正负。
如果number为正，返回的字符串包含一前导空格暗示有一正号。
Val（string）
必要的string参数可以是任何有效的字符串表达式。
4VBA语法基础
4.1注释
好的程序一般都有注释，这对程序的维护有很大的好处。
在VBA程序中，注释可以通过以下两种方式实现：
（1）使用Rem语句。
（2）用单引号（'
）号。
4.3变量和常量
4.3.1变量
变量用于临时保存数据。
程序运行时，变量的数据可以改变。
在VBA代码中可声明和使用变量来临时存储数据或对象。
DimMyNameAsString'
声明一个名为MyName的字符串变量
MyName="
北京"
'
给变量赋值
上海"
改变量的值
4.3.2变量的数据类型
表4.1数据类型
数据类型
存储空间
数值范围
Integer（整型）
2字节
-32768～32767
Long（长整型）
4字节
-2147483648～2147483647
String（字符串）
字符串的长度
变长字符串：
0～20亿个字符
定长字符串：
1～64K个字符
4.4运算符
VBA中的运算符有四种：
算术运算符、比较运算符、逻辑运算符和连接运算符。
用来组成不同类型的表达式。
4.4.1算术运算符
VBA算术运算符有7个，他们用于构建数值表达式或返回数值运算结果，各运算符的作用和示例见表4.2
4.4.2比较运算符
比较运算符用于构建关系表达式，返回逻辑值True、False或Null（空）。
4.4.3逻辑运算符
逻辑运算符用于构建逻辑表达式，返回逻辑值True、False或Null（空）。
4.4.4连接运算符
字符串连接运算符有两个：
“&
”和“+”。
其中“+”运算符既可用来计算数值的和，也可以用来做字符串的串接操作。
不过，最好还是使用“&
”运算符来做字符串的串接操作。
4.4.5运算符的优先级
按优先级由高到低的次序排列的运算符如下：
括号→指数→一元减→乘法和除法→整除→取模→加法和减法→连接→比较→逻辑（And、Or、Not、Xor、Eqv、Imp）。
5过程
5.1模块与过程
每个VBA应用程序都存在于一个“工程”中。
工程下面可分为若干个“模块”。
模块包括标准模块、类模块和对象模块三种。
模块中可以定义若干个“过程”。
过程中包含一系列用于执行某个任务的语句。
每个过程都有唯一的名字。
过程可以是函数，也可以是子程序。
函数执行一系列操作后返回一个函数值。
子程序只执行一个或多个操作，而不返回数值。
当录制完宏查看代码时，所看到的就是子程序。
宏只能录制子程序，而不能录制函数过程。
函数过程通常情况下称为函数，要返回一个数值。
这个数值通常是计算的结果或是测试的结果，例如False或True。
可以在模块中创建和使用自定义函数。
5.2过程的创建与运行
进入Excel或打开一个工作簿，系统自动建立一个工程，工程中自动包含ThisWorkbook、Sheet1等对象模块。
过程可以在对象模块中建立，也可以在标准模块或类模块中建立。
如果模块不存在，首先需要向工程中添加一个模块。
工作簿中的模块与过程随工作簿一起保存。
在VBA编辑器或工作簿窗口都可以通过“文件”菜单保存工作簿。
5.3子程序
子程序以Sub开头，EndSub结尾。
常用语法格式如下：
[Public|Private]Sub子程序名（[<
参数>
]）
[<
语句组>
]
[ExitSub]
EndSub
Public关键字可以使子程序在所有模块中有效。
Private关键字使子程序只在本模块中有效。
如果没有显式指定，缺省情况是公用的。
子程序可以带参数。
ExitSub语句的作用是退出子程序。
5.4自定义函数
在VBA中，提供了大量的内置函数。
比如字符串函数Mid（）、统计函数Max（）等。
在编程时直接引用就可以了。
但有时需要按自己的要求定制函数，比如计算半径为R的圆的面积
A=3.14*R^2
我们不可能为每一个不同半径的圆来写上相似的一段代码，而应该使用函数。
用Function语句可以定义函数，常用的语法形式如下：
[Public|Private]Function函数名（[<
]）[As数据类型]
[函数名=<
表达式>
[ExitFunction]
EndFunction
定义函数时用Public关键字，则所有模块都可以调用它。
用Private关键字，函数只用于同一模块。
如果没有显式指定，则缺省为公用。
函数名末尾可使用As子句来声明返回值的数据类型，参数也可指定数据类型。
若省略数据类型说明，系统会自动根据赋值确定。
ExitFunction语句的作用是退出Function过程。
例求圆的面积，代码如下：
PublicFunctionmianji（rAsSingle）AsSingle
mianji=3.14*r^2
EndFunction
6流程控制语句
6.1If语句
If语句是我们最常用的一种分支语句。
它符合人们通常的语言习惯和思维习惯。
If语句有三种语法形式。
①if<
条件>
then<
语句1>
[else<
语句2>
②if<
then
<
语句组1>
[else
语句组2>
endif
③if<
条件1>
[elseif<
条件2>
…
else
语句组n>
是一个关系表达式或逻辑表达式。
若值为真，则执行紧接在关键字then后面的语句组。
若<
的值为假，则检测下一个elseif<
或执行else关键字后面的语句组，然后继续执行下一个语句。
6.2SelectCase语句
如果条件复杂，程序需要多个分支，用If语句就会显得相当累赘，而且程序变得不易阅读。
这时我们可以使用SelectCase语句来写出结构清晰的程序。
SelectCase语法如下：
SelectCase<
检验表达式>
Case<
比较列表1>
[CaseElse
EndSelect
其中的<
是任何数值或字符串表达式。
比较元素>
可以是下列几种形式之一：
①表达式
②表达式To表达式
③Is<
比较操作符>
表达式
说明：
如果<
与Case子句中的一个<
相匹配，则执行该子句后面的语句组。
若含有To关键字，则第一个表达式必须小于第二个表达式，<
值介于两个表达式之间为匹配。
若含有Is关键字，Is代表<
构成的关系表达式的值为真则匹配。
6.3For...Next语句
For...Next是一个循环语句，其语法形式如下：
For循环变量=初值To终值[Step步长]
[ExitFor]
Next[循环变量]
该循环语句执行时，首先把循环变量的值设为初值，如果循环变量的值没有超过终值，则执行循环体，遇到Next，把步长加到循环变量上，若没有超过终值，再循环，直至循环变量的值超过终止时，才结束循环，继续执行后面的语句。
步长可正、可负，为1时可以省略。
遇到ExitFor时，退出循环。
6.4Do...Loop语句
6.5While…Wend语句
While循环条件
[语句组]
Wend
6.6With语句
在引用对象的时候，用With可以简化代码中对复杂对象的引用。
可以用With语句建立一个“基本”对象，然后进一步引用这个对象上的对象、属性或方法，直至终止With语句。
其语法形式如下：
With<
对象引用>
EndWith
7综合实例：
教学工作量统计模板
两个功能：
自动计算：
输入数据自动产生“理论课总学时”列和“实验课总学时”和“教师总学时”和“课时费列”的数据。
排序求和：
点击“排序求和”按钮，将数据以“职称”为第一关键字“姓名”为第二关键字进行排序，并对各职称教师的工作量及教师总工作量进行求和显示在最后一行中。
清除汇总：
点击“清除汇总”按钮，将汇总信息删除。
如图所示：
图1排序求和之前的表格数据
图2点击“排序求和”之后的表格数据
点击“清除汇总”后，回到图1所示的状态。
自动计算
两种思路:
1）将总学时，课时费等单元格中预先输入公式，在用户输入数据后，这些单元格中的内容随着自动变化，但是，在未输入用户数据之前I列等单元格会显示“*=”等内容，J列等单元格会显示0。
所以，先将有公式的单元格的字体颜色设为白色，在用户输入数据时，再将字体颜色设为蓝色。
2）在用户输入数据时，在总学时和课时费单元格中输入公式，得出结果，在worksheet的change事件中编程。
思路1
设置格式输入公式只需对表格内容的第一行进行操作，然后录制自动填充的宏，再将代码中的区域值改变，就可以改变后面行的设置（本例做200行的表）。
模块4中“填充公式”宏
录制宏，做如下操作：
选中I5单元格，输入公式，将字体颜色变成白色，拖动I5到I10（可以随便拖动到大于5的某一行）。
进入VisualBasic编辑界面，对代码做简单修改。
Sheet2中编程：
在worksheet的change事件中编程。
IfInStr（addr,"
:
）<
>
0ThenExitSub'
如果改变的是区域的内容，退出过程,在删除一些数据的时候，会出现这种情况
G"
H"
theory_week_no_addr="
theory_week_no_val=Range（theory_week_no_addr）.Value
theory_week_hour_addr="
theory_week_hour_val=Range（theory_week_hour_addr）.Value
theory_sum_hour_express_addr="
I"
theory_sum_hour_value_addr="
J"
IfLen（theory_week_no_val）<
0AndLen（theory_week_hour_val）<
0Then
Range（theory_sum_hour_express_addr）.Select
Selection.Font.ColorIndex=5
Range（theory_sum_hour_value_addr）.Select
Selection.Font.ColorIndex=5
Selection.Font.ColorIndex=2
Selection.Font.ColorIndex=2
程序中用到的函数如下：
InStr函数
返回Long类型的数值，指定一字符串在另一字符串中最先出现的位置。
语法InStr（[start,]string1,string2[,compare]）
string1必要参数。
接受搜索的字符串表达式。
string2必要参数。
被搜索的字符串表达式。
Len函数
返回Long，其中包含字符串内字符的数目，或是存储一变量所需的字节数。
语法Len（string|varname）
string任何有效的字符串表达式。
Varname任何有效的变量名称。
Font对象
包含对象的字体属性（字体名称、字体大小、字体颜色等）。
ColorIndex属性
该属性可应用于多个对象，例如border、font等。
应用于font对象时，返回或设置字体的颜色。
该颜色可指定为当前调色板中颜色的编号，或下列XlColorIndex常量之一。
Variant类型，可读写。
XlColorIndex可为常量xlColorIndexAutomatic（自动设置颜色）。
语法：
对象.ColorIndex
思路2
sheet1中编程
DimaddrAsString,col_letterAsString,row_noAsS
升级会员 