VBA入門(mén)級(jí)培訓(xùn)

1、Microsoft CoE 2010 IBM CorporationVBA入門(mén)級(jí)培訓(xùn)Web Competency Microsoft CoE 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationAgenda VBAVBA知識(shí)知識(shí)簡(jiǎn)介簡(jiǎn)介 VBAVBA語(yǔ)法說(shuō)明語(yǔ)法說(shuō)明 VBAVBA代碼示例代碼示例 VBAVBA工具演示工具演示 VBAVBA工具代碼說(shuō)明工具代碼說(shuō)明 VBAVBA錄制宏錄制宏小技巧小技巧 結(jié)束語(yǔ)結(jié)束語(yǔ) 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporation

2、什么是什么是 VBAVBA？ VBA是Visual Basic for Application的簡(jiǎn)寫(xiě)，它以VB語(yǔ)言為基礎(chǔ)，經(jīng)過(guò)修改并運(yùn)行在Microsoft Office的應(yīng)用程序，如Excel，Word中，它雖然不能像VB一樣生成可執(zhí)行的程序，但是能幫助用戶(hù)編寫(xiě)一系列的宏，用來(lái)對(duì)數(shù)據(jù)進(jìn)行批量處理，進(jìn)行快速的邏輯操作和算術(shù)操作，或者進(jìn)行文件的讀寫(xiě)操作，繁瑣復(fù)雜的文件處理，變得輕松易做。 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVB與與VBA的區(qū)別的區(qū)別VBA是內(nèi)含與office各軟件的宏語(yǔ)言,VB則是單純包裝及執(zhí)行的程

3、序語(yǔ)言, 二者差異如下: 編譯執(zhí)行文件:執(zhí)行文件的擴(kuò)展名為EXE,VB由于內(nèi)含編譯器,故可制作EXE文件。VBA則由于內(nèi)含于office系列各軟件內(nèi)，且不提供編譯器，故VBA程序只可依附于各軟件而執(zhí)行，無(wú)法制作執(zhí)行文件。 可用的資源：也就是程序內(nèi)可引用的資源，包括對(duì)象、函數(shù)等。VB在此方面的資源較VBA為廣，因二者比較 ，VB方面是較專(zhuān)業(yè)的程序設(shè)計(jì)語(yǔ)言，而VBA的目的則是強(qiáng)化Office應(yīng)用系統(tǒng)，故在可用資源方面，VBA不及VB。 基本語(yǔ)法：VBA及VB語(yǔ)法完全相同，故只要稍有Basic基礎(chǔ)，即可使用VBA。 2009 IBM CorporationIBMrMicrosoft CoE 2010

4、 IBM Corporation 相關(guān)網(wǎng)站相關(guān)網(wǎng)站 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporation代碼示例代碼示例 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporation 可用 Cells 屬性及行號(hào)和列標(biāo)引用單個(gè)單元格。該屬性返回代表單個(gè)單元格的 Range 對(duì)象。下例中，Cells(6,1) 返回工作表“Sheet1”上的單元格“A6”，然后將 Value 屬性設(shè)置為 10。 Sub EnterValue() Worksheets(Sheet1).Cells(

5、6, 1).Value = 10 End Sub 可用變量代入單元格索引值，Cells 屬性非常適于在單元格區(qū)域中循環(huán)，如下例所示。 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 Corporation1 1、激活工作簿、激活工作簿用 Activate 方法激活工作簿后，該工作

6、簿將放在活動(dòng)窗口中。下述過(guò)程激活已打開(kāi)的工作簿“MyBook.xls”。 Sub MakeActive() Workbooks(MyBook.xls).Activate End Sub 2 2、創(chuàng)建新工作簿、創(chuàng)建新工作簿如果要用 Visual Basic 創(chuàng)建新的工作簿，應(yīng)使用 Add 方法。下述過(guò)程創(chuàng)建了新工作簿。Microsoft Excel 自動(dòng)將該工作簿命名為“BookN”，其中 N 是下一個(gè)可用的數(shù)字。新工作簿將成為活動(dòng)工作簿。 Sub AddOne() Workbooks.Add End Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010

7、IBM Corporation3 3、打開(kāi)工作簿、打開(kāi)工作簿用 Open 方法打開(kāi)一個(gè)工作簿時(shí)，該工作簿將成為 Workbooks 集合的成員。下述過(guò)程打開(kāi) C 盤(pán)上“MyFolder”文件夾內(nèi)的工作簿“MyBook.xls”。 Sub OpenUp() Workbooks.Open(C:MyFolderMyBook.xls) End Sub 4 4、用編號(hào)引用單元格、用編號(hào)引用單元格可用 Cells 屬性及行號(hào)和列標(biāo)引用單個(gè)單元格。因?yàn)榭捎米兞看雴卧袼饕?，所?Cells 屬性非常適于在單元格區(qū)域中循環(huán)。 Sub CycleThrough() Dim counter As Intege

8、r For counter = 1 To 20Worksheets(Sheet1).Cells(counter, 3).Value = counter Next counter End Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporation5 5、關(guān)閉工作簿、關(guān)閉工作簿Sub CloseFirst()Workbooks(1).CloseEnd Sub 6 6、操作文件的輸入、操作文件的輸入/ /輸出輸出 (I/O)(I/O) 語(yǔ)法Open pathname For mode Access access lock As #fi

9、lenumber Len=reclength（1）下列代碼以順序輸入模式打開(kāi) TESTFILE 文件。Open “TESTFILE” For Input As #1 若要以其他方式打開(kāi)文件，必需先關(guān)閉此文件。Close #1 （2）下列代碼以只允許寫(xiě)操作的二進(jìn)制方式打開(kāi)文件。Open “TESTFILE” For Binary Access Write As #1 若要以其他方式打開(kāi)文件，必需先關(guān)閉此文件。Close #1 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporation（3）下列代碼以隨機(jī)方式打開(kāi)文件，文件中含有用戶(hù)自定義數(shù)

10、據(jù)類(lèi)型 Record 的記錄。Type Record 定義用戶(hù)自定義數(shù)據(jù)類(lèi)型。 ID As Integer Name As String * 20End TypeDim MyRecord As Record 聲明變量。Open “TESTFILE” For Random As #1 Len = Len(MyRecord) 若要以其他方式打開(kāi)文件，必需先關(guān)閉此文件。Close #1 （4）下列代碼以順序輸出方式打開(kāi)文件；任何過(guò)程都可以讀寫(xiě)該文件。Open “TESTFILE” For Output Shared As #1 若要以其他方式打開(kāi)文件，必需先關(guān)閉此文件。Close #1 （5）下列代

11、碼以只允許讀的二進(jìn)制方式打開(kāi)文件；其他過(guò)程不可以讀該文件。Open TESTFILE For Binary Access Read Lock Read As #1 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporation7 7、關(guān)閉工作簿、關(guān)閉工作簿本示例使用 Input # 語(yǔ)句將文件內(nèi)的數(shù)據(jù)讀入兩個(gè)變量中。本示例假設(shè) TESTFILE文件內(nèi)含數(shù)行以 Write # 語(yǔ)句寫(xiě)入的數(shù)據(jù)；也就是說(shuō)，每一行數(shù)據(jù)中的字符串部分都是用雙引號(hào)括起來(lái)，而與數(shù)字用逗號(hào)隔開(kāi)，例如，（Hello, 234）。Dim MyString, MyNumberO

12、pen “TESTFILE” For Input As #1 打開(kāi)輸入文件。Do While Not EOF(1) 循環(huán)至文件尾。 Input #1, MyString, MyNumber 將數(shù)據(jù)讀入兩個(gè)變量。 Debug.Print MyString, MyNumber 在立即窗口中顯示數(shù)據(jù)。LoopClose #1 關(guān)閉文件。 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporationa)ODBC Driver for Accessi)普通安全模式oConn.Open “Driver=Microsoft Access Driver

13、(*.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;

14、 Pwd=;ii)使用信任安全模式oConn.Open Driver=SQL Server; Server=carl2; & _Database=pubs; Uid=; Pwd=; 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM Corporationc)ODBC Driver for Oraclei)使用現(xiàn)有的Oracle ODBC Driver from Microsoft:oConn.Open Driver=Microsoft ODBC for Oracle; & _Server=OracleServer.world; Uid

15、=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基本一樣，所以通過(guò)上述的實(shí)例，根據(jù)自己項(xiàng)目中做的小工具，進(jìn)行描述，讓大家有個(gè)感性的認(rèn)識(shí)。 2009

16、IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示工具工具1.1.自動(dòng)批量生成自動(dòng)批量生成SQLSQL文文由于項(xiàng)目中，每天日方會(huì)提供大量（上百萬(wàn)）的初始數(shù)據(jù)，并需要把這些數(shù)據(jù)進(jìn)行選擇性抽出。但DB2的select A from B where k in()語(yǔ)句，最多一次性只能in 幾千條數(shù)據(jù)。所以要分批抽出。于是分割數(shù)據(jù)，通過(guò)每1000行抽一次數(shù)據(jù)，循環(huán)做成sql語(yǔ)句,方便處理。 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具

17、演示工具演示在第一列貼入初始數(shù)據(jù)，然后按下SQL做成按鈕，結(jié)果如下所示： 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代碼說(shuō)明工具代碼說(shuō)明Private intHinnbannRows As IntegerPrivate intHinnbannCount As IntegerPrivate Const cnKoujilyouSheet As String = 工場(chǎng)Private Const strPreSelect As String = select ITEM_NO,FACTORY_CD from CO_

18、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當(dāng)工場(chǎng)sheet的某行的第一列數(shù)值不等于空

19、的時(shí)候，就一直循環(huán)掃描賦值。 While ThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intHinnbannRows, 1).Value intHinnbannCount = intHinnbannCount + 1 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代碼說(shuō)明工具代碼說(shuō)明strHinnbann = ThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intHinnbannRows, 1).Value如果

20、品番的數(shù)量除以1000的余數(shù)是1的時(shí)候，執(zhí)行下列語(yǔ)句。初始前半部sql語(yǔ)句，進(jìn)行下一條sql語(yǔ)句的做成。 If (intHinnbannCount Mod 1000 = 1) Then strSql = strPreSelect End If strSql = strSql + strHinnbann如果品番的數(shù)量除以1000的余數(shù)是0的時(shí)候，執(zhí)行下列語(yǔ)句。目的是為了連接多個(gè)品番。 If (intHinnbannCount Mod 1000 = 0) Then strSql = strSql + strNextSelect ThisWorkbook.Worksheets(cnKoujilyou

21、Sheet).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工具代碼說(shuō)明工具代碼說(shuō)明 如果品番的數(shù)量不是1000的倍數(shù)時(shí)，對(duì)最后一句sql語(yǔ)句做特殊處理。補(bǔ)全后半部sql。If (intHinnbannCount Mod

22、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內(nèi)容，生成報(bào)表內(nèi)容，生成報(bào)表由于本番環(huán)境操作非常嚴(yán)格，需要

23、有相應(yīng)的log文件記錄。此工具的目的是對(duì)這些log文件進(jìn)行check，查出任何時(shí)間某人雖然操作了本番環(huán)境，但沒(méi)有及時(shí)申請(qǐng)，那么該人員就會(huì)出力到這個(gè)文件里，留有記錄，加強(qiáng)本番安全性。 入力文件如下所示。 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示按下“檢證開(kāi)始”按鈕 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示PF基盤(pán)（Windows）_pdfsvr1_201004.xls的文件出力。 2009

24、IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具演示工具演示結(jié)果文件顯示,XXXX是代名。 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代碼說(shuō)明工具代碼說(shuō)明上述工具應(yīng)用的知識(shí)點(diǎn)有xls文件的讀寫(xiě)操作，表格的復(fù)制，數(shù)據(jù)的排序以及基本的vba語(yǔ)句循環(huán)賦值。xls文件的讀寫(xiě)操作表格的復(fù)制數(shù)據(jù)的排序 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代碼說(shuō)明工具

25、代碼說(shuō)明1.xls文件的讀寫(xiě)操作Public Sub SetLeaderParamList() 関連付表.xlsindexRow Dim indexRow As Integer intLeaderParamListCount Dim intLeaderParamListCount As Integer 関連付表.xls Dim strLeaderListFile As String strLeaderListFile = pstrLocalPath & & conLeaderKanlanFile 関連付表.xls選択 Application.Workbooks(strLeade

26、rListFile).Activate 関連付表.xls開(kāi) Workbooks.Open (strLeaderListFile) indexRow = 4 intLeaderParamListCount = 0 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代碼說(shuō)明工具代碼說(shuō)明當(dāng)conLeaderSheet的某行的第3列數(shù)值不等于空的時(shí)候，就一直循環(huán)掃描賦值。做成g_LeaderParamList的結(jié)構(gòu)體數(shù)組。While ActiveWorkbook.Worksheets(conLeaderSheet).Ce

27、lls(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(conLeade

28、rSheet).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 CorporationIBMrMicro

29、soft CoE 2010 IBM CorporationVBAVBA工具代碼說(shuō)明工具代碼說(shuō)明未申請(qǐng)報(bào)告書(shū)書(shū)Private Sub writeToMiSinnSeyiSilyoSheet(strServerId As String, sheetAppName As String, arrLogContext() As String) Dim strLineDate() As String ReDim strLineDate(2) 使用者情報(bào) Dim siyousilyaJilyouhou As SiyousilyaJilyouhouParam 年月日 Dim pstrYmd As String

30、 時(shí)間 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工具代碼說(shuō)明工具代碼說(shuō)明pstrTime = Mid(arrLogContext(0), 12, 8) & - & Mid(arrLogContex

31、t(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).Ce

32、lls(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

33、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 =

34、siyousilyaJilyouhou.strSiyousilyaSosiki Worksheets(sheetAppName).Cells(intIndexAppRows, 9).Value = siyousilyaJilyouhou.strSiyousilyaLeaderName intIndexAppRows = intIndexAppRows + 1End Sub 2009 IBM CorporationIBMrMicrosoft CoE 2010 IBM CorporationVBAVBA工具代碼說(shuō)明工具代碼說(shuō)明2.表格的復(fù)制GetsheetNPrivate Sub Getsheet

35、N(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.Wor

36、ksheets(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 Corporatio

37、nVBAVBA工具代碼說(shuō)明工具代碼說(shuō)明3.數(shù)據(jù)的排序結(jié)果、年月日時(shí)間昇順並替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: