入门级培训.pptx

2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBAVBAVBA知识知识知识知识简介简介简介简介什么是什么是 VBAVBA？VBA是Visual Basic for Application的简写，它以VB语言为基础，经过修改并运行在Microsoft Office的应用程序，如Excel，Word中，它虽然不能像VB一样生成可执行的程序，但是能帮助用户编写一系列的宏，用来对数据进行批量处理，进行快速的逻辑操作和算术操作，或者进行文件的读写操作，繁琐复杂的文件处理，变得轻松易做。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBAVBAVBA知识知识知识知识简介简介简介简介VB与与VBA的区别的区别VBA是内含与office各软件的宏语言,VB则是单纯包装及执行的程序语言,二者差异如下:编译执行文件:执行文件的扩展名为EXE,VB由于内含编译器,故可制作EXE文件。VBA则由于内含于office系列各软件内，且不提供编译器，故VBA程序只可依附于各软件而执行，无法制作执行文件。可用的资源：也就是程序内可引用的资源，包括对象、函数等。VB在此方面的资源较VBA为广，因二者比较，VB方面是较专业的程序设计语言，而VBA的目的则是强化Office应用系统，故在可用资源方面，VBA不及VB。基本语法：VBA及VB语法完全相同，故只要稍有Basic基础，即可使用VBA。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBAVBAVBA语法说明语法说明语法说明语法说明相关网站相关网站http:/ IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporation代码示例代码示例 VBAVBA简单宏定义的代码示例简单宏定义的代码示例 VBAVBA文件操作的代码示例文件操作的代码示例 VBAVBA链接数据库的代码示例链接数据库的代码示例 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA简单宏定义的代码示例简单宏定义的代码示例简单宏定义的代码示例简单宏定义的代码示例 可用 Cells 属性及行号和列标引用单个单元格。该属性返回代表单个单元格的 Range 对象。下例中，Cells(6,1)返回工作表“Sheet1”上的单元格“A6”，然后将 Value 属性设置为 10。Sub EnterValue()Worksheets(Sheet1).Cells(6,1).Value=10 End Sub 可用变量代入单元格索引值，Cells 属性非常适于在单元格区域中循环，如下例所示。Sub CycleThrough()Dim counter As Integer For counter=1 To 20Worksheets(Sheet1).Cells(counter,3).Value=counter Next counter End Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA文件操作的代码示例文件操作的代码示例文件操作的代码示例文件操作的代码示例1 1、激活工作簿、激活工作簿用 Activate 方法激活工作簿后，该工作簿将放在活动窗口中。下述过程激活已打开的工作簿“MyBook.xls”。Sub MakeActive()Workbooks(MyBook.xls).Activate End Sub 2 2、创建新工作簿、创建新工作簿如果要用 Visual Basic 创建新的工作簿，应使用 Add 方法。下述过程创建了新工作簿。Microsoft Excel 自动将该工作簿命名为“BookN”，其中 N 是下一个可用的数字。新工作簿将成为活动工作簿。Sub AddOne()Workbooks.Add End Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA文件操作的代码示例文件操作的代码示例文件操作的代码示例文件操作的代码示例3 3、打开工作簿、打开工作簿用 Open 方法打开一个工作簿时，该工作簿将成为 Workbooks 集合的成员。下述过程打开 C 盘上“MyFolder”文件夹内的工作簿“MyBook.xls”。Sub OpenUp()Workbooks.Open(C:MyFolderMyBook.xls)End Sub 4 4、用编号引用单元格、用编号引用单元格可用 Cells 属性及行号和列标引用单个单元格。因为可用变量代入单元格索引值，所以 Cells 属性非常适于在单元格区域中循环。Sub CycleThrough()Dim counter As Integer For counter=1 To 20Worksheets(Sheet1).Cells(counter,3).Value=counter Next counter End Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA文件操作的代码示例文件操作的代码示例文件操作的代码示例文件操作的代码示例5 5、关闭工作簿、关闭工作簿Sub CloseFirst()Workbooks(1).CloseEnd Sub 6 6、操作文件的输入、操作文件的输入/输出输出 (I/O)(I/O)语法Open pathname For mode Access access lock As#filenumber Len=reclength（1）下列代码以顺序输入模式打开 TESTFILE 文件。Open“TESTFILE”For Input As#1 若要以其他方式打开文件，必需先关闭此文件。Close#1 （2）下列代码以只允许写操作的二进制方式打开文件。Open“TESTFILE”For Binary Access Write As#1 若要以其他方式打开文件，必需先关闭此文件。Close#1 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA文件操作的代码示例文件操作的代码示例文件操作的代码示例文件操作的代码示例（3）下列代码以随机方式打开文件，文件中含有用户自定义数据类型 Record 的记录。Type Record 定义用户自定义数据类型。ID As Integer Name As String*20End TypeDim MyRecord As Record 声明变量。Open“TESTFILE”For Random As#1 Len=Len(MyRecord)若要以其他方式打开文件，必需先关闭此文件。Close#1（4）下列代码以顺序输出方式打开文件；任何过程都可以读写该文件。Open“TESTFILE”For Output Shared As#1 若要以其他方式打开文件，必需先关闭此文件。Close#1（5）下列代码以只允许读的二进制方式打开文件；其他过程不可以读该文件。Open TESTFILE For Binary Access Read Lock Read As#1 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA文件操作的代码示例文件操作的代码示例文件操作的代码示例文件操作的代码示例7 7、关闭工作簿、关闭工作簿本示例使用 Input#语句将文件内的数据读入两个变量中。本示例假设 TESTFILE文件内含数行以 Write#语句写入的数据；也就是说，每一行数据中的字符串部分都是用双引号括起来，而与数字用逗号隔开，例如，（Hello,234）。Dim MyString,MyNumberOpen“TESTFILE”For Input As#1 打开输入文件。Do While Not EOF(1)循环至文件尾。Input#1,MyString,MyNumber 将数据读入两个变量。Debug.Print MyString,MyNumber 在立即窗口中显示数据。LoopClose#1 关闭文件。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA链接数据库的代码示例链接数据库的代码示例链接数据库的代码示例链接数据库的代码示例a)ODBC Driver for Accessi)普通安全模式oConn.Open“Driver=Microsoft Access Driver(*.mdb);”&_“Dbq=somepathmydb.mdb;Uid=Admin;Pwd=;”ii)如果使用了System database:oConn.Open“Driver=Microsoft Access Driver(*.mdb);”Dbq=somepathmydb.mdb;SystemDB=somepathmydb.mdw;,admin,b)ODBC Driver for SQL Serveri)普通安全模式oConn.Open Driver=SQL Server;Server=carl2;&_Database=pubs;Uid=sa;Pwd=;ii)使用信任安全模式oConn.Open Driver=SQL Server;Server=carl2;&_Database=pubs;Uid=;Pwd=;2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA链接数据库的代码示例链接数据库的代码示例链接数据库的代码示例链接数据库的代码示例c)ODBC Driver for Oraclei)使用現有的Oracle ODBC Driver from Microsoft:oConn.Open Driver=Microsoft ODBC for Oracle;&_Server=OracleServer.world;Uid=demo;Pwd=demo;ii)使用老版本的Oracle ODBC Driver from Microsoft:oConn.Open Driver=Microsoft ODBC Driver for Oracle;&ConnectString=OracleServer.world;Uid=demo;Pwd=demo;2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示 由于VBA和VB基本一样，所以通过上述的实例，根据自己项目中做的小工具，进行描述，让大家有个感性的认识。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示工具工具1.1.自动批量生成自动批量生成SQLSQL文文由于项目中，每天日方会提供大量（上百万）的初始数据，并需要把这些数据进行选择性抽出。但DB2的select A from B where k in()语句，最多一次性只能in 几千条数据。所以要分批抽出。于是分割数据，通过每1000行抽一次数据，循环做成sql语句,方便处理。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示在第一列贴入初始数据，然后按下SQL做成按钮，结果如下所示：2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明Private intHinnbannRows As IntegerPrivate intHinnbannCount As IntegerPrivate Const cnKoujilyouSheet As String=工場Private Const strPreSelect As String=select ITEM_NO,FACTORY_CD from CO_ITEM_FACT_RLSE where item_no in(Private Const strNextSelect As String=)and ad_kb=A order by item_no;“Private Sub CommandButton1_Click()各自SQL取得。Dim strSql As String Dim strHinnbann As String Dim intSqlRows As Integer intHinnbannRows=2 intHinnbannCount=0 intSqlRows=2当工場sheet的某行的第一列数值不等于空的时候，就一直循环扫描赋值。While ThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intHinnbannRows,1).Value intHinnbannCount=intHinnbannCount+1 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明strHinnbann=ThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intHinnbannRows,1).Value如果品番的数量除以1000的余数是1的时候，执行下列语句。初始前半部sql语句，进行下一条sql语句的做成。If(intHinnbannCount Mod 1000=1)Then strSql=strPreSelect End If strSql=strSql+strHinnbann如果品番的数量除以1000的余数是0的时候，执行下列语句。目的是为了连接多个品番。If(intHinnbannCount Mod 1000=0)Then strSql=strSql+strNextSelect ThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intSqlRows,9).Value=strSql intSqlRows=intSqlRows+1 strSql=Else strSql=strSql+,End If intHinnbannRows=intHinnbannRows+1 Wend 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明 如果品番的数量不是1000的倍数时，对最后一句sql语句做特殊处理。补全后半部sql。If(intHinnbannCount Mod 1000 0)Then strSql=strSql+strNextSelect strSql=Replace(strSql,)ThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intSqlRows,9).Value=strSql End If MsgBox 検証完了。End Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示工具工具2 2.提取提取loglog内容，生成报表内容，生成报表由于本番环境操作非常严格，需要有相应的log文件记录。此工具的目的是对这些log文件进行check，查出任何时间某人虽然操作了本番环境，但没有及时申请，那么该人员就会出力到这个文件里，留有记录，加强本番安全性。入力文件如下所示。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示按下“检证开始”按钮 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示PF基盤（Windows）_pdfsvr1_201004.xls的文件出力。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示结果文件显示,XXXX是代名。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明上述工具应用的知识点有xls文件的读写操作，表格的复制，数据的排序以及基本的vba语句循环赋值。1.xls文件的读写操作2.表格的复制3.数据的排序 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明1.xls文件的读写操作Public Sub SetLeaderParamList()関連付表.xlsindexRow Dim indexRow As Integer intLeaderParamListCount Dim intLeaderParamListCount As Integer 関連付表.xls Dim strLeaderListFile As String strLeaderListFile=pstrLocalPath&conLeaderKanlanFile 関連付表.xls選択 Application.Workbooks(strLeaderListFile).Activate 関連付表.xls開 Workbooks.Open(strLeaderListFile)indexRow=4 intLeaderParamListCount=0 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明当conLeaderSheet的某行的第3列数值不等于空的时候，就一直循环扫描赋值。做成g_LeaderParamList的结构体数组。While ActiveWorkbook.Worksheets(conLeaderSheet).Cells(indexRow,3).Value ReDim Preserve g_LeaderParamList(intLeaderParamListCount)組織 g_LeaderParamList(intLeaderParamListCount).strSosiki=ActiveWorkbook.Worksheets(conLeaderSheet).Cells(indexRow,2).Value 名 g_LeaderParamList(intLeaderParamListCount).strGroupName=ActiveWorkbook.Worksheets(conLeaderSheet).Cells(indexRow,3).Value 名 g_LeaderParamList(intLeaderParamListCount).strLeaderName=ActiveWorkbook.Worksheets(conLeaderSheet).Cells(indexRow,4).Value intLeaderParamListCount=intLeaderParamListCount+1 indexRow=indexRow+1WendActiveWorkbook.Close End Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明未申請報告書書Private Sub writeToMiSinnSeyiSilyoSheet(strServerId As String,sheetAppName As String,arrLogContext()As String)Dim strLineDate()As String ReDim strLineDate(2)使用者情報 Dim siyousilyaJilyouhou As SiyousilyaJilyouhouParam 年月日 Dim pstrYmd As String 時間 Dim pstrTime As String ID Dim pstrUserId As String 名 Dim pstrServerId As String IP Dim pstrIpAddress As String pstrYmd=Mid(arrLogContext(0),1,10)2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明pstrTime=Mid(arrLogContext(0),12,8)&-&Mid(arrLogContext(1),12,8)pstrServerId=RTrim(arrLogContext(2)pstrUserId=RTrim(Replace(arrLogContext(3),LOGON,)pstrIpAddress=RTrim(arrLogContext(7)Worksheets(sheetAppName).Unprotect Password:=workBookPassword Worksheets(sheetAppName).Cells(intIndexAppRows,2).Value=pstrYmd Worksheets(sheetAppName).Cells(intIndexAppRows,3).NumberFormatLocal=h:mm:ss Selection.NumberFormatLocal=h:mm:ss Worksheets(sheetAppName).Cells(intIndexAppRows,3).Value=pstrTime Worksheets(sheetAppName).Cells(intIndexAppRows,4).Value=pstrServerId Worksheets(sheetAppName).Cells(intIndexAppRows,5).Value=pstrUserId Worksheets(sheetAppName).Cells(intIndexAppRows,6).Value=pstrIpAddress siyousilyaJilyouhou=getSiyousilyaJilyouhou(pstrUserId,pstrServerId)Worksheets(sheetAppName).Cells(intIndexAppRows,7).Value=siyousilyaJilyouhou.strSiyousilyaName Worksheets(sheetAppName).Cells(intIndexAppRows,8).Value=siyousilyaJilyouhou.strSiyousilyaSosiki Worksheets(sheetAppName).Cells(intIndexAppRows,9).Value=siyousilyaJilyouhou.strSiyousilyaLeaderName intIndexAppRows=intIndexAppRows+1End Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明2.表格的复制GetsheetNPrivate Sub GetsheetN(sheetInfo As PramSheetInfo)If Worksheets.count=3 Then Application.DisplayAlerts=False Worksheets(Sheet2).Delete Worksheets(Sheet3).Delete Application.DisplayAlerts=True End If Dim fileName As String fileName=GetErrorFileName(sheetInfo)ThisWorkbook.Unprotect workBookPasswordThisWorkbook.Worksheets(conWinSheet).Visible=TrueThisWorkbook.Worksheets(conWinSheet).Copy After:=Workbooks(fileName).Sheets(1)ThisWorkbook.Worksheets(conWinSheet).Visible=FalseThisWorkbook.Protect workBookPassword,Structure:=True,Windows:=FalseEnd Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代码说明工具代码说明3.数据的排序結果、年月日時間昇順並替Private Sub OrderSheet(sheetAppName As String)Worksheets(sheetAppName).Range(B7&:&O&intIndexAppRows-1).Select Worksheets(sheetAppName).Range(B7&:&O&intIndexAppRows-1).Sort Key1:=Worksheets(sheetAppName).Range(B7),Order1:=xlAscending,Key2:=Worksheets(sheetAppName).Range(_ C7),Order2:=xlAscending,Header:=xlGuess,OrderCustom:=1,MatchCase _ :=False,Orientation:=xlTopToBottom,SortMethod:=xlPinYin,DataOption1:=_ xlSortNormal,DataOption2:=xlSortNormalEnd Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA录制宏录制宏小技巧小技巧所谓录制宏，就是在编成中发现一些不懂的问题，通过录制宏的功能可以直接生成需要的代码。下面就如何使用宏录制功能，进行描述。Sheet1的第6行的第一列输入 10。代码将自动生成。Sub EnterValue()Range(A6).SelectActiveCell.FormulaR1C1=10 End Sub操作步骤：1.选择菜单的ToolsMacroRecord New Macro。2.点击Record New Macro，启动宏录制功能。3.Sheet1的第6行的第一列输入 10。然后按停止的小图标，代码将自动生成。4.选择菜单的ToolsMacroVisual Basic Editor,显示代码。5.选择菜单的ToolsMacroMacros,运行宏。6.查看运行宏后的结果。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA录制宏录制宏小技巧小技巧1.菜单的ToolsMacroRecord New Macro 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA录制宏录制宏小技巧小技巧2.点击Record New Macro,跳出下面框框，直接点OK，宏录制功能启动。会出现下方的小图标，表明宏录制启动成功。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA录制宏录制宏小技巧小技巧 3.Sheet1的第6行的第一列输入 10。然后按停止的小图标，代码将自动生成。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA录制宏录制宏小技巧小技巧4.选择菜单的ToolsMacroVisual Basic Editor,显示代码 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA录制宏录制宏小技巧小技巧5.选择菜单的ToolsMacroMacros,运行宏，消息框跳出。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA录制宏录制宏小技巧小技巧6.查看运行宏后的结果。2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporation结束语结束语Q&A 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporation结束语结束语 谢谢大家