是否可以使用自动过滤器或在字典中查找? [英] Is it possible to use auto-filter or find on a dictionary?

问题描述

所以我有一个带有comboBox的用户窗体，它是一个动态搜索框.

so I have a userform with comboBox serving as a dynamic-search box.

需要搜索的数据位于另一本工作簿(1200多行)中.为了避免不断打开和关闭该数据工作簿，我在表单初始化期间将它们全部加载到字典中.

The data needed to be searched is located in another workbook (1200+ rows). To avoid constant opening and closing of that data-workbook, I load it all into dictionary during form initialization.

现在我的问题是:随着用户的输入，是否可以快速过滤出字典数据(并更新组合框)?还是我需要更改方法?

Now my question is: is it possible to quickly filter out dictionary data (and update combobox), as user is typing or do I need to change my approach?

任何帮助将不胜感激.

这是我到目前为止的代码:

here is the code I have so far:

Option Explicit
Private emplDict As Object
'all other constants and functions are declared in a separate module named "code"
Private Sub btnClose_Click()
    Unload Me
End Sub
Private Sub comboSearch_Change()
    Me.comboSearch.DropDown
End Sub
Private Sub UserForm_Initialize()
    Dim xlWS As Worksheet
    Dim xlWB As Workbook
    Dim rng As Range
    Dim lstRw As Long
    Dim item As Variant

    Application.Run "code.xlHelper", False ' turn off screen updating, alerts, events

    Set emplDict = CreateObject("scripting.dictionary")
    Set xlWB = Workbooks.Open(Filename:=SUB_PLANNING & EMPLOYEE_LIST)
    Set xlWS = xlWB.Sheets("namen_werknemers")

    With xlWS
        lstRw = .Cells(Rows.Count, 1).End(xlUp).Row
        Set rng = .Range(.Cells(2, 1), .Cells(lstRw, 1))
    End With

    For Each item In rng
        If Not emplDict.exists(item.Value) Then
            emplDict.Add item.Text, item.Offset(0, 1).Text
        End If
    Next

    xlWB.Close False

    Set xlWS = Nothing
    Set xlWB = Nothing

    Application.Run "code.xlHelper", True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Set emplDict = Nothing
End Sub

推荐答案

关键是使用词典的密钥.

使用VBA筛选器方法返回已筛选键的数组.

The Key is to use the Dictionary's Keys.

Use the VBA Filter method to return an array of filtered Keys.

Private EEDict As Object

Private Sub cboEEList_Change()
    Dim Keys
    Keys = EEDict.Keys
    cboEEList.List = Filter(Keys, cboEEList.Text, True, vbTextCompare)
    cboEEList.DropDown

End Sub

Private Sub UserForm_Initialize()
    Dim arData
    Dim x As Long

    Set EEDict = CreateObject("scripting.dictionary")

    arData = Worksheets("Employees").Range("A1").CurrentRegion.Value2

    For x = 2 To UBound(arData)
        EEDict(arData(x, 1)) = arData(x, 2)
    Next

    cboEEList.List = EEDict.Keys
End Sub

---

我从以下位置获取了示例数据:融合表-Employees.csv

这篇关于是否可以使用自动过滤器或在字典中查找?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！