VBA字典用法集錦及代碼詳解一、字典基礎(chǔ)認知1.字典核心優(yōu)勢字典（Dictionary）是VBA中高效的數(shù)據(jù)結(jié)構(gòu)，本質(zhì)是“鍵-值（Key-Item）”映射集合，核心優(yōu)勢：查找速度快（哈希表原理），百萬級數(shù)據(jù)查詢效率遠超數(shù)組遍歷；自動去重（Key值唯一），無需手動判斷重復；支持動態(tài)增刪改查，無需預設(shè)長度；可直接按Key索引值，無需遍歷定位。2.適用場景數(shù)據(jù)去重與統(tǒng)計（如計數(shù)、求和）；兩表數(shù)據(jù)匹配（如VLOOKUP功能升級）；快速查找與替換；復雜數(shù)據(jù)分組與分類匯總。3.引用與創(chuàng)建方式1：前期綁定（需添加引用）'步驟：工具→引用→勾選"MicrosoftScriptingRuntime"SubDict_Create_1()DimdictAsNewDictionary'聲明并創(chuàng)建字典對象'核心屬性驗證Debug.Print"初始字典是否為空："&dict.IsEmpty'輸出：TrueDebug.Print"初始字典條目數(shù)："&dict.Count'輸出：0EndSub方式2：后期綁定（無需引用，兼容性更強）SubDict_Create_2()DimdictAsObjectSetdict=CreateObject("Scripting.Dictionary")'動態(tài)創(chuàng)建字典'等價于前期綁定，推薦跨設(shè)備使用EndSub二、字典核心操作（增刪改查）1.新增數(shù)據(jù)（Add方法）SubDict_Add()DimdictAsObjectSetdict=CreateObject("Scripting.Dictionary")'1.基礎(chǔ)添加：Key-Item成對添加（Key唯一）dict.AddKey:="姓名",Item:="張三"dict.Add"年齡",25'簡化寫法dict.Add"部門","技術(shù)部"'2.批量添加（數(shù)組遍歷）DimarrAsVariant,iAsIntegerarr=Array("學歷","本科","工齡",3,"薪資",15000)Fori=0ToUBound(arr)Step2IfNotdict.Exists(arr(i))Then'避免Key重復報錯dict.Addarr(i),arr(i+1)EndIfNextiDebug.Print"添加后條目數(shù)："&dict.Count'輸出：6EndSub注意：Key值不能重復，重復添加會報錯，需用Exists方法提前判斷。2.查詢數(shù)據(jù)（Item屬性/Exists方法）SubDict_Query()DimdictAsObjectSetdict=CreateObject("Scripting.Dictionary")'先初始化數(shù)據(jù)dict.Add"A",100:dict.Add"B",200:dict.Add"C",300'1.按Key查詢值（兩種寫法）Debug.Print"Key=B對應值："&dict("B")'直接索引，簡潔Debug.Print"Key=C對應值："&dict.Item("C")'顯式使用Item屬性'2.判斷Key是否存在（避免查詢不存在的Key報錯）DimkeyAsStringkey="D"Ifdict.Exists(key)ThenDebug.Printkey&"對應值："&dict(key)ElseDebug.Printkey&"不存在于字典中"'輸出：D不存在于字典中EndIf'3.遍歷所有Key（ForEach循環(huán)）Debug.PrintvbCrLf&"所有Key值："ForEachkeyIndict.KeysDebug.Printkey&"→"&dict(key)NextkeyEndSub3.修改數(shù)據(jù)（直接賦值）SubDict_Modify()DimdictAsObjectSetdict=CreateObject("Scripting.Dictionary")dict.Add"產(chǎn)品","手機":dict.Add"價格",3999'1.修改已有Key的值（直接賦值覆蓋）dict("價格")=4299'將價格從3999改為4299'2.不存在的Key賦值（自動新增條目）dict("庫存")=100'等價于dict.Add"庫存",100Debug.Print"修改后價格："&dict("價格")'輸出：4299Debug.Print"新增庫存："&dict("庫存")'輸出：100EndSub4.刪除數(shù)據(jù)（Remove/RemoveAll方法）SubDict_Delete()DimdictAsObjectSetdict=CreateObject("Scripting.Dictionary")dict.Add"1","蘋果":dict.Add"2","香蕉":dict.Add"3","橙子"'1.刪除指定Keydict.Remove"2"'刪除Key=2的條目（香蕉）Debug.Print"刪除后條目數(shù)："&dict.Count'輸出：2'2.刪除所有條目（清空字典）dict.RemoveAllDebug.Print"清空后條目數(shù)："&dict.Count'輸出：0EndSub三、字典高級應用場景1.數(shù)據(jù)去重（Excel單元格去重）SubDict_RemoveDuplicates()DimdictAsObject,rngAsRange,cellAsRangeSetdict=CreateObject("Scripting.Dictionary")Setrng=Sheet1.Range("A1:A10")'待去重數(shù)據(jù)范圍'1.加載數(shù)據(jù)到字典（Key自動去重）ForEachcellInrngIfcell.ValueAndNotdict.Exists(cell.Value)Thendict.Addcell.Value,""'Item值可留空，僅用Key去重EndIfNextcell'2.去重后結(jié)果輸出到B列Sheet1.Range("B1:B"&dict.Count)=Application.Transpose(dict.Keys)MsgBox"去重完成！原數(shù)據(jù)"&rng.Cells.Count&"行，去重后"&dict.Count&"行"EndSub2.數(shù)據(jù)統(tǒng)計（計數(shù)/求和）場景：統(tǒng)計Excel中各產(chǎn)品銷售數(shù)量SubDict_Statistics()DimdictAsObject,rngAsRange,iAsIntegerDimproductAsString,qtyAsIntegerSetdict=CreateObject("Scripting.Dictionary")Setrng=Sheet1.Range("A2:B100")'A列產(chǎn)品名，B列數(shù)量'1.遍歷數(shù)據(jù)統(tǒng)計Fori=1Torng.Rows.Countproduct=rng.Cells(i,1).Valueqty=rng.Cells(i,2).ValueIfproductIfdict.Exists(product)Thendict(product)=dict(product)+qty'已有產(chǎn)品累加數(shù)量Elsedict(product)=qty'新產(chǎn)品添加初始數(shù)量EndIfEndIfNexti'2.統(tǒng)計結(jié)果輸出到D:E列Sheet1.Range("D1")="產(chǎn)品":Sheet1.Range("E1")="總銷量"Sheet1.Range("D2:D"&dict.Count+1)=Application.Transpose(dict.Keys)Sheet1.Range("E2:E"&dict.Count+1)=Application.Transpose(dict.Items)EndSub3.兩表數(shù)據(jù)匹配（高效替代VLOOKUP）場景：表1（A:B列）按產(chǎn)品名匹配表2（D:E列）的單價SubDict_Vlookup()DimdictAsObject,ws1AsWorksheet,ws2AsWorksheetDimiAsInteger,lastRow1AsInteger,lastRow2AsIntegerSetdict=CreateObject("Scripting.Dictionary")Setws1=Sheet1:Setws2=Sheet2'1.表2數(shù)據(jù)加載到字典（Key=產(chǎn)品名，Item=單價）lastRow2=ws2.Cells(ws2.Rows.Count,"D").End(xlUp).RowFori=2TolastRow2'從第2行開始（跳過表頭）Ifws2.Cells(i,"D").Valueists(ws2.Cells(i,"D").Value)Thendict.Addws2.Cells(i,"D").Value,ws2.Cells(i,"E").ValueEndIfNexti'2.表1匹配并填充單價lastRow1=ws1.Cells(ws1.Rows.Count,"A").End(xlUp).RowFori=2TolastRow1Ifdict.Exists(ws1.Cells(i,"A").Value)Thenws1.Cells(i,"C").Value=dict(ws1.Cells(i,"A").Value)'匹配成功填充單價Elsews1.Cells(i,"C").Value="無此產(chǎn)品"'匹配失敗提示EndIfNextiMsgBox"數(shù)據(jù)匹配完成！"EndSub優(yōu)勢：百萬級數(shù)據(jù)匹配速度比VLOOKUP快10-100倍，且支持反向匹配。4.字典與數(shù)組互轉(zhuǎn)（批量處理數(shù)據(jù)）SubDict_Array_Convert()DimdictAsObject,arrAsVariant,arrKeysAsVariant,arrItemsAsVariantSetdict=CreateObject("Scripting.Dictionary")'1.字典轉(zhuǎn)數(shù)組（Keys/Items屬性）dict.Add"A",1:dict.Add"B",2:dict.Add"C",3arrKeys=dict.Keys'鍵數(shù)組：{"A","B","C"}arrItems=dict.Items'值數(shù)組：{1,2,3}Debug.Print"鍵數(shù)組第2個元素："&arrKeys(1)'輸出：B（數(shù)組索引從0開始）Debug.Print"值數(shù)組第3個元素："&arrItems(2)'輸出：3'2.二維數(shù)組轉(zhuǎn)字典DimarrDataAsVariant,iAsIntegerarrData=Sheet1.Range("A1:B5").Value'二維數(shù)組（5行2列）dict.RemoveAll'清空字典Fori=1ToUBound(arrData,1)'遍歷行（UBound(arrData,1)獲取行數(shù)）dict.AddarrData(i,1),arrData(i,2)'第1列當Key，第2列當ItemNextiDebug.PrintvbCrLf&"二維數(shù)組轉(zhuǎn)字典后："ForEachkeyIndict.KeysDebug.Printkey&"→"&dict(key)NextkeyEndSub5.多條件匹配（復合Key）場景：按“產(chǎn)品+地區(qū)”雙條件匹配銷售額SubDict_MultiCondition()DimdictAsObject,iAsIntegerDimcompositeKeyAsString'復合Key（產(chǎn)品+地區(qū)）Setdict=CreateObject("Scripting.Dictionary")'加載數(shù)據(jù)：A列產(chǎn)品，B列地區(qū)，C列銷售額Fori=2ToSheet1.Cells(Sheet1.Rows.Count,"A").End(xlUp).RowcompositeKey=Sheet1.Cells(i,"A").Value&"|"&Sheet1.Cells(i,"B").Value'用分隔符拼接dict(compositeKey)=Sheet1.Cells(i,"C").Value'直接賦值（自動去重）Nexti'匹配查詢：產(chǎn)品=手機，地區(qū)=北京DimtargetKeyAsStringtargetKey="手機|北京"Ifdict.Exists(targetKey)ThenMsgBox"手機（北京）銷售額："&dict(targetKey)ElseMsgBox"無匹配數(shù)據(jù)"EndIfEndSub注意：復合Key需用特殊分隔符（如|、#），避免與數(shù)據(jù)本身沖突。四、字典常見問題與注意事項1.Key值數(shù)據(jù)類型限制Key值支持字符串、數(shù)字、日期等，但不能是數(shù)組或?qū)ο螅粩?shù)字型Key與字符串型Key視為不同（如dict.Add1,"a"與dict.Add"1","b"可共存）。2.避免報錯的關(guān)鍵判斷新增前用dict.Exists(Key)判斷是否重復；查詢前用dict.Exists(Key)避免Key不存在報錯；遍歷刪除時需先轉(zhuǎn)數(shù)組（直接遍歷字典Keys刪除會報錯）：'錯誤寫法：ForEachkeyIndict.Keys→刪除時改變集合導致遍歷中斷'正確寫法：DimarrKeysAsVariant,keyAsVariantarrKeys=dict.KeysForEachkeyInarrKeysIfkey="無用數(shù)據(jù)"Thendict.RemovekeyNextkey3.字典與數(shù)組性能對比操作場景字典數(shù)組推薦選擇隨機查找極快（O(1)）慢（O(n)）字典數(shù)據(jù)去重自動去重需手動遍歷字典批量讀寫一般極快數(shù)組（批量加載后轉(zhuǎn)字典處理）內(nèi)存占用較高較低數(shù)據(jù)量小時用數(shù)組，大時用字典4.釋放內(nèi)存SubDict_ReleaseMemory()DimdictAsObjectSetdict=CreateObject("Scripting.Dictionary")'業(yè)務(wù)處理...'釋放內(nèi)存（避免內(nèi)存泄漏）dict.RemoveAllSetdict=Nothing'關(guān)鍵：解除對象引用EndSub五、實用工具函數(shù)（直接調(diào)用）1.字典快速去重函數(shù)'功能：對指定范圍數(shù)據(jù)去重，返回去重后數(shù)組FunctionDict_Unique(rngAsRange)AsVariantDimdictAsObject,cellAsRangeSetdict=CreateObject("Scripting.Dictionary")ForEachcellInrngIfcell.Value""AndNotdict.Exists(cell.Value)Thendict.Addcell.Value,""EndIfNextcellIfdict.Count>0ThenDict_Unique=Application.Transpose(dict