字典的基本功能
1 Public Const PR_SALARY_GROUP As String = "班组" '为TestC定义全局常量CopyEHLee1892 2 Public Const PR_SALARY_BONUS As String = "绩效奖金" '为TestC定义全局常量 3 '===============一般写法===================== 4 Sub TestA() 5 Dim Dic, Arr, I&, lRow& 6 lRow = Sheet1.Cells(Rows.Count, 1).End(3).Row 7 Arr = Sheet1.Range("a2:e" & lRow) 8 Set Dic = CreateObject("Scripting.Dictionary") 9 For I = 1 To UBound(Arr) 10 Dic(Arr(I, 2)) = Dic(Arr(I, 2)) + Arr(I, 5) 11 Next 12 End Sub 13 14 '=========DTitle字典作为列字段的变量=============== 15 Sub TestB() 16 Dim DTitle, Arr, I&, Dic 17 Arr = Sheet1.[a1].CurrentRegion 18 Set DTitle = CreateObject("Scripting.Dictionary") 19 For I = 1 To UBound(Arr, 2) 20 DTitle(Arr(1, I)) = I 21 Next 22 Set Dic = CreateObject("Scripting.Dictionary") 23 For I = 2 To UBound(Arr) 24 Dic(Arr(I, DTitle("班组"))) = Dic(Arr(I, DTitle("班组"))) _ 25 + Arr(I, DTitle("绩效奖金")) 26 Next 27 End Sub 28 29 '===========训练多流汗,战时少流血!编写多常量,更改不挠头!=============== 30 Sub TestC() 31 Dim DTitle, Arr, I&, Dic 32 Arr = Sheet1.[a1].CurrentRegion 33 Set DTitle = CreateObject("Scripting.Dictionary") 34 For I = 1 To UBound(Arr, 2) 35 DTitle(Arr(1, I)) = I 36 Next 37 Set Dic = CreateObject("Scripting.Dictionary") 38 For I = 2 To UBound(Arr) 39 Dic(Arr(I, DTitle(PR_SALARY_GROUP))) = _ 40 Dic(Arr(I, DTitle(PR_SALARY_GROUP))) + _ 41 Arr(I, DTitle(PR_SALARY_BONUS)) 42 Next 43 End Sub
数据源: