Excel函數(shù)VBA教程基礎(chǔ)入門(mén)(上).ppt

VBA簡(jiǎn)易教程 （上）,直到90年代早期,使應(yīng)用程序自動(dòng)化還是充滿(mǎn)挑戰(zhàn)性的領(lǐng)域.對(duì)每個(gè)需要自動(dòng)化的應(yīng)用程序,人們不得不學(xué)習(xí) 一種不同的自動(dòng)化語(yǔ)言. 例如:可以用EXCEL的宏語(yǔ)言來(lái)使EXCEL自動(dòng)化,使用WORD BASIC使WORD自動(dòng)化,等等.微軟決定讓它開(kāi)發(fā)出來(lái)的應(yīng)用程序共享一種通用的自動(dòng)化語(yǔ)言-Visual Basic For Application(VBA),可以認(rèn)為VBA是非常流行的應(yīng)用程序開(kāi)發(fā)語(yǔ)言VASUAL BASIC 的子集.實(shí)際上VBA是“寄生于”VB應(yīng)用程序的版本.VBA和VB的區(qū)別包括如下幾個(gè)方面: 1. VB是設(shè)計(jì)用于創(chuàng)建標(biāo)準(zhǔn)的應(yīng)用程序,而VBA是使已有的應(yīng)用程序(EXCEL等)自動(dòng)化 2. VB具有自己的開(kāi)發(fā)環(huán)境,而VBA必須寄生于已有的應(yīng)用程序. 3. 要運(yùn)行VB開(kāi)發(fā)的應(yīng)用程序,用戶(hù)不必安裝VB,因?yàn)閂B開(kāi)發(fā)出的應(yīng)用程序是可執(zhí)行文件(*.EXE),而VBA開(kāi)發(fā)的程序必須依賴(lài)于它的“父”應(yīng)用程序,例如EXCEL. 盡管存在這些不同,VBA和VB在結(jié)構(gòu)上仍然十分相似.事實(shí)上,如果你已經(jīng)了解了VB,會(huì)發(fā)現(xiàn)學(xué)習(xí)VBA非?？? VBA究竟是什么?更確切地講,它是一種自動(dòng)化語(yǔ)言,它可以使常用的程序自動(dòng)化,可以創(chuàng)建自定義的解決方案.,VBA是什么,EXCEL環(huán)境中基于應(yīng)用程序自動(dòng)化的優(yōu)點(diǎn),也許你想知道VBA可以干什么?使用VBA可以實(shí)現(xiàn)的功能包括:,使重復(fù)的任務(wù)自動(dòng)化,自定義EXCEL工具欄, 菜單和界面,簡(jiǎn)化模板的使用,自定義EXCEL,使其 成為開(kāi)發(fā)平臺(tái),創(chuàng)建報(bào)表,對(duì)數(shù)據(jù)進(jìn)行復(fù)雜的 操作和分析,EXCEL環(huán)境中基于應(yīng)用程序自動(dòng)化的優(yōu)點(diǎn),用EXCEL作為開(kāi)發(fā)平臺(tái)有如下原因:,EXCEL本身功能強(qiáng)大,包括打印,文件處理,格式化和文本編輯,EXCEL內(nèi)置大量函數(shù),EXCEL界面熟悉,可連接到多種數(shù)據(jù)庫(kù),錄制簡(jiǎn)單的宏,在介紹學(xué)習(xí)VBA之前，應(yīng)該花幾分鐘錄制一個(gè)宏。,新術(shù)語(yǔ)：“宏”，指一系列EXCEL能夠執(zhí)行的VBA語(yǔ)句。 以下將要錄制的宏非常簡(jiǎn)單，只是改變單元格顏色。請(qǐng)完成如下步驟：,打開(kāi)新工作簿，確認(rèn)其他工作簿已經(jīng)關(guān)閉。,選擇A1單元格。調(diào)出“常用”工具欄。,選擇“工具”“宏”“錄制新宏”。,輸入“改變顏色”作為宏名替換默認(rèn)宏名，單擊確定。,選擇“格式”的“單元格”，選擇“圖案”選項(xiàng)中的紅色,單擊“確定”。,單擊“停止錄制”工具欄按鈕，結(jié)束宏錄制過(guò)程。,宏名最多可為255 個(gè)字符，并且必須以字母 開(kāi)始。其中可用的字符 包括：字母、數(shù)字和 下劃線(xiàn)。,執(zhí)行宏,選擇任何一個(gè) 單元格，比如A3,選擇“工具”“宏” “宏”，顯示“宏”對(duì)話(huà)框,選擇“改變顏色”， 選擇“執(zhí)行”，則A3單 元格的顏色變?yōu)?紅色,當(dāng)執(zhí)行一個(gè)宏時(shí)，EXCEL按照宏語(yǔ)句執(zhí)行的情況就像VBA代碼在對(duì)EXCEL進(jìn)行 “遙控”。但VBA的“遙控”不僅能使操作變得簡(jiǎn)便，還能使你獲得一些使用 EXCEL標(biāo)準(zhǔn)命令所無(wú)法實(shí)現(xiàn)的功能。而且，一旦熟悉了EXCEL的“遙控”，你都會(huì) 奇怪自己在沒(méi)有這些“遙控”的情況下，到底是怎么熬過(guò)來(lái)的。要 執(zhí)行剛才 錄制的宏，可以按以下步驟進(jìn)行：, 試著選擇其它單元格和幾個(gè)單元格組成的區(qū)域，然后再執(zhí)行宏，以便加深印象。,查看錄制的代碼,“改變顏色”是 宏的名稱(chēng),注意：有一小圓點(diǎn)，它的作用在于簡(jiǎn)化語(yǔ)句，小圓點(diǎn)代替出現(xiàn)在With后的詞,它是With結(jié)構(gòu)的一部分。,Sub 改變顏色() 改變顏色 Macro xw 記錄的宏 2000-6-10 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End Sub,到底是什么在控制EXCEL的運(yùn)行呢?你可能有些疑惑. 好,讓我們看看VBA的語(yǔ)句吧.,編輯錄制的代碼,Sub 改變顏色() 改變顏色 Macro xw 記錄的宏 2000-6-10 With Selection.Interior .ColorIndex = 3 End With End Sub,在上一節(jié)，我們錄制了一個(gè)宏并查看了代碼，代碼中有兩句實(shí)際上 并不起作用。哪兩句？ 現(xiàn)在，在宏中作一個(gè)修改，刪除多余行，直到和下面代碼相同：,完成后，在工作表中試驗(yàn)一下。 你會(huì)發(fā)現(xiàn)結(jié)果和修改前的狀況一樣。 在With 語(yǔ)句前加入一行: Range(“A5”).Select 試著運(yùn)行該宏,則無(wú)論開(kāi)始選擇 哪個(gè)單元格,宏運(yùn)行結(jié)果都是使 A5單元格變紅.,錄制宏的局限性,錄制的宏無(wú)判斷或循環(huán)能力,人機(jī)交互能力差,即用戶(hù)無(wú)法 進(jìn)行輸入,計(jì)算機(jī)無(wú)法給出提示,無(wú)法顯示EXCEL 對(duì)話(huà)框,無(wú)法顯示自定義窗體,希望自動(dòng)化的許多EXCEL過(guò)程大多都可以用錄制宏來(lái)完成. 但是宏記錄器存在以下局限性.通過(guò)宏記錄器無(wú)法完成的工作有:,為宏指定快捷鍵,你也許希望為經(jīng)常使用的宏指定快捷鍵。當(dāng)給宏指定了快捷鍵后， 就可以用快捷鍵來(lái)執(zhí)行宏，而不必通過(guò)“工具”菜單。,注意：當(dāng)包含宏的工作簿打開(kāi)時(shí)間， 為宏指定快捷鍵會(huì)覆蓋EXCEL默認(rèn)的快捷鍵。,例如：把CTRL+C指定 給某個(gè)宏，那么CTRL+C 就不再執(zhí)行復(fù)制命令。,可以在創(chuàng)建宏時(shí)指定快捷鍵，也可以在創(chuàng)建后再指定。 要在創(chuàng)建（錄制）宏時(shí)指定快捷鍵，只須在錄制宏時(shí) 在輸入宏名后，在“快捷鍵”文本框中輸入相應(yīng)的鍵。,錄制宏后指定快捷鍵也很簡(jiǎn)單，只需選擇“工具”-“宏”，顯示“宏” 對(duì)話(huà)框，選擇要指定快捷鍵的宏，再單擊“選項(xiàng)”按鈕，通過(guò)“選項(xiàng)” 對(duì)話(huà)框進(jìn)行設(shè)置。,決定宏保存的位置,宏可保存在三種可能的位置： 1）當(dāng)前工作簿。（只有該工作簿打開(kāi)時(shí)，該宏才可用。） 2）新工作簿。 3）個(gè)人宏工作簿。,個(gè)人宏工作簿,個(gè)人宏工作簿，是為宏而設(shè)計(jì)的一種特殊的具有自動(dòng)隱藏特性的工作簿。,第一次將宏創(chuàng)建到個(gè)人宏 工作簿時(shí)，會(huì)創(chuàng)建名為 “PERSONAL.XLS”的 新文件。,如果該文件存在，則每當(dāng)EXCEL 啟動(dòng)時(shí)會(huì)自動(dòng)將此文件打開(kāi) 并隱藏在活動(dòng)工作簿后面,如果你要讓某個(gè)宏在多個(gè)工作簿 都能使用，那么就應(yīng)當(dāng)創(chuàng)建 個(gè)人宏工作簿， 并將宏保存于其中。,個(gè)人宏工作簿保存在“XLSTART”文件夾中。 具體路徑為： C:WINDOWSProfilesApplication Data MicrosoftExcelXLSTART。,個(gè)人宏工作簿,試著練習(xí)一下保存一個(gè)簡(jiǎn)單的宏到個(gè)人宏工作簿,該宏為文本加下劃線(xiàn)并改為斜體,步驟如下: 1）建立一個(gè)名為“HOUR2”的工作簿，選擇“工具”-“宏”-“錄制新宏”,顯示“錄制新宏”對(duì)話(huà)框。 2）輸入“格式化文本”作為宏名。 3）從“保存在”下拉框中選擇“個(gè)人宏工作簿”。 4）單擊“確定”按鈕.現(xiàn)在進(jìn)入錄制模式 。 5）單擊“斜體”工具欄按鈕.一段時(shí)間內(nèi),鼠標(biāo)出現(xiàn)沙漏,特別是在第一次創(chuàng)建個(gè)人宏工作簿時(shí),因?yàn)镋XCEL在創(chuàng)建該工作簿 。 6）單擊“下劃線(xiàn)”按鈕 。 7）停止錄制 。,使用并編輯個(gè)人宏工作簿中的宏,剛才已經(jīng)保存了一個(gè)宏到個(gè)人宏工作簿,現(xiàn)在可以在任何工作簿中使用該宏.可按如下步驟操作:,現(xiàn)在A3單元格中，你的名字變?yōu)樾斌w字還帶有下劃線(xiàn)。 選擇“窗口”-“取消隱藏”，可以將PERSONAL.XLS顯示出來(lái)，通過(guò)VBA編輯器可以在其中的模塊中找到“格式化文本”這個(gè)宏。在VBA編輯器中可以對(duì)該宏進(jìn)行直接編輯或者刪除。,關(guān)閉所有EXCEL工作簿,任意打開(kāi)一個(gè)EXCEL文件 （EXCEL自動(dòng)將個(gè)人宏工作簿同時(shí)打開(kāi)并隱藏）,在A3中輸入您的名字,選擇“工具”-“宏”，顯示宏對(duì)話(huà)框，現(xiàn)在 可以在宏列表中看到“格式化文本”這個(gè)宏。,選擇“格式化文本”宏，并執(zhí)行,將宏指定給按鈕,“按鈕”是最常見(jiàn)的界面組成元素之一.通過(guò)使用“窗體”工具欄，可以為工作簿中的工作表添加按鈕。在創(chuàng)建完一個(gè)按鈕后，可以為它指定宏，然后你的用戶(hù)就可以通過(guò)單擊按鈕來(lái)執(zhí)行宏。在本練習(xí)中，將創(chuàng)建一個(gè)按鈕，并為它指定一個(gè)宏，然后用該按鈕來(lái)執(zhí)行宏。具體步驟如下：,打開(kāi)“HOUR2”工作簿,調(diào)出”窗體“工具欄,單擊”窗體“工具欄 中的“按鈕”控件,在希望放置按鈕的 位置按下鼠標(biāo)左鍵， 拖動(dòng)鼠標(biāo)畫(huà)出一個(gè) 矩形,在Excel自動(dòng)出現(xiàn)的“指 定宏”對(duì)話(huà)框中，選擇“格式化 文本”，單擊“確定”,試著在某個(gè)單 元格中輸入文 本，單擊按鈕 運(yùn)行該宏,將宏指定給圖片或其他對(duì)象,要執(zhí)行宏有多種方法可以選擇，可以將宏指定給按鈕等控件，還可以指定給圖片、自定義工具欄、窗體甚至可以將宏指定給某個(gè)“事件”，比如單擊工作表，雙擊工 作表，激活工作表，打開(kāi)工作簿等等，“事件”是一個(gè)重要的概念，除此而外“方法”“對(duì)象”都是將來(lái)你會(huì)經(jīng)常接觸到的。 指定宏到圖片十分簡(jiǎn)單，只需單擊某個(gè)圖片，單擊快捷菜單中的“指定宏”進(jìn)行設(shè)置即可。 如果不希望在工作表上添加控件或圖片執(zhí)行宏，還有一種方法可以選擇：將宏指定給“工具欄按鈕”，可按如下步驟進(jìn)行：,打開(kāi)“HOUR2”工作簿，選擇 “工具”-“定義”，顯示“自定義 工具欄”對(duì)話(huà)框,從“類(lèi)別”列表框中選擇“宏”， 從“命令”列表框中選擇 “自定義按鈕”。,將“自定義按鈕”拖動(dòng) 到工具欄,右鍵單擊該按鈕，選擇 “指定宏”，顯示“指定 宏”對(duì)話(huà)框,選擇“格式化文 本”并確定,單擊“關(guān)閉”按鈕， 關(guān)閉“自定義工具 欄”對(duì)話(huà)框,試著在某 個(gè)單元格 中輸入文 本，單擊 工具欄按 鈕運(yùn)行該 宏,EXCEL開(kāi)發(fā)過(guò)程簡(jiǎn)介,需要對(duì)以下問(wèn)題有個(gè)大致的概念.,誰(shuí)使用 這決定了程序的操作 難度及界面感觀,數(shù)據(jù)來(lái)源和保存在哪里 這決定了程序的結(jié)構(gòu),如何操作 這將決定程序的 界面和細(xì)節(jié),數(shù)據(jù)處理的結(jié)果 最終決定程序的價(jià)值,認(rèn)識(shí)不同的控件,開(kāi)始時(shí)請(qǐng)關(guān)閉所有工作簿,打開(kāi)一個(gè)新工作簿并另存為“HOUR3”. 在工具欄上單擊鼠標(biāo)右鍵,從快捷菜單中選擇“窗體”,顯示“窗體”工具欄. 其中有16個(gè)控件，只有9個(gè)可放到工作表內(nèi)。,向工作表添加控件,創(chuàng)建新工作簿并另存為“HOUR3”,顯示“窗體”工具欄。 選擇“標(biāo)簽”控件。 將鼠標(biāo)定位到E1,此時(shí)鼠標(biāo)變成小十字。 按下左鍵,拖動(dòng)大約四個(gè)單元格長(zhǎng)度,放開(kāi)鼠標(biāo)左鍵.如果希望控件大小易于控制,可在創(chuàng)建該控件時(shí)按下ALT拖動(dòng)。 在標(biāo)簽1上單擊右鍵,選擇“編輯文字“,現(xiàn)在可以輸入文字.完成后,單擊任何單元格退出文字編輯。 通過(guò)以上步驟可以添加其它控件到工作表中,不再贅述。,用EXCEL設(shè)計(jì)界面十分簡(jiǎn)單,要將控件添加到工作表上, 可以按以下步驟操作:,設(shè)置控件的特性,設(shè)置控件的特性,可以按以下步驟操作: 選中先前創(chuàng)建的復(fù)選框控件,如果沒(méi)有馬上創(chuàng)建一個(gè). 右擊該控件,選擇“控制“選項(xiàng)卡. 在“單元格鏈接“中輸入A1并確定. 單擊任意單元格,退出設(shè)置. 用鼠標(biāo)左鍵單擊復(fù)選框,A1出現(xiàn)TRUE,這意味著該控件被選中.再次單擊 該控件,A1出現(xiàn)FALSE. 選擇剛才創(chuàng)建的滾動(dòng)條控件.并調(diào)出“設(shè)置控件格式“對(duì)話(huà)框. 在“單元格鏈接“中輸入A3并確定. 在滾動(dòng)條外任意單元格單擊鼠標(biāo)左鍵,使?jié)L動(dòng)條不被選擇. 用鼠標(biāo)單擊滾動(dòng)條上的箭頭,則A1的數(shù)值增加1,繼續(xù)單擊則A1的數(shù)值繼 續(xù)增加. 保存并關(guān)閉該工作簿.,給控件命名使用用戶(hù)窗體,當(dāng)創(chuàng)建一個(gè)控件時(shí)EXCEL會(huì)自動(dòng)給它指定一個(gè)名字,但不便于理解和記憶,為控件取名的方法基本和給單元格或區(qū)域取名的方法相同.選中某個(gè)控件,再在位于公式欄上的“名字”編輯框輸入控件名字.這樣就給控件更改了名字 。 如果希望創(chuàng)建專(zhuān)業(yè)級(jí)的應(yīng)用程序,并且方便用戶(hù)輸入數(shù)據(jù),那么應(yīng)該使用用戶(hù)窗體.用戶(hù)窗體可以作為程序的對(duì)話(huà)框和窗口.向用戶(hù)窗體添加控件基本類(lèi)似于向工作表添加控件,然而第一步要?jiǎng)?chuàng)建一個(gè)用戶(hù)窗體.這可以通過(guò)VBA編輯器實(shí)現(xiàn).具體按以下步驟操作: 1)打開(kāi)“HOUR3“工作簿,選擇“工具“-“宏“-“VBA編輯器“,打開(kāi)VBA編輯器. 2) 在VBA編輯器中選擇工具欄上的“插入用戶(hù)窗體“按鈕或者選擇“插入“菜單,從下拉菜單中選擇“用戶(hù)窗體“ 現(xiàn)在,VBA編輯器中出現(xiàn)一個(gè)名為“USERFORM1“的窗體,“控件工具箱“同時(shí)出現(xiàn),在其中有許多已經(jīng)熟悉的控件,另外還有一些新的控件. 這些新的控件是:,使用用戶(hù)窗體,切換按鈕,該控件如果被選中,那么會(huì)保持被按下的狀態(tài).如果再次單擊它就恢復(fù)為沒(méi)有 按下的狀態(tài).EXCEL工具欄中有幾個(gè)這樣的按鈕, 例如:“全屏顯示“,“加粗“,“下劃線(xiàn)“以及“窗體“工具欄中的“切換網(wǎng)格“等.,選項(xiàng)卡條(TabStrip),它是包含多個(gè)選項(xiàng)卡的控件.通常用來(lái)對(duì)相關(guān)的信息進(jìn)行組織或分類(lèi). 例如:你也許希望用選項(xiàng)卡條來(lái)顯示各個(gè)地區(qū)的銷(xiāo)售信息,可以給每個(gè)地區(qū)設(shè)置 一個(gè)選項(xiàng)卡.在默認(rèn)時(shí),選項(xiàng)卡包含兩頁(yè),分別叫做TAB1和TAB2,可以添加更多 的選項(xiàng)卡.,多頁(yè),外觀類(lèi)似選項(xiàng)卡條,是包含一頁(yè)或多頁(yè)的控件.選項(xiàng)卡條給人相似的外觀, 而多頁(yè)控件的各頁(yè)包含各自不同的控件,有各自不同的布局.多頁(yè)的例子很多, 例如:“設(shè)置控件格式“對(duì)話(huà)框和“工具“菜單中的“選項(xiàng)“對(duì)話(huà)框. 以及“格式“菜單中的“單元格.“對(duì)話(huà)框.,使用用戶(hù)窗體,圖像控件,它允許向窗體上放置圖片.圖片格式須為: *.bmp,*.cur,*.gif,*.ico,*.jpg,*.wmf.,RefEdit,這是工具箱中默認(rèn)情況下的最后一個(gè)控件。它外觀象文本框， 通過(guò)這個(gè)控件可以將用戶(hù)窗體折疊起來(lái)，以便選擇單元格區(qū)域。,在對(duì)用戶(hù)窗體設(shè)計(jì)得滿(mǎn)意時(shí)，可以對(duì)其進(jìn)行預(yù)覽，方法是在VBA編輯器中 選擇該窗體，單擊“運(yùn)行”菜單中的三角符號(hào)“運(yùn)行子過(guò)程/用戶(hù)窗體”， 三角符號(hào)在 VBA工具欄上也可能看得到，旁邊是一個(gè)垂直的等于符號(hào)， 最右邊是個(gè)小正方形符號(hào)，它們類(lèi)似于錄音機(jī)上的按鈕。運(yùn)行窗體的 另一個(gè)方法是按F5鍵。,小結(jié),代碼存在的位置,VBA代碼必須存放在某個(gè)位置，這個(gè)地方就是模塊。 有兩種基本類(lèi)型的模塊：標(biāo)準(zhǔn)模塊和類(lèi)模塊。 模塊中的每個(gè)過(guò)程或者是函數(shù)過(guò)程，或者是子程序概念. 新術(shù)語(yǔ)： 你的大部分工作集中在標(biāo)準(zhǔn)模塊中（簡(jiǎn)稱(chēng)為模塊）當(dāng)錄制宏時(shí)如果不存在模塊，EX