如何优化下面的VB代码？运行它需要花费大量的时间，而且每次都挂着Excel。

Question

我正在创建一个需求跟踪M矩阵在Excel表格和下面的VB代码是花费更多的时间来执行和excel工作表是挂在5分钟，每次我输入一个单元格。

VBA代码：

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)

    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long

    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If 

End Function

Answer 1

↓将字典↓中的所有键连在一起

联接(Dictionary.Key()，"，")

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) As String

    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long

    On Error Resume Next
    xRows = LookupRange.Rows.count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next

    If xDic.count > 0 Then
        MultipleLookupNoRept = Join(xDic.Keys(), ",")
    End If

End Function

下面是代码的超修改版本。前面的代码应该在2-5秒内处理10K行。

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) As String

    Dim addresses As Variant, values As Variant
    Dim r As Long

    With LookupRange.Parent
        With Intersect(LookupRange.Columns(1), .UsedRange)
            values = .Value
            addresses = .Columns(ColumnNumber).Value
        End With
    End With

    With CreateObject("System.Collections.ArrayList")
        For r = 1 To UBound(values)
            If values(r, 1) = Lookupvalue And r <= UBound(addresses) And addresses(r, 1) <> "" Then
                .Add addresses(r, 1)
            End If
        Next

        MultipleLookupNoRept = Join(.ToArray(), ",")
    End With

End Function