EXCEL宏编程简明教程有实例.docx
《EXCEL宏编程简明教程有实例.docx》由会员分享，可在线阅读，更多相关《EXCEL宏编程简明教程有实例.docx（27页珍藏版）》请在冰豆网上搜索。
EXCEL宏编程简明教程有实例
Excel宏编程简明教程
一）、宏学习
首先需要明确的是，本文不可能教会您关于宏的所有内容。
您需要学会利用"录制宏"的方法来学习宏：
点击Excel"工具"下拉菜单中"宏"下?
quot;录制新宏"，此后可象平时一样进行有关操作，待完成后停止录制。
然后再点击"工具"下拉菜单中"宏"下"宏"的"编辑"选项即可打开刚才所录制的宏的VisualBasic源程序，并且可以在此时的"帮助"下拉菜单中获得有关的编程帮助。
对录制宏进行修改不仅可以学习宏的使用，还能大大简化宏的编写。
二）、基本概念
为了学习Excel中的宏，我们需要先了解以下一些基本概念。
1、工作簿：
Workbooks、Workbook、ActiveWorkbook、ThisWorkbook
Workbooks集合包含Excel中所有当前打开的Excel工作簿，亦即所有打开的Excel文件；Workbook对应Workbooks中的成员，即其中的Excel文件；ActiveWorkbook代表当前处于活动状态的工作簿，即当前显示的Excel文件；ThisWorkbook代表其中有VisualBasic代码正在运行的工作簿。
在具体使用中可用Workbooks（index）来引用Workbook对象，其中index为工作簿名称或编号；如Workbooks
（1）、Workbooks（"年度报表.xls"）。
而编号按照创建或打开工作簿的顺序来确定，第一个打开的工作簿编号为1，第二个打开的工作簿为2……。
2、工作表：
Worksheets、Worksheet、ActiveSheet
Worksheets集合包含工作簿中所有的工作表，即一个Excel文件中的所有数据表页；而Worksheet则代表其中的一个工作表；ActiveSheet代表当前处于的活动状态工作表，即当前显示的一个工作表。
可用Worksheets（index）来引用Worksheet对象，其中index为工作表名称或索引号；如Worksheets
（1）、Worksheets（"第一季度数据"）。
工作表索引号表明该工作表在工作表标签中的位置：
第一个（最左边的）工作表的索引号为1，最后一个（最右边的）为Worksheets.Count。
需要注意的是：
在使用过程中Excel会自动重排工作表索引号，保持按照其在工作表标签中的从左至右排列，工作表的索引号递增。
因此，由于可能进行的工作表添加或删除，工作表索引号不一定始终保持不变。
3、图表：
Chart、Charts、ChartObject、ChartObjects、ActiveChart
Chart代表工作簿中的图表。
该图表既可为嵌入式图表（包含在ChartObject中），也可为一个分开的（单独的）图表工作表。
Charts代表指定工作簿或活动工作簿中所有图表工作表的集合，但不包括嵌入式在工作表或对话框编辑表中的图表。
使用Charts（index）可引用单个Chart图表，其中index是该图表工作表的索引号或名称；如Charts
（1）、Charts（"销售图表"）。
图表工作表的索引号表示图表工作表在工作簿的工作表标签栏上的位置。
Charts
（1）是工作簿中第一个（最左边的）图表工作表；Charts（Charts.Count）为最后一个（最右边的）图表工作表。
ChartObject代表工作表中的嵌入式图表，其作用是作为Chart对象的容器。
利用ChartObject可以控制工作表上嵌入式图表的外观和尺寸。
ChartObjects代表指定的图表工作表、对话框编辑表或工作表上所有嵌入式图表的集合。
可由ChartObjects（index）引用单个ChartObject，其中index为嵌入式图表的编号或名称。
如Worksheets（"Sheet1"）.ChartObjects
（1）、Worksheets（"sheet1"）.ChartObjects（"chart1"）分别对应"Sheet1"工作表中的第一个嵌入式图表、以及名为"Chart1"的嵌入式图表。
ActiveChart可以引用活动状态下的图表，不论该图表是图表工作表，或嵌入式图表。
而对于图表工作表为活动工作表时，还可以通过ActiveSheet属性引用之。
4、单元格：
Cells、ActiveCell、Range、Areas
Cells（row,column）代表单个单元格，其中row为行号，column为列号。
如可以用Cells（1,1）、Cells（10,4）来引用"A1"、"D10"单元格。
ActiveCell代表活动工作表的活动单元格，或指定工作表的活动单元格。
Range代表工作表中的某一单元格、某一行、某一列、某一选定区域（该选定区域可包含一个或若干连续单元格区域）或者某一三维区域。
可用Range（arg）来引用单元格或单元格区域，其中arg可为单元格号、单元格号范围、单元格区域名称。
如Range（"A5"）、Range（"A1:
H8"）、Range（"Criteria"）。
虽然可用Range（"A1"）返回单元格A1，但用Cells更方便，因为此时可用变量指定行和列。
可将Range与Cells结合起来使用，如Range（Cells（1,1）,Cells（10,10））代表单元格区域"A1:
J10"；而expression.Cells（row,column）返回单元格区域中的一部分，其中expression是返回Range的表达式，row和column为相对于该区域的左上角偏移量。
如由Range（"C5:
C10"）.Cells（1,1）引用单元格C5。
Areas为选定区域内的连续单元格块的集合，其成员是Range对象。
而其中的每个Range对象代表选定区域内与其它部分相分离的一个连续单元格块。
某些操作不能在选定区域内的多个单元格块上同时执行；必须在选定区域内的单元格块数Areas.Count上循环，对每个单独的单元格块分别执行该操作。
此时，可用Areas（index）从集合中返回单个Range对象，其中index为单元格块编号；如Areas
（1）。
5、行与列：
Rows、Columns、Row、Column
Rows、Columns分别代表活动工作表、单元格区域范围Range、指定工作表中的所有行数、列数。
对于一个多选单元格区域范围Range的Rows、Columns，只返回该范围中第一个区域的行数、列数。
例如，如果Range对象有两个区域（areas）A1:
B2和C3:
D4，Rows.Count返回2而不是4。
可通过Rows（行号）、Columns（列号）来引用相应的行与列；如Rows（3）、Columns（4）分别对应第三行、D列。
利用Rows、Column可以获得区域中第一块的第一行行号、第一列列号，所得值均以十进制数表示。
三）、处理单元格
1、直接赋值与引用
将变量、常量值直接赋给单元格、或将单元格的值直接赋给变量、常量，这是在Excel中最简单的单元格赋值及引用方法。
如下例将工作表"Sheet1"A1单元格的值赋给Integer变量I，并将I+1的值赋给当前工作表中的B1单元格：
DimIAsInteger
I=Worksheets（"Sheet1"）.Cells（1,1）
Cells（1,2）.Select'选定B1单元格，使其成为当前单元格
ActiveCell=I+1'以I+1为当前单元格赋值
2、用公式赋值
在宏的使用中，可能会更多地用公式来给单元格赋值。
如下例将相对于活动单元格左侧第4列、向上第6行至向上第2行的单元格数值之和赋给活动单元格（以本行、本列为第0行、0列）：
ActiveCell.Formula="=AVERAGE（R[-6]C[-4]:
R[-2]C[-4]）"
3、引用其它工作表中的单元格
当赋值公式中需要引用其它工作表中的单元格时，在被引用的单元格前加上"工作表名！
"即可。
如以下即在赋值中引用了"Sheet1"工作表中的A1至A4单元格：
Range（"E10"）.Formula="=SUM（Sheet1!
R1C1:
R4C1）"
但需注意的是：
当被引用的工作表名中含有某些可能引起公式歧义的字符时，需要用单引号'将工作表名括起来。
如：
Worksheets（"Sheet1"）.ActiveCell.Formula="=Max（'1-1剖面'!
D3:
D5）"
4、引用其它工作簿中的单元格
在被引用单元格所在工作表名前加上"[工作簿名]"，即可引用其它工作簿中的单元格。
如：
ActiveCell.Formula="=MAX（[Book1.xls]Sheet3!
R1C:
RC[4]）"
同样需注意的是：
当被引用的工作簿名中含有某些可能引起公式歧义的字符时，需要用中括号"["、"]"及单引号'将工作簿名括起来。
如：
Cells（1,2）.Formula="=MIN（'[1995-2000总结.xls]1995-1996年'!
$A$1:
$A$6）"
5、避免循环引用
在上述公式赋值过程中，应避免在公式中引用被赋值的单元格，防止循环引用错误。
6、添加批注
可按如下方法格给单元格添加批注：
Dim批注文本AsString
批注文本="批注示例"'准备批注文本
ActiveCell.AddComment'添加批注
ActiveCell.Comment.TextText:
=临时'写入批注文本
ActiveCell.Comment.Visible=False'隐藏批注
7、添加、删除、复制、剪切、粘贴单元格
Range（"D10"）.InsertShift:
=xlToRight'在D10单元格处添加一新单元格，原D10格右移
Range（"C2"）.InsertShift:
=xlDown'在C2单元格处添加一新单元格，原C2格下移
Rows
（2）.EntireRow.Insert'在第2行前添加一空白行，原第2行下移
Columns（3）.EntireColumn.Insert'在C列前添加一空白列，原C列右移
Columns（"A:
D"）.DeleteShift:
=xlToLeft'删除A列至D列，其右侧列左移
Rows（"3:
5"）.DeleteShift:
=xlUp'删除第3行至第5行，其下方行上移
Range（"B2"）.EntireRow.Delete'删除第2行
Range（"C4"）.EntireColumn.Delete'删除C列
Range（"B10:
C13"）.Copy'复制B10至C13单元格区域
Cells（1,2）.Cut'剪切B1单元格
Range（"D10"）.Select
ActiveSheet.Paste'自D10单元格起粘贴剪贴板中的内容
四）、图表
1、工作表图表
以下为一添加工作表图表的实例。
Charts.Addafter:
=Worksheets（"Sheet1"）'在"Sheet1"工作表之后添加新图表工作表
ActiveChart.ChartType=xlXYScatterSmooth'图表类型为XY平滑线散点图
ActiveChart.SetSourceDataSource:
=Sheets（"结点坐标"）.Range（"A1:
B69"）,PlotBy:
=_
xlColumns'图表数据来源于"结点坐标"工作表的A1至B69单元格，且按列绘图。
ActiveChart.LocationWhere:
=xlLocationAsNewSheet
WithActiveChart
.HasTitle=True
.ChartTitle.Characters.Text="节点坐标"'图表标题"节点坐标"
.Axes（xlCategory,xlPrimary）.HasTitle=True
.Axes（xlCategory,xlPrimary）.AxisTitle.Characters.Text="x"'x轴标题"x"
.Axes（xlValue,xlPrimary）.HasTitle=True
.Axes（xlValue,xlPrimary）.AxisTitle.Characters.Text="y"'y轴标题"y"
EndWith
WithActiveChart.Axes（xlCategory）
.HasMajorGridlines=True'显示x轴主网格线，默认情况下为显示
.HasMinorGridlines=True'显示x轴次网格线，默认情况下为不显示
EndWith
WithActiveChart.Axes（xlValue）
.HasMajorGridlines=True'标出x轴主网格值，默认情况下为标注
.HasMinorGridlines=False'取消x轴次网格值标注，默认情况下为不标注
EndWith
ActiveChart.Legend.Position=xlRight'图例显示在图表右侧
2、嵌入式图表
嵌入式图表仅在添加方式及引用格式上与工作表图表有所不同，而对图表的设置基本类似。
详见下例。
Set嵌入表=ActiveSheet.ChartObjects.Add（0,0,200,300）'在当前工作表（0,0）坐标处添加宽200，高300的嵌入式图表
嵌入表.Chart.ChartType=xlColumnClustered'图表类型为簇状柱形图
嵌入表.Chart.SetSourceDataSource:
=Sheets
（1）.Range（"A2:
B2"）,PlotBy:
=xlRows'设置图表数据来源
With嵌入表.Chart
.HasTitle=False'无图表标题
.Axes（xlCategory,xlPrimary）.HasTitle=False'无x轴标题
.Axes（xlValue,xlPrimary）.HasTitle=False'无y轴标题
EndWith
五）、工作表
1、添加
Sheets.Addbefore:
=Sheets
（1）'在第1工作表前添加新工作表
Sheets.Addafter:
=Sheets（Sheets.Count）'在最后工作表后添加新工作表
2、移动
ActiveSheet.Movebefore:
=Sheets
（2）'将当前工作表移动至第2工作表之前
3、命名
ActiveSheet.Name="工作表名"'将当前工作表命名为"工作表名"
4、删除
可以用以下语句删除当前工作表。
ActiveSheet.Delete
但在删除前Excel会自动弹出提示框，需在用户确认后方可执行删除。
为避免这一干扰，可以先用以下语句关闭Excel的警告提示。
Application.DisplayAlerts=False
在删除完成后，再重新打开Excel的警告提示
Application.DisplayAlerts=True
六）、工作簿
Excel的宏对工作簿的操作主要为保存。
Dim存盘文件名AsString
ActiveWorkbook.Save'保存当前工作簿
存盘文件名="工作表名"
ActiveWorkbook.SaveAsFilename:
=存盘文件名'当前工作簿另存为"工作表名.xls"
在另存时，若指定的存盘文件名不包含路径，则保存在该工作簿的打开目录下。
而若此存盘文件已存在，也可用关闭Excel警告提示的方法以免其自动弹出提示框。
64、用过VB的人都应该知道如何声明变量，在VBA中声明变量和VB中是完全一样的！
使用Dim语句
Dimaasinteger '声明A为整形变量
Dima '声明A为变体变量
Dimaasstring '声明A为字符串变量
Dima,b,cascurrency '声明A,b,c为货币变量
声明变量可以是：
Byte、Boolean、Integer、Long、Currency、Single、Double、Decimal（当前不支持）、Date、String（只限变长字符串）、String*length（定长字符串）、Object、Variant、用户定义类型或对象类型。
强制声明变量
OptionExplicit
说明：
该语句必在任何过程之前出现在模块中。
声明常数，用来代替文字值。
Const
'常数的默认状态是Private。
ConstMy=456
'声明Public常数。
PublicConstMyString="HELP"
'声明PrivateInteger常数。
PrivateConstMyIntAsInteger=5
'在同一行里声明多个常数。
ConstMyStr="Hello",MyDoubleAsDouble=3.4567
在EXCEL97中，有一个十分好的功能，他就是把鼠标放置在一个有效数据单元格中，执行该段代码，你就可以将连在一起的一片数据全部选中。
只要将该段代码加入到你的模块中。
SubMy_Select
Selection.CurrentRegion.Select
Endsub
删除当前单元格中数据的前后空格。
submy_trim
Trim（ActiveCell.Value）
endsub
使单元格位移
submy_offset
ActiveCell.Offset（0,1）.Select'当前单元格向左移动一格
ActiveCell.Offset（0,-1）.Select'当前单元格向右移动一格
ActiveCell.Offset（1, 0）.Select'当前单元格向下移动一格
ActiveCell.Offset（-1 ,0）.Select'当前单元格向上移动一格
endsub
如果上述程序产生错误那是因为单元格不能移动，为了解除上述错误，我们可以往
submy_offset之下加一段代码onerrorresumenext
注意以下代码都不再添加sub“代码名称”和endsub请自己添加!
给当前单元格赋值：
ActiveCell.Value="你好"
给特定单元格加入一段代码:
例如：
在Ａ１单元格中插入"ＨＥＬＬＯ"
Range（"a1"）.value="hello"
又如：
你现在的工作簿在sheet1上，你要往sheet2的Ａ１单元格中插入"ＨＥＬＬＯ"
1.sheets（"sheet2"）.select
range（"a1"）.value="hello"
或2.Sheets（"sheet1"）.Range（"a1"）.Value="hello"
说明：
1.sheet2被打开，然后在将“HELLO"放入到A1单元格中。
2.sheet2不被打开，将“HELLO"放入到A1单元格中。
隐藏工作表
'隐藏SHEET1这张工作表
sheets（"sheet1"）.Visible=False
'显示SHEET1这张工作表
sheets（"sheet1"）.Visible=True
有时候我们想把所有的EXCEL中的SHEET都打印预览，请使用该段代码，它将在你现有的工作簿中循环，直到最后一个工作簿结束循环预览。
DimmyAsWorksheet
ForEachmyInWorksheets
my.PrintPreview
Nextmy
得到当前单元格的地址
msgboxActiveCell.Address
得到当前日期及时间
msgboxdate&chr（13）&time
保护工作簿
ActiveSheet.Protect
取消保护工作簿
ActiveSheet.Unprotect
给当前工作簿改名为"liu"
ActiveSheet.Name="liu"
打开一个应用程序
AppActivate（Shell（"C:
\WINDOWS\CALC.EXE"））
增加一个工作簿
Worksheets.Add
删除当前工作簿
activesheet.delete
打开一个文件
Workbooks.OpenFileName:
="C:
\MyDocuments\Book2.xls"
关闭当前工作簿
ActiveWindow.Close
当前单元格定为：
左对齐
Selection.HorizontalAlignment=xlLeft
当前单元格定为：
中心对齐
Selection.HorizontalAlignment=xlCenter
当前单元格定为：
右对齐
Selection.HorizontalAlignment=xlRight
当前单元格为百分号风格
Selection.Style="Percent"
当前单元格字体为粗体
Selection.Font.Bold=True
当前单元格字体为斜体
Selection.Font.Italic=True
当前单元格字体为宋体20号字
WithSelection.Font
.Name="宋体"
.Size=20
EndWith
With语句
With对象
.描述
EndWith
让你的机器发出响声
BEEP
清除单元格中所有文字、批注、格式、所有的东西！
ActiveCell.Clear
测试选择状态的单元格的行数
MsgBoxSelection.Rows.Count
测试选择状态的单元格的列数
MsgBoxSelection.Columns.Count
测试选择状态的单元格的地址
Selection.Address
让所有的错误不再发生
ONERRORRESUMENEXT
产生错误时让错误转到另一个地方
onerrorgotol
'code
l:
'code
删除一个文件
kill"c:
\1.txt"
定制自己的状态栏
Application.StatusBar="现在时刻:
"&Time
恢复自己的状态栏
Application.StatusBar=false
在运行期执行一个宏
Application.Runmacro:
="text"
滚动窗口到a1的位置
ActiveWindow.ScrollRow=1
ActiveWindow.ScrollColumn=1
定制系统日期
DimMyDate,MyDay
MyDate=#12
升级会员 