|
VBA數(shù)組學(xué)習(xí)筆記 聲明數(shù)組 數(shù)組的聲明方式和其它的變量是一樣的，它可以使用 Dim、Static、Private 或 Public 語句來聲明。標(biāo)量變量（非數(shù)組）與數(shù)組變量的不同在于通常必須指定數(shù)組的大小。若數(shù)組的大小被指定的話，則它是個固定大小數(shù)組。若程序運行時數(shù)組的大小可以被改變，則它是個動態(tài)數(shù)組。 數(shù)組是否從 0 或 1 索引是根據(jù) Option Base 語句的設(shè)置。如果 Option Base 沒有指定為 1，則數(shù)組索引從零開始。 聲明固定大小的數(shù)組 下面這行代碼聲明了一個固定大小的數(shù)組，它是個 11 行乘以 11 列的 Integer 數(shù)組： Dim MyArray(10, 10) As Integer 第一個參數(shù)代表的是行；而第二個參數(shù)代表的是列。 與其它變量的聲明一樣，除非指定一個數(shù)據(jù)類型給數(shù)組，否則聲明數(shù)組中元素的數(shù)據(jù)類型為 Variant。數(shù)組中每個數(shù)組的數(shù)字型 Variant 元素占用 16 個字節(jié)。每個字符串型 Variant 元素占用 22 個字節(jié)。為了盡可能使寫的代碼簡潔明了，則要明確聲明的數(shù)組為某一種數(shù)據(jù)類型而非 Variant。下面的這幾行代碼比較了幾個不同數(shù)組的大?。?br> ' 整型數(shù)組使用 22 個字節(jié)（11 元素* 2 字節(jié)） ReDim MyIntegerArray(10) As Integer ' 雙精度數(shù)組使用 88 個字節(jié)（11 元素 * 8 字節(jié)）。 ReDim MyDoubleArray(10) As Double ' 變體型數(shù)組至少使用 176 字節(jié)（11 元素 * 16 字節(jié)）。 ReDim MyVariantArray(10) ' 整型數(shù)組使用 100 * 100 * 2 字節(jié)（20,000 字節(jié)）。 ReDim MyIntegerArray (99, 99) As Integer ' 雙精度數(shù)組使用 100 * 100 * 8 字節(jié)（80,000 字節(jié)）。 ReDim MyDoubleArray (99, 99) As Double ' 變體型數(shù)組至少使用 160,000 字節(jié)（100 * 100 * 16 字節(jié)）。 ReDim MyVariantArray(99, 99) 數(shù)組變量的最大值，是以的操作系統(tǒng)與有多少可用內(nèi)存為基礎(chǔ)。若使用的數(shù)組大小超過了系統(tǒng)中可用內(nèi)存總數(shù)的話，則速度會變慢，因為必須從磁盤中讀寫回數(shù)據(jù)。 聲明動態(tài)數(shù)組 若聲明為動態(tài)數(shù)組，則可以在執(zhí)行代碼時去改變數(shù)組大小。可以利用 Static、Dim、Private 或 Public 語句來聲明數(shù)組，并使括號內(nèi)為為空，如下示例所示。 Dim sngArray() As Single 注意 可以在過程中使用 ReDim 語句來做隱含性的數(shù)組聲明。當(dāng)使用 ReDim 語句時要小心點，不要拼錯數(shù)組的名稱。否則即使在模塊中有包含 Option Explicit 語句，仍然會因此而生成第二個數(shù)組。 對于過程中的數(shù)組范圍，可以使用 ReDim 語句去改變它的維數(shù)，去定義元素的數(shù)目以及每個維數(shù)的底層綁定。每當(dāng)需要時，可以使用 ReDim 語句去更改動態(tài)數(shù)組。然而當(dāng)做這個動作時，數(shù)組中存在的值會丟失。若要保存數(shù)組中原先的值，則可以使用 ReDim Preserve 語句來擴(kuò)充數(shù)組。例如，下列的語句將 varArray 數(shù)組擴(kuò)充了10 個元素，而原本數(shù)組中的當(dāng)前值并沒有消失掉。 ReDim Preserve varArray(UBound(varArray) + 10) 注意 當(dāng)對動態(tài)數(shù)組使用 Preserve 關(guān)鍵字時，只可以改變最后維數(shù)的上層綁定，而不能改變維數(shù)的數(shù)目。 常常在論壇看到很多VBA高手運用數(shù)組解決各種問題，速度快，代碼簡潔，很是羨慕，所以逐漸收集了一些資料，與大家分享，也請多多指教。在此，也向高手們致以謝意。 一、數(shù)組的分類 按元素數(shù)目分：元素數(shù)目大小固定的數(shù)組和元素數(shù)目大小不固定的動態(tài)數(shù)組。 按維數(shù)分：一維數(shù)組、多維數(shù)組。 Arr(1 to 12)、Arr1(0 to 24)----一維固定數(shù)組； Arr2(1 to 5,1 to 8)---- 二維固定數(shù)組； Arr3(5 to 10,6 to 12,1 to 100) ---- 三維固定數(shù)組。 動態(tài)數(shù)組 Dim Arr2(),r% r=r+1 ReDim Preserve Arr2(1 To r) ―――動態(tài)數(shù)組；可以重新聲明（只有最后一維的數(shù)目才能重新聲明）； 用了關(guān)鍵字 Preserve 可確保原來包含數(shù)據(jù)的數(shù)組中的任何數(shù)據(jù)都不會丟失 二、數(shù)組的賦值 2.1，單元格區(qū)域保存到數(shù)組 arr = [e22:i24] arr=Range(“e22:i24”) 2.2，Array函數(shù) myArray = Array("AAA", "BBB", 200, 500, "2006-7-12") 如果代碼頭沒有 Option Base 1 的語句，則數(shù)組myArray的上限為4，下限為0。 即下限LBound(myArr)=0 ，上限 UBound(myArr)=4 二維數(shù)組的第一維的上限：UBound(Arr,1) 二維數(shù)組的第二維的上限：UBound(Arr,2) 多維數(shù)組上限的求法一樣。 2.3，把單元格區(qū)域公式賦給數(shù)組 如果a5=B4+1 arr = [a4:c8].Formula '將單元格絕對引用公式保存到數(shù)組 [e4:g8]=arr 此時e5中的公式也＝B4+1; 如果將單元格相對引用公式保存到數(shù)組 arr = [a4:c8].FormulaR1C1 如果a5=B4+1 [e4:g8]=arr 此時e5中的公式就＝E4+1; 三、數(shù)組的處理 3.1，數(shù)組里的最大值和最小值 最大值aa = Application.WorksheetFunction.Max(Arr) aa= Application.WorksheetFunction.Large(Arr,1) 最小值aa = Application.WorksheetFunction.Min(Arr) aa= Application.WorksheetFunction.Small(Arr,1) 3.2，數(shù)組里搜索 Temp = Filter(Arr, xm(i)) '搜索數(shù)組 Sub yy() Dim Arr(), aa$, x% aa = "asssfffssssaaasss": bb = "s" For x = 1 To Len(aa) ReDim Preserve Arr(1 To x) Arr(x) = Mid(aa, x, 1) Next x temp = Filter(Arr, bb) cc = UBound(temp) + 1 ‘cc=”s”的個數(shù) End Sub 用于對字符串?dāng)?shù)組進(jìn)行搜索，得到一個新的數(shù)組temp， 缺點：只告訴你某元素是否存在于數(shù)組中，而不知道其具體位置； 數(shù)組精確搜索： Sub FilterExactMatch() ' 該函數(shù)在一個字符串?dāng)?shù)組中搜索那些 ' 與搜索字符串完全匹配的元素。 Dim astrFilter() As String Dim astrTemp() As String Dim lngUpper As Long Dim lngLower As Long Dim lngIndex As Long Dim lngCount As Long astrItems = Array("a", "sas", "s", "Sas", "s", "f", "f", "f", "f", "sas", "s", "sas", "a", "a", "Sas", "s", "s") strSearch = "Sas" ' 為搜索字符串而過濾數(shù)組。 astrFilter = Filter(astrItems, strSearch) ' 存儲結(jié)果數(shù)組的上限和下限。 lngUpper = UBound(astrFilter) lngLower = LBound(astrFilter) ' 將臨時數(shù)組調(diào)整到相同大小。 ReDim astrTemp(lngLower To lngUpper) ' 在經(jīng)過濾的數(shù)組的每個元素中循環(huán)。 For lngIndex = lngLower To lngUpper ' 檢查該元素是否與搜索字符串完全匹配。 If astrFilter(lngIndex) = strSearch Then ' 在另一個數(shù)組中存儲完全匹配的元素。 astrTemp(lngCount) = strSearch lngCount = lngCount + 1 End If Next lngIndex ' 重新調(diào)整包含完全匹配的元素的數(shù)組的大小。 ReDim Preserve astrTemp(lngLower To lngCount - 1) ' 返回包含完全匹配的元素的數(shù)組。 [a5].Resize(1, UBound(astrTemp) + 1) = Application.Transpose(astrTemp) End Sub 3.3，轉(zhuǎn)置 取工作表區(qū)域的轉(zhuǎn)置到數(shù)組：arr=Application.Transpose([a1:c5]) ‘此時arr是轉(zhuǎn)置成3行5列的數(shù)組，arr(1 to 3,1 to 5) [e1:i3]=arr ‘此時3行5列。 數(shù)組間也可以轉(zhuǎn)置：arr1=Application.Transpose(arr) 取數(shù)組arr的第n列賦值到某列區(qū)域：[e1:e5]=Application.Index(arr, 0, n) 也可寫成 [e1:e5]=Application.Index(arr, , n) 賦值產(chǎn)生一個新數(shù)組：arr1=Application.Index(arr,0 , n) 取數(shù)組arr的第n行賦值到某行區(qū)域：[a6:c6]=Application.Index(arr,n ,0 ) 也可寫成 [a6:c6]=Application.Index(arr,n ) 省略0，也省略了“，“ 賦值產(chǎn)生一個新數(shù)組：arr1=Application.Index(arr, n ) 3.4，數(shù)組的比較（字典法） 題目：將A列中的數(shù)據(jù)與C列相比較,輸出C列中沒有的數(shù)據(jù)到D列： Sub cc() ‘by:ccwan Dim arr, brr, i&, x&, d As Object arr = Range("a1:a" & [a65536].End(xlUp).Row) brr = Range("c1:c" & [c65536].End(xlUp).Row) Set d = CreateObject("scripting.dictionary") For i = 1 To UBound(arr) d(arr(i, 1)) = "" Next For x = 1 To UBound(brr) If d.exists(brr(x, 1)) Then d.Remove brr(x, 1) End If Next [d1].Resize(d.Count, 1) = Application.Transpose(d.keys) End Sub 3.5，數(shù)組的排序 字符串?dāng)?shù)組不能用Large(Arr,i) 或者 Small(Arr,i) 來排序； 但數(shù)值數(shù)組可以； 一個很好的字典+數(shù)組排序的實例： Sub yy1() ‘by:oobird Dim i%, c As Range, x, d As Object Set d = CreateObject("Scripting.Dictionary") For Each c In Sheet2.UsedRange If c.Value <> "" Then If Not d.exists(c.Value) Then d.Add c.Value, 1 Else d(c.Value) = d(c.Value) + 1 End If End If Next k = d.keys: t = d.items 'k是各個不重復(fù)值，t是各個不重復(fù)值的個數(shù) ReDim x(1 To 2, 1 To d.Count) For i = 1 To d.Count x(2, i) = Application.Large(k, i) ‘從大到小排序 x(1, i) = d(x(2, i)) Next i With Sheet1 .[b2].Resize(2, i - 1) = x ReDim x(1 To 2, 1 To d.Count) For i = 1 To d.Count x(1, i) = Application.Max(t) ‘從大到小排序 w = Application.Match(x(1, i), t, 0) – 1 ‘查找此值在不重復(fù)值系列中的排位，因為w是從0開始的，所以－1 x(2, i) = k(w) ‘求得對應(yīng)的不重復(fù)值 t(w) = "" ‘使前面的最大值為空，繼續(xù)循環(huán) Next i .[b5].Resize(2, i - 1) = x ‘兩行一起賦值給B5開始的單元格 End With End Sub 字符串?dāng)?shù)組的排序，可以使用輔助列，把數(shù)組各元素依次賦給單元格，然后對這些單元格運用Excel自有的數(shù)據(jù)排序功能進(jìn)行排序，再把單元格排過序的值重新賦給數(shù)組。 3.6，數(shù)組賦給單元格區(qū)域 r=Ubound(Arr) r為一維數(shù)組的上限; Range("a2").Resize(1, r) = Arr '填充到工作表的一行之中（Arr為一維數(shù)組） 或者寫成 Range("a2").Resize(1, Ubound(Arr)) = Arr 賦值方面的補(bǔ)充： Sub y() Dim arr arr = [mmult(row(1:100),column(a:f))] [a1].Resize(100, 6) = arr End Sub Sub yy() Dim arr arr = [column(a:z)^3] MsgBox Join(arr, ",") arr = [transpose(row(1:222))] MsgBox Join(arr, ",") End Sub Sub yyy() Dim arr arr = Split("a b c d e f g") MsgBox Join(arr, ",") End Sub |
|
|
