三日excel编程教程

最近由于要修改一个excelreport，见识了vba的强大。这个report是一个大牛3年前写的，每天只需打开该文件，就会自动连接oracle的dev和uat数据库读取最新的市场数据，生成6个透视图，并比较dev和uat的数据的异同。vba操作数据之方便，生成的report之复杂，深深的吸引了我，于是乎觉得不学点vba真的对不起老本行。花了3天时间学习，目前觉得基本可以满足大多数需求，即便是有不懂的地方，也知道在哪里查资料，该怎么查资料。为了防止自己很快忘记，于是有了vbaexcel编程3日谈,内容涵盖对象模型，基本语法，excel表格基本操作，事件，ADODB连接数据库读取数据，生成透视图。本人没有学过vb，写出来的东西在过来人看来未免太简单，但只求对新手有帮助。vbaexcel编程三日谈(1)vbaexcel编程三日谈(2)vbaexcel编程三日谈(3)准备工作vba是一种寄宿语言，像javascript生存在浏览器中一样，vba生存在office应用程序中。所以请确保你安装了office，本人学习的时候采用的office2007，版本差异会引起一些问题，但是office2003和office2007的差异不是很大。新建一个excel文档test.xls，按alt＋F11进入vba编程界面。双击左面的thisWorkbook，在右边输入：PrivateSubWorkbook_Open()MsgBoxHello,worldEndSub保存并退出excel，然后重新打开该excel，则会看到弹出的消息Hello，world。如果遇到macro安全警告，请enable。这就是第一个vba程序，Workbook_Open是对事件open的响应函数（关于事件在后面会有更多介绍）。当然你可以把代码写在任何一个sheet里面，也可以就地运行，调试。双击sheet1，在右边输入：Subtest()MsgBoxHello,worldEndSub然后把光标移到函数名test上，点击工具栏上的绿色的箭头即可运行测试该函数。我们还可以在excel的工作表上添加一个控件（比如按钮），通过点击控件来执行函数。把菜单切换到Developer标签，如下图点击Insert即可选择插入的控件(如果是office2003则在菜单试图-工具栏-控件工具箱)点击按钮，在工作表的任意位置画一个按钮，同时在选择框中点击你刚编写的函数test，保存。此时点击按钮即可触发该函数。当然你还可以录制宏，这也是学习vba的好方法。在这里不做介绍。OK，至此，我们学会在怎么定义一个过程(函数)，然后怎么触发运行它(通过事件，通过vba运行调试，通过按钮)，准备工作到此结束。vbaexcel对象模型类似javascript中的dom模型,windows编程中的组件对象模型,vba也有自己的对象模型.excel的模型中的对象有很多(自己可以google一下),但是经常用到的无非这四五个:application-workbooks-worksheets-range-cells.application处于最顶上,表示正在运行的程序(Excel)本身.workbook工作薄,即一个excel文件单元,对应着一个xls文件.worksheet是工作表,新建一个workbook里面默认包含了3个worksheet;range表示工作表中的一块区域,比如Range(A1:D10)表示A1:D10之间的那块区域,一个range包含多个Cell,一个cell即worksheet中的一个小格子.新建一个文件1.xls,并在sheet1的A1单元格中随便输入内容,下面的程序将更清楚的表明它们之间的关系.继续在刚才的test.xls的sheet1的代码区中输入如下代码:[vb]viewplaincopyprint?1.Subtest()2.'去读本文件的单元格的内容3.MsgBoxApplication.Workbooks(test.xls).Worksheets(Sheet1).Range(A1).Value4.'如果是当前的application,则application可以省略5.MsgBoxWorkbooks(test.xls).Worksheets(Sheet1).Range(A1).Value6.'如果是当前workbooks,则workbooks可以省略7.MsgBoxWorksheets(Sheet1).Range(A1).Value8.'如果是当前的sheet,则worksheet可以省略9.MsgBoxRange(A1).Value10.'还可以这么用11.MsgBoxSheets(Sheet1).Range(A1).Value12.'ActiveWorkbook代表当前活动的workbook13.'这样就更直接了14.MsgBoxCells(1,1).Value15.'也可以改写cell的value16.Cells(1,1).value=我在学vba17.MsgBoxActiveWorkbook.Worksheets(1).Range(A1).Value18.'还可以这么用19.MsgBoxThisWorkbook.Worksheets(1).Range(A1).Value20.'也可读取外部xls文件的内容21.MsgBoxApplication.Workbooks(1.xls).Worksheets(Sheet1).Range(A1).Value22.'也可以通过下标应用对象23.MsgBoxApplication.Workbooks(2).Worksheets(1).Range(A1).Value24.EndSubSubtest()'去读本文件的单元格的内容MsgBoxApplication.Workbooks(test.xls).Worksheets(Sheet1).Range(A1).Value'如果是当前的application,则application可以省略MsgBoxWorkbooks(test.xls).Worksheets(Sheet1).Range(A1).Value'如果是当前workbooks,则workbooks可以省略MsgBoxWorksheets(Sheet1).Range(A1).Value'如果是当前的sheet,则worksheet可以省略MsgBoxRange(A1).Value'还可以这么用MsgBoxSheets(Sheet1).Range(A1).Value'ActiveWorkbook代表当前活动的workbook'这样就更直接了MsgBoxCells(1,1).Value'也可以改写cell的valueCells(1,1).value=我在学vbaMsgBoxActiveWorkbook.Worksheets(1).Range(A1).Value'还可以这么用当然每个对象都有很多属性和方法可以使用.如例:[vb]viewplaincopyprint?1.Subtest1()2.MsgBoxApplication.Name3.MsgBoxApplication.Workbooks(2).Name4.MsgBoxWorksheets.Count5.6.MsgBoxThereare&CStr(Range(A1:D10).Cells.Count)&Cells7.8.Sheets(2).Select9.ActiveSheet.Cells(1,1).Value=ThisisthefirstcellinSheet210.Range(A2).Font.FontStyle=Bold11.Range(A2).Font.Size=1312.Range(A3).Borders.LineStyle=xlContinuous13.Range(A3).Borders.Weight=xlThin14.EndSubSubtest1()MsgBoxApplication.NameMsgBoxApplication.Workbooks(2).NameMsgBoxWorksheets.CountMsgBoxThereare&CStr(Range(A1:D10).Cells.Count)&CellsSheets(2).SelectActiveSheet.Cells(1,1).Value=ThisisthefirstcellinSheet2Range(A2).Font.FontStyle=BoldRange(A2).Font.Size=13Range(A3).Borders.LineStyle=xlContinuousRange(A3).Borders.Weight=xlThin这么多方法和属性记不住怎么办?没关系,有自动语法提示:菜单-tools-options勾选autolistmembers.关于对象模型就说这么多,记不住或不了解的,可通过录制宏,自动代码提示,google等渠道获得.基础语法数据类型和定义变量vba的基础数据类型有bytebooleanintegerlongsingledoublecurrencydecimaldate...长度精度上有所差别.如下展示基本数据类型的定义和使用,其中要注意的是Date类型的赋值比较特殊,要用##包含起来,常用类型还有更简单的定义方式:常用类型说明符%integer&long!single#Double$string@currency[vb]viewplaincopyprint?1.Subtest1()2.DimiAsInteger,jAsInteger3.DimsAsString4.i=25.j=36.MsgBoxi+j7.s=Theresultis:&(i+j)8.MsgBoxs9.DimdAsDate10.d=#12/12/20023:23:00AM#11.MsgBoxd12.Dimk%,l&13.k=514.l=615.MsgBoxk+l16.DimmAsCurrency17.m=123.45618.MsgBoxm19.EndSubSubtest1()DimiAsInteger,jAsIntegerDimsAsStringi=2j=3MsgBoxi+js=Theresultis:&(i+j)MsgBoxsDimdAsDated=#12/12/20023:23:00AM#MsgBoxdDimk%,l&k=5l=6MsgBoxk+l如果定义的变量是对象类型,比如WorkSheet,则要用set来赋值.[vb]viewplaincopyprint?1.Subtest1()2.DimshAsWorksheet3.Setsh=ActiveWorkbook.Sheets(1)4.MsgBoxsh.Cells(1,1).Value5.EndSubSubtest1()DimshAsWorksheetSetsh=ActiveWorkbook.Sheets(1)MsgBoxsh.Cells(1,1).ValueEndSub定义和使用数组:[vb]viewplaincopyprint?1.Subtest6()2.Dimmyarr(3)AsInteger3.myarr(1)=34.myarr(2)=45.myarr(3)=56.MsgBoxmyarr(1)+myarr(2)7.EndSubSubtest6()Dimmyarr(3)AsIntegermyarr(1)=3myarr(2)=4myarr(3)=5MsgBoxmyarr(1)+myarr(2)EndSub过程&函数通过subsubname(param1,param2...)......endsub可定义过程,也可定义function,function和sub的区别是function有返回值.其他都一样,在这里就不讨论function了.一个过程可以调用其他过程.[vb]viewplaincopyprint?1.Subtest1()2.Msg