chapter-11宏與VBA程序設(shè)計

第11章宏與VBA程序設(shè)計Excel數(shù)據(jù)處理與統(tǒng)計初步第4版認識在學校，一般都要學習類似于C語言、VB、VFP之類的編程語言；一般都要編寫類似于排序、查詢之類的程序。但工后，絕大多數(shù)人都不會再用這樣的程序了。然而，這對Excel是個例外！這些程序不僅能減化繁雜的工作，而且還能使工作自動化。在EXCEL中：VBA能夠使那些枯燥反復的工作變得高效、準確而自動化。若想建立自已的辦公自動化數(shù)據(jù)管理系統(tǒng)，需要用到VBA程序。若想成為一個真正的Excel專家，需要很好地掌握VBA程序設(shè)計。本章學習目標1、了解Excel宏的基本知識2、掌握宏的錄制、編寫和運行方法3、了解VBA程序的基本知識4、掌握VBA的基本數(shù)據(jù)類型5、掌握VBA宏與函數(shù)的編寫和調(diào)用方法6、掌握VBA條件、循環(huán)程序的設(shè)計方法7、了解VBA自動宏11.1Excel宏宏的概念宏是用VBA程序設(shè)計語言編寫或錄制的程序，其中保存有一系列Excel的命令，可以被多次重復使用。宏可以自動執(zhí)行復雜的任務，減少完成任務所需的步驟。VBA即VisualBasicforApplications，它是VisualBasic的一個派生體，它有針對性地對VisualBasic進行了優(yōu)化和設(shè)置。兩者的主要區(qū)別在于：VisualBasic開發(fā)的應用程序可以獨立在Windows系統(tǒng)中運行，而用VBA開發(fā)的程序只能在提供它的應用程序中運行。在Excel中，可以用Excel提供的宏錄制工具錄制宏程序，也可以使用它提供的“VisualBasic編輯器”直接編寫宏。11.1Excel宏2、錄制宏宏錄制器是Excel提供的一種軟件工具，它能夠?qū)⒂脩舻牟僮鬟^程記錄下來，并自動將所記錄的操作轉(zhuǎn)換成為VBA程序代碼。對于經(jīng)常重復進行的操作過程，可以通過宏錄制器將它記錄下來，當需要再次進行這些操作時，只需要運行錄制的宏，Excel就能自動完成這些重復的操作。說明：當錄制宏的工作開始后，所有的操作步驟都將被記錄在宏中，所以應盡量減少不必要的或錯誤的操作，如果在錄制宏時出現(xiàn)失誤，更正失誤的操作也會記錄在宏中。11.1Excel宏錄制宏的案例【例11.1】某班主任共管理10個班級的學生，要為每個班級建立學生檔案，檔案表結(jié)構(gòu)如圖所示。11.1Excel宏建立學生檔案宏選擇“工具”|“宏”|“錄制新宏”菜單項，在彈出的下示對話框中輸入宏名字“學生檔案”單擊“確定”

11.1Excel宏3.輸入表格內(nèi)容（1）單擊A1單元格，在其中輸入“2001級學生檔案”；（2）單擊A2單元格，在其中輸入“學號”；（3）單擊B2單元格，在其中輸入“姓名”；（4）單擊C2單元格，在其中輸入“班級”；（5）單擊D2單元格，在其中輸入“性別”；（6）單擊E2單元格，在其中輸入“籍貫”；（7）單擊F2單元格，在其中輸入“寢室”；（8）單擊G2單元格，在其中輸入“電話號碼”；（13）選擇A1:G1，然后單擊工具條中的跨列居中按鈕。（14）選擇“工具”|“宏”|“停止錄制”菜單命令。

11.1Excel宏4.停止錄制，查看錄制的宏選擇“工具”|“宏”|“停止錄制”選擇“工具”|“宏”|“宏”菜單項，會顯示“宏”對話框。選中其中的“學生檔案”，單擊“編輯”。11.1Excel宏宏代碼1 Sub學生檔案()2 '學生檔案Macro3 '宏由dk錄制，時間:2005-4-204 '快捷鍵:Ctrl+s5 Range("A1").Select6 ActiveCell.FormulaR1C1="2001級一班學生檔案"7 Range("A2").Select8 ActiveCell.FormulaR1C1="學號"9 Range("B2").Select10 ActiveCell.FormulaR1C1="姓名"11 Range("C2").Select12 ActiveCell.FormulaR1C1="班級“13 Range("D2").Select14 ActiveCell.FormulaR1C1="性別"15 Range("E2").Select16 ActiveCell.FormulaR1C1="籍貫"17 Range("F2").Select18 ActiveCell.FormulaR1C1="寢室"11.1Excel宏19 Range("G2").Select20 ActiveCell.FormulaR1C1="電話號碼"21 Range("A1:G1").Select22 WithSelection23 .HorizontalAlignment=xlCenter24 .VerticalAlignment=xlBottom25 .WrapText=False26 .Orientation=027 .AddIndent=False28 .ShrinkToFit=False29 EndWith30 Selection.Merge31 EndSub31 EndSub11.1Excel宏5、宏的結(jié)構(gòu)Sub宏名()

命令代碼1

命令代碼2

……EndSub6、With的結(jié)構(gòu)WithSelection

……

EndWith11.1Excel宏7、宏的保存保存在個人宏工作簿“Personal.xls”中。保存在專門保存宏的“新的工作簿”中。保存在建立宏的當前工作薄中。11.1Excel宏8、宏的執(zhí)行通過快捷鍵運行宏通過對話框運行宏通過自定義工具按鈕運行宏通過圖形控件或窗體命令按鈕運行宏通過自定義菜單運行宏（以后再講）11.2VBA程序設(shè)計基礎(chǔ)1、數(shù)據(jù)類型的概念在計算機中，數(shù)據(jù)也是按不同的類別進行運算和保存的，人們稱之為數(shù)據(jù)類型。同種類型的數(shù)據(jù)占用相同大小的存儲空間，相互之間可以進行計算、比較或賦值等操作；不同類型的數(shù)據(jù)占用的存儲空間大小不一定相同，且相互之間不能進行計算和比較等操作。2、VBA的數(shù)據(jù)類型（1）常量

數(shù)值常量，如：1，2，65，98.65等11.2VBA程序設(shè)計基礎(chǔ)字符及字符串常量

字符類型的常量稱為字符常量，字符常量要用定界符雙引號（“

”）界定。例如，“d”、“5”、“A”等都是字符常量。符號常量

Const常量名=常量值例如，ConstPI=3.14，ConstABC="OK!China!"11.2VBA程序設(shè)計基礎(chǔ)（2）變量變量是在程序運行期間其值可以發(fā)生變化的數(shù)據(jù)。例如：1 DimA,BAsInteger2 A=13 B=24 A=4+bA=3A在本程序段中有3個不同的值，這就是變！11.2VBA程序設(shè)計基礎(chǔ)VBA數(shù)值數(shù)據(jù)類型數(shù)據(jù)類型存儲空間數(shù)據(jù)范圍Byte1字節(jié)0~255Boolean2字節(jié)True或falseInteger2字節(jié)-32768~32767Long4字節(jié)-2147483648~2147483647Single4字節(jié)負數(shù)：-3.402823E38~-1.401298E-45正數(shù)：1.401298E-45~3.402823E38Double8字節(jié)負數(shù)：1.79769313486232E308~-4.94065645841247E-324正數(shù)：4.94065645841247E-324~1.79769313486232E308Currency8字節(jié)-922337203685477.5808~922337203685477.5807Date8字節(jié)0100年1月1日到9999年12月31日Object4字節(jié)任何對象引用String字符長度分為定長和可變長度兩種，可變長可達0～20億個字符，定長最多65536個字符Variant(數(shù)字)16字節(jié)任何數(shù)字，最大達到double的數(shù)值范圍Variant(字符)22+字符串長度約0～20億11.2VBA程序設(shè)計基礎(chǔ)字符串類型在VBA中有兩種類型的字符串，變長與定長的字符串。Dims1AsString//變長類型Dims2AsString*10//定長類型s1="dddkdk"s2="d1234567890sssss"11.2VBA程序設(shè)計基礎(chǔ)布爾類型布爾類型是比較運算或邏輯運算的結(jié)果值，它只有兩個取值：True和False。True是比較結(jié)果為真時的值，F(xiàn)alse是比較結(jié)果為假時的值。

DimAAsBooleanDimBAsBooleanA=3>5B=True11.2VBA程序設(shè)計基礎(chǔ)日期類型日期型數(shù)據(jù)用于保存日期，占8個字節(jié)的存儲空間，以浮點數(shù)值形式保存日期，可以表示的日期范圍從公元100年1月1日到公元9999年12月31日，而時間可以從0:00:00到23:59:59。日期文字以“#”作界定符。Dimd1,d2AsDated1=#1Jul98# 'D1的取值是1998年7月1號d2=#12/2/2000# 'D2的值是2000年12月2號11.2VBA程序設(shè)計基礎(chǔ)變體數(shù)據(jù)類型Variant是一種特殊的數(shù)據(jù)類型，除了定長String數(shù)據(jù)及用戶定義類型外，它可以包含任何種類的數(shù)據(jù)。DimarAsVariantar=12ar="stringtype"ar="abc"&arar=12.23在本例中，ar的類型是不定的！11.2VBA程序設(shè)計基礎(chǔ)數(shù)組在VBA中，可以聲明一個數(shù)組來代表一組具有相同數(shù)據(jù)類型的數(shù)據(jù)，它就是數(shù)組。假設(shè)一個班有20個同學，每個同學有5門課程，可以定義一個20行5列的二維數(shù)組來保存他們的成績Dimstu(1To20,1To5)AsSingle這條命令定義了一個二維表格，如下所示。stu(1,1)=78stu(1,2)=90stu(1,3)=87stu(1,4)=88stu(1,5)=76數(shù)組訪問方法7811.2VBA程序設(shè)計基礎(chǔ)對象、屬性和方法計算機程序設(shè)計中的對象是從現(xiàn)實世界中抽象出來的，它與現(xiàn)實世界中的對象具有相同的含義。對象具有屬性和方法兩種特性。年齡：30學歷：碩士身高：173cm體重：69公斤說自己的年齡學習授課……李立屬性方法對象屬性語法規(guī)則李立.年齡=32李立.體重=70方法的調(diào)用也要按這種語法規(guī)則李立.學習李立.授課Excel對象示例11.2VBA程序設(shè)計基礎(chǔ)宏代碼中的對象

Sub宏4()1Range("E6").Select2ActiveCell.FormulaR1C1="eqw"3Range("E6:E7").Select4Selection.Font.Bold=True5Selection.Font.Italic=True6WithSelection.Font7.Name="華文楷體"8.Size=129.Shadow=False10.Underline=xlUnderlineStyleNone11.ColorIndex=xlAutomatic12EndWith13Selection.Font.ColorIndex=314EndSub11.3

子程序子程序的兩種結(jié)構(gòu)子程序是VBA的最小程序單位，它必須獨立存在，但在一個子程序中可以調(diào)用另外一個子程序。它有兩種形式，第一種沒有參數(shù)，第二種有參數(shù)Sub子程序名

……

子程序代碼

……EndSubSub子程序名(p1,p2,p3…)

……

子程序代碼

……EndSub11.3

子程序2、子程序的調(diào)用形式1）直接調(diào)用直接調(diào)用子程序名，如果有參數(shù)，則在子程序后面直接寫上調(diào)用參數(shù)；2）用Call命令調(diào)用在Call命令的后面寫上了程序的名字，如果子程序有參數(shù)，則必須將參數(shù)寫在括號中。11.3

子程序3、子程序調(diào)用舉例1SubMain()2HouseCalc99800,431003CallHouseCalc(380950,49500)4CircleArea(4)5CircleArea46Message7EndSubSubHouseCalc(priceAsSingle,wageAsSingle)If3*wage<=0.85*priceThenMsgBox"你的薪水不能承擔房價！"ElseMsgBox"你的薪金足以承擔房價！"EndIfEndSubSubCircleArea(RasSingle)MsgBox3.14*R*REndsubSubMessage()MsgBox"這是一個無參子程序"EndSub調(diào)用11.4自定義函數(shù)1函數(shù)結(jié)構(gòu)Function函數(shù)名([p1,p2,p3,…])[AsType]

……函數(shù)代碼

……函數(shù)名=表達式EndFunction11.4自定義函數(shù)2、定義函數(shù)的注意事項①函數(shù)由Function和EndFunction

語句所包含起來的VBA語句。②Function函數(shù)和Sub子程序很類似，但函數(shù)有一個返回值。Function函數(shù)必須通過表達式調(diào)用。③如果一個Function函數(shù)沒有參數(shù)，它的Function語句必須包含一個空的圓括號。④在函數(shù)體中，函數(shù)名至少被賦值一次。⑤函數(shù)開頭行的[AsType]用于指定函數(shù)值的返回值類型，如果省掉該定義，被視為Variant類型。11.4自定義函數(shù)3、【例11.3】

編寫一個計算圓面積的簡單函數(shù)，圓半徑作為函數(shù)參數(shù)。FunctionCircleArea(rAsSingle)AsSingle

CircleArea=3.14*r*rEndFunction11.4自定義函數(shù)4、函數(shù)調(diào)用函數(shù)只能在表達式中調(diào)用。在Excel中，函數(shù)至少有以下3種調(diào)用方式。在Sub子程序中調(diào)用函數(shù)在其他函數(shù)中調(diào)用函數(shù)在Excel工作表單元格的公式中調(diào)用函數(shù)11.4自定義函數(shù)5、自定義函數(shù)案例假設(shè)有一個學生成績表如圖所示?，F(xiàn)要計算其中的綜合成績，在本例中，綜合成績的計算方法為：綜合成績=考試科目×0.7+考查科目×0.3。編寫計算綜合成績的函數(shù)ss。11.4自定義函數(shù)計算綜合成績的自定義函數(shù)Functionss(ks1,ks2,kc1,kc2,s,c)ss=(ks1+ks2)*s+(kc1+kc2)*cEndFunction該函數(shù)定義出來之后，可在編寫它的任何工作表中調(diào)用，與調(diào)用Excel內(nèi)置函數(shù)無任何差別。比如，在上圖的G5中輸入公式=ss(C5,D5,E5,F5,$E$2,$E$3)然后把該公式向下填充復制到最后一位同學的“綜合成績”單元格，這樣就可計算出所有同學的綜合成績。

11.5VBA選擇結(jié)構(gòu)1、VBA程序執(zhí)行的方式在一般情況下，一個VBA的Sub子程序和Function函數(shù)的執(zhí)行都是從程序代碼的第一個語句行開始，逐行運行程序代碼中的語句，直到遇到EndSub或EndFunction來結(jié)束整個程序的執(zhí)行。有些時候，需要程序代碼按一定的條件執(zhí)行，當條件成立的時候，執(zhí)行一部分程序代碼，條件不成立的時候執(zhí)行另外一部分程序代碼。這種功能需要用VBA的選擇結(jié)構(gòu)來實現(xiàn)。11.5VBA選擇結(jié)構(gòu)2、VBA條件語句的語法形式1：If條件Then[語句組1][Else語句組2]例如：Ifsalary>1500Thenrate=0.1Elserate=0.05

如果salary的值為2000，則rate=0.1

如果salary的值為100，則rate=0.0511.5VBA選擇結(jié)構(gòu)形式2If條件Then語句組1

……Else語句組2

……EndIfFunctionabc(a,b)Ifa>bThent=aa=bb=tEndIfabc=aEndFunction例如11.5VBA選擇結(jié)構(gòu)形式3If條件1Then

語句組1ElseIf條件2Then

語句組2

……

ElseIf條件nThen

語句組nElse

語句組n+1EndIf11.5VBA選擇結(jié)構(gòu)3、條件函數(shù)案例某汽車出租公司可為顧客提供運送貨物的業(yè)務，根據(jù)貨物的重量及路程可對運費進行適當?shù)膬?yōu)惠。設(shè)運費F（單位為元），重量P（單位為噸），路程S（公里）及優(yōu)惠系數(shù)（D）之間的關(guān)系式為：F=P*S*W*(1-D)。優(yōu)惠系數(shù)D與路程遠近的關(guān)系如下，編寫計算折扣的函數(shù)11.5VBA選擇結(jié)構(gòu)計算折扣的函數(shù)Functiond(s)Ifs>=1000Thend=0.1ElseIfs>=750Thend=0.07ElseIfs>=500Thend=0.05ElseIfs>=250Thend=0.02Elsed=0EndIfEndFunction11.5VBA選擇結(jié)構(gòu)調(diào)用自定義函數(shù)計算11.5VBA選擇結(jié)構(gòu)4分情況選擇語句SelectCase語句的語法結(jié)構(gòu)如下。SelectCase測試表達式Case表達式1

語句組1Case表達式2

語句組2

……CaseElse

語句組nEndSelect

11.5VBA選擇結(jié)構(gòu)Select案例某學校的職工人事數(shù)據(jù)存在Excel工作表中，如圖所示。現(xiàn)要按職稱提升每位職工的工資，各種職稱的工資增長情況如下：教授150、副教授130、講師100、助教80、高級工程師150、工程師140、助工90。用select語句編寫計算增加工資的函數(shù)。11.5VBA選擇結(jié)構(gòu)編寫的Seclect函數(shù)FunctionAddSalary(職稱)'職稱作為一個參數(shù)

SelectCase職稱

Case"教授","高級工程師"

AddSalary=150Case"副教授"

AddSalary=130Case"講師"

AddSalary=100Case"助教"

AddSalary=80Case"工程師"

AddSalary=140Case"助工"

AddSalary=90EndSelectEndFunction11.6VBA循環(huán)結(jié)構(gòu)在計算機中，一些被重復執(zhí)行的語句是通過循環(huán)來完成的。1、For…Next循環(huán)結(jié)構(gòu)Forcounter=startToend[step步長]

循環(huán)語句1循環(huán)語句2循環(huán)語句3

……循環(huán)語句nNext[counter]11.6VBA循環(huán)結(jié)構(gòu)For循環(huán)的工作過程如下：（1）計算出start，end和s的值（這三項都可以是變量、表達式或常數(shù)），并把start的計算結(jié)果值賦給循環(huán)控制變量x。（2）比較x與end的計算結(jié)果，如果x的值超過了end的計算結(jié)果就停止循環(huán)，執(zhí)行Next后面的語句。否則，執(zhí)行第3步。（3）執(zhí)行循環(huán)語句組中的語句。（4）將x中的值增加一個步長值s，然后執(zhí)行第2步。11.6VBA循環(huán)結(jié)構(gòu)For循環(huán)案例某公司職工檔案數(shù)據(jù)保存在Excel工作表中，如下圖所示。該公司共有1234名職工，每月要從工資表中扣除一定的住房公積金，假設(shè)住房公積金按以下的百分比扣除。編寫計算公積金比例的宏程序。11.6VBA循環(huán)結(jié)構(gòu)Functioncountrate(salaryAsDouble)'這個函數(shù)計算公積金的百分比

DimrateAsDoubleIfsalary>2000Then

rate=0.1ElseIfsalary>1500Thenrate=0.07ElseIfsalary>1200Then

rate=0.05ElseIfsalary>1000Then

rate=0.02ElseIfsalary>800Then

rate=0.01Else

rate=0EndIf

countrate=rateEndFunction數(shù)函的例比金積公算計11.6VBA循環(huán)結(jié)構(gòu)計算所有職工工積金的宏SubCountData()Fori=3To1236'從工作表的第3行開始計算。第3行中的是第1位職工工資

r=countrate(Cells(i,2))'計算出第i位職工的公積金比例

Cells(i,3)=r'將第i位職工的公積金比例填入本行的第3列中

Cells(i,3).Style="Percent"'將公積金比例的格式設(shè)置為百分比

Cells(i,4)=r*Cells(i,2)'計算第1位職工的公積金并填入本行的第4列中

Cells(i,5)=Cells(i,2)-Cells(i,4)'計算第i位職工的應發(fā)工資并填入第5列中NextEndSub11.6VBA循環(huán)結(jié)構(gòu)調(diào)用宏計算公積金11.6VBA循環(huán)結(jié)構(gòu)2While…Wend循環(huán)結(jié)構(gòu)在事先知道循環(huán)次數(shù)時，用For循環(huán)方便。如果事先并不知道循環(huán)次數(shù)，但知道執(zhí)行或結(jié)束循環(huán)的條件，則用Do…Loop循環(huán)或While…Wend循環(huán)。While循環(huán)的語法結(jié)構(gòu)如下While判定條件

語句1

語句2

……Wend11.6VBA循環(huán)結(jié)構(gòu)3Do…Loop循環(huán)結(jié)構(gòu)第一種結(jié)構(gòu)為：DoWhile判定條件

……Loop第二種結(jié)構(gòu)為：Do

……LoopWhile判定條件第三種結(jié)構(gòu)為：DoUntil判定條件

……Loop第四種結(jié)構(gòu)為：Do

……LoopUntil判定條件11.6VBA循環(huán)結(jié)構(gòu)案例在上節(jié)的For循環(huán)實例中，假設(shè)并不知道有多少位職工，在其他條件都相同的條件下，現(xiàn)用While…Wend循環(huán)結(jié)構(gòu)來計算每位職工的住房公積金比例、應交公積金和實際發(fā)放工資。改寫的函數(shù)如下11.6VBA循環(huán)結(jié)構(gòu)用While循環(huán)改定的函數(shù)SubCountDataB()i=3'第1位職工在第3行WhileNot(IsEmpty(Cells(i,2)))

‘從工作表的第3行開始計算

r=countrate(Cells(i,2))

'計算出第i位職工的公積金比例

Cells(i,3)=r

'把第i位職工的公積金比例填入本行的第3列中

Cells(i,3).Style="Percent"

'把公積金比例的格式設(shè)置為百分比

Cells(i,4)=r*Cells(i,2)

'計算第1位職工的公積金并填入本行的第4列中

Cells(i,5)=Cells(i,2)-Cells(i,4)

'計算第i位職工的應發(fā)工資并填入本行第5列中

i=i+1

'計算下一位職工的公積金WendEndSub11.6VBA循環(huán)結(jié)構(gòu)用do循環(huán)改寫的函數(shù)SubCountDataC()i=3Dor=countrate(Cells(i,2))Cells(i,3)=rCells(i,3).Style="Percent"Cells(i,4)=r*Cells(i,2)Cells(i,5)=Cells(i,2)-Cells(i,4)i=i+1LoopUntil(IsEmpty(Cells(i,2)))EndSub

11.6VBA循環(huán)結(jié)構(gòu)4數(shù)組在循環(huán)中的應用循環(huán)的主要功能是進行VBA代碼的重復執(zhí)行，數(shù)組的主要功能是存儲大量的數(shù)據(jù)。在計算機應用中，數(shù)組和循環(huán)相結(jié)合能夠解決許多實際問題。例如大批量數(shù)據(jù)的排序、統(tǒng)計、檢索等功能。案例11.6VBA循環(huán)結(jié)構(gòu)某學校每年對新生都要進行一次英語入學測試，以了解每位學生的英語能力?？荚囃戤吅螅y(tǒng)計每個分數(shù)段的人數(shù)，并根據(jù)學生的成績將他們分為A、B、C……若干個班，第1~25名同學進入A班，第26~50名同學進入B班，其余的以此類推。該校每年招收的人數(shù)都有細小的差別。假設(shè)學生的英語測試成績?nèi)缦马搱D示。

編寫一個宏完成以下功能。自動統(tǒng)計出各分數(shù)段的人數(shù)，并將統(tǒng)計結(jié)果保存在一個新的工作表中。對所有的考試成績進行排序。根據(jù)排名對學生進行分班，每個班25名學生，每個班保存在一個新的工作表中。11.6VBA循環(huán)結(jié)構(gòu)成績表本例程序代碼較多，不便PPT演示，可直接查看Excel中的宏。11.7VBA自動宏1、概述所謂自動宏就是指在某一特定時間自動執(zhí)行的宏程序ExcelVBA提供了兩個非常有用的自動運行宏，一個是宏Auto_Open，另一個是宏Auto_Close。Auto_Open宏，在打開它所在的工作簿時，它就會自動運行。Auto_Close會在關(guān)閉它所在的工作薄時自動運行。這兩個宏常被用來完成一些自動化工作，如打開工作薄時啟動自定義菜單，打開必備的工作薄或工作表，或初始化一些數(shù)據(jù)，如此等等……。關(guān)閉工作薄時，完成一些結(jié)束工作，如保存數(shù)據(jù)，刪除不必要的工作表等。11.7VBA自動宏案例-Auto_openSubauto_open() '打開工作薄時自動運行的宏i=3Dor=countrate(Cells(i,2))Cells(i,3)=rCells(i,3).Style="Percent"Cells(i,4)=