如何使用字典提高性能？

hu山K

我正在使用excel宏来验证表中的数据字段。数据包含一些字段，这些字段可以包含词典中列出的值之一。

当我尝试为700,000条记录运行验证宏时，它实际上卡住了，并且需要很长时间才能完成。任何人都可以帮助改善此代码的性能吗？

以下是我用于其中一个字段的示例代码，用于对照字典中定义的列表检查列中单元格的内容。当运行超过700,000列记录时，这永远不会完成，而50,000列记录大约需要30秒。

Sub Validate_Action_Type()

'Speed Up

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

'Speed Up end


'Define the variables
Dim DicActionType As New Scripting.Dictionary
Dim CountActionTypeErrors As Long
Dim StartTime As Double
Dim SecondsElapsed As Double


'Start the timer, used to calculate elapsed time
StartTime = Timer

'Create a dictionary of allowed marker type values
DicActionType.Add "Insert", 1
DicActionType.Add "Update", 2
DicActionType.Add "Delete", 3

'Check the Marker Type Column using the dictionery created
For Each d2 In Range(Range("C2"), Range("C2").End(xlDown))
   If Not DicActionType.Exists(d2.Text) Then
        d2.Interior.ColorIndex = 3
        CountActionTypeErrors = CountActionTypeErrors + 1
    Else
        d2.Interior.ColorIndex = xlNone
    End If
Next

'Calculate elapsed time
SecondsElapsed = Round(Timer - StartTime, 2)

'Pop-up the outcome message
MsgBox "Time taken in Seconds = " & SecondsElapsed _
        & vbCrLf _
        & "Total Errors = " & CountActionTypeErrors _
        , , "Check Cells Highlighted RED"

'Restore state: undo the speed up settings

Application.ScreenUpdating = ScreenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting

End Sub

约臣

引用单元格总是很慢。当您要给单元格上色时，您需要一个参考，但仅用于着色，而不用于检查。为了进行检查，您可以使用更快的数组。

在下面的代码中，我使用了一个数组来检查动作。在我的机器上，其速度提高了大约5倍，样本单元中的错误率约为17％。

Sub Validate_Action_Type()

'Speed Up

Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

'Speed Up end


'Define the variables
Dim DicActionType As New Scripting.Dictionary
Dim CountActionTypeErrors As Long
Dim StartTime As Double
Dim SecondsElapsed As Double
Dim ActionArr, I As Integer


ActionArr = Range(Range("D2"), Range("C2").End(xlDown)).Value

'Start the timer, used to calculate elapsed time
StartTime = Timer

'Create a dictionary of allowed marker type values
DicActionType.Add "Insert", 1
DicActionType.Add "Update", 2
DicActionType.Add "Delete", 3

'Check the Marker Type Column using the dictionery created
Columns("C").Interior.ColorIndex = xlNone
For I = 1 To UBound(ActionArr)
   If Not DicActionType.Exists(ActionArr(I, 1)) Then
        'ActionArr(I, 2) = 3
        Cells(I + 1, 3).Interior.ColorIndex = 3
        CountActionTypeErrors = CountActionTypeErrors + 1
    Else
        ActionArr(I, 2) = 0
    End If
Next I

'Calculate elapsed time
SecondsElapsed = Round(Timer - StartTime, 2)

'Pop-up the outcome message
MsgBox "Time taken in Seconds = " & SecondsElapsed _
        & vbCrLf _
        & "Total Errors = " & CountActionTypeErrors _
        , , "Check Cells Highlighted RED"

'Restore state: undo the speed up settings

Application.ScreenUpdating = ScreenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。