EXCEL宏编程简明教程完整版.docx

1、EXCEL宏编程简明教程完整版Excel 宏编程简明教程一）、宏学习首先需要明确的是 , 本文不可能教会您关于宏的所有内容。您需要学会利用 录制宏的方法来学习宏：点击Excel工具下拉菜单中宏下？quot;录制新宏,此 后可象平时一样进行有关操作 , 待完成后停止录制。然后再点击 工具下拉菜单中 宏下宏的编辑选项即可打开刚才所录制的宏的 Visual Basic 源程序,并且可 以在此时的 帮助下拉菜单中获得有关的编程帮助。对录制宏进行修改不仅可以学 习宏的使用 , 还能大大简化宏的编写。二） 、基本概念为了学习 Excel 中的宏,我们需要先了解以下一些基本概念。1、工作簿 ：Workboo

2、ks、Workbook、ActiveWorkbook 、ThisWorkbookWorkbooks集合包含Excel中所有当前打开的Excel工作簿,亦即所有打开的Excel文件;Workbook对应 Workbooks中的成员，即其中的Excel文件；ActiveWorkbook 代表当前处于活动状态的工作簿 , 即当前显示的 Excel 文件; ThisWorkbook 代表其中有 Visual Basic 代码正在运行的工作簿。在具体使用中可用 Workbooksndex）来引用 Workbook对象，其中index为工 作簿名称或编号 ; 如 Workbooks（1） 、Workboo

3、ks（ 年度报表 .xls） 。而编号按照创 建或打开工作簿的顺序来确定 , 第一个打开的工作簿编号为 1, 第二个打开的工作簿 为2 。2、工作表 ：Worksheets 、Worksheet、ActiveSheetWorksheets 集合包含工作簿中所有的工作表 , 即一个 Excel 文件中的所有数 据表页 ; 而 Worksheet 则代表其中的一个工作表 ;ActiveSheet 代表当前处于的活动 状态工作表 , 即当前显示的一个工作表。可用 Worksheets(index) 来引用 Worksheet 对象, 其中 index 为工作表名称或 索引号；如Worksheets(

4、l)、Worksheets第一季度数据)。工作表索引号表明该 工作表在工作表标签中的位置 :第一个(最左边的)工作表的索引号为 1,最后一个 ( 最右边的 ) 为 Worksheets.Count 。需要注意的是 : 在使用过程中 Excel 会自动重排 工作表索引号 , 保持按照其在工作表标签中的从左至右排列 , 工作表的索引号递增。 因此, 由于可能进行的工作表添加或删除 , 工作表索引号不一定始终保持不变。3、图表 :Chart 、 Charts 、 ChartObject 、 ChartObjects 、 ActiveChartChart 代表工作簿中的图表。该图表既可为嵌入式图表 (

5、包含在 ChartObject 中), 也可为一个分开的 (单独的)图表工作表。Charts 代表指定工作簿或活动工作簿中所有图表工作表的集合 , 但不包括嵌 入式在工作表或对话框编辑表中的图表。使用 Charts(index) 可引用单个 Chart 图表, 其中 index 是该图表工作表的索引号或名称 ； 如 Charts(1) 、 Charts( 销售图 表 ) 。图表工作表的索引号表示图表工作表在工作簿的工作表标签栏上的位置。 Charts(1) 是工作簿中第一个 (最左边的)图表工作表 ； Charts(Charts.Count) 为最 后一个 ( 最右边的 ) 图表工作表。Cha

6、rtObject 代表工作表中的嵌入式图表 , 其作用是作为 Chart 对象的容器。 利用 ChartObject 可以控制工作表上嵌入式图表的外观和尺寸。ChartObjects 代表指定的图表工作表、对话框编辑表或工作表上所有嵌入式 图表的集合。可由 ChartObjects(index) 引用单个 ChartObject, 其中 index 为嵌 入式图表的编号或名称。如 Worksheets(Sheet1).ChartObjects(1) 、 Worksheets(sheet1).ChartObjects(chart1) 分别对应 Sheet1 工作表中的第一个嵌入式图表、以及名为

7、Chart1 的嵌入式图表。ActiveChart 可以引用活动状态下的图表 , 不论该图表是图表工作表 , 或嵌入 式图表。而对于图表工作表为活动工作表时 , 还可以通过 ActiveSheet 属性引用 之。4、单元格 :Cells 、 ActiveCell 、Range、AreasCells(row,column) 代表单个单元格 , 其中 row 为行号 ,column 为列号。如可 以用 Cells(1,1) 、Cells(10,4) 来引用A1、D10单元格。ActiveCell 代表活动 工作表的活动单元格 , 或指定工作表的活动单元格。Ra nge代表工作表中的某一单元格、某一

8、行、某一列、某一选定区域 (该选定区域可包含一个或若干连续单元格区域 )或者某一三维区域。可用 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,co

9、lumn) 返回单元格区域中的一 部分,其中expression是返回Range的表达式,row和column为相对于该区域的左 上角偏移量。如由 Range(C5:C10).Cells(1,1) 引用单元格 C5。Areas为选定区域内的连续单元格块的集合,其成员是Range对象。而其中的 每个Range对象代表选定区域内与其它部分相分离的一个连续单元格块。某些操作 不能在选定区域内的多个单元格块上同时执行 ;必须在选定区域内的单元格块数 Areas.Count 上循环 , 对每个单独的单元格块分别执行该操作。此时 , 可用Areas(index)从集合中返回单个 Range对象,其中ind

10、ex为单元格块编号；如 Areas(1) 。5、行与列 :Rows、Columns、Row、ColumnRows Columns分别代表活动工作表、单元格区域范围 Range指定工作表中的所有行数、列数。对于一个多选单元格区域范围 Range的Rows Columns,只返回该范围中第一个区域的行数、列数。例如，如果Range对象有两个区域 (areas)A1:B2 和 C3:D4,Rows.Count 返回 2而不是 4。可通过Rows行号)、Columns(列号)来引用相应的行与列；如Rows(3)、 Columns(4)分别对应第三行、D列。利用Rows Column可以获得区域中第一块

11、的第一行行号、第一列列号 ,所得值均以十进制数表示。3)、处理单元格1、 直接赋值与引用将变量、常量值直接赋给单元格、或将单元格的值直接赋给变量、常量 , 这是 在 Excel 中最简单的单元格赋值及引用方法。如下例将工作表 Sheet1A1 单元格 的值赋给Integer变量I,并将1+1的值赋给当前工作表中的 B1单元格：Dim I As IntegerI=Worksheets(Sheet1).Cells(1,1)Cells(1,2).Select 选定B1单元格,使其成为当前单元格ActiveCell=I+1 以 I+1 为当前单元格赋值2、 用公式赋值在宏的使用中 , 可能会更多地用公

12、式来给单元格赋值。如下例将相对于活动单 元格左侧第 4列、向上第 6 行至向上第 2 行的单元格数值平均值赋给活动单元格 ( 以本行、本列为第 0 行、0 列):ActiveCell.Formula=AVERAGE(R-6C-4:R-2C-4)3、引用其它工作表中的单元格当赋值公式中需要引用其它工作表中的单元格时 , 在被引用的单元格前加上 工作表名!即可。如以下即在赋值中引用了 Sheetl工作表中的A1至A4单元格 :Range(E10).Formula=SUM(Sheet1!R1C1:R4C1)但需注意的是 : 当被引用的工作表名中含有某些可能引起公式歧义的字符时 , 需要用单引号 将工

13、作表名括起来。如 :Worksheets(Sheet1).ActiveCell.Formula=Max(1-1 剖面!D3:D5)4、引用其它工作簿中的单元格在被引用单元格所在工作表名前加上 工作簿名 , 即可引用其它工作簿中的 单元格。如 :ActiveCell.Formula=MAX(Book1.xlsSheet3!R1C:RC4)同样需注意的是 : 当被引用的工作簿名中含有某些可能引起公式歧义的字符时 需要用中括号 、 及单引号 将工作簿名括起来。如 :Cells(1,2).Formula=MIN(1995-2000 总结.xls1995-1996 年!$A$1:$A$6)5、避免循环引

14、用在上述公式赋值过程中 ,应避免在公式中引用被赋值的单元格 , 防止循环引用 错误。6、添加批注可按如下方法格给单元格添加批注 :Dim 批注文本 As String批注文本 = 批注示例 准备批注文本ActiveCell.AddComment 添加批注ActiveCell.Comment.Text Text:= 临时 写入批注文本ActiveCell.Comment.Visible=False 隐藏批注7、添加、删除、复制、剪切、粘贴单元格Range(D10).lnsert Shift:=xlToRight 在 D10单元格处添加一新单元格,原D10格右移Range(C2).lnsert S

15、hift:=xlDown 在 C2单元格处添加一新单元格，原 C2格下移Rows(2).EntireRow.lnsert 在第 2行前添加一空白行 ,原第 2行下移删除A列至D列,其右侧列左移Columns(3).EntireColumn.lnsert 在C列前添加一空白列，原C列右移Columns(A:D).Delete Shift:=xlToLeft Rows(3:5).Delete Shift:=xlUp 删除第 3行至第 5行, 其下方行上移 Range(B2).EntireRow.Delete 删除第 2 行Range(C4).EntireColumn.Delete 删除 C列Ran

16、ge(B10:C13).Copy 复制 B10至 C13单元格区域Cells(1,2).Cut 剪切 B1 单元格Range(D10).SelectActiveSheet.Paste 自D10单元格起粘贴剪贴板中的内容四 ) 、图表1 、工作表图表以下为一添加工作表图表的实例。在Sheet1工作表之后添加图表类型为XY平滑线散点图Charts.Add after:=Worksheets(Sheet1) 新图表工作表ActiveChart.ChartType=xlXYScatterSmooth II).Range(A1:B69), PlotBy:= _xlColumns 图表数据来源于结点坐标工

17、作表的A1至B69单元格,且按列绘ActiveChart.Location Where:=xlLocationAsNewSheetWith ActiveChart.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(

18、xlValue, xlPrimary).AxisTitle.Characters.Text = y y 轴标题yEnd WithWith ActiveChart.Axes(xlCategory).HasMajorGridlines = True 显示 x 轴主网格线 , 默认情况下为显示.HasMinorGridlines = True 显示 x 轴次网格线 , 默认情况下为不显示End WithWith ActiveChart.Axes(xlValue).HasMajorGridlines = True 标出 x 轴主网格值 , 默认情况下为标注.HasMinorGridlines = Fa

19、lse 取消 x 轴次网格值标注 , 默认情况下为不标注End WithActiveChart.Legend.Position = xlRight 图例显示在图表右侧2、嵌入式图表 嵌入式图表仅在添加方式及引用格式上与工作表图表有所不同 , 而对图表的设 置基本类似。详见下例。Set 嵌入表 =ActiveSheet.ChartObjects.Add(0,0,200,300) 在当前工作表 (0,0) 坐标处添加宽 200, 高 300的嵌入式图表嵌入表 .Chart.ChartType = xlColumnClustered 图表类型为簇状柱形图嵌入表.Chart.SetSourceData

20、 Source:=Sheets(1).Ra nge(A2:B2),PlotBy:=xlRows 设置图表数据来源With 嵌入表 .Chart.HasTitle = False 无图表标题.Axes(xlCategory, xlPrimary).HasTitle = False 无 x 轴标题.Axes(xlValue, xlPrimary).HasTitle = False 无 y 轴标题 End With五 ) 、工作表1 、添加Sheets.Add before:=Sheets(1) 在第 1 工作表前添加新工作表Sheets.Add after:=Sheets(Sheets.Count

21、) 在最后工作表后添加新工作2、移动ActiveSheet.Move before:=Sheets(2) 将当前工作表移动至第 2 工作表之、八前3、命名ActiveSheet.Name= 工作表名 将当前工作表命名为 工作表名 4、删除可以用以下语句删除当前工作表。ActiveSheet.Delete但在删除前 Excel 会自动弹出提示框 , 需在用户确认后方可执行删除。为避免 这一干扰 , 可以先用以下语句关闭 Excel 的警告提示。Application.DisplayAlerts = False在删除完成后 , 再重新打开 Excel 的警告提示Application.Displa

22、yAlerts = True六) 、工作簿Excel 的宏对工作簿的操作主要为保存。Dim 存盘文件名 As StringActiveWorkbook.Save 保存当前工作簿存盘文件名 =工作表名 ActiveWorkbook.SaveAs Filename:= 存盘文件名 当前工作簿另存为 工作 表名.Xis在另存时 , 若指定的存盘文件名不包含路径 , 则保存在该工作簿的打开目 录下。而若此存盘文件已存在 , 也可用关闭 EXcei 警告提示的方法以免其自动 弹出提示框。64、用过VB的人都应该知道如何声明变量,在VBA中声明变量和VB中是完全 一样的 !使用 Dim 语句Dim a a

23、s integer 声明A为整形变量Dim a 声明A为变体变量Dim a as string 声明A为字符串变量Dim a,b,c as currency 声明 A,b,c 为货币变量声明变量可以是 :Byte 、 Booiean、 Integer 、 Long、 Currency、 Singie 、 Doubie、 Decimai（ 当前不支持 ） 、 Date、 String（ 只限变长字符串 ） 、 String * iength （ 定长字符串 ） 、 Object 、 Variant 、用户定义类型或对象类型。强制声明变量Option EXpiicit说明 : 该语句必在任何过程之

24、前出现在模块中声明常数 , 用来代替文字值。Const 常数的默认状态是 Private 。Const My = 456 声明 Public 常数。Public Const MyString = HELP 声明 Private Integer 常数。Private Const MyInt As Integer = 5 在同一行里声明多个常数。Const MyStr = Hello, MyDouble As Double = 3.4567在EXCEL9中,有一个十分好的功能,他就是把鼠标放置在一个有效数据单元 格中 , 执行该段代码 , 你就可以将连在一起的一片数据全部选中。只要将该段代码加 入

25、到你的模块中。Sub My_SelectSelection.CurrentRegion.SelectEnd sub删除当前单元格中数据的前后空格。sub my_trimTrim(ActiveCell.Value)end sub使单元格位移sub my_offsetActiveCell.Offset(0, 1).SelectActiveCell.Offset(0, -1).SelectActiveCell.Offset(1 , 0).SelectActiveCell.Offset(-1 , 0).Selectend sub如果上述程序产生错误那是因为单元格不能移动当前单元格向左移动一格当前单元格

26、向右移动一格当前单元格向下移动一格当前单元格向上移动一格, 为了解除上述错误 , 我们可以往sub my_offset 之下加一段代码 on error resume next注意以下代码都不再添加 sub “代码名称” 和 end sub 请自己添加 !给当前单元格赋值 :ActiveCell.Value = 你好 !给特定单元格加入一段代码 :例如：在A1单元格中插入HELLORange(a1).value=hello又如：你现在的工作簿在sheetl上，你要往sheet2的A1单元格中插入HELLO1.sheets(sheet2).selectrange(a1).value=hello或

27、 2.Sheets(sheet1).Range(a1).Value = hello说明：1.sheet2被打开，然后在将“ HELLO放入到A1单元格中。2.sheet2不被打开，将“ HELLO放入到A1单元格中。隐藏工作表隐藏SHEET这张工作表sheets(sheet1).Visible=False显示SHEET这张工作表sheets(sheet1).Visible=True有时候我们想把所有的EXCELS的SHEETS打印预览,请使用该段代码,它将 在你现有的工作簿中循环 , 直到最后一个工作簿结束循环预览。Dim my As WorksheetFor Each my In Works

28、heetsmy.PrintPreviewNext my 得到当前单元格的地址 msgbox ActiveCell.Address 得到当前日期及时间 msgbox date & chr(13) & time 保护工作簿 ActiveSheet.Protect 取消保护工作簿 ActiveSheet.Unprotect 给当前工作簿改名为 liu ActiveSheet.Name = liu 打开一个应用程序AppActivate (Shell(C:WINDOWSCALC.EXE) 增加一个工作簿Worksheets.Add删除当前工作簿activesheet.delete打开一个文件关闭当前工

29、作簿Workbooks.Open FileName:=C:My DocumentsBook2.xls ActiveWindow.Close 当前单元格定为 : 左对齐Selection.HorizontalAlignment = xlLeft 当前单元格定为 : 中心对齐 Selection.HorizontalAlignment = xlCenter 当前单元格定为 : 右对齐Selection.HorizontalAlignment = xlRight 当前单元格为百分号风格 Selection.Style = Percent 当前单元格字体为粗体 Selection.Font.Bold

30、= True 当前单元格字体为斜体Selection.Font.Italic = True当前单元格字体为宋体 20 号字With Selection.Font.Name = 宋体 .Size = 20End WithWith 语句With 对象. 描述End With让你的机器发出响声BEEP! ActiveCell.Clear清除单元格中所有文字、批注、格式、所有的东西 测试选择状态的单元格的行数MsgBox Selection.Rows.Count 测试选择状态的单元格的列数MsgBox Selection.Columns.Count 测试选择状态的单元格的地址Selection.Address让所有的错误不再发生ON ERROR RESUME NEXT 产生错误时让错误转到另一个地方 on error goto l code l:恢复自己的状态栏code 删除一个文件 kill c:1.txt 定制自己的状态栏Application.StatusBar = 现在时刻 : & TimeApplication.StatusBar = false 在运行期执行一个宏