在所附的代码(两个宏)中,如果我从“ EcodeKeep”中调用“ SortBy Ecode”,则代码永无休止。(或者至少在我强制退出Excel时5分钟之内没有结束)。
但是,如果我在运行“ EcodeKeep”之前分别运行“ SortByEcode”,它们每个都将在2秒内运行。
电子表格中的数据行超过19K。另外,这是我在VBA中使用数组的首次尝试。
我究竟做错了什么?
Sub EcodeKeep()
Dim i As Long
Dim LastRow As Long
Call SortByEcode 'Calling this sort macro here causes this code to run forever.
Dim wks As Worksheet
Set wks = rawData5 'Work in sheet("RawEquipHistory")
LastRow = wks.Range("A" & Rows.Count).End(xlUp).Row
StartTime = Timer
Dim Ecode_arr() As Variant
ReDim Ecode_arr(LastRow)
Dim Results_arr() As String
ReDim Results_arr(LastRow)
For i = 0 To LastRow - 1 'Read data into Ecode_arr(i)
Ecode_arr(i) = wks.Range("A" & i + 1)
Next i
wks.Range("AM1") = "ECODE KEEP" 'Add the header to "S1"
For i = 0 To LastRow - 1
If Ecode_arr(i + 1) <> Ecode_arr(i) Then
Results_arr(i) = True
Else
Results_arr(i) = False
End If
wks.Range("AM" & i + 2) = Results_arr(i)
Next i
End Sub
Sub SortByEcode()
' SORT sheet by E-Code (Column A)
Dim LastRow As Long
LastRow = ThisWorkbook.Sheets("RawEquipHistory").Range("A" & Rows.Count).End(xlUp).Row
With ThisWorkbook.Sheets("RawEquipHistory").Sort ' SORT sheet by E-Code(a)
.SortFields.Clear
.SortFields.Add Key:=Range("A1:A" & LastRow), Order:=xlAscending
.SetRange Range("A1:AZ" & LastRow)
.Header = xlYes
.Apply
End With
End Sub
您的循环不是无限的,而是效率低下的。
除非您禁用了自动计算,应用程序/工作表事件和屏幕更新,否则每次写入单元格时,Excel都会尝试跟上所做的更改,直到最终失败,否则将变为“(不响应)”,那时没有什么可做的了,只是等一下……这可能需要一段时间。
您可以处理这些症状并禁用自动计算,应用程序/工作表事件和屏幕更新-您的代码将更快地完成运行。
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
当然,您需要在循环完成后将它们重置为原始值,并且如果过程中出现任何错误,也要小心重置它们,例如,每当切换它们时,都需要一个错误处理子例程。
或者,您可以解决根本原因,并通过将工作表操作减少到最低限度来稍微调整方法:一次读取,一次写入。...然后是否启用了自动计算,是否在每次写入单元格时Excel触发工作表事件并重新绘制屏幕都没有任何区别。
秘诀是变型阵列。您在这里有正确的主意:
Dim Ecode_arr() As Variant ReDim Ecode_arr(LastRow) Dim Results_arr() As String ReDim Results_arr(LastRow)
但是,然后逐个读取值会造成很大的损失:
For i = 0 To LastRow - 1 'Read data into Ecode_arr(i) Ecode_arr(i) = wks.Range("A" & i + 1) Next i
不用理会数组的大小,将它们保留为普通的旧Variant
包装器-使用Application.Transpose
,您可以Variant
从一列源范围获得一维数组:
Dim ecodes As Variant
ecodes = Application.Transpose(wks.Range("A1:A" & LastRow).Value)
现在您可以迭代此数组以填充您的输出数组-但现在还不写入工作表:将值一个接一个地写入工作表消除了对结果/输出数组的需求!
请注意,由于我们在条件的一个分支和另一个分支中分配的Boolean
值,我们可以通过直接分配条件的布尔表达式来简化分配:True
False
ReDim results(LBound(ecodes), UBound(ecodes))
Dim i As Long
For i = LBound(results) To UBound(results) - 1
results(i) = ecodes(i + 1) <> ecodes(i)
Next
既然results
已经填充了数组,我们就可以一次将其全部转储到工作表上了-由于这是我们正在做的唯一工作表写操作,因此Excel想要重新计算,引发事件和重新绘制都没关系:我们完成了!
wks.Range("AM2:AM" & i + 1).Value = results
注意:这都不是经过测试的代码,在我调整偏移量时(可能会从Range.Value
1开始接收偏移的错误)。但是你明白了:)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句