VBA中集合collection方法的基础知识

擎、天

向楼主学习了

OKJSJSF

《实战技巧精粹》正好提到这个集合的用法。列表框去除空值与重复值。

kinkon123

请教群子老师，定义集合元素后（元素有许多重复的，不能定义成key），有没有类似字典的 dic.Exists 功能 来快捷定位集合元素的位置？

OKJSJSF

这几天专门练习了集合在数据有效性中的用法，用字典的方法还没开始。工作簿一个，包括参数表、操作一表、操作二表。参照教材《EXCEL VBA实战技巧精粹（修订版）》268页去除列表框数据源的重复值或空格，《EXCEL VBA程序开发自学宝典第3版》328页利用字典创建三级选单。发现二个问题，特求助：一是如果右键单击有效性单元格，选择菜单中“从下拉列表中选择（K）”，或快捷键ALT +下箭头，有效性限制将失效，不会出现出错限制，可以选择有效性单元格上方的其它单元格的值。二是我的操作表二的第三列下方单格有效性消失，不知何故？
文件百度链接
链接：https://pan.baidu.com/s/1APU6rxZUyTeHWh7lIih-kA 密码：fy6v

OKJSJSF

操作表2代码：
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 3 Or Target.Row = 1 Then Exit Sub
    If Target.Column = 1 Then Target.Offset(0, 1).Resize(1, 2).ClearContents
    If Target.Column = 2 Then Target.Offset(0, 1).ClearContents
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column > 3 Or Target.Row = 1 Then Exit Sub
    On Error Resume Next
    Dim col As New Collection, ran As Range, c As Byte, val As String
    If Target.Column = 1 Then
        For Each ran In Sheets(1).Range("e2:e9")
            col.Add ran, key:=CStr(ran)
        Next
        For c = 1 To col.Count
           val = val & col(c) & ","
        Next
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=val
        End With
    ElseIf Target.Column = 2 Then
        For Each ran In Sheets(1).Range("f2:f9")
            If ran.Offset(0, -1) = Target.Offset(0, -1) Then
                col.Add ran ', key:=CStr(ran)
            End If
        Next
        For c = 1 To col.Count
           val = val & col(c) & ","
        Next
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=val
        End With
    ElseIf Target.Column = 3 Then
        For Each ran In Sheets(1).Range("g2:k9")
            If ran.Offset(0, -1).Row = Target.Offset(0, -1).Row And ran.Offset(0, -2).Row = Target.Offset(0, -2).Row Then
                col.Add ran ', key:=CStr(ran)
            End If
        Next
        For c = 1 To col.Count
           val = val & col(c) & ","
        Next
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=val
        End With
    End If
End Sub
操作表代码：
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 3 Or Target.Row = 1 Then Exit Sub
    If Target.Column = 1 Then Target.Offset(0, 1).Resize(1, 2).ClearContents
    If Target.Column = 2 Then Target.Offset(0, 1).ClearContents
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column > 3 Or Target.Row = 1 Then Exit Sub
    On Error Resume Next
    Dim col As New Collection, ran As Range, c As Byte, val As String
    If Target.Column = 1 Then
        For Each ran In Sheets(1).Range("a2:a25")
            col.Add ran, key:=CStr(ran)
        Next
        For c = 1 To col.Count
           val = val & col(c) & ","
        Next
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=val
        End With
    ElseIf Target.Column = 2 Then
        For Each ran In Sheets(1).Range("b2:b25")
            If ran.Offset(0, -1) = Target.Offset(0, -1) Then
                col.Add ran, key:=CStr(ran)
            End If
        Next
        For c = 1 To col.Count
           val = val & col(c) & ","
        Next
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=val
        End With
    ElseIf Target.Column = 3 Then
        For Each ran In Sheets(1).Range("c2:c25")
            If ran.Offset(0, -1) = Target.Offset(0, -1) And ran.Offset(0, -2) = Target.Offset(0, -2) Then
'            If ran.Offset(0, -2) & ran.Offset(0, -1) = Target.Offset(0, -2) & Target.Offset(0, -1) Then
                col.Add ran ', key:=CStr(ran)
            End If
        Next
        For c = 1 To col.Count
           val = val & col(c) & ","
        Next
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=val
        End With
    End If
End Sub

OKJSJSF

下面为 操作表1代码：，漏了1字

OKJSJSF

操作表1与2的参数排列不同，但截图无法上传

OKJSJSF

[url=链接：https://pan.baidu.com/s/1APU6rxZUyTeHWh7lIih-kA 密码：fy6v]利用集合设置数据有效性[/url]

OKJSJSF

利用集合创建三级选单(数据有效性序列来源)

OKJSJSF

原来操作表2的第三列有效性设置代码是错的.