EXCEL-VBA编程集结

~1~EXCEL代码示例目录EXCEL代码示例................................................................................................................................11.常量....................................................................................................................................................22.按值传递与按引用传递...............................................................................................................23.Sub过程与函数过程.....................................................................................................................34.单元格常见属性和方法...............................................................................................................45.工作表常见属性方法....................................................................................................................66.显式声明变量..................................................................................................................................67.对象变量...........................................................................................................................................78.with语句........................................................................................................................................119.If语句与SelectCase语句示例................................................................................................1210.Doloop语句实例ForNext语句实例...............................................................................1311.Foreachinnext语句实例.......................................................................................................1412.VBA代码中使用公式函数......................................................................................................1513.自定义函数..................................................................................................................................1614.VBA数组......................................................................................................................................1615.中断禁用事件..............................................................................................................................1916.窗体案例.......................................................................................................................................2017.常量与枚举..................................................................................................................................2118.查找定位函数..............................................................................................................................2219.汇总工作表..................................................................................................................................2420.合并工作簿..................................................................................................................................2621.拆分工作簿..................................................................................................................................28~2~1.常量2.按值传递与按引用传递OptionExplicitConstPI=3.1415926Function周长(直径AsDouble)AsDouble周长=直径*3.1415926EndFunctionFunction圆面积(半径AsDouble)AsDouble圆面积=3.1415926*(半径*半径)EndFunctionSubCircel()DimCircRadAsDoubleDimCircDiamAsDoubleCircRad=InputBox(请输入圆半径)CircDiam=CircRad*2MsgBox圆周长为：&周长(CircDiam)MsgBox圆面积为：&圆面积(CircRad)EndSubOptionExplicitSubdemo()DimxAsIntegerx=5ChangexMsgBoxxEndSubSubChange(ByRefxAsInteger)x=x+2EndSub按地址传递参数~3~3.Sub过程与函数过程Excel可以调用自己在VBE中编写的函数。OptionExplicitSubdemo()DimtangyuyingworkAgeAsBytetangyuyingworkAge=10MsgBox唐宇盈工作年限为&tangyuyingworkAge&年EndSubOptionExplicitSubtotal(aAsInteger,bAsInteger)DimiAsIntegerDimsumAsIntegerFori=aTobsum=sum+iNextMsgBoxsumEndSubSubusetotal()Calltotal(50,100)EndSubFunctionTax(x)Ifx=83500ThenTax=(x-3500)*0.45-13505ElseIfx=58500ThenTax=(x-3500)*0.35-5505ElseIfx=38500ThenTax=(x-3500)*0.3-2755ElseIfx=12500ThenTax=(x-3500)*0.25-1005ElseIfx=58500ThenTax=(x-3500)*0.2-555ElseIfx=5000ThenTax=(x-3500)*0.1-105ElseIfx=3500ThenTax=(x-3500)*0.03ElseTax=0EndIfEndFunction~4~4.单元格常见属性和方法激活sheet“员工信息”从A1按行移动至字符下一个空白单元空白赋值=上一个单元格数值+1右移1列输入值：康传平右移2列输入值：4/9/2015Sub定位()Worksheets(员工信息).ActivateRange(A1).End(xlDown).Offset(1,0).SelectActiveCell.Value=ActiveCell.Offset(-1,0).Value+1ActiveCell.Offset(0,1).Value=康传平ActiveCell.Offset(0,2).Value=#4/9/2015#EndSubSub批量定位()Range(A3,Range(A1).End(xlDown).End(xlToRight)).SelectSelection.Interior.Color=RGB(94,230,76)EndSub~5~其他操作：Sub复制()Worksheets(员工信息).ActivateRange(A1).CurrentRegion.CopyWorksheets(sheet3).Range(A1)Columns().AutoFitEndSubSub员工信息录入()Worksheets(员工信息).ActivateRange(A13).SelectActiveCell.Value=11Cells(13,2).SelectActiveCell.Value=袁艳[C13].SelectActiveCell.Value=#5/9/2015#EndSubSub表格格式设置2()Range(标题).Font.Bold=TrueRange(编号).Interior.Color=RGB(221,221,221)[入职时间].Interior.Color=RGB(221,221,221)EndSub~6~5.工作表常见属性方法6.显式声明变量Sub工作薄操作()Workbooks(工作表.xlsm).ActivateActiveWorkbook.CloseTrueEndSubSub打开工作薄()Workbooks.OpenD:\A-项目-08-ExcelVBA\00-素材\函数.xlsxEndSubSub新增工作薄()Workbooks.AddActiveWorkbook.SaveActiveWorkbook.SaveAsD:\工作表.xlsxEndS