《VBA应用基础》PPT课件.ppt

,VBA（VisualBasicforApplications），是Office的内置编程语言，是Office的重要组件。是基于VB（VisualBasic）发展起来的，与VB有很好的兼容性。它可以将繁琐、机械的日常工作自动化，提高办公效率。,VBA与VB主要有以下区别：VB用于创建标准的应用程序，VBA是使已有的应用程序（Office）自动化。VB具有自己的开发环境，VBA寄生于已有的应用程序（Office）。VB开发出的应用程序可以是可执行文件（EXE文件），VBA开发的程序必须依赖于它的父应用程序（Office）。,用VBA可以实现如下功能：使重复的任务自动化。将Office作为开发平台，进行应用软件开发。对数据进行复杂的操作和分析。,用Office作为开发平台有以下优点：VBA程序只起辅助作用。许多功能Office已经提供，可以直接使用，简化了程序设计。通过宏录制，可以部分地实现程序设计的自动化，大大提高软件开发效率。便于发布。只要发布含有VBA代码的文件即可。无需安装和卸载，不影响系统配置，属于绿色软件。Office界面对于广大计算机应用人员来说比较熟悉，符合一般操作人员的使用习惯，便于软件推广应用。用VBA编程比较简单，即使非计算机专业人员，也可以很快编出自己的软件。,3.1用录制宏的方法编写VBA程序3.1.1宏的安全性,宏（Macro）是一组VBA语句。可以理解为一个程序段，或一个子程序。宏可以直接编写，也可以通过录制形成。为了防止“宏病毒”，Office提供了一种安全保护机制，就是设置“宏”的安全性。当我们需要频繁使用带有VBA代码的应用软件时，完全可以把“宏”的安全性设置为“低”，而把宏病毒与其他成千上万种病毒同样对待，用统一的防护方式和防毒工具进行防毒。,3.1用录制宏的方法编写VBA程序3.1.2宏的录制与保存,录制宏：启动Excel。在“工具”“宏”菜单中选择“录制新宏”命令。在“录制新宏”对话框中输入宏名，单击“确定”按钮。进行需要的操作。单击“停止录制”工具栏的“停止录制”按钮，结束宏录制过程。也可以选择“工具”“宏”“停止录制”菜单命令结束宏录制。,3.1用录制宏的方法编写VBA程序3.1.2宏的录制与保存,执行宏：选择“工具”“宏”“宏”菜单命令，在“宏”对话框中选择一个宏名，单击“执行”按钮。保存宏：将宏保存在当前工作簿或新工作簿，只有该工作簿打开时，相应的宏才可以使用。如果需要让某个宏在多个工作簿都能使用，就应当将宏保存于个人宏工作簿中。,3.1用录制宏的方法编写VBA程序3.1.3宏代码的分析与编辑,编辑宏：选择“工具”“宏”“宏”菜单命令，在“宏”对话框中选择一个宏,单击“编辑”按钮。1.代码分析开始、结束语句。注释语句。With语句。,3.1用录制宏的方法编写VBA程序3.1.3宏代码的分析与编辑,2.代码编辑删除多余的语句、属性和参数。加入判断或循环等无法录制的语句。使用字符串连接运算符“&”。循环控制语句For.Next的语法、执行过程、注意事项。宏的录制、编辑、运行等操作还可以通过“VisualBasic”工具栏进行。在“工具”“宏”菜单中选择“VisualBasic编辑器”命令，或用Alt+F11快捷键，可以直接打开VisualBasic编辑器。删除宏：在“工具”“宏”菜单中选择“宏”命令，选择宏名，单击“删除”按钮。,3.1用录制宏的方法编写VBA程序3.1.4用其他方式执行宏,1.用快捷键执行宏录制宏时，在“录制新宏”对话框中可以直接指定快捷键。录制宏后，选择“工具”“宏”“宏”菜单命令，在“宏”对话框，选择宏，再单击“选项”按钮，进行设置。,3.1用录制宏的方法编写VBA程序3.1.4用其他方式执行宏,2.用按钮执行宏在Excel中，右击工具栏，在弹出的菜单中选择“窗体”命令，显示“窗体”工具栏。单击“窗体”工具栏中的“按钮”控件。在当前工作表的适当位置按下鼠标左键并拖动鼠标，将命令按钮就添加到工作表中，系统自动显示“指定宏”对话框。从“指定宏”对话框中选择一个宏，单击“确定”。修改按钮的标题。单击按钮外的任意位置，结束按钮设计。此后，单击按钮就可以运行该宏。,3.1用录制宏的方法编写VBA程序3.1.4用其他方式执行宏,3.用图片或工具栏按钮执行宏指定宏到图片：用“插入”“图片”菜单命令或其他方法在当前工作表放置图片后，右击图片，在快捷菜单中选择“指定宏”命令即可进行设置。用同样的方法可以给自选图形、艺术字、文本框、组织结构图指定宏。,3.1用录制宏的方法编写VBA程序3.1.4用其他方式执行宏,将宏指定给工具栏按钮：在Excel中，选择“工具”“自定义”菜单命令。在“自定义”对话框的“命令”卡中，从“类别”列表框中选择“宏”，从“命令”列表框中将“自定义按钮”拖动到任意一个工具栏上。右击该按钮，选择“指定宏”，显示“指定宏”对话框。选择需要的宏名并单击“确定”按钮。单击“关闭”按钮，关闭“自定义”对话框。,3.1用录制宏的方法编写VBA程序3.1.4用其他方式执行宏,从工具栏中删除自定义的按钮：选择“工具”“自定义”菜单命令，在显示出“自定义”对话框的情况下，用鼠标将按钮从工具栏中拖出即可。创建新的工具栏：在“自定义”对话框的“工具栏”卡中，单击“新建”按钮，在“新建工具栏”对话框中输入工具栏名，然后单击“确定”按钮。,3.2变量和运算符3.2.1变量与数据类型,1变量程序运行时，变量的值可以改变。可以用变量来存储数据或对象。【例3-1】在宏代码中使用变量。,3.2变量和运算符3.2.1变量与数据类型,2变量的数据类型变量的数据类型决定变量允许保存何种类型的数据。表3-1列出了VBA支持的数据类型，列出了各种类型的变量所需要的存储空间和能够存储的数据范围。我们不必记住每一种数据的精确范围，但应该知道数据类型名和大致范围，以便选用合适的数据类型。,3.2变量和运算符3.2.1变量与数据类型,3声明变量变量在使用之前，最好进行声明，也就是定义变量的数据类型，这样可以提高程序的可读性和节省存储空间。在不关心存储空间，而注重简化代码、突出重点的情况下，可以不经声明直接使用变量。声明语句放到过程中，该变量在过程内有效。声明语句若放到模块顶部，则变量在模块中有效。,3.2变量和运算符3.2.1变量与数据类型,例如：下面语句创建了变量strName并且指定为String数据类型。DimstrNameAsString下形式的Public语句声明公共变量。PublicstrNameAsString在下面的语句中，变量intX、intY、与intZ被声明为Integer类型。DimintXAsInteger,intYAsInteger,intZAsInteger下面的语句为工作表的新建实例声明了一个变量。DimXAsNewWorksheet如果定义对象变量时没有使用New关键字，则在使用该变量之前，必须使用Set语句将该引用对象的变量赋值为一个已有对象。,3.2变量和运算符3.2.1变量与数据类型,4声明数组数组是具有相同数据类型并共用一个名字的一组变量的集合。数组中的不同元素通过下标加以区分。数组也用Dim或Public语句来声明。若数组的大小被固定的话，则它是静态数组。若程序运行时数组的大小可以被改变，则它是个动态数组。数组的下标从0还是从1开始，可用OptionBase语句进行设置。标默认从0开始。,3.2变量和运算符3.2.1变量与数据类型,下面这行代码声明了一个11行乘以11列的Integer型二维数组：DimMyArray(10,10)AsInteger下面语句声明的是一个动态数组：DimsngArray()AsSingle动态数组声明后，可以在程序中用ReDim语句重新定义数组的维数以及每个维的上界。重新声明数组，数组中存在的值一般会丢失。若要保存数组中原先的值，可以使用ReDimPreserve语句来扩充数组。下列的语句将varArray数组扩充了10个元素，而数组中原来值并不丢失。ReDimPreservevarArray(UBound(varArray)+10),3.2变量和运算符3.2.1变量与数据类型,5给变量赋值给变量或数组元素赋值，通常使用赋值语句。【例3-2】变量和数组的赋值。在一行中写几个语句，需要用“：”来分开不同语句。一个语句太长，希望将其分开写成几行，用到空格加下划线“_”作为断行标记。,3.2变量和运算符3.2.1变量与数据类型,6常量静态信息可以用常量表示，它可以供程序多次使用而且便于记忆。例如，要声明一个常量来保存圆周率，可以使用以下语句：ConstPIAsSingle=3.1415926在过程中声明，只能在该过程中使用。在模块顶部声明，在该模块的所有过程中都可以使用。公共级常量必须在模块顶部的“通用声明”区域中定义，它可以在应用程序的任何过程中使用。例如，下面语句可以创建一个公共级常量E：PublicConstEAsSingle=2.7182,3.2变量和运算符3.2.2运算符,3.2变量和运算符3.2.2运算符,3.2变量和运算符3.2.2运算符,3.2变量和运算符3.2.2运算符,4连接运算符字符串连接运算符有两个：“&”和“+”。例如：MyStr=Hello&World返回HelloWorldMyStr=Check&123返回Check123MyNumber=34+6返回40MyNumber=34+6返回346（字符串被串接起来）,3.2变量和运算符3.2.2运算符,5运算符的优先级按优先级由高到低的次序排列的运算符如下：括号指数一元减乘法和除法整除取模加法和减法连接比较逻辑（Not、And、Or）。,3.2变量和运算符3.2.2运算符,【例3-3】百钱买百鸡问题。假设公鸡每只5元，母鸡每只3元，小鸡3只1元。要求用100元钱买100只鸡，问公鸡、母鸡、小鸡可各买多少只？请编一个VBA程序求解。分析、编程、测试。Chr函数,3.2变量和运算符3.2.2运算符,If语句有三种语法形式：(1)ifthenelse(2)ifthenelseendif(3)ifthenelseifthen.elseendif,3.2变量和运算符3.2.2运算符,例如，根据一个字符串是否以字母A到F、G到N或O到Z开头来设置整数值。程序段如下：DimstrMyStringAsString,strFirstAsString,intValAsIntegerstrFirst=Mid(strMyString,1,1)IfstrFirst=AAndstrFirst=GAndstrFirst=OAndstrFirstLimitThenc.Interior.ColorIndex=27EndIfNextcEndSubForEach.Next语句语法形式：ForEachInExitForNext,3.5工作簿、工作表和单元格3.5.2单元格和区域的引用方式,5.相对引用与多区域引用【例3-30】将位于活动工作表上活动单元格下1行和右3列的单元格设置为双下划线格式。ActiveCell.Offset(1,3).Font.Underline=xlDouble【例3-31】清除当前工作表上3个区域的内容。Range(C5:D9,G9:H16,B14:D18).ClearContents【例3-32】将该组合区域的字体设置为加粗。SubMRange()Dimr1,r2,myMRAsRangeSetr1=Sheets(Sheet1).Range(A1:B2)Setr2=Sheets(Sheet1).Range(C3:D4)SetmyMR=Union(r1,r2)myMR.Font.Bold=TrueEndSub,3.5工作簿、工作表和单元格3.5.3对单元格和区域的操作,1.选定和激活单元格【例3-34】单元格D8中输入公式，而不选定单元格。Range(D8).Formula=SUM(D1:D7)Select方法激活工作表和工作表上的对象Selection属性返回选定的对象在工作表Sheet1的A1和B1单元格输入文字“姓名”和“地址”，并设置为粗体：SubMacro1()Sheets(Sheet1).SelectRange(A1).SelectActiveCell.FormulaR1C1=姓名Range(B1).SelectActiveCell.FormulaR1C1=地址Range(A1:B1).SelectSelection.Font.Bold=TrueEndSub,3.5工作簿、工作表和单元格3.5.3对单元格和区域的操作,【例3-35】将Sheet1中的第1行复制到Sheet2上。SubCopyRow()Worksheets(Sheet1).Rows(1).CopyWorksheets(Sheet2).SelectWorksheets(Sheet2).Rows(1).SelectWorksheets(Sheet2).PasteEndSub【例3-36】选定一个区域，然后激活该区域内的一个单元格。SubMakeActive()Worksheets(Sheet1).ActivateRange(A1:D4).SelectRange(B2).ActivateEndSub,3.5工作簿、工作表和单元格3.5.3对单元格和区域的操作,2.处理活动单元格ActiveCell属性返回活动单元格Activate方法指定活动单元格【例3-37】使B5成为活动单元格，然后将其字体设置为加粗。SubSetA()Worksheets(Sheet1).ActivateRange(B5).ActivateActiveCell.Font.Bold=TrueEndSub【例3-38】选定区域、插入文本、右移。SubMoveA()Worksheets(Sheet1).ActivateRange(A1:D10).SelectActiveCell.Value=姓名ActiveCell.Offset(0,1).ActivateEndSub,3.5工作簿、工作表和单元格3.5.3对单元格和区域的操作,3.在单元格区域中循环【例3-40】在区域C1:C20中循环，将所有绝对值小于10的数字都设置红色。Subtest()Forcnt=1To20Setcurc=Worksheets(sheet1).Cells(cnt,3)curc.Font.ColorIndex=0先置成黑色IfAbs(curc.Value)10Thencurc.Font.ColorIndex=3若小于10则改成红色NextcntEndSub,3.5工作簿、工作表和单元格3.5.3对单元格和区域的操作,求数据区末尾行号：r=Range(A1).End(xlDown).Row求A1单元格数据区末尾行号r=Cells(1,1).End(xlDown).Row求A1单元格数据区末尾行号r=Range(A65536).End(xlUp).Row求A列数据区末尾行号r=Cells(65536,1).End(xlUp).Row求A列数据区末尾行号r=Columns(1).End(xlDown).Row求A列数据区末尾行号求数据区末尾列号：c=Range(A1).End(xlToRight).Column求A1单元格数据区末尾列号c=Cells(1,1).End(xlToRight).Column求A1单元格数据区末尾列号c=Cells(1,256).End(xlToLeft).Column求第1行数据区末尾列号c=Rows(1).End(xlToRight).Column求第1行数据区末尾列号,3.5工作簿、工作表和单元格3.5.3对单元格和区域的操作,【例3-41】在区域A1:D10中循环，将所有绝对值小于10的数字都设置为红色。Subtest()ForEachcInWorksheets(Sheet1).Range(A1:D10).CellsIfAbs(c.Value)10Thenc.Font.ColorIndex=3NextEndSub【例3-42】在数据区循环，将所有绝对值小于10的数字都设置为红色。Subtest()ForEachcInActiveCell.CurrentRegion.CellsIfAbs(c.Value)10Thenc.Font.ColorIndex=3NextEndSub,3.5工作簿、工作表和单元格3.5.3对单元格和区域的操作,4.处理三维区域【例3-44】设置三维单元格区域的边框格式。SubFmSheets()Sheets(Array(Sheet2,Sheet3,Sheet5).SelectRange(A1:H1).SelectSelection.Borders(xlBottom).LineStyle=xlDoubleEndSub【例3-45】将Sheet2上指定区域的格式和内容复制到所有工作表相应区域。SubFillAll()Worksheets.FillAcrossSheets(Worksheets(Sheet2).Range(A1:H1)EndSub,3.5工作簿、工作表和单元格3.5.4自动生成年历,在Excel中，为任意指定的年份生成完整的年历。界面设计：字体、字号、字体颜色、填充颜色、边框、列宽、行高。子程序GenerateCalendar分析、运行。,3.6工作表函数与图形3.6.1在VBA中使用Excel工作表函数,1.在VBA中调用工作表函数【例3-46】使用Min工作表函数求出某个区域中的最小值。SubUF()DimmyRAsRangeSetmyR=Worksheets(Sheet1).Range(A1:C10)answer=Application.WorksheetFunction.Min(myR)MsgBoxanswerEndSubVBA函数和Excel工作表函数可能同名，但作用和引用方式是不同的。,3.6工作表函数与图形3.6.1在VBA中使用Excel工作表函数,2.在单元格中插入工作表函数【例3-47】将RAND工作表函数应用于A1:B3区域。Worksheets(Sheet1).Range(A1:B3).Formula=RAND()【例3-48】用工作表函数Pmt来计算住宅抵押贷款的月支付额。SubFml()StaticlAmt,lInt,lTrmlAmt=Application.InputBox(Prompt:=贷款额：,Default:=lAmt,Type:=1)lInt=Application.InputBox(Prompt:=年利率：,Default:=lInt,Type:=1)lTrm=Application.InputBox(Prompt:=年限：,Default:=lTrm,Type:=1)lPmt=Application.WorksheetFunction.Pmt(lInt/1200,lTrm*12,lAmt)MsgBox月支付额：&Format(lPmt,Currency)EndSub程序运行时，分别输入“贷款额”、“年利率”、“年限”为80000、5.6、20，得到“月支付额：-￥555”。,3.6工作表函数与图形3.6.2处理图形对象,1.设置图形的属性【例3-49】设置指定图形的填充前景色。Worksheets(1).Shapes(1).Fill.ForeColor.RGB=RGB(255,0,0)2.使用ShapeRange集合【例3-50】将选定的图形填充绿色。Subtest()Setsr=Selection.ShapeRangesr.Fill.ForeColor.SchemeColor=17EndSub,3.6工作表函数与图形3.6.2处理图形对象,【例3-51】假设在Excel当前工作簿的第一张工作表上建立了两个图形，并分别命名为“Sp1”和“Sp2”。下面程序图形应用渐变填充格式。Subtest()SetmyD=Worksheets(1)SetmyR=myD.Shapes.Range(Array(Sp1,Sp2)myR.Fill.PresetGradientmsoGradientHorizontal,1,msoGradientBrassEndSub【例3-52】在工作表上对所有图形进行循环，更改每个自选图形的前景色。Subtest()SetmyD=Worksheets(1)ForEachshInmyD.ShapesIfsh.Type=msoAutoShapeThensh.Fill.ForeColor.RGB=RGB(255,0,0)EndIfsh.Fill.Visible=msoTrueNextEndSub,3.6工作表函数与图形3.6.3多元一次方程组求解,设计一个小软件，对任意一个多元一次方程组求解。1界面初始化程序设计三部分：系数矩阵a、向量b、解向量x。初始化子程序。2求解方程组程序设计原理：用工作表函数，先计算系数矩阵a的逆矩阵，再与向量b进行矩阵相乘就得到了向量x。运行、测试。,3.6工作表函数与图形3.6.4创建动态三维图表,建立工作簿，在工作表中输入数据。用“图表向导”，创建“三维柱形图”。用宏录制方法，获得“设置三维视图格式”上下仰角、左右旋转代码。编写子程序“动态效果”。运行子程序。,3.7在工作表中使用控件,1.“窗体”工具栏控件在Excel工具栏上单击鼠标右键，选择“窗体”命令，打开“窗体”工具栏。有16个按钮，9个是可以放到工作表上的控件。将控件添加到工作表上：在“窗体”工具栏中单击需要的控件，在工作表中按下鼠标左键并拖动。设置控件的格式：在控件上右击，选择“设置控件格式”命令。例：滚动条、复选框，“设置控件格式”对话框，“控制”选项卡，“单元格链接”。给控件改名：用鼠标右击控件，选择“编辑文字”命令。为控件指定宏：在控件上右击鼠标，选择“指定宏”命令。,3.7在工作表中使用控件,2.“控件工具箱”工具栏控件在Excel工具栏上单击鼠标右键，选择“控件工具箱”命令，打开“控件工具箱”工具栏。设计模式，可以对控件的属性、代码进行设计。运行模式，可执行代码。单击“属性”按钮，可以打开“属性”窗口，进行设置或显示控件的属性。右击控件，选择“属性”命令，也可以打开“属性”窗口。单击“查看代码”按钮，可以进入VBA编辑器环境，查看或编写控件的代码。右击控件，选择“查看代码”命令，也可以查看或修改代码。,3.7在工作表中使用控件,3.在工作表上处理控件例3-54】向工作表添加命令按钮。Subacb()Worksheets(1).OLEObjects.AddForms.CommandButton.1,_Left:=200,Top:=200,Height:=20,Width:=100EndSub【例3-55】滚动工作簿窗口，使CommandButton1位于窗口的左上角。Subtest()Sett=Sheet1.CommandButton1.TopLeftCellWithActiveWindow.ScrollRow=t.Row.ScrollColumn=t.ColumnEndWithEndSub,3.8使用Office命令栏3.8.1添加和修改工具栏,对工具栏作运行时间修改【例3-56】创建包含一个命令按钮的命令栏，用代码改变按钮外观。进入Excel的VBA编辑器，插入一个模块，在模块中输入如下三个过程：SubCreateCB()SetmyBar=CommandBars.Add(Name:=cbt,Position:=msoBarTop)myBar.Visible=TrueSetoldc=myBar.Controls.Add(Type:=msoControlButton,ID:=23)oldc.OnAction=ChangeFacesEndSubSubChangeFaces()Setnewc=CommandBars.FindControl(Type:=msoControlButton,ID:=19)newc.CopyFaceSetoldc=CommandBars(cbt).Controls(1)oldc.PasteFaceEndSubSubDelCB()CommandBars(cbt).DeleteEndSub,3.8使用Office命令栏3.8.1添加和修改工具栏,2.添加和修改组合框控件【例3-57】在自定义工具栏中添加一个组合框。Subtest()SetmyBar=CommandBars.Add(Name:=Custom,_Position:=msoBarTop,Temporary:=True)myBar.Visible=TrueSetnewCombo=myBar.Controls.Add(Type:=msoControlComboBox)WithnewCombo.AddItemQ1.AddItemQ2.AddItemQ3.AddItemQ4.Style=msoComboNormal.OnAction=STOQEndWithEndSub,3.8使用Office命令栏3.8.2管理菜单栏和菜单项,1.在运行时间添加菜单栏【例3-58】添加一个菜单栏，固定在应用程序窗口的右边。Subtest()SetMenuBar=CommandBars.Add(Name:=mBar,_Position:=msoBarRight,MenuBar:=True)WithMenuBar.Protection=msoBarNoMove.Visible=TrueEndWithEndSub,3.8使用Office命令栏3.8.2管理菜单栏和菜单项,2.在运行时间修改菜单栏、菜单项【例3-59】在系统活动菜单栏的末尾添加一个名为“Custom”的临时弹出式菜单项，然后在该菜单项中添加一个按钮控件“Import”。Subtest()SetmyM=CommandBars.ActiveMenuBarSetnewM=myM.Controls.Add(Type:=msoControlPopup,Temporary:=True)newM.Caption=CustomSetctrl1=newM.Controls.Add(Type:=msoControlButton,ID:=1)ctrl1.Caption=ImportEndSub,3.8使用Office命令栏3.8.3添加和显示快捷菜单,【例3-60】新建一个快捷菜单，在其中添加两个菜单项（带标题），然后用ShowPopup方法显示该菜单。Subtest()Setcapm=CommandBars.Add(Name:=Custom,_Position:=msoBarPopup,Temporary:=True)SetCopy=capm.Controls.AddWithCopy.FaceId=23.Caption=复制EndWithSetPaste=capm.Controls.AddWithPaste.FaceId=17.Caption=图表向导EndWithcapm.ShowPopup200,200EndSub,3.8使用O