![运行宏时 Excel 冻结](https://linux22.com/image/1565803/%E8%BF%90%E8%A1%8C%E5%AE%8F%E6%97%B6%20Excel%20%E5%86%BB%E7%BB%93.png)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim i As Integer
Set KeyCells = Range("H4:H100")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
i = Range(Target.Address).Row
If Cells(i, "L") = "" Then
Cells(i, "H") = "In Progress"
End If
End If
End Sub
运行脚本后,我的 Excel 一直冻结。有人能帮我解决这个问题吗?
答案1
当使用事件处理程序并进行可能再次触发事件处理程序的更改时,最好通过声明来禁用事件监视:
应用程序.启用事件 = False
请确保在程序结束之前重新启用事件,否则它们将保持禁用状态,直到您重新启动 Excel。
禁用并重新启用事件的代码:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Dim i As Integer
Set KeyCells = Range("H4:H100")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
i = Range(Target.Address).Row
Application.EnableEvents = False
If Cells(i, "L") = "" Then
Cells(i, "H") = "In Progress"
End If
Application.EnableEvents = True
End If
End Sub
答案2
问题很简单。您检查某个范围是否有变化。如果发生了变化,则执行某些操作,从而更改已检查范围内的单元格。这会导致此范围的更改事件,并且...调用该方法。然后一遍又一遍...
为了避免此问题,您必须创建全局或静态标志并忽略所有二次调用:
Dim IsWorking As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
If IsWorking Then Exit Sub ' Exit if it is a 'slave' call
IsWorking = True ' Set 'main call is processed' flag
' perform actions you need
IsWorking = False ' Drop the flag, main call is finished
Exit Sub
此外:Target
可以是多单元格区域,而不是单个单元格。您至少要检查其中每一行的交集 - 可能需要将多个单元格更改为“进行中”值...
另外-2:Application.DoEvents
在代码中添加语句 - 如果宏“释放”,则允许通过 Ctrl-C 中断代码执行。