宏運(yùn)用入門及數(shù)組運(yùn)用

1、1、 宏命令的使用方法11按圖1 步驟操作可添加控件及編寫相關(guān)代碼，圖2為代碼編寫窗口圖1 在工作表中添加控件圖2 編寫控件代碼12按圖3 所示步驟自定義函數(shù)后就可以像使用excel函數(shù)一樣運(yùn)用它了，如圖4圖3 自定義函數(shù)圖4 自定義函數(shù)的使用13按圖5 所示步驟編寫自己的宏命令，例如在代碼窗口輸入如下代碼：sub sheetcount()dim num as integernum = thisworkbook.sheets.countrange("a1") = numend sub此函數(shù)功能是求取工作薄中所有工作表的個(gè)數(shù)，按圖6方法運(yùn)行該宏命令，也可以按圖7所示步驟執(zhí)行宏

2、命令圖5 編寫宏命令圖6運(yùn)行宏命令圖7在excel中調(diào)用宏命令2、用array創(chuàng)建數(shù)組按alt+f11打開vbe窗口，插入一新的模塊，在代碼窗口中寫入代碼：sub test()紅繩子 = array("辣1", "辣2", "辣3", "辣4", "辣5", "辣6", "辣7", "辣8", "辣9", "辣10")end sub按f8，逐行運(yùn)行代碼，程序成功創(chuàng)建了一個(gè)數(shù)組，數(shù)組名稱是“紅繩子”

3、，"辣1", "辣2", "辣3", "辣4", "辣5", "辣6", "辣7", "辣8", "辣9", "辣10"是數(shù)組元素。用array()創(chuàng)建數(shù)組，各元素之間用逗號(hào)隔開。元素如果是文本，要用半角引號(hào)作定界符。練習(xí)，創(chuàng)建兩個(gè)數(shù)組：數(shù)組名為cname，數(shù)據(jù)為8個(gè)同學(xué)的姓名：林思明、曾玉婷、曾國(guó)文、林偉權(quán)、林興發(fā)、劉卓懷、曾仙婷、林平數(shù)組名為cj，數(shù)據(jù)為8個(gè)數(shù)值：99, 85, 92.5, 7

4、0, 78.5, 65, 84, 100參考答案：數(shù)組入門01.xls點(diǎn)擊瀏覽該文件 推薦：excelhome技術(shù)論壇的最佳學(xué)習(xí)方法 山菊花 -3、我們已經(jīng)初步認(rèn)識(shí)了數(shù)組，數(shù)組是把一組數(shù)保存到一個(gè)數(shù)組變量中，它有兩個(gè)重要特征。它是一個(gè)變量，對(duì)一組數(shù)進(jìn)行統(tǒng)一管理帶來方便。上面建立的姓名數(shù)組，我們要把它寫入到工作表區(qū)域a1向右連續(xù)8個(gè)單元格中，可用命令：range(“a1:h1”)=cname我們認(rèn)識(shí)的這類數(shù)組，稱為一維數(shù)組，一維數(shù)組是一個(gè)水平數(shù)組，相當(dāng)于工作表中的行。如果要把姓名按垂直方向填充到單元格，可轉(zhuǎn)置命令。如，把這個(gè)數(shù)組寫入到a1:a8單元格中：range(“a1:a8”)= works

5、heetfunction.transpose(cname)練習(xí)：把數(shù)組cname分別寫入到單元格c4:j4、d8:d15。答案：數(shù)組入門02.xls點(diǎn)擊瀏覽該文件第二個(gè)重要的特征，是數(shù)組元素的有序性，一組數(shù)據(jù)有序地保存到數(shù)組變量中。數(shù)組中，用數(shù)組名加序號(hào)(稱索引值)構(gòu)成唯一的名稱對(duì)元素進(jìn)行引用。元素與序號(hào)一一對(duì)應(yīng)，根據(jù)序號(hào)，可以修改和讀取指定位置上的元素。把一串姓名保存到數(shù)組cname中，數(shù)據(jù)在數(shù)組中按順序保存，默認(rèn)從0開始，依次是1、2、3，用cname(0)表示第一個(gè)姓名、cname(1)表示第二個(gè)姓名，等等。在工作表中插入一個(gè)按鈕，點(diǎn)擊按鈕，把姓名填入單元格a1:a8中：private

6、sub commandbutton1_click() cname = array("林思明", "曾玉婷", "曾國(guó)文", "林偉權(quán)", "林興發(fā)", "劉卓懷", "曾仙婷", "林平")for i = 1 to 8 range("a" & i) = cname(i - 1)nextend sub數(shù)組序號(hào)(索引值)默認(rèn)從0開始，也可以通過命令option base 1修改為從1開始。該命令寫在模塊的前面。如

7、果設(shè)定索引值從1開始，則上面的代碼修改為：private sub commandbutton1_click() cname = array("林思明", "曾玉婷", "曾國(guó)文", "林偉權(quán)", "林興發(fā)", "劉卓懷", "曾仙婷", "林平")for i = 1 to 8 range("a" & i) = cname(i ) 工作表行號(hào)與數(shù)組序號(hào)相吻合nextend sub參考：數(shù)組入門03.xls點(diǎn)擊瀏

8、覽該文件-4、想起瑞士軍刀array()很好用，但有時(shí)split()更好用，我們?nèi)〕鲞@瑞士軍刀的第二利器。split()把一個(gè)文本，根據(jù)指定的分隔符，建立一個(gè)數(shù)組?？匆粋€(gè)實(shí)例：?jiǎn)卧馻1中有一串姓名：朱清燕(女),林鑫,林秋靜(女),林永鑫,林云艷(女),林嘉惠(女),曾文婷(女),林悅,陳誠(chéng),林偉健,賴紫嵐(女),曾雁(女),賴文強(qiáng),鐘娟(女),張琪(女),林文彬,黃曉婷(女),李朕,林依婷(女),林佳利(女),曾德福要求：用split()，把這串文本創(chuàng)建為一個(gè)數(shù)組：sub test()xm = split(range("a1"), ",")end s

9、ub同樣可以用本地窗口檢查轉(zhuǎn)換結(jié)果。注意，split()函數(shù)把文本轉(zhuǎn)換成數(shù)組，并不受option base的影響，索引號(hào)總是從0開始。很自然想知道，a1單元格中有多少個(gè)姓名？創(chuàng)建的數(shù)組有多少個(gè)元素？瑞士軍刀中有把小尺子，可以量一量：ubound()返回?cái)?shù)組的最大下標(biāo)。sub test()xm = split(range("a1"), ",")msgbox "數(shù)組最大下標(biāo)是 " & ubound(xm) & chr(13) _& "數(shù)組共有 " & ubound(xm) + 1 &am

10、p; " 個(gè)元素"end sub參考：數(shù)組入門04.xls點(diǎn)擊瀏覽該文件5、三維表示一個(gè)空間，四維、五維、六維呢？三維已經(jīng)可以代表寬廣無垠的宇宙，而數(shù)組卻允許聲明為4維、5維、6維，直至60維。這是一個(gè)什么樣的概念？我曾經(jīng)打過一個(gè)這樣的比方：6、由工作表區(qū)域創(chuàng)建數(shù)組。與array()和split()相比，由工作表區(qū)域創(chuàng)建數(shù)組更加直觀而簡(jiǎn)易，一個(gè)等號(hào)便解決問題：如：用sheet2工作表a1:e83的數(shù)據(jù)創(chuàng)建一個(gè)數(shù)組：private sub commandbutton1_click()arr = sheet2.range("a1:e83")end sub如果

11、把該數(shù)據(jù)復(fù)制到sheet1工作表相同區(qū)域，用數(shù)組操作，代碼如下：private sub commandbutton1_click()arr = sheet2.range("a1:e83")range("a1:e83") = arrend sub共兩句，前一句把數(shù)據(jù)保存到數(shù)組，第二句，把數(shù)組內(nèi)容寫入到工作表。選擇第3行代碼，按f9設(shè)置斷點(diǎn)。點(diǎn)擊按鈕運(yùn)行代碼，到該行，程序自動(dòng)中斷，此時(shí)，可從本地窗口中觀察到運(yùn)行結(jié)果，一個(gè)83行2列的數(shù)組已經(jīng)創(chuàng)建。也可以按f8逐行運(yùn)行代碼，查看數(shù)組創(chuàng)建的結(jié)果。參考：數(shù)組入門05.xls點(diǎn)擊瀏覽該文件7、隨心所欲創(chuàng)建數(shù)組以上幾種

12、辦法都是根據(jù)已有的數(shù)據(jù)創(chuàng)建數(shù)組。實(shí)際使用中，更多時(shí)候，程序需要先創(chuàng)建數(shù)組，然后對(duì)數(shù)組進(jìn)行賦值和修改。private、public、dim、static，這些語句都可聲明數(shù)組變量，區(qū)別在于聲明的數(shù)組的使用范圍，可查看各語句的幫助。新建一個(gè)過程：sub 聲明數(shù)組()dim myarray(10) '在過程中間聲明一個(gè)數(shù)組，過程結(jié)束，本數(shù)組將被釋放。myarray(1) = 100 '給數(shù)組賦值myarray(5) = 90end sub按f8逐行運(yùn)行程序，在本地窗口中可以看到數(shù)組myarray()已成功創(chuàng)建。dim是聲明變量的語句；myarray是數(shù)組名稱；10是數(shù)組最大下標(biāo)。當(dāng)o

13、ption base 0的模式下，數(shù)組的序號(hào)(索引值)從0到10，表示數(shù)組有11個(gè)元素，當(dāng)option base 1的模式下，數(shù)組的索引值從1到10，該語句創(chuàng)建的數(shù)組共有10個(gè)元素。上面聲明的數(shù)組是一維數(shù)組，數(shù)組最大可以定義60維。多維數(shù)組的表示方法，維與維之間用逗號(hào)相隔。sub 聲明數(shù)組b()dim data(10,5) 聲明一個(gè)二維數(shù)組data (1,1) = “a001” '給數(shù)組賦值data (1,2) = 90data (3,1) = “a003” data (3,2) = 80end sub這里，data是一個(gè)二維數(shù)組。二維數(shù)組的兩維，我們通常用“行、列”稱呼它，如data

14、，最大行號(hào)是10，最大列號(hào)是5，在option base 1 模式下，表示是一個(gè)10行5列共50個(gè)元素的數(shù)組空間。在option base 0 模式下，表示一個(gè)11行6列共66個(gè)元素的數(shù)組空間。二維數(shù)組是使用頻率最高的數(shù)組形式，因?yàn)槲覀兊墓ぷ鞅硪彩嵌S的。用工作表來領(lǐng)會(huì)二維數(shù)組，將會(huì)更加直觀：data(1,1)相當(dāng)于cells(1,1)，表示第1行，第1列；data(5,3)相當(dāng)于cells(5,3)，表示第5行，第3列。下面的語句，聲明的是一個(gè)三維數(shù)組：sub 聲明數(shù)組b()dim x(3,30,3) x (1,1,1) = “1班” '給數(shù)組賦值x (1,1,2) = “張三”x

15、(1,1,3) = 88 x (1,2,1) = “1班” '給數(shù)組賦值x (1,2,2) = “李四”x (1,2,3) = 95 x (2,1,1) = “2班” '給數(shù)組賦值x (2,1,2) = “劉日”x (2,1,3) = 92 end sub此主題相關(guān)圖片如下：我們可以用工作簿的概念來遷移領(lǐng)會(huì)三維數(shù)組：x(1,2,3)：第1個(gè)工作表第2行第3列；x(2,14,10)：第2個(gè)工作表第14行第10列。假如我們要測(cè)量數(shù)組各維的最大下標(biāo)，可以在ubound()函數(shù)使用第二個(gè)參數(shù)：ubound(x,1)，返回?cái)?shù)組x第1維的最大下標(biāo)；ubound(x,2)，返回?cái)?shù)組x第2維的

16、最大下標(biāo)；ubound(x,3)，返回?cái)?shù)組x第3維的最大下標(biāo)。參考：數(shù)組入門06.xls點(diǎn)擊瀏覽該文件8、進(jìn)階操作聲明數(shù)組聲明數(shù)組還可以根據(jù)需要自己指定下標(biāo)和上標(biāo)，而且不受option base 的影響：sub 聲明數(shù)組()dim carr(11 to 20) 聲明一個(gè)數(shù)組carr，下標(biāo)從11到20，共有10個(gè)元素。carr(11) = "上"carr(12) = "中"carr(13) = "下"end sub數(shù)組的索引值還允許為負(fù)整數(shù)：sub 聲明數(shù)組()dim narr(-10 to 10) 聲明一個(gè)數(shù)組 narr，下標(biāo)從-10

17、到10，共21個(gè)元素。narr(-10) = 123narr(-9) = 456narr(-2) = 789end sub可變的下標(biāo)給你的程序帶來靈活性，推薦另一個(gè)函數(shù)：ubound()函數(shù)的姐妹花lbound()。lbound()可以返回指定數(shù)組維可用的最小下標(biāo)。如：xb = lbound(narr)msgbox xb聲明數(shù)組時(shí)，還允許像聲明單值變量一樣為數(shù)組指定為數(shù)據(jù)類型。sub 聲明數(shù)組()dim carr(11 to 20) as stringcarr(11) = "上"carr(12) = "中"carr(13) = "下"

18、end subas string 表示數(shù)組carr()是一個(gè)字符型變量，在本地窗口中可以看到，沒有賦值的元素默認(rèn)為空文本。sub 聲明數(shù)組()dim narr(-10 to 10) as integernarr(-10) = 123narr(-9) = 456narr(-2) = 789end subas integer表示數(shù)組narr()是一個(gè)整型變量，在本地窗口中可以看到，沒有賦值的元素默認(rèn)為0。沒有指定類型的變量，默認(rèn)為variant，沒有賦值的元素默認(rèn)為“空值”。此主題相關(guān)圖片如下：參考：數(shù)組入門07.xls點(diǎn)擊瀏覽該文件變量的級(jí)別問題以上都是在過程內(nèi)部聲明變量，當(dāng)過程結(jié)果時(shí)，變量自動(dòng)

19、被釋放。在模塊級(jí)別中聲明變量，對(duì)該模塊中的所有過程都是可用的。參考：數(shù)組入門08.xls點(diǎn)擊瀏覽該文件如果要聲明級(jí)別更高的變量，可使用public語句。詳見幫助。9、動(dòng)態(tài)數(shù)組以上聲明的數(shù)組都屬于“靜態(tài)數(shù)組”，每個(gè)數(shù)組有確定的維數(shù)，每維有確定的最大下標(biāo)。與之相對(duì)應(yīng)的是動(dòng)態(tài)數(shù)組。程序中，數(shù)組的大小有時(shí)不是固定的，而是隨數(shù)據(jù)的變化而變化。聲明數(shù)組的時(shí)候，不指定數(shù)組的維數(shù)與下標(biāo)，這樣的數(shù)組就是動(dòng)態(tài)數(shù)組，在程序中可用命令redim為動(dòng)態(tài)數(shù)組變量重新分配存儲(chǔ)空間。例：?jiǎn)卧馻1中有一串姓名：朱清燕(女),林鑫,林秋靜(女),林永鑫,林云艷(女),林嘉惠(女),曾文婷(女),林悅,陳誠(chéng),林偉健,賴紫嵐(女)

20、,曾雁(女),賴文強(qiáng),鐘娟(女),張琪(女),林文彬,黃曉婷(女),李朕,林依婷(女),林佳利(女),曾德福要求：把名單整理成兩列，第一列是姓名，第二列是性別，把它輸出到b:c兩列。與例4相同，用split()，把這串文本創(chuàng)建為一個(gè)數(shù)組，然后根據(jù)這個(gè)數(shù)組的大小，創(chuàng)建另一個(gè)數(shù)組：redim arr(1 to s, 1 to 2)其中s是數(shù)組xm()的最大下標(biāo)，xm()有多少行，arr()就有多少行，列數(shù)為2，一列是姓名，另一列是性別。參考：數(shù)組入門09.xls點(diǎn)擊瀏覽該文件允許使用redim語句反復(fù)地改變數(shù)組的元素以及維數(shù)的數(shù)目，請(qǐng)自己練習(xí)。10、對(duì)同一數(shù)組的反復(fù)定義對(duì)同一數(shù)組反復(fù)改變大小的時(shí)候

21、，也有規(guī)則必須遵守。如果數(shù)組中存在數(shù)據(jù)，再次定義的時(shí)候默認(rèn)把原數(shù)據(jù)清除，重新定義后的數(shù)組是一個(gè)空的數(shù)組。如果你要保留原來的數(shù)據(jù)，必須加上參數(shù)preserve。使用preserve參數(shù)又有一個(gè)限制，只能改變最后一維的大小，比如二維數(shù)組，只能改變第2維(列)的大小而不能改第1維(行)的大小。還是使用上個(gè)示例，要求把a(bǔ)1單元格中的女同學(xué)的姓名整理成兩列，第一列序號(hào)，第二列是姓名。使用數(shù)組arr()記錄整理結(jié)果，我們?cè)诒闅v原始數(shù)組xm()的時(shí)候，根據(jù)性別的判斷即時(shí)改變arr()的大小。但不能把數(shù)組arr()定義為n行2列，第1列保存序號(hào)，第2列保存姓名，因?yàn)槲覀儽仨毷褂胮reserve參數(shù)保留原來的數(shù)

22、據(jù)，此時(shí)數(shù)組arr(n,2)的第一維是不允許改變的。為了解決為個(gè)矛盾，我們可以把數(shù)組定義為2行n列，第一行記錄序號(hào)，第二行記錄姓名，列數(shù)在程序過程中反復(fù)改變。n = n + 1redim preserve arr(1 to 2, 1 to n) '改變動(dòng)態(tài)數(shù)組的大小，共2行，n列數(shù)組第1行記錄序號(hào)，第2行記錄姓名：arr(1, n) = narr(2, n) = left(xm(i), len(xm(i) - 3) 對(duì)數(shù)組xm()遍歷結(jié)束后，把結(jié)果寫入工作表，因?yàn)閿?shù)組的方向與結(jié)果要求的方向不一致，所以要加一個(gè)轉(zhuǎn)置函數(shù)：range("b6").resize(n, 2)

23、 = worksheetfunction.transpose(arr)參考：數(shù)組入門10.xls點(diǎn)擊瀏覽該文件11、搜索數(shù)組園子里是不是有紅蘋果？你手中的牌是不是有紅桃k？我們用眼睛一瞄就知道。數(shù)組元素中是不是有“張三”，是不是有“李四”，我們?nèi)绾沃?？不用?dān)心，filter()函數(shù)剛好可以幫你忙。filter()函數(shù)在一維數(shù)組中搜索文本型數(shù)據(jù)，并把結(jié)果保存到指定的數(shù)組中。a1單元格是以逗號(hào)分隔的姓名串，部分有重復(fù)，要求剔除其中重復(fù)的姓名，把不重復(fù)的姓名寫入單元格a2向右的區(qū)域。第一步，把所有姓名保存到數(shù)組xm()中；第二步，建立一個(gè)數(shù)組arr()保存結(jié)果，遍歷xm()過程中，檢查arr()中是否存在當(dāng)前的姓名，用命令：temp = filter(arr, xm(i) '在數(shù)組arr()中搜索當(dāng)前值xm(i)，如果找到，結(jié)果保存到temp()數(shù)組中，如果找不到，返回一個(gè)空的數(shù)組temp()根據(jù)temp()數(shù)組的大小，可以知道當(dāng)前值xm(i)是不是一個(gè)重復(fù)的值。參考：數(shù)組入門11.xls點(diǎn)擊瀏覽該文件12、連接數(shù)組元素有一個(gè)函數(shù)，是spli