《VBA基础教程》PPT课件.ppt

自動化報表簡介-VBA,1,目錄,VBA基礎窗體設計字典法ADO對象+SQL語句圖表對象API、XML、數據透析表等技術分享,2,第一章：VBA基礎VBA簡介,1.什麽是VBA？VBA(VisualBasicForApplication)是VisualBasic的一种宏语言,宏也稱巨集，是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。主要能用来扩展Windows的应用程序功能，特别是MicrosoftOffice软件。也可说是一种应用程序视觉化的Basic脚本。该语言于1993年由微软公司发行。微软在1994年发行的Excel5.0版本中，即具备了VBA的巨集功能。使用巨集可以增强Word，Excel等软件的自动化能力，使用户更高效的完成特定任务。2.VBA的作用a.實現Excel沒有的功能b.提高Excel的運行效率c.編寫自訂函數d.實現自動化功能e.通過插入表單實現消息資料管理軟體,3.VBA與巨集巨集是什麼？我理解的巨集是一系列固定動作的集合，這個集合當遇到讓他執行的條件後就逐個執行。有人把我們錄製的宏比喻成一個武術運動員在比賽中練就的一段武術套路動作，這個套路遇到一定的條件（比賽）就執行。巨集實際上就是一個簡單的VBA的Sub過程，它保存在模組裡，以Sub開頭，以EndSub結尾，執行時就從第一句逐句執行，直到EndSub結束。巨集的優點：1）巨集能夠説明使用者處理複雜的重複性的工作，比如上色，改變格式等；2）説明使用者快速創建部分內置功能VBA代碼，如排序、篩選等；3）巨集的錄製簡單，不需要VBA知識，對與初學者易上手。巨集的缺點：1）代碼生硬，欠缺靈活，並且嚴重沉餘，不便於閱讀；2）不可以建立公式，函數；3）沒有判斷或迴圈的功能；4）不能進行人機交互；5）無法顯示使用者表單；6）無法與其他軟體或檔進行互動。,VBA簡介之巨集優缺點,VBA簡介之錄製與執行巨集（宏）,4,4.巨集錄製與執行錄製巨集可使excel自動產生VBA程式代碼，是學習VBA最好的老師。如何錄製巨集？打開一個Excel檔，步驟如下：（1）選中任意一個儲存格；（2）打開菜單開發人員錄製巨集，調出巨集錄制對話方塊，輸入巨集名（mysub），確定；（3）設置儲存格字體為仿宋體，紅色；（4）點擊停止錄巨集。這樣，一個簡單的設置單元格格式的巨集就錄好了。如何執行巨集？滑鼠點一點，巨集給儲存格換新裝（執行這段巨集）。（1）選中需要設置的任意儲存格或儲存格區域（為了看出效果，儲存格最好有字元）；（2）打開菜單檢視巨集檢視巨集，顯示“巨集”對話方塊（快捷鍵：Alt+F8）;（3）選擇我們剛才錄製的巨集，按一下“執行”。,5,VBA簡介之VBE（1）,5.VBE簡介什麽是VBE？VBE就是VBA的編輯窗口，所有的VBA代碼都在VBE里完成。VBE是一個分離出來的應用程序，它可以與Excel無縫結合，但是需要說明的是運行VBE必須先運行Excel，VBA模塊與Excel工作薄文件一起存儲，除非激活VBE，否則VBA模塊是看不見的。、运行Excel后，怎样切换换到VBE窗口？（1）按ALT+F11快捷键或者选择“開發人員”“VisualBasic”命令。,6,VBA簡介之VBE（2）,“工程資源管理器”顯示一個樹型圖示，包含了當前在Excel中打開的所有Excel物件，包含工作表，模組，表單，增益集及隱藏的工作薄，每個工作薄被認為是一個工程。,菜單欄,工具欄,工程資源管理器,屬性窗口,立即窗口,代碼窗口,初识VBE窗口,7,VBA基礎對象、屬性、方法，事件,1.對象对象就是存在的物體，是VBA处理的内容，包括工作薄、工作表、工作表上的单元格区域、图表等等。对象可以相互包含，一个工作薄对象可以包含多个多个工作表对象，一个工作表对象又可以包含多个单元格(或图表,图形等)，这种对象的排列模式称为Excel的对象模型。在我們編程中常用到的有四大對象，它們分別是：application、workbook、worksheet、range。關於四大對象的介紹，由於他內容繁多，就像字典一樣，我們只需使用時去查找便可。2.屬性每一个对象都有属性，一个属性就是对一个对象的一个设置，引用对象的属性同样也要用点来分隔。3.方法每一个对象都有方法，方法就是在对象上执行的某个动作。和属性相比，属性表示的是对象某种状态或样子，是静态的，就像是语文里的名词、形容词和副词，而方法则是做某件事的一个动作，就像动词，对象和方法同样用点来分隔。4.事件事件就是由用户或者系统触发的，可以在代码中响应的一段代码,VBA基礎之過程,8,一個過程就是執行某些動作的代碼組合。VBA過程分Sub過程和Function過程。1、Sub過程總是以“sub過程名()”開頭，以EndSub”結尾，一個過程就是執行某項動作的一套指令，Sub過程不返回運行的結果。*Sub子過程可執行的一系列操作，無返回值可在該過程外用CALL顯示調用調用CALLmysub(參數1，參數2，)2、Function總是以“Function程式名()”開頭，以“EndFunction”結尾，和Sub過程的區別是Function過程返回程式運行的值，值可以是一個值或一個陣列，就像我們的工作表函數，Function過程也就是我們說的自訂函數。*Function函數執行一系列操作，返回執行結果返回值直接利用函數名調用獲得調用x=myFuntion(參數),VBA過程,9,要設計一個過程，其實很簡單，把需要的物件收集起來，看看我們要對他們的屬性進行怎樣的修改？我們需要做什麼？需要用什麼樣的方法？要用哪些事件？收集起來就是一個完整的過程。同時，建議你在VBE窗口中，按一下“工具選項”，在彈出的“選項”對話方塊裡勾選“自動列出成員”。設置好後,試著在代碼視窗裡輸入點代碼,對於初學的我們,很需要它。,VBA基礎之變量(1),10,變數是用於保存在程式運行過程中需要臨時保存的值或物件。變量可以使程式變得更加富有活力.定義變數Dim語句定義變數，語法如下：Dim變數名As資料類型DimMyNameAsString在Dim語句中，我們可以不必聲明變數的資料類型，直接輸入“Dim變數名”此時定義的變數將被指定為Variant類型。但我們在程式設計的過程中，一般應該明確資料的類型，因為指定資料類型後會提高程式的運行速度。作用域定義變數除了可以使用Dim語句外，比較常的還有：static語句，Private語句，Public語句。使用不同的語句定義的變數不同的是它們的作用作用域不同，具體為：,VBA基礎之變量(2),11,（1）如果在一個過程中包含了一個Dim或Static語句，此時聲明的變數作用域為此過程，即本地變數。（2）如果在一個模組的第一個過程之前包含了Dim或Prvate語句，此時聲明的變數作用域為此模組裡所有的過程，也就是在此模組裡所有的過程都可以使用它，即模塊作用域下的變量；（3）如果在一個模組的第一個過程之前包含了Public語名，此時聲明的變數作用域為所有模組，即公有變數。變數賦值用等號（）是VBA裡的設定運算子。如：給字串A賦值為，“我在學習VBA!”。則直接輸入:A=我在學習VBA!“變量名稱要注意幾點:A.變量名必須以字母開始，並且只能包含字母數字和特定的特殊字符。B.不能把關鍵字做變數名,部分符號不能在變數中出現,如(#*%!)C.不區分大小寫,但我們一般大小寫混寫，第一個字母大寫，其它的小寫。D.也可以使用中文名稱，但是儘量不用中文名稱，如果在英文版會報錯。E.變量名最大長度為255個字符,VBA基礎之變量數據類型,12,資料類型是變數的特性，資料類型包括：,數據類型（續表）,13,VBA基礎之常量/數組,14,常量:執行過程時，變數的值會發生改變，所以我們稱它為“變數”，但有時候我們需要引用不會發生變化的資料，這就是常量。常量聲明後，不以對它進行再賦值。聲明常量設定常量的值，需要用Const語句：Const常量名As資料類型常量的值ConstPIAsLong=3.1415926注：常量通常用全大寫字母以區別于變量。與變量一樣，常量的定義位置決定了它的作用域數組:陣列是具有相同資料類型並且共用一個名字的一組變數的集合。陣列也是變數。如何聲明陣列?可以用Dim語句或者Public語句聲明陣列。Dim/Public陣列名稱(atob)as資料類型a和b是陣列中元素的個數，陣列元素素最小值為a，最大值為b，元素個數為(b-a+1)個，當然，你也可以直接輸入個數，此時預設最小索引號為0。注：在VBA中數組的下標默認的是從0開始。,VBA基礎之运算符和表达式,(1)算术运算符:有+,-,*,/,extendedproperties=excel8.0;datasource=”ExtendedProperties=Excel8.0;DataSource=extendedproperties=excel8.0;datasource=datasource=&檔全路徑3.設置sql語句Sql=sql語句4.執行sql語句放置結果的區域的左上角儲存格.CopyFromRecordsetConn.Execute(Sql)5.關閉聯接conn.Close6.對象置空SetConn=Nothing處理不同問題時深色部分的代碼可以保持不變，只要根據不同情況改變紅色部分的代碼即可。,第五章VBA圖表,图表对象结构图,Chart對象:代表工作簿中的圖表。該圖表既可為嵌入式圖表（包含在ChartObject中），也可為一個分開的圖表工作表。Charts集合物件:指定工作簿或活動工作簿中所有圖表工作表的集合。每個圖表工作表由一個Chart對象代表。本集合不包括嵌入式在工作表或對話方塊編輯表中的圖表。ChartObjects集合物件:指定的圖表工作表、對話方塊編輯表或工作表上所有ChartObject物件的集合。每個ChartObject物件代表一個嵌入式圖表。ChartObject對象:代表工作表中的嵌入式圖表。ChartObject對象的作用是作為Chart對象的容器。ChartObject物件的屬性和方法控制工作表上嵌入式圖表的外觀和尺寸。ChartObject對象是ChartObjects集合的成員。ChartObjects集合包含單個工作表上所有的嵌入式圖表。,新建图表,54,Add方法（Charts集合）新建圖表工作表並將其添加到工作簿中。返回Chart對象。語法expression.Add(Before,After,Count)expression必選。該運算式返回Charts對象。Before/AfterVariant類型，可選。指定工作表物件，新建的圖表將置於此工作表之前/後。CountVariant類型，可選。要創建的圖表的數目。預設值為1。說明如果Before和After兩者均省略，新建的圖表將插入到活動工作表之前下例向活動工作簿添加新圖表工作表，並將該圖表工作表置於工作表”Sheet1”之後。Charts.Addafter:=Worksheets(sheet1)可用Charts(index)（其中index為圖表工作表的編號或名稱）返回單個Chart對象。,Add方法（ChartObjects集合）創建新的嵌入式圖表。返回Chart對象。語法object.Add(Left,Top,Width,Height)object必選。該運算式返回ChartObjects對象。Left,TopLong類型，必選。以磅為單位指定新物件的初始座標，該座標是相對於工作表上儲存格“A1”的左上角或圖表的左上角的座標。Width,HeightLong類型，必選。以磅為單位指定新物件的初始位置本示例創建新的嵌入式圖表。Setco=Sheets(Sheet1).ChartObjects.Add(50,40,200,100),嵌入式圖表實例,55,嵌入式圖表就是嵌入到工作表上的嵌入對象，如下圖Setrng1=Sheets(“sheet1”).Range(B3:N3)Setrng2=Sheets(“sheet1”).Range(B13:N13)Setrng=Union(rng1,rng2)Setmychart1=sheets(“sheet1”).ChartObjects.Add(160,620,350,250)Withmychart1.Chart.ChartType=xlPie.SetSourceDataSource:=rngWith.SeriesCollection(1).Name=“統計圖表.ApplyDataLabels.DataLabels.ShowValue=False.DataLabels.ShowPercentage=True.DataLabels.ShowCategoryName=True.DataLabels.Position=xlLabelPositionBestFit.Border.ColorIndex=1EndWith.ChartArea.Interior.ColorIndex=2EndWith,API、XML、數據透析表等技術（概述）,56,VBA可以實現excel與XML之間的交互應用，如瀏覽并編輯XML數據導入和映射XML數據以及將工作表數據導回到XML文件中。,使用VBA代碼，通過創建過程來建立數據透析表，就可以在需要建立的時候，調用宏代碼來創建數據透析表。同時，使用VBA，能對數據透析表設置一些格式等等。,API(ApplicationProgrammingInerface)函數是構筑Windows框架的基石，它的下面是Windows的操作系統核心，而它的上面則是所有的Windows應用程序。API調用可以提供在VBA中調用Windows一些函數的方法，如查詢Windows系統的一些信息等等。,經驗教訓之談,57,一、提高VBA執行效率,方法（1）儘量實用VBA原有的屬性、方法和worksheet函數例如:ForEachcInWorksheet(1).Range(A1:A1000)TotalValue=TotalValuec.ValueNextAverageValue=TotalValue/Worksheet(1).Range(A1:A1000).Rows.Count而下面代碼程式比上面例子快得多：AverageValue=Application.WorksheetFunction.Average(Worksheets(1).Range(A1:A1000)其它函數如Count,Counta,Countif,Match,Lookup等等，都能代替相同功能的VBA程式碼，提高程式的運行速度。,提高VBA執行效率,58,方法（2）儘量減少實用對象的引用,尤其在循環中1使用With語句。Workbooks(1).Sheets(1).Range(A1:A1000).Font.Name=PayWorkbooks(1).Sheets(1).Range(A1:A1000).Font.FontStyle=Bold.則以下語句比上面的快WithWorkbooks(1).Sheets(1).Range(A1:A1000).Font.Name=Pay.FontStyle=Bold.EndWith2使用對象變數。如果你發現一個物件引用被多次使用，則你可以將此物件用Set設置為對象變數，以減少對物件的訪問。如：Workbooks(1).Sheets(1).Range(A1).Value=100Workbooks(1).Sheets(1).Range(A2).Value=200則以下代碼比上面的要快：SetMySheet=Workbooks(1).Sheets(1)MySheet.Range(A1).Value=100MySheet.Range(A2).Value=200,59,3在迴圈中要儘量減少對象的訪問。Fork=1To1000Sheets(Sheet1).SelectCells(k,1).Value=Cells(1,1).ValueNextk,方法（3）減少對象的啟動和選擇如果你的通過錄製巨集來學習VBA的，則你的VBA程式裡一定充滿了物件的啟動和選擇，例如Workbooks(XXX).Activate,Sheets(XXX).Select,Range(XXX).Select等,但事實上大多數情況下這些操作不是必需的。,則以下代碼比上面的要快：SetTheValue=Cells(1,1).ValueSheets(Sheet1).SelectFork=1To1000Cells(k,1).Value=TheValueNextk,例如：Sheets(Sheet3).SelectRange(A1).Value=100Range(A2).Value=200,可改為：WithSheets(Sheet3).Range(A1).Value=100.Range(A2).Value=200EndWith,60,方法（4）關閉螢幕更新Application.ScreenUpdating=FalseApplication.ScreenUpdating=true如果你的VBA程式前面三條做得比較差，則關閉螢幕更新是提高VBA程式運行速度的最有效的方法，縮短執行時間2/3左右。,方法（5）添加公式時，關閉自動計算Application.Calculation=xlCalculationManualApplication.Calculation=xlCalculationAutomatic,61,二，在ADO自連自的時候，excel系統經常會出現一些毛病，其中最大的就是對內存的佔用，數據量大的時候，經常會出現記憶體已滿，系統資源不足等提示。在搜索了很多網上論壇後，這裡我們給出一個很好的解決方案。Sub欺騙excel()Application.ScreenUpdating=FalseApplication.DisplayAlerts=FalseThisWorkbook.ChangeFileAccessxlReadOnly轉為唯讀所要執行的SQL語句ThisWorkbook.ChangeFileAccessxlReadWriteApplication.DisplayAlerts=TrueApplication.ScreenUpdating=TrueEndSubThisWorkbook.ChangeFileAccessxlReadOnly關鍵代碼就這一句，實現了欺騙excel的效果，其他兩句作用關閉螢幕刷新，避免出現“唯讀”字樣；禁止提示，避免出現詢問對話方塊,以下案例案例十分精彩,62,這裡我們給出一個學習VBA技術的交流論壇：EXCELHOME,