Excel巨集學(xué)習(xí)課件

EXCELVBA解讀ExcelVBA綱要第一講:VBA的簡(jiǎn)單介紹第二講:界面與窗口認(rèn)識(shí)第三講:VBA程序設(shè)計(jì)基礎(chǔ)第四講:單元格操作第五講:工作表操作第六講:舉例說(shuō)明一些程式的運(yùn)用第一講:VBA的簡(jiǎn)單介紹

VisualBasicForApplicationVBA是一種由微軟開發(fā)的自動(dòng)化語(yǔ)言,這種自動(dòng)化語(yǔ)言主要用於控制Offce應(yīng)用程序,包括Excel,Outlook,Word,

PowerPoint等.以下內(nèi)容我們主要講解VBA在Excel中應(yīng)用,即ExcelVBA.

從名稱上就可以看出,VBA與VB有緊密的關(guān)聯(lián).

不過(guò)不同的地方在於:

1.VB是設(shè)計(jì)用于創(chuàng)建標(biāo)準(zhǔn)的應(yīng)用程序,而VBA是使已有的應(yīng)用程序(excel等)自動(dòng)化

2.VB具有自己的開發(fā)環(huán)境,而VBA必須寄生于已有的應(yīng)用程序.

3.要運(yùn)行VB開發(fā)的應(yīng)用程序,用戶不必安裝VB,因?yàn)閂B開發(fā)出的應(yīng)用程序是可執(zhí)行文件(*.EXE),而VBA開發(fā)的程序必須依賴于它的"父"應(yīng)用程序,例如excel.

那VBA究竟是什么呢?更確切地講,它就是一種自動(dòng)化語(yǔ)言,可以使常用的程序自動(dòng)化,創(chuàng)建自定義的解決方案.ExcelVBA使用ExcelVBA可以完成如下工作:1.使重復(fù)的任務(wù)自動(dòng)化.2.自定義excel工具欄,菜單和界面.3.簡(jiǎn)化模板的使用.4.自定義excel,使其成為開發(fā)平臺(tái).5.創(chuàng)建報(bào)表.6.對(duì)數(shù)據(jù)進(jìn)行復(fù)雜的操作和分析.

第二講:界面與窗口圖(一)EXCEL界面(VBA應(yīng)用程序窗口)工程資源管理器窗口屬性窗口代碼窗口立即窗口圖(二)VBA編輯界面及窗口圖三(VBA的控件窗口)控件介紹打開一個(gè)新工作簿.在工具欄上單擊鼠標(biāo)右鍵,從快捷菜單中選擇“表單”,顯示“表單”工具欄.其中有16個(gè)控件,只有9個(gè)可放到工作表內(nèi)1）標(biāo)簽：它用于表現(xiàn)靜態(tài)文本2）分組框：它用于將其他控件進(jìn)行組合3）按鈕：用于執(zhí)行宏命令4）複選框：它是一個(gè)選擇控件，通過(guò)單擊可以選擇和取消選擇，可以多項(xiàng)選擇。5）選項(xiàng)按鈕：通常幾個(gè)選項(xiàng)按鈕組合在一起使用，在一組中隻能選擇一個(gè)選項(xiàng)按鈕。6）列表框：用于顯示多個(gè)選項(xiàng)并從中選擇。隻能單選7）組合框：用于顯示多個(gè)選項(xiàng)并從中選擇。可以選擇其中的項(xiàng)目或者輸入一個(gè)其它值8）滾動(dòng)條：不是你常見的來(lái)給很長(zhǎng)的窗體添加滾動(dòng)能力的控件，而是一種選擇機(jī)制.例如調(diào)節(jié)過(guò)渡色的滾動(dòng)條控件。包括水平滾動(dòng)條和垂直滾動(dòng)條9）微調(diào)控件：也是一種數(shù)值選擇機(jī)制，通過(guò)單擊控件的箭頭來(lái)選擇數(shù)值。例如改變Windows日期或時(shí)間就會(huì)使用到微調(diào)控件

第三講:VBA程序設(shè)計(jì)基礎(chǔ)

錄制簡(jiǎn)單的巨集(宏)巨集指一系列EXCEL能夠執(zhí)行的VBA語(yǔ)句.以下將要錄制的巨集非常簡(jiǎn)單,只是改變單元格顔色,如下步驟：1)打開新工作簿,確認(rèn)其他工作簿已經(jīng)關(guān)閉2)選擇A1單元格3)選擇“工具”—“巨集”—“錄制新巨集”4)輸入“改變顔色”作爲(wèi)巨集名替換默認(rèn)巨集名,單擊確定,注意,此時(shí)狀態(tài)欄中顯示“錄制”,特別是“停止錄制”工具欄也顯示出來(lái).替換默認(rèn)巨集主要是便于分別這些宏.巨集名最多可爲(wèi)255個(gè)字符,并且必須以字母開始.其中可用的字符包括：字母、數(shù)字和下劃線.巨集名中不允許出現(xiàn)空格.通常用下劃線代表空格.5)選擇“格式””單元格”,選擇“圖案”選項(xiàng)中的紅色,單擊“確定”.6)單擊“停止錄制”工具欄按鈕,結(jié)束宏錄制過(guò)程代碼說(shuō)明Subaa()--巨集名稱‘ --註解符號(hào)'Range(“A1”).Select--選定A1單元格

With

Selection.Interior

--選定區(qū)域的內(nèi)部

.ColorIndex=3–紅色,.作用在於簡(jiǎn)化語(yǔ)句

.Pattern=xlSolid

--內(nèi)部圖案,表示純色.

.PatternColorIndex=xlAutomatic

--表示內(nèi)部圖案底紋顏色為自動(dòng)配色.

EndWith--with結(jié)構(gòu)語(yǔ)句,巨集的主要部份EndSub --整合巨集結(jié)束句錄製巨集缺陷1)錄制的巨集無(wú)判斷或循環(huán)能力.2)人機(jī)交互能力差,即用戶無(wú)法進(jìn)行輸入,計(jì)算機(jī)無(wú)法給出提示.3)無(wú)法顯示EXCEL對(duì)話框.4)無(wú)法顯示自定義窗體

注:.Pattern=xlSolid

.PatternColorIndex=xlAutomatic

這兩條語(yǔ)句在以上代碼段中,可以將其刪除,並不影響結(jié)果.

現(xiàn)在可以看到，編輯錄制的宏同樣非常簡(jiǎn)單。需要編輯宏是因?yàn)橐韵氯齻€(gè)方面的原因。一：在錄制中出錯(cuò)而不得不修改。二：錄制的宏中有多余的語(yǔ)句需要?jiǎng)h除，提高宏的運(yùn)行速度.三：希望增加宏的功能。比如加入判斷或循環(huán)等無(wú)法錄制的語(yǔ)句.

保存宏的方法宏可保存在三種可能的位置：1）當(dāng)前工作簿.(只有該工作簿打開時(shí)，

該宏才可用.）2）新工作簿。3）個(gè)人宏工作簿。參數(shù),語(yǔ)法,語(yǔ)句基本參數(shù)說(shuō)明基本數(shù)據(jù)類型解釋VBA流程控制語(yǔ)句VBA參數(shù)標(biāo)識(shí)符

VBA標(biāo)識(shí)符特指在VBA程序中標(biāo)識(shí)變量,常量,過(guò)程,函數(shù),類等語(yǔ)言要素的符號(hào).VBA程序中可以使用的標(biāo)識(shí)符需符合以下命名規(guī)則:1)以英文字母或漢字字符為首字符,只包含英文字母,數(shù)字,漢字字符和下劃線字符.2)字符長(zhǎng)度要小於254個(gè)字符

3)不能與VBA語(yǔ)言的保留字衝突.變量

計(jì)算機(jī)程序就是一個(gè)獲取數(shù)據(jù),處理數(shù)據(jù),輸出數(shù)據(jù)的過(guò)程.一切信息都要在計(jì)算機(jī)中被轉(zhuǎn)換為數(shù)據(jù),然後才能被存儲(chǔ),理解和運(yùn)算,因此,計(jì)算機(jī)執(zhí)行複雜功能的基礎(chǔ)都在於存儲(chǔ)數(shù)據(jù),而VBA變量就是VBA程序中用於存儲(chǔ)數(shù)據(jù)的VBA語(yǔ)言要素.所以說(shuō)變量是用于臨時(shí)保存數(shù)值的地方.每次應(yīng)用程序運(yùn)行時(shí),變量可能包含不同的數(shù)值,而在程序運(yùn)行時(shí),變量的數(shù)值可以改變.

為了說(shuō)明為什么需要變量,可以按照如下步驟創(chuàng)建一個(gè)簡(jiǎn)單的過(guò)程:

1)創(chuàng)建一個(gè)名為"你叫什么名字"的過(guò)程.

2)在過(guò)程中輸入如下代碼:

Inputbox"輸入你的名字:"

3)按下F5鍵運(yùn)行過(guò)程,這時(shí)會(huì)顯示一個(gè)輸入框,要求輸入你的名字.

4)輸入你的名字并按"確定"按鈕,則結(jié)束該過(guò)程.

你輸入的名字到那里去了?如何找到用戶在輸入框中輸入的信息?在這種情況下,需要使用變量來(lái)存儲(chǔ)用戶輸入的結(jié)果.

使用變量的第一步是了解變量的數(shù)據(jù)類型.變量的數(shù)據(jù)類型控制變量允許保存何種類型的數(shù)據(jù)

VBA程序需要特定的方法識(shí)別變量,這種告知應(yīng)用程序某一個(gè)標(biāo)識(shí)符用來(lái)標(biāo)識(shí)變量的過(guò)程,稱之為變量定義.1)用Dim聲明變量該語(yǔ)句的語(yǔ)法:Dim變量名as數(shù)據(jù)類型2)用Public聲明變量該語(yǔ)句的語(yǔ)法:Public變量名as數(shù)據(jù)類型3)用Static聲明變量該語(yǔ)句的語(yǔ)法:Static變量名as數(shù)據(jù)類型以上三種各有利弊:Public在作用域上與dim有所差別,而用Static聲明的變量在調(diào)用時(shí)仍保留它原先的值.以下以dim聲明變量為例.

用Dim語(yǔ)句創(chuàng)建變量(聲明變量)Dim變量名AS數(shù)據(jù)類型(缺省為Variant類型)注:Variant類型是變體型,它可以存儲(chǔ)除定長(zhǎng)字符串的一切基本數(shù)據(jù)類型1)創(chuàng)建一個(gè)名爲(wèi)"顯示你的名字"的子程序.

2)輸入以下代碼:

Sub顯示你的名字()

Dims名字AsString

s名字=Inputbox(“請(qǐng)輸入你的名字:”)

Msgbox“你好”&s名字

EndSub執(zhí)行以上代碼段,會(huì)出現(xiàn)如下兩個(gè)對(duì)話框圖一圖二

sub認(rèn)識(shí)變量()‘變量聲明

DimnumberAsInteger‘複製A1單元格內(nèi)容到A2單元格

number=Range(“A1”).ValueRang(“A2”).Value=numberEndSub常量VBA程序中還有一類特殊的數(shù)據(jù)需要存儲(chǔ),這類數(shù)據(jù)的值在程序執(zhí)行的整個(gè)過(guò)程中都不可改變,這類數(shù)據(jù)稱之為常量.它可以供程序多次使用而且便于記憶.比如圓周率比3.1415926,它是不隨程序改變而改變的;再比如,一個(gè)計(jì)算稅收的VBA程序,稅率在整個(gè)程序中都不應(yīng)該改變,那嚜稅率的數(shù)據(jù)就應(yīng)存儲(chǔ)在常量中.VBA常量有兩種類型:字符常量&符號(hào)常量用const創(chuàng)建常量const銷項(xiàng)稅率AsLong=0.17常量聲明後,不能對(duì)它賦一個(gè)新的值語(yǔ)法如下:Const標(biāo)識(shí)符as數(shù)據(jù)類型=值如:ConstpiasSingle=3.1415926注:single為單精度浮點(diǎn)型基本的數(shù)據(jù)類型

在VBA程序中共有11種基本數(shù)據(jù)類型:布爾型(Boolean),日期型(Date),字符串型(String),貨幣型(Currency),小數(shù)型(Decimal),字節(jié)型(Byte),整數(shù)型(Integer),長(zhǎng)整數(shù)型(Long),單精度浮點(diǎn)型(Single),雙精度浮點(diǎn)型(Double)和變體型(Variant)VBA流程控制語(yǔ)句循環(huán)語(yǔ)句以fornext為例:For…Next語(yǔ)句是用來(lái)實(shí)現(xiàn)指定次數(shù)的循環(huán),其語(yǔ)法如下:For<循環(huán)變量>=<初值>to<終值>(step步長(zhǎng)值)

循環(huán)體

Exitfor

循環(huán)體

Next循環(huán)變量Sub矩形2_Click()DimXAsIntegerForX=1To20Sheets("sheet1").Cells(X,1)=XNextXEndSub單元格,工作表,工作薄,列,行的解說(shuō)舉例單元格的基本錄入方法新建一個(gè)EXCEL檔，用繪圖插入一個(gè)矩形，點(diǎn)擊右鍵，在功能表中點(diǎn)擊指定巨集，再點(diǎn)擊新增,會(huì)出現(xiàn):

Sub矩形1_Click()EndSub在兩句中間加入

Sheets("sheet1").Range("a1")=100

Sheets("sheet1").Cells(2,1)=200Range(“D3”).ClearContents清除內(nèi)容加總Range("a21").Value=Application.WorksheetFunction.Sum(Range("a1:a20"))在VBA不直接支援在EXCEL工作表中的一些函數(shù)，如果要調(diào)用就必須加上Application.WorksheetFunctionSUM求和語(yǔ)法和工作表中的一樣,但表示不能直接SUM(A1:A20),要用VBA的表示方SUM(Range("a1:a20"))整列:Range(“D:D”)或Columns(4)

Range("B2").Value=Application.WorksheetFunction.CountA(Range("D:D"))行列COLUMN是指列,COLUMNS是指列的集合ROW是表示行,ROWS是行的集合如果用Sheets(“sheet1”).columns.select會(huì)選定所有列即整個(gè)工作表習(xí)題請(qǐng)?jiān)诠ぷ鞅鞸HEET1的D4：D36單格中填充4-36的數(shù)值，並在D37單格中求和.那麼如何讓程式自動(dòng)在最好一行填入求得的和?答案Sub矩形2_Click()DimiAsIntegerDimYYYAsIntegerFori=4To36Sheets("sheet1").Cells(i,4)=iNextYYY=Application.WorksheetFunction.CountA(C