Excel VBA 實戰(3)
先放答案
Option ExplicitnnPublic Sub Main()n n printDiff 1, 2nnEnd SubnnnFunction printDiff(ByVal ColA As Integer, ByVal ColB As Integer, Optional ByVal startRow As Long = 2)n n 1. load Rng into Dictionaryn Dim d1 As Objectn n Dim d2 As Objectn n Cells(Rows.Count, ColA).End(xlUp) the last filled cell in ColAn Set d1 = loadRngIntoDict(Range(Cells(startRow, ColA), Cells(Rows.Count, ColA).End(xlUp)))n n Set d2 = loadRngIntoDict(Range(Cells(startRow, ColB), Cells(Rows.Count, ColB).End(xlUp)))n n 2. print the elements only in A columnn n Debug.Print "Following elements exist only in A Col"n n printDiffOfDicts d1, d2n n Debug.Print String(10, "*")n 3. print the elements only in B columnn n Debug.Print "Following elements exist only in B Col"n n printDiffOfDicts d2, d1nnEnd FunctionnnnPrivate Function loadRngIntoDict(ByRef rng As Range) As Objectn n create dict objectn Dim res As Objectn Set res = CreateObject("scripting.dictionary")n n loop through the cells in the range and load the value into dictn Dim c As Rangen For Each c In rng.Cellsn wipe out the possible invisible blank in the stringn res(CStr(Trim(c.Value))) = 1n Next cn n Set loadRngIntoDict = resn n release the memoryn Set res = NothingnnEnd Functionnn print key in dict1 but not in dict2nPrivate Function printDiffOfDicts(ByRef dict1 As Object, ByRef dict2 As Object)n Dim kn n loop through dict1n For Each k In dict1.keysn if not in dict2 print it outn If Not dict2.exists(k) Thenn Debug.Print kn End Ifn Next kn nEnd Functionn
運行結果
再說思路。這種問題在實踐中相當常見。用兩個Array同樣也可以達到目的,但是採用Dictionary這種容器執行效率會高得多,具體而言是O(N^2) 與 O(lnN)的關係。
額外定義兩個功能函數,簡化代碼。
總體思路非常容易,1 將各列數值以鍵的形式存入Dictionary中,2 比較並列印兩Dictionary中的差異。
題外話,我自己編寫了一個Dicts的類文件(函數式編程風格,我的最愛)專門處理與表格相關的數據交互, 運用Dicts會大大縮減代碼量。有機會專門跟大家介紹。
GitHub 地址如下
6234456/Excel-VBA-Dicts![圖標](http://i1.wp.com/pic1.zhimg.com/v2-423c187c76c35bd9591bd2ae88c22e98_ipico.jpg)
安利下自己的作品。 相關代碼位於TestDicts模塊下。
Option ExplicitnnPublic Sub main()n n printDiffWithDicts 1, 2nnEnd SubnnFunction printDiffWithDicts(ByVal ColA As Integer, ByVal ColB As Integer, Optional ByVal startRow As Long = 2)n n Dim d1 As New Dictsn Dim d2 As New Dictsn n Call d1.load("", ColA, ColA, startRow)n Call d2.load("", ColB, ColB, startRow)n n Debug.Print "Following elements exist only in A Col"n n d1.minus(d2).pkn n Debug.Print String(10, "*")nn Debug.Print "Following elements exist only in B Col"n n d2.minus(d1).pknnEnd Functionn
代碼文件下載。
qiou.eu/xl/Case2.xlsm本期問題,自己實現 內部收益率IRR 函數。對於函數具體描述請參見Excel公式。
請大家務必預先嘗試,歡迎給出你們的代碼。
解析留待下期。
有任何問題請在下方留言。
本專欄所有文章著作權歸屬本人。未經本人書面許可,除知乎日報外,任何人不得轉載。
推薦閱讀:
※你為什麼覺得Excel VBA有點難?
※偽裝成萬葉假名的亂碼生成器v0.9
※VBA入門教程
※【VBA初學者教程】- 第一章 VBA入門知識:引用若干單元格區域
※Excel VBA入門(一)數據類型
TAG:MicrosoftExcel | VBA |