1、VBA入门级培训,Web Competency Microsoft CoE,Agenda,VBA知识简介 VBA语法说明 VBA代码示例 VBA工具演示 VBA工具代码说明 VBA录制宏小技巧 结束语,VBA知识简介,什么是 VBA？VBA是Visual Basic for Application的简写，它以VB语言为基础，经过修改并运行在Microsoft Office的应用程序，如Excel，Word中，它虽然不能像VB一样生成可执行的程序，但是能帮助用户编写一系列的宏，用来对数据进行批量处理，进行快速的逻辑操作和算术操作，或者进行文件的读写操作，繁琐复杂的文件处理，变得轻松易做。,VBA
2、知识简介,VB与VBA的区别VBA是内含与office各软件的宏语言,VB则是单纯包装及执行的程序语言, 二者差异如下: 编译执行文件:执行文件的扩展名为EXE,VB由于内含编译器,故可制作EXE文件。VBA则由于内含于office系列各软件内，且不提供编译器，故VBA程序只可依附于各软件而执行，无法制作执行文件。 可用的资源：也就是程序内可引用的资源，包括对象、函数等。VB在此方面的资源较VBA为广，因二者比较 ，VB方面是较专业的程序设计语言，而VBA的目的则是强化Office应用系统，故在可用资源方面，VBA不及VB。 基本语法：VBA及VB语法完全相同，故只要稍有Basic基础，即可使
3、用VBA。,VBA语法说明,相关网站 http:/ http:/ http:/ VBA文件操作的代码示例 VBA链接数据库的代码示例,VBA简单宏定义的代码示例,可用 Cells 属性及行号和列标引用单个单元格。该属性返回代表单个单元格的 Range 对象。下例中，Cells(6,1) 返回工作表“Sheet1”上的单元格“A6”，然后将 Value 属性设置为 10。Sub EnterValue()Worksheets(“Sheet1“).Cells(6, 1).Value = 10End Sub可用变量代入单元格索引值，Cells 属性非常适于在单元格区域中循环，如下例所示。Sub Cyc
4、leThrough()Dim counter As IntegerFor counter = 1 To 20Worksheets(“Sheet1“).Cells(counter, 3).Value = counterNext counterEnd Sub,VBA文件操作的代码示例,1、激活工作簿用 Activate 方法激活工作簿后，该工作簿将放在活动窗口中。下述过程激活已打开的工作簿“MyBook.xls”。Sub MakeActive()Workbooks(“MyBook.xls“).ActivateEnd Sub 2、创建新工作簿如果要用 Visual Basic 创建新的工作簿，应使用
5、 Add 方法。下述过程创建了新工作簿。Microsoft Excel 自动将该工作簿命名为“BookN”，其中 N 是下一个可用的数字。新工作簿将成为活动工作簿。Sub AddOne()Workbooks.AddEnd Sub,VBA文件操作的代码示例,3、打开工作簿用 Open 方法打开一个工作簿时，该工作簿将成为 Workbooks 集合的成员。下述过程打开 C 盘上“MyFolder”文件夹内的工作簿“MyBook.xls”。Sub OpenUp()Workbooks.Open(“C:MyFolderMyBook.xls“)End Sub 4、用编号引用单元格可用 Cells 属性及行
6、号和列标引用单个单元格。因为可用变量代入单元格索引值，所以 Cells 属性非常适于在单元格区域中循环。Sub CycleThrough()Dim counter As IntegerFor counter = 1 To 20Worksheets(“Sheet1“).Cells(counter, 3).Value = counterNext counterEnd Sub,VBA文件操作的代码示例,5、关闭工作簿 Sub CloseFirst()Workbooks(1).Close End Sub 6、操作文件的输入/输出 (I/O)语法Open pathname For mode Access
7、 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,VBA文件操作的代码示例,（3）下列代码以随机方式打开文件，文件中含有用户自定义数据类型 Record 的记录。Type Record
8、 定义用户自定义数据类型。 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）下列代码以只允许读的二进制方式打开文件；其他过程不可以读该
9、文件。Open “TESTFILE“ For Binary Access Read Lock Read As #1,VBA文件操作的代码示例,7、关闭工作簿本示例使用 Input # 语句将文件内的数据读入两个变量中。本示例假设 TESTFILE文件内含数行以 Write # 语句写入的数据；也就是说，每一行数据中的字符串部分都是用双引号括起来，而与数字用逗号隔开，例如，（“Hello“, 234）。Dim MyString, MyNumberOpen “TESTFILE” For Input As #1 打开输入文件。Do While Not EOF(1) 循环至文件尾。 Input #1,
10、 MyString, MyNumber 将数据读入两个变量。 Debug.Print MyString, MyNumber 在立即窗口中显示数据。LoopClose #1 关闭文件。,VBA链接数据库的代码示例,a)ODBC Driver for Access i)普通安全模式 oConn.Open “Driver=Microsoft Access Driver (*.mdb);” “,VBA链接数据库的代码示例,c)ODBC Driver for Oracle i)使用現有的Oracle ODBC Driver from Microsoft: oConn.Open “Driver=Micro
11、soft ODBC for Oracle;“ “,VBA工具演示,由于VBA和VB基本一样，所以通过上述的实例，根据自己项目中做的 小工具，进行描述，让大家有个感性的认识。,VBA工具演示,工具1.自动批量生成SQL文由于项目中，每天日方会提供大量（上百万）的初始数据，并需要把这些数据进行选择性抽出。但DB2的select A from B where k in()语句，最多一次性只能in 几千条数据。所以要分批抽出。于是分割数据，通过每1000行抽一次数据，循环做成sql语句,方便处理。,VBA工具演示,在第一列贴入初始数据，然后按下SQL做成按钮，结果如下所示：,VBA工具代码说明,Pri
12、vate intHinnbannRows As Integer Private intHinnbannCount As Integer Private 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;“
13、 Private Sub CommandButton1_Click()各自SQL取得。Dim strSql As StringDim strHinnbann As StringDim intSqlRows As IntegerintHinnbannRows = 2intHinnbannCount = 0intSqlRows = 2 当工場sheet的某行的第一列数值不等于空的时候，就一直循环扫描赋值。While ThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intHinnbannRows, 1).Value “intHinnbannCount
14、= intHinnbannCount + 1,VBA工具代码说明,strHinnbann = ThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intHinnbannRows, 1).Value如果品番的数量除以1000的余数是1的时候，执行下列语句。初始前半部sql语句，进行下一条sql语句的做成。If (intHinnbannCount Mod 1000 = 1) ThenstrSql = strPreSelectEnd IfstrSql = strSql + strHinnbann如果品番的数量除以1000的余数是0的时候，执行下列语句。目的
15、是为了连接多个品番。If (intHinnbannCount Mod 1000 = 0) ThenstrSql = strSql + strNextSelectThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intSqlRows, 9).Value = strSqlintSqlRows = intSqlRows + 1strSql = “ElsestrSql = strSql + “,“End IfintHinnbannRows = intHinnbannRows + 1Wend,VBA工具代码说明,如果品番的数量不是1000的倍数时，对最后一句
16、sql语句做特殊处理。补全后半部sql。If (intHinnbannCount Mod 1000 0) ThenstrSql = strSql + strNextSelectstrSql = Replace(strSql, “,“, “)ThisWorkbook.Worksheets(cnKoujilyouSheet).Cells(intSqlRows, 9).Value = strSqlEnd IfMsgBox “検証完了。“ End Sub,VBA工具演示,工具2.提取log内容，生成报表由于本番环境操作非常严格，需要有相应的log文件记录。此工具的目的是对这些log文件进行check，
17、查出任何时间某人虽然操作了本番环境，但没有及时申请，那么该人员就会出力到这个文件里，留有记录，加强本番安全性。入力文件如下所示。,VBA工具演示,按下“检证开始”按钮,VBA工具演示,PF基盤（Windows）_pdfsvr1_201004.xls的文件出力。,VBA工具演示,结果文件显示,XXXX是代名。,VBA工具代码说明,上述工具应用的知识点有xls文件的读写操作，表格的复制，数据的排序以及基本的vba语句循环赋值。 xls文件的读写操作 表格的复制 数据的排序,VBA工具代码说明,1.xls文件的读写操作Public Sub SetLeaderParamList() 関連付表.xlsi
18、ndexRowDim indexRow As IntegerintLeaderParamListCountDim intLeaderParamListCount As Integer関連付表.xlsDim strLeaderListFile As StringstrLeaderListFile = pstrLocalPath & “ & conLeaderKanlanFile関連付表.xls選択Application.Workbooks(strLeaderListFile).Activate関連付表.xls開Workbooks.Open (strLeaderListFile)indexRow
19、= 4intLeaderParamListCount = 0,VBA工具代码说明,当conLeaderSheet的某行的第3列数值不等于空的时候，就一直循环扫描赋值。做成g_LeaderParamList的结构体数组。 While ActiveWorkbook.Worksheets(conLeaderSheet).Cells(indexRow, 3).Value “ReDim Preserve g_LeaderParamList(intLeaderParamListCount)組織g_LeaderParamList(intLeaderParamListCount).strSosiki = Ac
20、tiveWorkbook.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(ind
21、exRow, 4).ValueintLeaderParamListCount = intLeaderParamListCount + 1indexRow = indexRow + 1 Wend ActiveWorkbook.CloseEnd Sub,VBA工具代码说明,未申請報告書書 Private Sub writeToMiSinnSeyiSilyoSheet(strServerId As String, sheetAppName As String, arrLogContext() As String)Dim strLineDate() As String ReDim strLineDat
22、e(2)使用者情報Dim siyousilyaJilyouhou As SiyousilyaJilyouhouParam年月日Dim pstrYmd As String時間Dim pstrTime As StringIDDim pstrUserId As String名Dim pstrServerId As StringIPDim pstrIpAddress As String pstrYmd = Mid(arrLogContext(0), 1, 10),VBA工具代码说明,pstrTime = Mid(arrLogContext(0), 12, 8) & “-“ & Mid(arrLogCo
23、ntext(1), 12, 8)pstrServerId = RTrim(arrLogContext(2)pstrUserId = RTrim(Replace(arrLogContext(3), “LOGON“, “)pstrIpAddress = RTrim(arrLogContext(7)Worksheets(sheetAppName).Unprotect Password:=workBookPasswordWorksheets(sheetAppName).Cells(intIndexAppRows, 2).Value = pstrYmdWorksheets(sheetAppName).C
24、ells(intIndexAppRows, 3).NumberFormatLocal = “h:mm:ss“Selection.NumberFormatLocal = “h:mm:ss“Worksheets(sheetAppName).Cells(intIndexAppRows, 3).Value = pstrTimeWorksheets(sheetAppName).Cells(intIndexAppRows, 4).Value = pstrServerIdWorksheets(sheetAppName).Cells(intIndexAppRows, 5).Value = pstrUserId
25、Worksheets(sheetAppName).Cells(intIndexAppRows, 6).Value = pstrIpAddresssiyousilyaJilyouhou = getSiyousilyaJilyouhou(pstrUserId, pstrServerId)Worksheets(sheetAppName).Cells(intIndexAppRows, 7).Value = siyousilyaJilyouhou.strSiyousilyaNameWorksheets(sheetAppName).Cells(intIndexAppRows, 8).Value = siy
26、ousilyaJilyouhou.strSiyousilyaSosikiWorksheets(sheetAppName).Cells(intIndexAppRows, 9).Value = siyousilyaJilyouhou.strSiyousilyaLeaderNameintIndexAppRows = intIndexAppRows + 1 End Sub,VBA工具代码说明,2.表格的复制 GetsheetN Private Sub GetsheetN(sheetInfo As PramSheetInfo)If Worksheets.count = 3 ThenApplication
27、.DisplayAlerts = FalseWorksheets(“Sheet2“).DeleteWorksheets(“Sheet3“).DeleteApplication.DisplayAlerts = TrueEnd If Dim fileName As StringfileName = GetErrorFileName(sheetInfo)ThisWorkbook.Unprotect workBookPasswordThisWorkbook.Worksheets(conWinSheet).Visible = TrueThisWorkbook.Worksheets(conWinSheet
28、).Copy After:=Workbooks(fileName).Sheets(1)ThisWorkbook.Worksheets(conWinSheet).Visible = FalseThisWorkbook.Protect workBookPassword, Structure:=True, Windows:=False End Sub,VBA工具代码说明,3.数据的排序 結果、年月日時間昇順並替 Private Sub OrderSheet(sheetAppName As String)Worksheets(sheetAppName).Range(“B7“ & “:“ & “O“ &
29、 intIndexAppRows - 1).SelectWorksheets(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
30、:=xlTopToBottom, SortMethod:=xlPinYin, DataOption1:= _xlSortNormal, DataOption2:=xlSortNormal End Sub,VBA录制宏小技巧,所谓录制宏，就是在编成中发现一些不懂的问题，通过录制宏的功能可以直接生成需要的代码。 下面就如何使用宏录制功能，进行描述。Sheet1的第6行的第一列输入 10。代码将自动生成。Sub EnterValue()Range(“A6“).SelectActiveCell.FormulaR1C1 = “10“ End Sub 操作步骤： 1.选择菜单的ToolsMacroReco
31、rd New Macro。 2.点击Record New Macro，启动宏录制功能。 3.Sheet1的第6行的第一列输入 10。然后按停止的小图标，代码将自动生成。 4.选择菜单的ToolsMacroVisual Basic Editor,显示代码。 5.选择菜单的ToolsMacroMacros,运行宏。 6.查看运行宏后的结果。,VBA录制宏小技巧,1.菜单的ToolsMacroRecord New Macro,VBA录制宏小技巧,2.点击Record New Macro,跳出下面框框，直接点OK，宏录制功能启动。会出现下方的小图标，表明宏录制启动成功。,VBA录制宏小技巧,3.Sheet1的第6行的第一列输入 10。然后按停止的小图标，代码将自动生成。,VBA录制宏小技巧,4.选择菜单的ToolsMacroVisual Basic Editor,显示代码,VBA录制宏小技巧,5.选择菜单的ToolsMacroMacros,运行宏，消息框跳出。,VBA录制宏小技巧,6.查看运行宏后的结果。,结束语,Q&A,结束语,谢谢大家,
