excel - 从数组中的大数据填充列表框

Question

我正在尝试为如何填充列表框的问题找到解决方案。问题是数据太大（100 万行和大约 20 列）。此代码适用于少量数据，但对于大数据，代码搜索过程大约需要 25 秒

Private Sub TextBox14_AfterUpdate()
Dim a, b(), temp, ws As Worksheet, i As Long, n As Long, c As Long

Set ws = ThisWorkbook.Worksheets("Data")
Me.ListBox1.Clear
temp = Me.TextBox14.Value
c = ComboBox1.ListIndex
If (c = -1) + (temp = "") Then MsgBox "Specify Search Column First And Make Sure TextBox Is Not Empty", vbExclamation: Exit Sub

a = ws.Range("A1:S" & ws.Cells(Rows.Count, 1).End(xlUp).Row).Value
ReDim b(1 To 100000)
b(1) = Application.Index(a, 1, 0)

For i = 2 To UBound(a, 1)
    If CStr(a(i, c + 1)) Like "*" & temp & "*" Then
        n = n + 1
        b(n) = Application.Index(a, i, 0)
    End If
Next i

If n > 0 Then
    ReDim Preserve b(1 To n)
    If n = 1 Then
        Me.ListBox1.Column = Application.Index(b, 0, 0)
    Else
        Me.ListBox1.List = Application.Index(b, 0, 0)
    End If
End If

End Sub

我怎样才能让这个大数据以测试方式运行？

标签： excelvba