EXCEL宏编程简明教程

《EXCEL宏编程简明教程》由会员分享，可在线阅读，更多相关《EXCEL宏编程简明教程（15页珍藏版）》请在装配图网上搜索。

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

2、isWorkbookWorkbooks集合包含Excel中所有当前打开的Excel工作簿，亦即所有打开 的Excel文件；Workbook对应Workbooks中的成员，即其中的Excel文件； ActiveWorkbook代表当前处于活动状态的工作簿，即当前显示的Excel文件； ThisWorkbook代表其中有Visual Basic代码正在运行的工作簿。在具体使用中可用Workbooks（index）来引用Workbook对象，其中index为 工作簿名称或编号；如Workbooks（l）、Workbooks（年度报表.xls）。而编号按 照创建或打开工作簿的顺序来确定，第一个打开的工

3、作簿编号为1，第二个打开 的工作簿为2。2、工作表：Worksheets、Worksheet、ActiveSheetWorksheets集合包含工作簿中所有的工作表，即一个Excel文件中的所有 数据表页；而Worksheet则代表其中的一个工作表；ActiveSheet代表当前处于 的活动状态工作表，即当前显示的一个工作表。可用Worksheets（index）来引用Worksheet对象，其中index为工作表名称 或索引号；如Worksheets（1）、Worksheets（第一季度数据）。工作表索引号表 明该工作表在工作表标签中的位置：第一个（最左边的）工作表的索引号为1， 最后一个（

4、最右边的）为Worksheets.Count。需要注意的是：在使用过程中Excel 会自动重排工作表索引号，保持按照其在工作表标签中的从左至右排列，工作表 的索引号递增。因此，由于可能进行的工作表添加或删除，工作表索引号不一定 始终保持不变。3、图表：Chart、Charts、ChartObject、ChartObjects、ActiveChartChart代表工作簿中的图表。该图表既可为嵌入式图表（包含在ChartObject 中），也可为一个分开的（单独的）图表工作表。Charts代表指定工作簿或活动工作簿中所有图表工作表的集合，但不包括 嵌入式在工作表或对话框编辑表中的图表。使用Char

5、ts（index）可引用单个 Chart图表，其中index是该图表工作表的索引号或名称;如Charts（1）、Charts（ 销售图表）。图表工作表的索引号表示图表工作表在工作簿的工作表标签栏上的 位置。Charts（1）是工作簿中第一个（最左边的）图表工作表； Char ts（Char ts.Coun t）为最后一个（最右边的）图表工作表。ChartObject代表工作表中的嵌入式图表，其作用是作为Chart对象的容器。利用ChartObject可以控制工作表上嵌入式图表的外观和尺寸。ChartObjects代表指定的图表工作表、对话框编辑表或工作表上所有嵌入 式图表的集合。可由Chart

6、Objects(index)引用单个ChartObject,其中index 为嵌入式图表的编号或名称。如Worksheets(Sheetl).ChartObjects(l)、 Workshee ts(shee tl ).Char tObjec ts(char tl)分别对应Shee tl工作表中的 第一个嵌入式图表、以及名为Char t1的嵌入式图表。ActiveChart可以引用活动状态下的图表，不论该图表是图表工作表，或嵌 入式图表。而对于图表工作表为活动工作表时，还可以通过ActiveSheet属性引 用之。4、单元格：Cells、ActiveCell、Range、AreasCells(

7、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)返回单元格Al，但

8、用Cells更方便， 因为此时可用变量指定行和列。可将 Range 与 Cells 结合起来使用，如 Range(Cells(1,1),Cells(10,10)代 表单元格区域A1J10；而expression.Cells(row,column)返回单元格区域中 的一部分，其中expression是返回Range的表达式，row和column为相对于该 区域的左上角偏移量。如由Range(C5:C10).Cells(1,1)引用单元格C5。Areas为选定区域内的连续单元格块的集合，其成员是Range对象。而其中 的每个Range对象代表选定区域内与其它部分相分离的一个连续单元格块。某些 操作

9、不能在选定区域内的多个单元格块上同时执行；必须在选定区域内的单元格 块数Areas.Count上循环，对每个单独的单元格块分别执行该操作。此时，可用 Areas(index)从集合中返回单个Range对象，其中index为单元格块编号；如 Areas(l)。5、行与列：Rows、Columns、Row、ColumnRows、Columns分别代表活动工作表、单元格区域范围Range、指定工作表 中的所有行数、列数。对于一个多选单元格区域范围Range的Rows、Columns， 只返回该范围中第一个区域的行数、列数。例如，如果Range对象有两个区域 (areas) A1:B2 和 C3:D4

10、，Rows.Count 返回 2 而不是 4。可通过Rows(行号)、Columns(列号)来引用相应的行与列；如Rows(3)、 Columns(4)分别对应第三行、D列。利用Rows、Column可以获得区域中第一块的第一行行号、第一列列号，所得值 均以十进制数表示。三) 、处理单元格1、直接赋值与引用 将变量、常量值直接赋给单元格、或将单元格的值直接赋给变量、常量，这 是在Excel中最简单的单元格赋值及引用方法。如下例将工作表Sheet1A1单 元格的值赋给Integer变量I,并将I+1的值赋给当前工作表中的B1单元格：Dim I As IntegerI=Worksheets(She

11、et1).Cells(1,1)Cells(l,2).Select 选定B1单元格，使其成为当前单元格ActiveCell=I+l 以1+1为当前单元格赋值2、用公式赋值 在宏的使用中，可能会更多地用公式来给单元格赋值。如下例将相对于活动单元格左侧第4列、向上第6行至向上第2行的单元格数值之和赋给活动单元格 (以本行、本列为第0行、0列)：ActiveCell.Formula=AVERAGE(R-6C-4:R-2C-4)3、引用其它工作表中的单元格当赋值公式中需要引用其它工作表中的单元格时，在被引用的单元格前加上 工作表名！ 即可。如以下即在赋值中引用了Sheet1工作表中的A1至A4单元 格：

12、Range(E10).Formula=SUM(Sheet1!R1C1:R4C1) 但需注意的是：当被引用的工作表名中含有某些可能引起公式歧义的字符 时，需要用单引号将工作表名括起来。如：Worksheets(Sheet1).ActiveCell.Formula二二Max(1T 剖面！D3:D5)4、引用其它工作簿中的单元格 在被引用单元格所在工作表名前加上工作簿名，即可引用其它工作簿中的单元格。如： ActiveCell.Formula=MAX(Book1.xlsSheet3！R1C:RC4) 同样需注意的是：当被引用的工作簿名中含有某些可能引起公式歧义的字符时， 需要用中括号、及单引号将工作

13、簿名括起来。如：CeHs(1,2).Formula=MIN(1995-2000 总结.xls1995-1996 年！ $A$1:$A$6)5、避免循环引用 在上述公式赋值过程中，应避免在公式中引用被赋值的单元格，防止循环引用错误。6、添加批注 可按如下方法格给单元格添加批注：Dim 批注文本 As String 批注文本=批注示例 准备批注文本 ActiveCell.AddComment 添加批注Acti veCell.Commen t.Text Tex t:二临时写入批注文本ActiveCell.Comment.Visible=False 隐藏批注7、添加、删除、复制、剪切、粘贴单元格Ran

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

15、移Range(B2).EntireRow.Delete 删除第2行Range(C4).EntireColumn.Delete 删除 C 列 Range(B10:C13).Copy 复制 BIO 至 C13 单元格区域Cells(l,2).Cut 剪切 Bl 单元格 Range(D1O).SelectActiveSheet.Paste 自D10单元格起粘贴剪贴板中的内容四) 、图表1、工作表图表以下为一添加工作表图表的实例。Charts.Add after:二Worksheets(Sheet1)在Sheet1工作表之后添加新 图表工作表ActiveChart.ChartType=xlXYScat

16、terSmooth 图表类型为 XY 平滑线散点图 Acti veChar t.Se tSourceDa ta Source:二Shee ts(结点坐标).Range(A1:B69), PlotBy:= _xlColumns 图表数据来源于结点坐标工作表的A1至B69单元格，且按列 绘图。ActiveChart.Location Where:=xlLocationAsNewSheetWith ActiveChart.HasTitle = True.ChartTitle.Characters.Text = 节点坐标 图表标题节点坐标 .Axes(xlCategory, xlPrimary).Ha

17、sTitle = True .Axes(xlCategory,xlPrimary).AxisTitle.Characters.Text=xx 轴标题x.Axes(xlValue, xlPrimary).HasTitle = True.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = y y 轴标题yEnd WithWith ActiveChart.Axes(xlCategory).HasMajorGridlines = True 显示x轴主网格线，默认情况下为显示 .HasMinorGridlines = True 显示x轴次网格线，默认

18、情况下为不显 示End WithWith ActiveChart.Axes(xlValue).HasMajorGridlines = True 标出x轴主网格值，默认情况下为标注 .HasMinorGridlines = False 取消x轴次网格值标注，默认情况下为 不标注End WithActiveChart.Legend.Position = xlRight 图例显示在图表右侧2、嵌入式图表嵌入式图表仅在添加方式及引用格式上与工作表图表有所不同，而对图表的 设置基本类似。详见下例。Set 嵌入表二ActiveSheet.ChartObjects.Add(0,0,200,300)在当前工作

19、表 (0,0)坐标处添加宽200，高300的嵌入式图表嵌入表.Char t.Char tType = xlColumnClus tered 图表类型为簇状柱形图嵌入表.Char t.Se tSourceDa ta Source:二Shee ts(l).Range(A2:B2), PlotBy:=xlRows 设置图表数据来源With 嵌入表.Chart.HasTitle = False 无图表标题.Axes(xlCategory, xlPrimary).HasTitle = False 无 x 轴标题 .Axes(xlValue, xlPrimary).HasTitle = False 无 y

20、 轴标题End With五) 、工作表1、添加Sheets.Add before:=Sheets(1) 在第1工作表前添加新工作表Sheets.Add after:二Sheets(Sheets.Count)在最后工作表后添加新工作表 2、移动ActiveSheet.Movebefore:=Sheets(2) 将当前工作表移动至第2工作表之 前3、命名Act iveShee t.Name二工作表名将当前工作表命名为工作表名4、删除 可以用以下语句删除当前工作表。ActiveSheet.Delete但在删除前Excel会自动弹出提示框，需在用户确认后方可执行删除。为避 免这一干扰，可以先用以下语句

21、关闭Excel的警告提示。Application.DisplayAlerts = False在删除完成后，再重新打开Excel的警告提示 Application.DisplayAlerts = True六) 、工作簿Excel的宏对工作簿的操作主要为保存。Dim 存盘文件名 As StringActiveWorkbook.Save 保存当前工作簿 存盘文件名=工作表名ActiveWorkbook.SaveAs Filename:= 存盘文件名 当前工作簿另存为工 作表名.xls在另存时，若指定的存盘文件名不包含路径，则保存在该工作簿的打开目录 下。而若此存盘文件已存在，也可用关闭Excel警告

22、提示的方法以免其自动弹出 提示框。64、用过VB的人都应该知道如何声明变量，在VBA中声明变量和VB中是完全一 样的！使用Dim语句Dim a as integer声明A为整形变量Dim a声明A为变体变量Dim a as st ring声明A为字符串变量Dim a,b,c as currency声明 A,b,c 为货币变量声明变量可以是：Byte、Boolean、Integer、Long、Currency、Single、Double、 Decimal (当前不支持)、Date、St ring (只限变长字符串)、St ring * leng th (定长字符串)、Object、Variant

23、、用户定义类型或对象类型。强制声明变量Option Explicit 说明：该语句必在任何过程之前出现在模块中。 声明常数，用来代替文字值。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在EXCEL97中，有一个十分好的功能，他就是把鼠标放置在一个

24、有效数据单元格中，执行该段代码，你就可以将连在一起的一片数据全部选中。只 要将该段代码加入到你的模块中。Sub My_SelectSelection.CurrentRegion.SelectEnd sub 删除当前单元格中数据的前后空格。sub my_trimTrim(ActiveCell.Value)end sub使单元格位移sub my_offsetActiveCell.Offset(0, 1).Select当前单元格向左移动一格ActiveCell.Offset(0, -l).Select当前单元格向右移动一格Acti veCell.Offse t(l ,0).Selec t当前单元格向

25、下移动一格Act iveCell.Offse t(T, 0).Selec t当前单元格向上移动一格end sub如果上述程序产生错误那是因为单元格不能移动，为了解除上述错误，我们可以 往sub my_offset 之下加一段代码 on error resume next 注意以下代码都不再添加sub “代码名称”和end sub请自己添加！ 给当前单元格赋值：ActiveCell.Value = 你好 给特定单元格加入一段代码:例如：在A1单元格中插入 HELLORange(a1).value=hello又如：你现在的工作簿在shee tl上，你要往shee t2的Al单元格中插入HE LLO

26、l. sheets(sheet2).selectrange(a1).value=hello或 2.Sheets(sheetl).Range(al).Value = hello 说明：1. sheet2被打开，然后在将“HELLO放入到Al单元格中。2. sheet2不被打开，将“HELLO放入到A1单元格中。隐藏工作表隐藏SHEET1这张工作表 sheets(sheetl).Visible=False显示SHEET1这张工作表 sheets(sheetl).Visible=True有时候我们想把所有的EXCEL中的SHEET都打印预览，请使用该段代码，它将在 你现有的工作簿中循环，直到最后一个

27、工作簿结束循环预览。Dim my As WorksheetFor Each my In Worksheets my.PrintPreviewNext my得到当前单元格的地址msgbox ActiveCell.Address得到当前日期及时间msgbox date & chr(l3) & time保护工作簿ActiveSheet.Protect取消保护工作簿ActiveSheet.Unprotect给当前工作簿改名为 liuActiveSheet.Name = liu打开一个应用程序AppActivate (Shell(C:WINDOWSCALC.EXE)增加一个工作簿Worksheets.A

28、dd删除当前工作簿activesheet.delete打开一个文件Workbooks.Open FileName:=C:My DocumentsBook2.xls 关闭当前工作簿ActiveWindow.Close当前单元格定为：左对齐Selection.HorizontalAlignment = xlLeft 当前单元格定为：中心对齐Selection.HorizontalAlignment = xlCenter 当前单元格定为：右对齐Selection.HorizontalAlignment = xlRight 当前单元格为百分号风格Selection.Style = Percent当前单

29、元格字体为粗体 Selection.Font.Bold = 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

30、 测试选择状态的单元格的地址 Selection.Address 让所有的错误不再发生 ON ERROR RESUME NEXT 产生错误时让错误转到另一个地方 on error goto lcodel:code 删除一个文件 kill c:1.txt 定制自己的状态栏 Application.StatusBar = 现在时刻: & Time 恢复自己的状态栏Application.StatusBar = false 在运行期执行一个宏Application.Run macro:=text 滚动窗口到al的位置ActiveWindow.ScrollRow = 1 ActiveWindow.Sc

31、rollColumn = l 定制系统日期Dim MyDate, MyDay MyDate = #l2/l2/69#MyDay = Day(MyDate)今天的年限Dim MyDate, MyYearMyDate = DateMyYear = Year(MyDate)MsgBox MyYear产生一个inputbox输入框InputBox (Enter number of months to add) 得到一个文件名：Dim kk As Stringkk = Applica tio n.Ge tO penFilename(EXCEL (* .XLS), * .XLS, Titl e:二提示：

32、请打开一个EXCEL文件：)msgbox kk 打开zoom显示比例对话框Application.Dialogs(xlDialogZoom).Show 激活字体对话框Application.Dialogs(xlDialogActiveCellFont).Show 打开另存对话框Dim kk As Stringkk = Application.GetSaveAsFilename(excel (*.xls), *.xls) Workbooks.Open kk此段代码寻找字符串中特定字符的位置，需要建立一个窗体，并在窗体中，放入TEXTBOX1,TEXTBOX2 和 TEXTBOX3(3 个文本框)

33、及 COMMANDBUTTON1(按钮) Private Sub CommandButton1_Click()a = TextBox1.Textb = TextBox2.Textaa = Len(a)i = 1Dim YY As StringIf b = Then Exit SubDoIf InStr(i, a, b, vbTextCompare) = 0 Then Exit Dokk = InStr(i, a, b, vbTextCompare)YY = YY & CStr(kk) & /i = kk + 1Loop While aa = iTextBox3.Text = YYEnd Sub

34、ActiveX控件在Excel97中的运用Excel97在工作表或图表上可使用ActiveX控件，根据我使用的体 会，在工作上处理控件时，必须注意和了解如下事项：(一)用 Excel5.0/95 工作簿文件格式保存Excel97 工作簿时，将选择ActiveX控件信息。当用户通过双 击鼠标来编辑内嵌在其它应用程序文档中的Excel97工作簿时，该工作簿上的控 件将不会正常工作。如果用户是通过用右键单击工作簿，然后选中快捷菜单上的 “打开”命令来编辑工作簿的话，工作簿上的控件就能正常工作了。(二)当ActiveX控件处于激活状态时，将禁用某些MicrosoftExcelVisualBasic方法

35、和属性。例如，当某一控件激活时，就不能使 用Sort方法，故下述按钮单击事件处理过程中的代码将失败(因为用户单击按钮 后，该按钮就处于激活状态)。PrivateSubCommandButton1 ClickRange(al:alO) SortKeyl:=Range(al)EndSub 解决办法是通过选激活工作表上其它元素的方法来绕过这种问题。 例如，可用下列代码对单元格区域排序：PrivateSubCommandButton1 ClickRange(al) ActivateRange(al:alO) SortKeyl:=Range(al) CommandButton1 ActivateEnd

36、Sub(三)在 MicrosoftExcel 中，用 OLEObjects 集合中的 OLEObject 对象 代表ActiveX控件。如果要用编程的方式向工作表添加ActiveX控件，可用 OLEObjects集合的Add方法。例如向第一张工作表添加命令按钮。Worksheets(l) OLEObjects AddForms CommandButton 1，_ Left:=10,Top:=10,Height:=20,Width:=100因为ActiveX控件也可用OLEObjects集合中的OLEObject对象代 表，所以也可用该集合中的对象来设置控件的属性。例如要设置控件 “Comman

37、dBo tt onl 的“左边位置属性。Worksheets(1) OLEObjects(CommandButtonl) Left=10 那些不属于OLEObject对象属性的控件属性，可通过由Object属性 返回的实际控件对象来设置。例如要设置控件“ CommandBu ttonl”的标题。Worksheets(1) OLEObjects(CommandButtonl)Object Caption二runme因为所有的OLE对象也是Shapes集合的成员，所以也可用该集合设 置若干控件的属性。例如要对齐第一张工作表上所有控件的左边框。ForEachsInWorksheets(l) Shap

38、esIfs Type=msoOLEControlObjectThens Left=l0Next 请注意，当在控件所在工作表的类模块之外使用控件的名称时，必 须用工作表的名称限定该控件的名称。在工作表上ActiveX控件的事件处理过程 中，Me关键字所指向的是工作表，而非控件在Excel中利用VBA创建多级选单Excel是我们常用的报表处理软件之一，对于大多数人来说只是使用它进行打印 报表，没有注意其他功能，其实利用Excel内嵌的VBA语言完全可以快速开发 出自己企业的应用系统来，而且应用系统界面与其它专业编程语言相当相似。下 面笔者简单通过一个实例说明如何利用VBA创建多级选单。首先，我们对

39、Excel中两个重要的内置函数auto_open()和auto_close()作一 简单说明。auto_open() ：在打开工作簿时系统将自动执行该函数，因此我们可以在该函数 中调用自己应用程序的选单函数以及其它需要初始化设置的函数及宏语句； auto_close()：在关闭工作簿时系统将自动执行该函数。所以我们需要在该函数 中放置删除用户自定义选单语句，否则只有退出Excel才能恢复EXCEL的系统选 单。在以下语句中，我们定义了选单设置函数OpenMyMenu()，用于设置多级选单， 其他有关说明见程序内注释，详细代码如下：Sub OpenMyMenu()，自定义多级选单函数On Err

40、or Resume Nex t，忽略错误MenuBars(MyMenu).Dele te，删除自定义选单 MenuBars.Add (MyMenu)，自定义选单项Sheets(sheet1).SelectMenuBars(MyMenu).Menus.Add Cap tion:二金融增加第一个选单项“金融”以下三句为在“金融”选单下增加“银行法、货币政策和条例”三项选单项 MenuBars(MyMenu).Menus(金融).MenuI tems.Add Cap ti on:二银行法， OnAc tion:二银行法MenuBars(MyMenu).Menus(金融).MenuI tems.Add

41、 Cap tion:二货币政策， OnAc ti on:二货币政策MenuBars(MyMenu).Menus(金融).MenuI tems.Add Cap ti on:二条例， OnAc tion:二条例以下为创建如图所示的多级选单MenuBars(MyMenu).Menus.Add Cap tion:二经济建立选单项“经济” 以下三句为在“经济”选单下增加“农业、工业和第三产业”三项选单项 MenuBars(MyMenu).Menus(经济).MenuI tems.Add Cap ti on:二农业， OnAc tion:二农业MenuBars(MyMenu).Menus(经济).Menu

42、I tems.Add Cap ti on:二工业， OnAc tion:二工业MenuBars(MyMenu).Menus(经济).MenuI tems.AddMenu Cap ti on:二第三产业 以下三句为在“第三产业”选单下增加“概况、范畴”二项选单项和“饮食服 务业”子选单MenuBars(MyMenu).Menus(经济).MenuI tems(“ 第三产业).MenuI tems.Add Cap tion:二概况，OnAc tion:二概况MenuBars(MyMenu).Menus(经济).MenuI tems(“ 第三产业).MenuI tems.Add Cap tion:二

43、范畴，OnAc tion:二范畴MenuBars(MyMenu).Menus(经济).MenuI tems(“ 第三产业).MenuI tems.AddMenu Cap ti on:二饮食服务业以下二句为在“饮食服务业”选单下增加“酒店1、酒店2”二项选单项 MenuBars(MyMenu).Menus(经济).MenuI tems(“ 第三产业).MenuI tems(“ 饮 食服务业).MenuI tems.Add Cap ti on:二酒店 1, OnAc tion:二酒店 1 MenuBars(MyMenu).Menus(经济).MenuI tems(“ 第三产业).MenuI tem

44、s(“ 饮 食服务业).MenuI tems.Add Cap ti on:二酒店 2， OnAc tion:二酒店 2MenuBars(MyMenu).Ac tiva te，激活自定义选单End SubSub aut o_open()系统自动打开运行宏OpenMyMenu，调用用户选单函数End SubSub aut o_close()系统自动关闭运行宏On Error Resume Nex t，忽略错误MenuBars(MyMenu).Dele te，删除自定义选单End Sub 读者可以在自己的工作簿选单“工具”中的“宏”下，创建以上三个函数并将以 上函数语句拷贝到其中即可运行。66、用V

45、BA(编程)保护Excel文档VBA (Visual Basic for Application)是Excel应用程序中功能非常强大 的编程语言，为了规范不同的用户对Excel应用程序的访问能力，需要对Excel 文档及有关的数据进行有效的保护，这里根据自己及同行们的体会，从以下二 个方面介绍用VBA编程法实现对Excel文档的保护。对工作簿的保护1. 利用VBA中Workbook对象的SaveAs方法实现对工作簿的保护，下面就对 SaveAs有关的参量作一介绍：Filename：该字符串表示要保存的文件名。可包含完整路径。如果不指定路径， Microsoft Excel 将文件保存到当前文件

46、夹。FileForma t：可选，文件的保存格式。Password :为一个区分大小写的字符串(不超过15个字符)，用于指定文件 的保护密码。Wri teResPassword：该字符串表示文件的写保护密码。如果文件保存时带有密 码，但打开文件时不输入密码，则该文件以只读模式打开。ReadOnlyRecommended :如果为True则在打开文件时显示一条信息，提示该文 件以只读模式打开。下例就是在Excel应用程序中添加一工作簿，将工作簿按常规文件格式存为 “C:pjobj经济评价.xls ”文件，并给该文件指定保护密码“12”以及写保护 密码“23”。Sub 保护工作簿()NewWork

47、book = Workbooks.addNewWorkbook.SaveAs Fil eName: =C: pjobjS 济评价.xls, Fil eForma t:= XlNormal,Password:=12,Wr it eResPassword:二 23End sub2. 利用VBA中Workbook对象的Protect方法对工作簿的结构和窗口进行保护， Workbook对象的Protect方法带有以下三个参量：Password：为加在工作表或工作簿上区分大小写的密码字符串。如果省略本参 数，不用密码就可以取消对该工作簿的保护。否则，取消对该工作表或工作簿的 保护时必须提供该密码。如果忘

48、记了密码，就无法取消对该工作表或工作簿的保 护。最好在安全的地方保存一份密码及其对应文档名的列表。Structure：若为True则保护工作簿结构(工作簿中工作表的相对位置)。默 认值为False。Windows若为True则保护工作簿窗口。下例就是对一名为“经济评价.xls ”的工作簿实现“结构”和“窗口”保护： Sub 保护工作簿()Workbooks (经济评价).Pro tect Password:二1234, St rue ture:二True, Windows:=TrueEnd subWorkbook对象的Unproteet方法用于取消对工作簿的保护Unproteet方法只有 一个

49、参量，就是保护工作表时所用的口令。3. 对工作簿进行隐藏保护，可使他人无法看到其对应的窗口。操作方法如下： 在VBA中使用Workbook对象下面的Windows对象的Visible属性对工作簿进行 隐藏和取消隐藏，Visible属性的值可取“True”和“False”两种。下面程序代码完成对工作簿“ book.xls ”的隐藏：Sub 隐藏工作簿()Workbooks(book).AetivateAetiveWindow.Visible = FalseEnd sub或：Sub 隐藏工作簿()Workbooks(book).Windows(1).Visible=FalseEnd sub对工作表

50、的保护1. 对工作表实现口令保护利用VBA调用Worksheet对象的Proteet方法对工作 表进行保护，Pro tee t带有以下参量：Password 用于保护工作表的口令。Drawingobjeets若为True,则对工作表中的Drawingobjeets对象进行保护，缺 省值为True。Con ten ts若为True，则对单元格内容进行保护，缺省值为True。 下面程序代码完成对工作表“基础数据表”的保护：Sub 保护工作表()Worksheets (基础数据表).Proteet Password:二1234End sub2. 对工作表实现隐藏保护，使他人无法看到工作表：利用VBA

51、设置Worksheet对象的Visible属性来隐藏工作表；Visible属性的值 为以下三个值中的一个：True 工作表为显示状态。False 工作表为隐藏状态。XlVerHidden 工作表为隐藏状态，且用户不能通过“取消隐藏”对话框将其改为 显示状态。当Visible的值为XlVerHidden时，只能利用VBA将其重新设置为 True。Sub 隐藏工作表()Workshee ts(“ 基础数据表).Visible二FalseEnd sub67、求:将所选区域中的数值全部转化为万元的最简代码. 求:将所选区域中的数值全部转化为万元的最简代码.或能完成此功能的最便捷的命令操作.写了一个,抛

52、砖引玉:Sub convt()Dim cel As RangeDim dec As VariantApplication.EnableEvents = Falseyesorno = MsgBox(确实将区域所有数值转换为“万元”？ , vbYesNo + vbQuestion + vbDefaultButton1)If yesorno = vbYes Then1:dec = Applica tion.Inpu tBox(请输入小数位数：，Default:=0, Type:=1)If dec = ThenGoTo 1End IfFor Each cel In SelectionIf IsNume

53、ric(cel.Value) Thencel = (Round(cel.Value / 10000， 2) & 万元 cel = (Round(cel.Value / 10000， dec) End IfNextElseIf yesorno = vbNo ThenExit SubEnd IfEnd Sub以下当为最简代码吧.Sub Macro2()Selection.NumberFormatLocal = #.#，End Sub68、在VBA开发环境中，点击工具一一附加控件，出现如下窗口： 在可选控件中选择microsoft orogressbar control,再点击确定，这时，你会 发现

54、在你的工具箱中增加了进度条工具，如图：下面说说进度条的用法：属性：Max：设置进度条控件的最大值Min：设置进度条控件的最小值Value :设置进度条控件对象的当前值Scrolling: 进度条的式样，即一格一格的进度条或是没有间隔的进度 条。例子：Private Sub UserForm_Activate()For i = 1 to 5000ProgressBar1.Max = 5000 设置进度条控件的最大值 ProgressBar1.Value = I进度条控件对象的当前值nextEnd Sub在程序中加入进度条的办法是将ProgressBarl.Max二最大值，ProgressBar1

55、.Value = 当前值放入循环中去。69、Excel是一个优秀的电子表格软件，如果你编的程序需要以报表的形式显示 最终结果，它会是个不错的选择。你可以通过VB控制Excel显示数据表格。如 果你不愿意深入了解Excel这些琐碎的细节，也不是没有权宜之计：你可以打开 Excel中的工具一宏一录制新宏，然后进行手工操作，结束后把所录的宏代码贴 进你的VB程序就行。这样得到的程序一般都能正常运行，但其中的宏代码往往 不够简洁，效率和可读性都不高。Excel编程碰到的第一个问题是表头。有时表头的形式比较复杂，需要横 向或纵向合并单元格。请放心，只要没有斜杠，Excel都能应付得了。例如合并A2A5这

56、4个单元格，你录制的宏代码会是这样：Range(A2:A5).SelectWith Selection.HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom.WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = FalseEnd WithSelection.Merge而自己编程只要一句 Range.(“A2:A5”).mergecells=True 就可以解决 问题。表头形式定了，再就是表头的内容。如果单元

57、格中的文本长度超过了列宽， 往往只能显示部分内容，行尾那一格的内容则会“越境”进入右边那个空白单元 格，很不美观。这个问题可以通过在程序中设置列宽加以解决。Co l umns(14).c o l umnwidth=12 设置第14列列宽为12(缺省列宽为8.38) 如果你不愿意劳神去逐列估计实际所需的列宽，干脆来一行Columns(“a:i).autofit a 到 i 列自动调整列宽让Excel随机应变吧。但也许你不喜欢这种方法，认为表头撑大了列宽，弄得浏览一张小表格还 得向右滚动，太不方便了。要是能保持默认列宽，让文本自动换行就好了。没问 题，Excel包你满意。Rows(3).WrapT

58、ext=True 让第三行各单元格中的文本自动换行 不过你最好再加一句 Rows(3) .VerticalAlignment = xlTop 让表头自动 向上对齐，这样比较符合习惯。你还可以给表头打上底色，让你的读者不至于看了打哈欠。Rows(2). Interior .ColorIndex = 5 设置第2行底色为蓝色 再给表格的标题上色，这样更醒目一点。Rows(1).Font.ColorIndex=4 表头完成后该填数据了，一个一个地填实在是太? ，如果你的数据是存放 在一个二维数组中，那问题就简单多了。Dim Data(3,4) 数据处理Range(“a2:d4”).Value=Dat

59、a这样可以一次填入一个表的所有数据，够快了吧！不过提醒一句，Range 对象大小最好与数组匹配，小了无法显示所有数据，大了则会在空白单元格只填 入“ N/A ”表示没有取得数据。如果需要在结果中显示多个同样规格的数据表，想在Range对象中加入循 环变量，这也好办。Dim cell11,cell2 Dim Data(3,4)For I =1 to 40 数据处理Set cell1=Worksheets(Sheet1).Cells(5*I-4,1)Set cell2=Worksheets(Sheet1).Cells(5*I-2,4) Worksheets(Sheet1).Range(cell1,cell2).value=DataNext I 表格填完了，现在该打表格线了，以下几条语句可以满足你的要求With Worksheets(Sheet1).Range(cell1,cell2).borders .LineStyle=xlContinuous.weight=xlThinEnd With