时间戳

首页 > 解决方案 > 了解 vba 脚本字典

excel - 了解 vba 脚本字典

问题描述

我从这里收集了一个 vba 代码，如下所示，来自谷歌搜索的片段，它识别多列之间的共同单元格值并将这些共同值放置到单独的列中。

Sub get_common_value_from_multiple_columns()

Dim a, c As Long, j As Long, e
Set d = CreateObject("Scripting.Dictionary")
Set a = Cells(1, 1).CurrentRegion
c = a.Columns.Count
a.Select

For j = 0 To c - 1:
    For Each e In a.Columns(j + 1).Value
        If d(e) = j _
            Then d(e) = j + 1
    Next e, j

For Each e In d
    If d(e) < j _
        Then d.Remove e
Next e

If d.Count > 0 _
    Then Cells(c + 1).Resize(d.Count) = Application.Transpose(d.keys)
End Sub

但是我无法理解下图中突出显示的某些子过程- 图片

请帮助理解突出显示的代码在做什么？

编辑

在得到一些评论后，我重新编写了子程序，如下所示 -

Option Explicit
Public Sub get_common_value_from_multiple_columns()

    Dim data_dictionary As New Scripting.Dictionary
    'Dim data_dictionary As Object
    'Set data_dictionary = CreateObject("Scripting.Dictionary")
    
    Dim columns_count As Long
    Dim iterator As Integer
    Dim dictionary_item As Variant
    Dim selected_cells As Variant
    
    Set selected_cells = Cells(1, 1).CurrentRegion
    columns_count = selected_cells.Columns.Count
    selected_cells.Select
    
    'Populate the dictionary defined earlier
    For iterator = 0 To columns_count - 1:
        For Each dictionary_item In selected_cells.Columns(iterator + 1).Value
            Debug.Print data_dictionary.Item(dictionary_item) = iterator, dictionary_item
            If data_dictionary.Item(dictionary_item) = iterator Then
                data_dictionary.Item(dictionary_item) = iterator + 1
            End If
        Next dictionary_item
    Next iterator
    
    'Remove the unique values
    For Each dictionary_item In data_dictionary
        If data_dictionary.Item(dictionary_item) < iterator Then
            data_dictionary.Remove dictionary_item
        End If
    Next dictionary_item
    
    'Copy the common value to a new columns
    If data_dictionary.Count > 0 Then
        Cells(columns_count + 2).Resize(data_dictionary.Count) = Application.Transpose(data_dictionary.Keys)
    End If
    
End Sub

但是，尽管如此，我还是不理解以下如果条件如何变为真或假-

    If data_dictionary.Item(dictionary_item) = iterator Then
        data_dictionary.Item(dictionary_item) = iterator + 1

对于 If 为 True 的第一列和 Excel 的第二列它为 False 但如何？

标签： excelvbadictionary

解决方案

我无法理解上述功能的工作方式。几个小时过去了，没有人回答。最后我最终得到了一个不同的解决方案，如下所示 -

Option Explicit
Public Sub get_common_value_from_multiple_columns()

    Dim data_dictionary As New Scripting.Dictionary
    'Dim data_dictionary As Object
    'Set data_dictionary = CreateObject("Scripting.Dictionary")
    
    Dim columns_count As Long
    Dim iterator As Integer
    Dim dictionary_item As Variant
    Dim selected_cells As Variant
    
    Set selected_cells = Cells(1, 1).CurrentRegion
    columns_count = selected_cells.Columns.Count
    selected_cells.Select
    
    'Populate the dictionary defined earlier
    For iterator = 0 To columns_count - 1:
        For Each dictionary_item In selected_cells.Columns(iterator + 1).Value
            If Not data_dictionary.Exists(dictionary_item) Then
                data_dictionary.Item(dictionary_item) = 1
            ElseIf data_dictionary.Exists(dictionary_item) Then
                data_dictionary.Item(dictionary_item) = data_dictionary.Item(dictionary_item) + 1
            End If
        Next dictionary_item
    Next iterator
    
    'Remove the unique values
    For Each dictionary_item In data_dictionary
        If data_dictionary.Item(dictionary_item) < iterator Then
            data_dictionary.Remove dictionary_item
        End If
    Next dictionary_item
    
    'Copy the common value to a new columns
    If data_dictionary.Count > 0 Then
        Cells(columns_count + 2).Resize(data_dictionary.Count) = Application.Transpose(data_dictionary.Keys)
    End If
    
End Sub

推荐阅读