是否可以使用自动过滤器或查找字典？ [英] is it ppossible to use autofilter or find on a dictionary?

问题描述

需要搜索的数据位于另一个工作簿（1200+行）中。为了避免不断打开和关闭数据工作簿，我在表单初始化期间将其全部加载到字典中。

现在我的问题是：是否可以快速过滤掉字典数据（和更新组合框），用户正在键入，还是需要更改我的方法？

任何帮助将不胜感激。

这里是我到目前为止的代码：

  Option Explicit 
 Private emplDict As Object 
'所有其他常量和函数在一个名为代码的单独模块中声明
 Private Sub btnClose_Click（）
卸载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.Runcode.xlHelper，False'关闭屏幕更新，警报，事件
 
设置emplDict = CreateObject（scripting.dictionary）
设置xlWB = Workbooks.Open（文件名：= SUB_PLANNING& EMPLOYEE_LIST）
设置xlWS = xlWB.Sheets（namen_werknemers）
 
与xlWS 
 lstRw = .Cells（Rows.Count，1）.End（xlUp）.Row 
设置rng = .Range（.Cells（2，1），.Cells（lstRw，1））
结束
 
对于每个项目在rng 
如果不是emplDict.exists（item.Value）然后
 emplDict.Add item.Text，item.Offset（0，1）.Text 
如果
下一个
 
 xlWB 。关闭False 
 
设置xlWS =没有
设置xlWB =没有
 
 Application.Runcode.xlHelper，True 
 End Sub 
 Private Sub UserForm_QueryClose（Cancel As Integer，CloseMode As Integer）
设置emplDict = Nothing 
 End Sub 
  

解决方案

Key是使用Dictionary的键。

使用VBA Filter方法返回一个已过滤的密钥。

 私人EEDict作为对象
 
私有子cboEEList_Change（）
 Dim keys 
 Keys = EEDict.Keys 
 cboEEList.List = Filter（Keys，cboEEList.Text，True， vbTextCompare）
 cboEEList.DropDown 
 
 End Sub 
 
私有子UserForm_Initialize（）
 Dim arData 
 Dim x As Long 
 
设置EEDict = CreateObject（scripting.dictionary）
 
 arData = Worksheets（Employees）。Range（A1）。CurrentRegion.Value2 
 
对于x = 2到UBound（arData）
 EEDict（arData（x，1））= arData（x，2）
下一个
 
 cboEEList.List = EEDict.Keys 
 End Sub 
  

---

我得到了样本数据来自： Fusion Tables - Employees.csv

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

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?

any help would be greatly appreciated.

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

解决方案

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

---

I got the sample data from: Fusion Tables - Employees.csv

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