Excel VBA 實戰(3)

01-29

先放答案

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-Dictsgithub.com

安利下自己的作品。相關代碼位於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.xlsmqiou.eu

本期問題，自己實現內部收益率IRR 函數。對於函數具體描述請參見Excel公式。

請大家務必預先嘗試，歡迎給出你們的代碼。

解析留待下期。

有任何問題請在下方留言。

本專欄所有文章著作權歸屬本人。未經本人書面許可，除知乎日報外，任何人不得轉載。