Excel高级应用.docx

Excel高级应用.docx

《Excel高级应用.docx》由会员分享，可在线阅读，更多相关《Excel高级应用.docx（25页珍藏版）》请在冰豆网上搜索。

Excel高级应用

EXCEL高级应用

1宏

宏（Macro）是一组VBA语句。

可以理解为一个程序段，或一个子程序。

在OfficeXP中，宏可以直接编写，也可以通过录制形成。

录制宏，实际上就是将一系列操作过程记录下来并由系统自动转换为VBA语句。

这是目前最简单的编程方法，也是VBA最有特色的地方。

1.1宏的录制与执行

下面录制一个非常简单的宏，它的功能是将当前选中的单元格背景置成蓝色。

如下步骤：

①进入Excel。

②在“工具|宏”菜单中选“录制新宏”项。

③在“录制新宏”对话框中输入宏名“填充颜色”，单击“确定”按钮。

此时，屏幕上显示出“停止录制”工具栏。

④在“格式”工具栏中单击“填充颜色”按钮右边的三角标志，选择蓝色。

⑤单击“停止录制”工具栏按钮，结束宏录制过程。

也可以选择“工具|宏|停止录制”菜单结束宏录制。

录制完一个宏后就可以执行它了。

1.2宏的编辑

对已经存在的宏，我们可以查看代码，也可以进行编辑修改。

选择“工具|宏|宏”菜单，在“宏”对话框中选择列表中的“填充颜色”,单击“编辑”按钮。

可以在VBA的编辑器窗口修改宏。

编辑宏同样非常简单。

同时我们知道，通过编辑宏可以删除多余的语句，提高运行速度，也可以加入判断或循环等无法录制的语句，增加宏的功能。

许多过程可以用录制宏来完成。

但录制的宏无判断或循环能力，人机交互能力差，即用户无法进行输入，计算机无法给出提示，无法显示对话框，无法显示自定义窗体。

因此，需要对录制的宏进行编辑修改。

1.3为宏指定快捷键

快捷键即快速执行某项操作的组合键。

例如：

CTRL+C在许多程序中代表“复制”命令。

当给宏指定了快捷键后，就可以用快捷键来执行宏，而不必通过“工具”菜单。

可以在创建宏时指定快捷键，也可以在创建后再指定。

注意：

当包含宏的工作簿打开时，为宏指定快捷键会覆盖原有快捷键的功能。

因此，在定义新的快捷键时，尽量避开系统已定义的常用快捷键。

1.4指定宏保存的位置

EXCEL中宏可保存在三种位置：

当前工作簿、新工作簿和个人宏工作簿。

将宏保存在当前工作簿或新工作簿，只有该工作簿打开时，相应的宏才可以用。

个人宏工作簿是为宏而设计的一种特殊的具有自动隐藏特性的工作簿。

第一次将宏创建到个人宏工作簿时，会创建名为“PERSONAL.XLS"的新文件。

如果该文件存在，则每当EXCEL启动时会自动将此文件打开并隐藏在活动工作簿后面。

在“窗口”菜单中选择“取消隐藏”后，可以发现它的存在。

WORD中宏可保存在两种位置：

当前文档、所有文档（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="B"Then

sum_addr="C"&row_no

Ifcol_letter="A"Then

A_val=Target.Value

B_addr="B"&row_no

B_val=Range（B_addr）.Value

Else

B_val=Target.Value

A_addr="A"&row_no

A_val=Range（A_addr）.Value

EndIf

Range（sum_addr）.Value="="&Str（Val（A_val））&"+"&Str（Val（B_val））'针对0的情况，需要将A_val和B_val先转换成数值类型再转换成字符串类型

‘str函数转换过来的数值前面虽然有空格，但可以正确进行运算

EndIf

EndSub

Str（number）

必要的number参数为任何有效的数值表达式。

说明

当一数字转成字符串时，总会在前头保留一空位来表示正负。

如果number为正，返回的字符串包含一前导空格暗示有一正号。

Val（string）

必要的string参数可以是任何有效的字符串表达式。

4VBA语法基础

4.1注释

好的程序一般都有注释，这对程序的维护有很大的好处。

在VBA程序中，注释可以通过以下两种方式实现：

（1）使用Rem语句。

（2）用单引号（'）号。

4.3变量和常量

4.3.1变量

变量用于临时保存数据。

程序运行时，变量的数据可以改变。

在VBA代码中可声明和使用变量来临时存储数据或对象。

例如：

DimMyNameAsString'声明一个名为MyName的字符串变量

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>then

<语句组1>

[elseif<条件2>then

<语句组2>…

else

<语句组n>]

endif

<条件>是一个关系表达式或逻辑表达式。

若值为真，则执行紧接在关键字then后面的语句组。

若<条件>的值为假，则检测下一个elseif<条件>或执行else关键字后面的语句组，然后继续执行下一个语句。

6.2SelectCase语句

如果条件复杂，程序需要多个分支，用If语句就会显得相当累赘，而且程序变得不易阅读。

这时我们可以使用SelectCase语句来写出结构清晰的程序。

SelectCase语法如下：

SelectCase<检验表达式>

Case<比较列表1>

<语句组1>

…

[CaseElse

<语句组n>]

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事件中编程。

PrivateSubWorksheet_Change（ByValTargetAsRange）

addr=Target.Address'求当前单元格地址

IfInStr（addr,":

"）<>0ThenExitSub'如果改变的是区域的内容，退出过程,在删除一些数据的时候，会出现这种情况

col_letter=Mid（addr,2,1）'求地址中列标

row_no=Mid（addr,4）'求地址中行号

Ifcol_letter="G"Orcol_letter="H"Then

theory_week_no_addr="G"&row_no

theory_week_no_val=Range（theory_week_no_addr）.Value

theory_week_hour_addr="H"&row_no

theory_week_hour_val=Range（theory_week_hour_addr）.Value

theory_sum_hour_express_addr="I"&row_no

theory_sum_hour_value_addr="J"&row_no

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

Else

Range（theory_sum_hour_express_addr）.Select

Selection.Font.ColorIndex=2

Range（theory_sum_hour_value_addr）.Select

Selection.Font.ColorIndex=2

EndIf

EndIf

EndSub

程序中用到的函数如下：

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中编程

PrivateSubWorksheet_Change（ByValTargetAsRange）

DimaddrAsString,col_letterAsString,row_noAsS