excel的宏教程應(yīng)用.doc

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA很多教編程的書籍，都喜歡用一個(gè)Hello World的小應(yīng)用程序開頭，我們的VBA之旅也從這里開始吧。進(jìn)入VBA編輯器，由插入菜單中插入一個(gè)模塊，然后再由插入菜單中插入我們的第一個(gè)應(yīng)用程序，名稱為ExcelbaSub1，然后VBA編輯器就自動(dòng)生成一個(gè)子過(guò)程，在子過(guò)程的中間空白處鍵入我們的代碼：Public Sub ExcelbaSub1()Dim TStr As StringTStr = Hello World!Application.ActiveSheet.Range(A1).Value = TStrDebug.Print TStrMsgBox TStr, , End Sub 我們的Hello World的小應(yīng)用程序就成完了，下面按運(yùn)行菜單中的運(yùn)行子過(guò)程/用戶窗體，或在點(diǎn)擊工具欄的運(yùn)行按鈕，或按F5就可以運(yùn)行我們的應(yīng)用程序了，除此外還可以從Excel主界面的工具/宏/宏中，選擇ExcelbaSub1來(lái)運(yùn)行。下面來(lái)解釋一下這個(gè)應(yīng)用程序的每個(gè)句的用途：第一句：Public關(guān)鍵字是聲明這個(gè)子應(yīng)用程序是公用的，Sub是聲明子應(yīng)用程序的關(guān)鍵字，而ExcelbaSub1就是我們的子應(yīng)用程序名稱了。第二句：Dim是聲明變量的關(guān)鍵字，這里我們用來(lái)聲明一個(gè)名為TStr的字符串變量。第三句：將TStr這個(gè)變量賦值為Hello World!第四句：將當(dāng)前工作表的A1格的值設(shè)定為Hello World!第五句：利用Debug.Print地立即窗口中顯示TStr的值。第六句：用消息對(duì)話框顯示TStr的值。最后一句：標(biāo)記結(jié)束子應(yīng)用程序。應(yīng)用程序雖小，但最基本的元素都齊了，也許，這就是Hello World的魅力所在吧*_*Microsoft excel是一款功能非常強(qiáng)大的電子表格軟件。它可以輕松地完成數(shù)據(jù)的各類數(shù)學(xué)運(yùn)算，并用各種二維或三維圖形形象地表示出來(lái)，從而大大簡(jiǎn)化了數(shù)據(jù)的處理工作。但若僅利用excel的常用功能來(lái)處理較復(fù)雜的數(shù)據(jù)，可能仍需進(jìn)行大量的人工操作。但excel的強(qiáng)大遠(yuǎn)遠(yuǎn)超過(guò)人們的想象-宏的引入使其具有了無(wú)限的擴(kuò)展性，因而可以很好地解決復(fù)雜數(shù)據(jù)的處理問(wèn)題。隨著支持WIN的應(yīng)用應(yīng)用程序的不斷增多和功能的不斷增強(qiáng)，越來(lái)越多的應(yīng)用程序增加了宏處理來(lái)方便用戶的自由擴(kuò)展。但初期各應(yīng)用應(yīng)用程序所采用的宏語(yǔ)言并不 統(tǒng)一，這樣用戶每使用一種應(yīng)用應(yīng)用程序時(shí)都得重新學(xué)習(xí)一種宏語(yǔ)言。為了統(tǒng)一各種應(yīng)用應(yīng)用程序下的宏，Microsoft推出了VBA（Visual Basic for Applications）語(yǔ)言。VBA是從流行的Visual Basic編程語(yǔ)言中派生出來(lái)的一種面向應(yīng)用應(yīng)用程序的語(yǔ)言，它適用于各種WIN應(yīng)用應(yīng)用程序，可以解決各應(yīng)用應(yīng)用程序的宏語(yǔ)言不統(tǒng)一的問(wèn)題。除此之外，使用 VBA語(yǔ)言還有如下優(yōu)點(diǎn)：1、VBA是一種通用應(yīng)用程序語(yǔ)言，通過(guò)它不僅可以共享Microsoft相關(guān)的各種軟件（如excel、 word、access），而且隨著其它的一些軟件（如大名鼎鼎的AutoCAD2000）等對(duì)VBA的支持，這些軟件也已進(jìn)入到了VBA的控制范 圍；2、可以將用VBA編寫的應(yīng)用程序復(fù)制到Visual Basic中調(diào)試并運(yùn)行，從而實(shí)現(xiàn)用Visual Basic來(lái)控制有關(guān)的應(yīng)用應(yīng)用程序；3、VBA提供的大量?jī)?nèi)部函數(shù)大大簡(jiǎn)化了用戶的操作。對(duì)于而今的宏，不僅語(yǔ)言統(tǒng)一規(guī)范，而且其功能也已非常強(qiáng)大。但在大多數(shù)介紹excel的傻瓜書、指南、入門與提高等參考書中往往略過(guò)不提，或淺淺帶過(guò)，讀者從中獲得的有關(guān)知識(shí)往往不足以應(yīng)付處理復(fù)雜數(shù)據(jù)的需求。為了完成工作，就讓我們一起來(lái)學(xué)習(xí)宏的妙用吧。一)、宏的自學(xué)首先需要明確的是，本文不可能教會(huì)您關(guān)于宏的所有內(nèi)容。您需要學(xué)會(huì)利用錄制宏的方法來(lái)學(xué)習(xí)宏：點(diǎn)擊excel 工具下拉菜單中宏下?quot;錄制新宏，此后可象平時(shí)一樣進(jìn)行有關(guān)操作，待完成后停止錄制。然后再點(diǎn)擊工具下拉菜單中宏下宏的編 輯選項(xiàng)即可打開剛才所錄制的宏的Visual Basic源應(yīng)用程序，并且可以在此時(shí)的幫助下拉菜單中獲得有關(guān)的編程幫助。對(duì)錄制宏進(jìn)行修改不僅可以學(xué)習(xí)宏的使用，還能大大簡(jiǎn)化宏的編寫。二)、基本概念為了學(xué)習(xí)excel中的宏，我們需要先了解以下一些基本概念。1、工作簿：Workbooks、Workbook、ActiveWorkbook、ThisWorkbookWorkbooks集合包含excel中所有當(dāng)前打開的excel工作簿，亦即所有打開的excel文件；Workbook對(duì)應(yīng)Workbooks中的成員，即其中的excel文件；ActiveWorkbook代表當(dāng)前處于活動(dòng)狀態(tài)的工作簿，即當(dāng)前顯示的excel文件；ThisWorkbook代表其中有Visual Basic代碼正在運(yùn)行的工作簿。在具體使用中可用Workbooks(index)來(lái)引用Workbook對(duì)象，其中index為工作簿名稱或編號(hào)；如Workbooks(1)、 Workbooks(年度報(bào)表.xls)。而編號(hào)按照創(chuàng)建或打開工作簿的順序來(lái)確定，第一個(gè)打開的工作簿編號(hào)為1，第二個(gè)打開的工作簿為2。2、工作表：Worksheets、Worksheet、ActiveSheetWorksheets集合包含工作簿中所有的工作表，即一個(gè)excel文件中的所有數(shù)據(jù)表頁(yè)；而Worksheet則代表其中的一個(gè)工作表；ActiveSheet代表當(dāng)前處于的活動(dòng)狀態(tài)工作表，即當(dāng)前顯示的一個(gè)工作表?？捎肳orksheets(index)來(lái)引用Worksheet對(duì)象，其中index為工作表名稱或索引號(hào)；如Worksheets(1)、 Worksheets(第一季度數(shù)據(jù))。工作表索引號(hào)表明該工作表在工作表標(biāo)簽中的位置：第一個(gè)（最左邊的）工作表的索引號(hào)為1，最后一個(gè)（最右邊 的）為Worksheets.Count。需要注意的是：在使用過(guò)程中excel會(huì)自動(dòng)重排工作表索引號(hào)，保持按照其在工作表標(biāo)簽中的從左至右排列，工作表的索引號(hào)遞增。因此，由于可能進(jìn)行的工作表添加或刪除，工作表索引號(hào)不一定始終保持不變。3、圖表：Chart 、Charts、ChartObject、ChartObjects、ActiveChartChart代表工作簿中的圖表。該圖表既可為嵌入式圖表（包含在ChartObject中），也可為一個(gè)分開的（單獨(dú)的）圖表工作表。Charts代表指定工作簿或活動(dòng)工作簿中所有圖表工作表的集合，但不包括嵌入式在工作表或?qū)υ捒蚓庉嫳碇械膱D表。使用Charts(index) 可引用單個(gè)Chart圖表，其中index是該圖表工作表的索引號(hào)或名稱；如Charts(1)、Charts(銷售圖表)。圖表工作表的索引號(hào)表示 圖表工作表在工作簿的工作表標(biāo)簽欄上的位置。Charts(1)是工作簿中第一個(gè)（最左邊的）圖表工作表；Charts(Charts.Count)為最 后一個(gè)（最右邊的）圖表工作表。 ChartObject代表工作表中的嵌入式圖表，其作用是作為Chart對(duì)象的容器。利用ChartObject可以控制工作表上嵌入式圖表的外觀和尺寸。ChartObjects代表指定的圖表工作表、對(duì)話框編輯表或工作表上所有嵌入式圖表的集合?？捎蒀hartObjects(index)引用單個(gè) ChartObject，其中index為嵌入式圖表的編號(hào)或名稱。如Worksheets(Sheet1).ChartObjects(1)、 Worksheets(sheet1).ChartObjects(chart1)分別對(duì)應(yīng)Sheet1工作表中的第一個(gè)嵌入式圖表、以及名 為Chart1的嵌入式圖表。ActiveChart可以引用活動(dòng)狀態(tài)下的圖表，不論該圖表是圖表工作表，或嵌入式圖表。而對(duì)于圖表工作表為活動(dòng)工作表時(shí)，還可以通過(guò)ActiveSheet屬性引用之。4、單元格：Cells、ActiveCell、Range、AreasCells(row,column)代表單個(gè)單元格，其中row為行號(hào)，column為列號(hào)。如可以用Cells(1,1)、Cells(10,4)來(lái)引用A1、D10 單元格。ActiveCell代表活動(dòng)工作表的活動(dòng)單元格，或指定工作表的活動(dòng)單元格。Range代表工作表中的某一單元格、某一行、某一列、某一選定區(qū)域（該選定區(qū)域可包含一個(gè)或若干連續(xù)單元格區(qū)域）或者某一三維區(qū)域?？捎肦ange(arg)來(lái)引用單元格或單元格區(qū)域，其中arg可為單元格號(hào)、單元格號(hào)范圍、單元格區(qū)域名稱。如Range(A5)、 Range(A1:H8)、Range(Criteria)。雖然可用Range(A1)返回單元格A1，但用Cells更方便，因?yàn)榇藭r(shí)可 用變量指定行和列。dvnews_page可將Range與Cells結(jié)合起來(lái)使用，如 Range(Cells(1,1),Cells(10,10)代表單元格區(qū)域A1:J10；而 expression.Cells(row,column)返回單元格區(qū)域中的一部分，其中expression是返回Range的表達(dá)式，row和 column為相對(duì)于該區(qū)域的左上角偏移量。如由Range(C5:C10).Cells(1,1)引用單元格C5。Areas 為選定區(qū)域內(nèi)的連續(xù)單元格塊的集合，其成員是Range對(duì)象。而其中的每個(gè)Range對(duì)象代表選定區(qū)域內(nèi)與其它部分相分離的一個(gè)連續(xù)單元格塊。某些操作不 能在選定區(qū)域內(nèi)的多個(gè)單元格塊上同時(shí)執(zhí)行；必須在選定區(qū)域內(nèi)的單元格塊數(shù)Areas.Count上循環(huán)，對(duì)每個(gè)單獨(dú)的單元格塊分別執(zhí)行該操作。此時(shí)，可用 Areas(index)從集合中返回單個(gè)Range對(duì)象，其中index為單元格塊編號(hào)；如Areas(1)。5、 行與列：Rows、Columns、Row、ColumnRows、Columns分別代表活動(dòng)工作表、單元格區(qū)域范圍Range、指定工作表中的所有行數(shù)、列數(shù)。對(duì)于一個(gè)多選單元格區(qū)域范圍Range的 Rows、Columns，只返回該范圍中第一個(gè)區(qū)域的行數(shù)、列數(shù)。例如，如果Range對(duì)象有兩個(gè)區(qū)域（areas）A1:B2和 C3:D4，Rows.Count返回2而不是4?？赏ㄟ^(guò)Rows(行號(hào))、Columns(列號(hào))來(lái)引用相應(yīng)的行與列；如Rows(3)、Columns(4)分別對(duì)應(yīng)第三行、D列。利用Rows、Column可以獲得區(qū)域中第一塊的第一行行號(hào)、第一列列號(hào)，所得值均以十進(jìn)制數(shù)表示。三)、處理單元格1、直接賦值與引用將變量、常量值直接賦給單元格、或?qū)卧竦闹抵苯淤x給變量、常量，這是在excel中最簡(jiǎn)單的單元格賦值及引用方法。如下例將工作表Sheet1A1單元格的值賦給Integer變量I，并將I+1的值賦給當(dāng)前工作表中的B1單元格：Dim I As IntegerI=Worksheets(Sheet1).Cells(1,1) Cells(1,2).Select 選定B1單元格，使其成為當(dāng)前單元格ActiveCell=I+1 以I+1為當(dāng)前單元格賦值2、用公式賦值在宏的使用中，可能會(huì)更多地用公式來(lái)給單元格賦值。如下例將相對(duì)于活動(dòng)單元格左側(cè)第4列、向上第6行至向上第2行的單元格數(shù)值之和賦給活動(dòng)單元格（以本行、本列為第0行、0列）：ActiveCell.Formula=AVERAGE(R-6C-4:R-2C-4)3、引用其它工作表中的單元格當(dāng)賦值公式中需要引用其它工作表中的單元格時(shí)，在被引用的單元格前加上工作表名！即可。如以下即在賦值中引用了Sheet1工作表中的A1至A4單元格：Range(E10).Formula=SUM(Sheet1!R1C1:R4C1)但需注意的是：當(dāng)被引用的工作表名中含有某些可能引起公式歧義的字符時(shí)，需要用單引號(hào)將工作表名括起來(lái)。如：Worksheets(Sheet1).ActiveCell.Formula=Max(1-1剖面!D3:D5)4、引用其它工作簿中的單元格在被引用單元格所在工作表名前加上工作簿名，即可引用其它工作簿中的單元格。如：ActiveCell.Formula=MAX(Book1.xlsSheet3!R1C:RC4)同樣需注意的是：當(dāng)被引用的工作簿名中含有某些可能引起公式歧義的字符時(shí)，需要用中括號(hào)、及單引號(hào)將工作簿名括起來(lái)。如：Cells(1,2).Formula=MIN(1995-2000總結(jié).xls1995-1996年! $A$1:$A$6)5、避免循環(huán)引用在上述公式賦值過(guò)程中，應(yīng)避免在公式中引用被賦值的單元格，防止循環(huán)引用錯(cuò)誤。6、添加批注可按如下方法格給單元格添加批注：Dim 批注文本 As String批注文本=批注示例 準(zhǔn)備批注文本ActiveCell.AddComment 添加批注ActiveCell.Comment.Text Text:=臨時(shí) 寫入批注文本ActiveCell.Comment.Visible=False 隱藏批注dvnews_page7、添加、刪除、復(fù)制、剪切、粘貼單元格Range(D10).Insert Shift:=xlToRight 在D10單元格處添加一新單元格，原D10格右移 Range(C2).Insert Shift:=xlDown 在C2單元格處添加一新單元格，原C2格下移Rows(2).EntireRow.Insert 在第2行前添加一空白行，原第2行下移Columns(3).EntireColumn.Insert 在C列前添加一空白列，原C列右移Columns(A:D).Delete Shift:=xlToLeft 刪除A列至D列，其右側(cè)列左移Rows(3:5).Delete Shift:=xlUp 刪除第3行至第5行，其下方行上移Range(B2).EntireRow.Delete 刪除第2行Range(C4).EntireColumn.Delete 刪除C列Range(B10:C13).Copy 復(fù)制B10至C13單元格區(qū)域Cells(1,2).Cut 剪切B1單元格Range(D10).SelectActiveSheet.Paste 自D10單元格起粘貼剪貼板中的內(nèi)容四)、圖表1、工作表圖表以下為一添加工作表圖表的實(shí)例。Charts.Add after:=Worksheets(Sheet1) 在Sheet1工作表之后添加新圖表工作表ActiveChart.ChartType=xlXYScatterSmooth 圖表類型為XY平滑線散點(diǎn)圖ActiveChart.SetSourceData Source:=Sheets(結(jié)點(diǎn)坐標(biāo)).Range(A1:B69), PlotBy:= _xlColumns 圖表數(shù)據(jù)來(lái)源于結(jié)點(diǎn)坐標(biāo)工作表的A1至B69單元格，且按列繪圖。ActiveChart.Location Where:=xlLocationAsNewSheetWith ActiveChart.HasTitle = True.ChartTitle.Characters.Text = 節(jié)點(diǎn)坐標(biāo) 圖表標(biāo)題節(jié)點(diǎn)坐標(biāo).Axes(xlCategory, xlPrimary).HasTitle = True.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = x x軸標(biāo)題x.Axes(xlValue, xlPrimary).HasTitle = True.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = y y軸標(biāo)題yEnd WithWith ActiveChart.Axes(xlCategory).HasMajorGridlines = True 顯示x軸主網(wǎng)格線，默認(rèn)情況下為顯示.HasMinorGridlines = True 顯示x軸次網(wǎng)格線，默認(rèn)情況下為不顯示End WithWith ActiveChart.Axes(xlValue).HasMajorGridlines = True 標(biāo)出x軸主網(wǎng)格值，默認(rèn)情況下為標(biāo)注.HasMinorGridlines = False 取消x軸次網(wǎng)格值標(biāo)注，默認(rèn)情況下為不標(biāo)注End WithActiveChart.Legend.Position = xlRight 圖例顯示在圖表右側(cè)2、嵌入式圖表嵌入式圖表僅在添加方式及引用格式上與工作表圖表有所不同，而對(duì)圖表的設(shè)置基本類似。詳見下例。Set嵌入表=ActiveSheet.ChartObjects.Add(0,0,200,300) 在當(dāng)前工作表(0,0)坐標(biāo)處添加寬200，高300的嵌入式圖表嵌入表.Chart.ChartType = xlColumnClustered 圖表類型為簇狀柱形圖嵌入表.Chart.SetSourceData Source:=Sheets(1).Range(A2