VBA程序开发语句集锦

ExcelVBAPROGRAMTECHNOLOGYITEMCODESUBJECTCONTENTS(ExcelVBA)1Event选择改变事件PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)EndSub当重新选择工作 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 中单元格时，此函数被调用(被激活)执行，其中Target.Row为选中单元格的行号，其中Target.Column为选中单元格的列号。2Event工作表单元格内容改变事件PrivateSubWorksheet_Change(ByValTargetAsRange)EndSub当改变了单元格的内容时，此函数被调用(被激活)执行，其中Target.Row为选中单元格的行号，其中Target.Column为选中单元格的列号。3Sentence为某个单元格的内容赋值赋予文本内容：Worksheet("SheetName").Cells(行号,列号).Formula="赋值内容"赋予数值内容：Worksheet("SheetName").Cells(行号,列号).Value=所赋的数值4Sentence为某个Excel文件薄内单元赋值Workbooks("Book1.xls").Worksheets("Sheet1").Cells(1,1).Formula="赋值内容"Workbooks("Book1.xls").Worksheets("Sheet1").Cells(1,1).Value=所赋的数值5Switch关闭屏幕更新Application.ScreenUpdating=False执行以上语句后，将停止屏幕显示更新动作，可以达到提高运算速度、避免屏幕闪动的效果6Sentence查找数据区域某列的最大行号RowMax=Workbooks(ExcelBookName).Worksheets(SheetName).Cells(65536,C1).End(xlUp).Row其中C1为指定的某列的列号7Sentence查找数据区域某行的最大列号ColMax=Workbooks(ExcelBookName).Worksheets(SheetName).Cells(R1,255).End(xlToLeft).Column其中R1为指定的某行的行号获取数据区的最大列号获取第3行的最大列号：Worksheets(1).cells(3,255).End(xlToLeft).Column8Sentence复制工作表内所有单元格Cells.Copy9Switch关闭预警设置Application.DisplayAlerts=False(Application.DisplayAlerts=True)当执行文件存盘，在存储目录中有相同名称的文件时，如果执行了以上语句，则不再提出预警一般在使用完以上语句后，则立即执行括号内的对应语句，避免不可预知的错误发生10Switch关闭文件时不执行存盘动作Workbooks(File).Closesavechanges:=False11Function从一个字符串中截取一个子串Mid(string,start,x)；其中string为原字符串，start为开始位置，x为截取子字符串的长度也可以使用函数:Right(text,num_chars)和Left(text,num_chars)返回子字符串12Function计算两个日期之间的天数、月份语法：=DATEDIF(start_date,end_date,unit)数、年数1.Start_date：代表一段时期的第一个日期或起始日的日期。日期可以放在引号内作为文本字符串输入（如"2001-1-30"），也可以作为序列数（如36921，如果使用的是1900日期系统，则它代表2001年130日）输入，或作为其他 公式 小学单位换算公式大全免费下载公式下载行测公式大全下载excel公式下载逻辑回归公式下载 或函数的结果（如DATEVALUE("2001-1-30")）输入。2.End_date：代表一段时期的最后一个日期或结束日的日期。3.Unit：要返回的信息的类型："Y","M","D","MD","YM","YD""YD"表示返回忽略了年份的天数;"MD"表示返回忽略了年份和月份的天数13Switch强制要求对变量进行定义OptionExplicit在模块的开始部分执行以上语句，则所有变量必须先行定义之后才能使用14Sentence使用程序在单元格内写入公式ActiveSheet.Cells(4,11).FormulaR1C1="=SUM(R[2]C:R[65532]C)"15Sentence判断语句1)If…Then…Else…;If…Then…Else…EndIf(此形式为多行写法)IfconditionThen[statements][Elseelsestatements]［EndIf]2)Select…Case…Case…CaseElse…EndCase16Sentence循环语句1)Forcounter=startToend[Stepstep]…[ExitFor]…Nextcounter2)ForEachelementIngroup…[ExitFor]…Next3)Do{while|until}condition…[ExitDo]…Loop4)Do…[ExitDo]…Loop{while|until}condition17Sentenceauto_openandauto_closeSubauto_open()…SubEnd;Subauto_close()…SubEnd当Exel文件打开时或者关闭时执行的模块18FunctionExcel定时器Application.OnTimeNow+TimeValue("00:05:00")"SaveIt"以上语句的功能为，当执行以上语句5分钟后，执行过程SaveIt，从而起到延时功能Application.OnTimeTimeValue("17:00:00"),"Show_my_msg"以上语句的功能为，当执行以上语句后，在之后的17点钟调用过程Show_my_msg19Sentence调用Windows打开文件对话框FileName=Application.GetOpenFilename("Excel文件(*.xls),*.xls")执行以上语句后，即出现Windows打开文件对话框，选择文件后，变量FileName的内容即为需要打开文件的路径名和文件名20Switch显现和隐藏工作表隐藏工作表：Sheets("SheetName").Visible=False显现工作表：Sheets("SheetName").Visible=True21Switch删除工作表上的按钮ActiveSheet.Shapes("CommandButton1").Delete22Sentence当前工作薄另存ActiveWorkbook.SaveAsFileName:=FilePatch&FileName23File关闭当前工作薄ActiveWindow.Close24Exprresion使用公式计算行号(列号)返回当前的行号：=row()返回当前的列号：=Column()25ErrorByRef参数不符的原因1.传递的变量未定义参数类型，系统默认为Variant类型，导致传递时类型不符；2.变量定义方式不正确：如DimI,jasInteger,中的i认为没有定义26Serface各个常用工具栏的显示和隐藏Application.CommandBars("WorksheetMenuBar").Enabled=True'显示菜单栏,False隐藏Application.CommandBars("ToolbarList").Enabled=True'显示工具栏,False隐藏下同Application.CommandBars("Standard").Visible=True'显示常用工具栏Application.CommandBars("Formatting").Visible=True'显示格式工具栏Application.DisplayFormulaBar=True'显示编辑栏WithActiveWindow.DisplayGridlines=True'显示网格.DisplayHeadings=True'显示行号列标.DisplayOutline=True'显示分级显示符号.DisplayZeros=True'显示零值.DisplayHorizontalScrollBar=True'显示水平滚动条.DisplayVerticalScrollBar=True'显示垂直滚动条.DisplayWorkbookTabs=True'显示工作表标签27Event文件关闭事件PrivateSubWorkbook_BeforeClose(CancelAsBoolean)EndSub28Event失去焦点事件PrivateSubWorkbook_WindowDeactivate(ByValWnAsWindow)EndSub28Sentence使用语句设置单元格公式在当前A1单元格内输入公式：=C2，则分别表现为Formula与FormulaR1C1的区别Cells(1,1).Formula="=C2"Cells(1,1).FormulaR1C1="=R[1]C[2]"'其中1与2为单元格相对偏移量29SentenceOnErrorResumeNext说明当一个运行时错误发生时，控件转到紧接着发生错误的语句之后的语句，并在此继续运行。30PublicConst用于在文件级强制对该文件中的所有变量进行显式声明31SentencePublic用于定义公用的全局变量32FunctionASCII码与字符之间的转换函数Chr(65)的值为A；Asc("A")的值为6533SentenceOptionExplicit用在模块的头部，要求强制对使用的变量进行预定义34OperationShift键以图片格式复制选定一单元格区域，按住Shift键，同时在主菜单中：编辑＝>复制图片35Operation转置粘贴使用选择性粘贴，选择对话框中的转置选项即可。36SentenceVBA生成下拉选单的设置1.设置下拉选单，同时不允许输入选单意外的内容Cells(I,j).Validation.DeleteCells(I,j).Validation.AddType:=xlValidateList,Formula1:="SelectList"2.设置下拉选单，但是可以输入选单以外的内容Cells(I,j).Validation.DeleteCells(I,j).Validation.AddType:=xlValidateList,Formula1:="SelectList"Cells(I,j).Validation.ShowErrow=False37Sentence禁止/启用事件触发1.禁止所有事件：Application.EnableEvents=False2.启用所有事件：Application.EnableEvents=True38Operation当VBA陷入死循环时如何处理当VBA陷入死循环时，按快捷键：Ctrl+Break即可停止循环39Sentence删除整行或整列删除第i行：Rows(CStr(i)&":"&CStr(i)).Delete删除第j行：Columns("A:C").Delete,列号必须使用字母40Sentence显示或者隐藏工作表标签ActiveWindow.DisplayWorkbookTabs=FalseActiveWindow.DisplayWorkbookTabs=True41Sentence显示或者隐藏编辑栏目Application.DisplayFormulaBar=FalseApplication.DisplayFormulaBar=True42Sentence设置数据有效性Cells(i,j).Validation.DeleteCells(i,j).Validation.AddType:=xlValidateList,Formula1:="个,只,根"Cells(i,j).Validation.ShowError=False43Sentence字符串函数IsStr(Number,String,SubString)asInteger函数功能说明：在字符串String中，从第Number位开始查找，与子字符串相同的字符串，没有找到返回0，找到了返回第一次找到的位置号44Sentence在第i行上面插入一个空行Worksheets("Sheet1").Rows(Cstr(i)&":"&Cstr(i)).InsertShift:=xlDownWorksheets("Sheet1").Rows(i).Insert45Sentence区域内容排序Worksheets("Sheet1").Range("D2:E9").SortKey1:=Range("D2")46Sentence设置/取消单元格的锁定属性取消选定区域单元格的锁定属性：Worksheets("Sheet1").Range("G2:L9").Locked=False设置选定区域单元格的锁定属性：Worksheets("Sheet1").Range("G2:L9").Locked=True47Sentence设置/取消工作表的保护功能取消工作表保护：Worksheets("Sheet1").Unprotect设置工作表保护：Worksheets("Sheet1").ProtectContents:=True,AllowInsertingRows:=True可以选择未锁定单元格：Worksheets("Sheet1").EnableSelection=xlUnlockedCells所有选项如下：ActiveSheet.ProtectDrawingObjects:=True,Contents:=True,Scenarios:=True,AllowFormattingCells:=True,AllowFormattingColumns:=True,AllowFormattingRows:=True,AllowInsertingRows:=True,AllowInsertingHyperlinks:=True48Sentence设置单元格填充颜色Worksheets("Sheet1").Cells(1,3).Interior.ColorIndex=3649Sentence设置单元格字体颜色Worksheets("Sheet1").Cells（1,1).Font.ColorIndex=050Sentence设置工作表的选择属性值Worksheets(1).xlEnableSelection的值可为以下XlEnableSelection常量之一:仅当工作表处于保护状态时，本属性才能起作用xlNoSelection防止在工作表上进行任何选择操作xlNoRestrictions允许选定任意单元格xlUnlockedCells仅允许Locked属性为False的单元格才能被选定例如：Worksheets(1).xlEnableSelection=xlUnlockedCells51Sentence设置工作表活动区域A1:F20之外的区域不能选定，也不能滚动：Worksheets(1).ScrollArea="A1:F20"整个工作表可以选择和滚动：Worksheets(1).ScrollArea=""52Sentence整行或整列隐藏/显示整行显隐：Rows("6:12").EntireRow.Hidden=True/False整列显隐：Columns("G:J").EntireColumn.Hidden=True/False53Sentence获取当前文件所在路径名Application.ThisWorkbook.Path54Sentence获得客户使用区域的最大行号RowMax=ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).RowMaxRow=ActiveSheet.UsedRange.Rows.CountMaxColumn=ActiveSheet.UsedRange.Column.Count55Sentence获取当前应用程序名称ProgramName=ActiveWorkbook.Name56Function隐藏＃DIV/0！错误字样方法一：修改公式=IF(ISERROR("原公式内容"),"","原公式内容")，这样就不会出现错误提示了方法二：用条件格式=将条件1设为:公式=ISERROR(F2)点"格式"->"字体"->"颜色",选中白色方法三：一个更加简单的方法，点"文件"->"页面设置"->"工作表",在"错误单元格打印为"右边选中"空白",点"确定"。这样预览和打印时就可以自动隐藏错误信息。57FunctionPower(number,power)返回某数字number的power次方的结果58FunctionSqrt(number)返回某数字number的平方根59FunctionStdevp(number1,…,numbern)返回数字number1至numbern的 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 差.60Function计算工作天数的方法NETWORKDAYS函数的用法是：NETWORKDAYS(start_date,end_date,holidays)Start_date：为一个代表开始日期的日期。21End_date：为终止日期。40787.0Holidays：表示不在工作日历中的一个或多个日期所构成的可选区域,函数将自动扣除区域内的日子40817.0这是一个扩展函数,需要Excel增加扩展宏40798.061FunctionVLookUP()语法:=VLookUP(lookup_value,table_array,col_index_num,range_lookup)1.Lookup_value为“需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。2.Table_array为“需要在其中查找数据的数据表”，可以使用单元格区域或区域名称等。3.Col_index_num为table_array中待返回的匹配值的列序号。4.Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。1)如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值2)如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。62FunctionUpper()将字符窜中的小写字符转换为大写字符,其它字符不变63Sentence返回工作表中已使用的行数ActiveSheet.UsedRange.Rows.Count‘当前工作表中已使用的行数64Sentence返回工作表中已使用的列数ActiveSheet.UsedRange.Columns.Count'当前工作表中已使用的列数65Sentence当前工作表中已使用的行数ActiveSheet.UsedRange.Rows.Count‘当前工作表中已使用的行数66Sentence清除选定区域内容Selection.ClearContents'清除选定区域内容必要条件：清除区域所在的工作表必须是当前活动的工作表67Sentence将当前工作表移至工作表的最后ActiveSheet.MoveAfter:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)将当前工作表移至工作表的最后68Sentence设置快捷键Application.onKey“^I”,”macro”'设置Ctrl+I键为macro过程的快捷键69Sentence字符串大写与小写转换Lcase()andUcase()70Sentence将字符串拆分成一个一维数组Split(expression[,delimiter[,limit[,compare]]])71Operation加入加载宏.xlaxla是MicrosoftOfficeExcel的加载宏文件，.xla文件就是用EXCEL软件打开,如果我们有现成的宏文件，可以加载到excel中去。单击“工具”菜单的“加载宏”命令即可将宏加载到excel文档中打开Excel后文件另存为“保存类型”最后一项就是“MicrosoftOfficeExcel加载宏（*.xla）”直接按下Alt+F11组合健，也可打开vb编辑器,直接编辑加载宏.72SentenceGet.Cell函数的语法如下介绍Excel中Get.Cell函数的功能就是获取单元格的信息。Get.Cell函数的语法如下介绍：=Get.Cell(类型号,单元格引用)Get.Cell函数按照常规方法在单元格中输入是没有任何用处的，还会提示函数无效。Get.Cell函数不能写在公式里，一般的使用时，都是写在名称。73SentenceVBA如何调用加载宏application.Run"test.xla!getid(str1)"test.xla为加载宏的名称getid为加载宏中的函数名称'格式:Application.Run("宏文件名!函数名","参数一","参数二")'最多可有30个参数.函数名也可以是子程序名nRetx=Application.Run("test.xla!getid",str1)74SentenceVBA中设置工作薄存盘ActiveWorkbook.SaveThisWorkbook.Save存盘无效75Operation界面按钮如何调用加载宏举例:Macro01.xla为一加载宏,已经设置有效,如果一个工具栏按钮要调用此加载宏中的模块Mod01则在宏指向栏内填入:Macro01.xla!Mod01即可,当点击此按钮时即可执行此模块.76Sentence如何让VBA发生错误时不报警使用语句:OnErrorGoTo即可77Sentence删除一整行或者连续数行举例:删除Excel中第i行,或者第i至第i+3行ActiveSheet.Rows(i).Deleteshift:=xlupActiveSheet.Rows(i&":"&i).EntireRow.Deleteshift:=xlupActiveSheet.Rows(i&":"&i+3).EntireRow.Deleteshift:=xlup78Sentence函数可选参数的设置1)使用Optional设置函数或者过程的可选参数例如:SubF_A(OptionalstrInasString)2)使用IsMissing(argname)检查可选参数,返回Boolean值，指出一个可选的Variant参数是否已经传递给过程。IfIsMissing(strIn)Then3)设置可选参数的缺省值publicfunctionA(byvalv1asstring,optionalv2asstring="")79Operation计算某日是一年中的第几周在单元格中输入：=WEEKNUM(TODAY())如果无效请启用加载宏：1.internetVBA辅助程式；2.VBA分析工具箱；3.分析工具箱80Sentence关于日期的常识1.IsDate(Cells(r,c).Formula)不正确,经常出错应改为:IsDate(Cells(r,c).Value)2.Cells(r,c).Formula>Date不正确,不能得出正确的比较结果应改为:Cdate(Cells(r,c).Formula)>Date3.Right(Format(Date,"mmm-dd-yy"),2),可以得到日期的年份值,其他同理81SentenceExit跳出当前模块ExitFunctionExitSubExitFor82Sentence获取选中单元格的行号和列号Selection.RowSelection.Column83Sentence反馈变量的数值MsgBox()84SentenceChr(10)在VBA中实现回车换行MsgBox"Row="&Selection.Row&Chr(10)&"Column="&Selection.Column85Sentence在Book中添加新工作表并命名Workbooks(BookName).Worksheets.Add().Name=SheetName86Sentence获得工作薄的属性ActiveWorkbook.NameActiveWorkbook.PathActiveWorkbook.FullNameActiveWorkbook.CodeNameActiveWorkbook.ReadOnlyActiveWorkbook.Saved87Sentence指向工作薄的语句ThisWorkbook对象代表的是代码所在的工作簿ActiveWorkbook对象代表的是活动工作簿88Sentence判断一个文件是否存在ifLen(Dir(FullFileName)>0ThenMsgBox"文件存在"89Sentence属性:CurrentRegionRange("C3").CurrentRegion.Select即选中C3周围被空行和空列包围的区域90Sentence高级过滤expression.AdvancedFilter(Action,CriteriaRange,CopyToRange,Unique)91SentenceInStr字符串中搜寻子字符串InStr([start,]string1,string2[,compare])string2可不可以为双引号（"）？如果能，怎么设置？完全可以，有两种方法。1.instr(start,string1,"""")'一定是四个双引号2.instr(start,string1,chr(34))'双引号的Ascii是34，所以可以这样写语法:在string1中从start位置开始搜索String292OperationExcel2003中定义区域名称1)选择需要定义的区域;2)选择菜单:Insert=>Name=>Define…93Sentence隐藏和显示窗口元素ActiveWindow.DisplayVerticalScrollBar=FalseActiveWindow.DisplayHorizontalScrollBar=FalseActiveWindow.DisplayWorkbookTabs=FalseActiveWindow.DisplayGridlines=FalseActiveWindow.DisplayHeadings=False94Sentence变量定义方法　dimi!　　相当于　dimiassingle　　dimi#　　相当于　dimiDouble　　dimi@　　相当于　dimiasCurrence　　dimi$　　相当于　dimiasString　　dimi%　　相当于　dimiasInteger　　dimi&　　相当于　dimiasiLong95SentenceVBA调用文件打开对话框选择文件expression.GetOpenFilename(FileFilter,FilterIndex,Title,ButtonText,MultiSelect)Application.GetOpenFilename("Excel文件(*.xls),*.xls",FilterIndex:=1,MultiSelect:=True)当MultiSelect:=True时，可以同时选择多个文件