VBA字典高效数据处理技巧分享

VBA字典高效数据处理技巧分享

Sub DictionaryExample()
    ' 创建字典对象
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 添加键值对
    dict.Add "A", 100
    dict.Add "B", 200
    dict.Add "C", 300
    
    ' 检查键是否存在
    If dict.Exists("B") Then
        MsgBox "Key B exists. Value: " & dict("B")
    End If
    
    ' 修改值
    dict("B") = 250
    
    ' 删除键
    dict.Remove "C"
    
    ' 遍历字典
    Dim key As Variant
    For Each key In dict.Keys
        Debug.Print "Key: " & key & ", Value: " & dict(key)
    Next
    
    ' 统计元素数量
    MsgBox "Total items: " & dict.Count
    
    ' 清空字典
    dict.RemoveAll
End Sub

Sub DictionaryAdvancedUsage()
    ' 创建字典对象
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' 数据去重示例
    Dim arr As Variant
    arr = Array("apple", "banana", "apple", "orange", "banana", "grape")
    
    Dim i As Long
    For i = LBound(arr) To UBound(arr)
        dict(arr(i)) = 1 ' 任意值，关键是键的唯一性
    Next
    
    ' 去重后的数组
    Dim uniqueArr() As String
    ReDim uniqueArr(dict.Count - 1)
    i = 0
    For Each key In dict.Keys
        uniqueArr(i) = key
        i = i + 1
    Next
    
    ' 输出去重后的数组
    For i = LBound(uniqueArr) To UBound(uniqueArr)
        Debug.Print uniqueArr(i)
    Next
    
    ' 数据查找示例
    Dim searchKey As String
    searchKey = "banana"
    
    If dict.Exists(searchKey) Then
        MsgBox "Found: " & searchKey
    Else
        MsgBox "Not found: " & searchKey
    End If
    
    ' 字典排序示例
    Dim sortedKeys() As Variant
    sortedKeys = dict.Keys
    Call QuickSort(sortedKeys, LBound(sortedKeys), UBound(sortedKeys))
    
    ' 输出排序后的键值对
    For i = LBound(sortedKeys) To UBound(sortedKeys)
        Debug.Print "Sorted Key: " & sortedKeys(i) & ", Value: " & dict(sortedKeys(i))
    Next
    
    ' 多字典操作示例
    Dim dict2 As Object
    Set dict2 = CreateObject("Scripting.Dictionary")
    dict2.Add "D", 400
    dict2.Add "E", 500
    
    ' 合并字典
    Dim combinedDict As Object
    Set combinedDict = CreateObject("Scripting.Dictionary")
    
    For Each key In dict.Keys
        combinedDict.Add key, dict(key)
    Next
    
    For Each key In dict2.Keys
        combinedDict.Add key, dict2(key)
    Next
    
    ' 输出合并后的字典
    For Each key In combinedDict.Keys
        Debug.Print "Combined Key: " & key & ", Value: " & combinedDict(key)
    Next
End Sub

' 快速排序算法用于排序数组
Sub QuickSort(arr() As Variant, first As Long, last As Long)
    Dim pivot As Variant
    Dim temp As Variant
    Dim i As Long
    Dim j As Long
    
    If first < last Then
        pivot = arr((first + last) \ 2)
        i = first - 1
        j = last + 1
        
        Do
            Do
                i = i + 1
            Loop While arr(i) < pivot
            
            Do
                j = j - 1
            Loop While arr(j) > pivot
            
            If i <= j Then
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Loop While i <= j
        
        QuickSort arr, first, j
        QuickSort arr, j + 1, last
    End If
End Sub