Excel工作簿速度慢，崩溃频繁

Question

我们有一个工作簿，可以与5个用户共享并持续使用。我们有大约1800条记录和26列数据。数据是VLOOKUPS标准值的混合。我有3个正在运行的宏和2个条件格式化规则，以及5张带有dumpdata的表(其中一张包含17K条记录和40列数据)。

我们已经尝试删除所有不需要的额外功能，并用标准文本替换它们，但我们没有注意到有什么不同。

工作簿不断崩溃，即使我们不触摸它，只是在里面点击，它也会冻结，可能需要10分钟才能再次使用。我还注意到它占用了相当多的资源(CPU有时会飙升到100%)，当你保存它时，它会使用100%的CPU。

有人知道如何加快工作簿的速度或至少防止文件崩溃吗？：)

---

在Sheet1(语音)中调用

Private Sub Worksheet_Change(ByVal Target As Range)
'43 = ok '41 = NOK
'check if change happened in column A
If Target.Column = 1 Then
'check if changed value is X
If Target.Value Like "*x*" Then
'add datestamp if it is
Cells(Target.Row, 43).Value = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
Else
End If
If Target.Value Like "*NOK*" Then
Cells(Target.Row, 41).Value = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
Else
End If
End If
End Sub

---

在本工作簿中调用

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set shtVO = Sheets("Voice")
endRowVO = shtVO.Range("J" & Rows.Count).End(xlUp).Row
For Row = 2 To endRowVO
If IsEmpty(shtVO.Cells(Row, 28).Value) = False Then
If shtVO.Cells(Row, 3).Value <> shtVO.Cells(Row, 28).Value Then
If shtVO.Cells(Row, 1).Value Like "*CheckDoneDate*" Then
Else
shtVO.Cells(Row, 1).Value = shtVO.Cells(Row, 1).Value + "CheckDoneDate"
End If
Else
If shtVO.Cells(Row, 3).Value = shtVO.Cells(Row, 28).Value Then
If shtVO.Cells(Row, 1).Value Like "*CheckDoneDate*" Then
shtVO.Cells(Row, 1) = Replace(shtVO.Cells(Row, 1), "CheckDoneDate", "")
End If
End If
End If
Else
If shtVO.Cells(Row, 1).Value Like "*CheckDoneDate*" Then
shtVO.Cells(Row, 1) = Replace(shtVO.Cells(Row, 1), "CheckDoneDate", "")
End If
End If
Next Row
End Sub

---

作为模块1 调用

Sub DateNow()
ActiveCell.Value = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")
End Sub
Sub UpdateLinks()
'
' Update Links
'
'ActiveWorkbook.UpdateLink Name:="F:VOICE.xlsm", Type:=xlExcelLinks
'ActiveWorkbook.CalculateFull
End Sub

---

当前状态：阅读我的答案。

Answer 1

Excel公式在其参数更改时(或者在任何单元格更改时，如果函数不稳定)重新计算。

在您的案例中，一个示例计算工作流是：

• 您在第1列的语音表上更改一个单元格
• 将重新计算引用数据表的所有VLOOKUP
• 为改变的小区激发CCD_ 2。从处理程序中，您可以修改同一表的第41或43列
• 将重新计算引用数据表的所有VLOOKUP
• 为第41/43列中的改变的单元激发CCD_ 4。什么都不做

保存时，每一行都会发生同样的情况。

您可以：

• 如果可能的话，让VLOOKUP看起来不要像列41那么远。这将消除第二次撤回
• 切换到手动重新计算

Answer 2

的几种可能性

您的Worksheet_Change更改事件将导致其自身的另一个触发器。这还不错，因为它测试的细胞是第1列，第41/43列发生了变化，但无论如何都值得修复。可能没有多大区别。

此外，如果工作表"语音"单元格可能在该工作表未激活时被代码更改，则会得到意外结果。将工作表限定符添加到要修复的范围调用中。

Private Sub Worksheet_Change(ByVal Target As Range)
'43 = ok '41 = NOK
'check if change happened in column A
If Target.Column = 1 Then
'check if changed value is X
Application.EnableEvents = False  ' <-- Add This
If Target.Value Like "*x*" Then
'add datestamp if it is
Me.Cells(Target.Row, 43).Value = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss") ' <---
' Else   <--- don't need this
'End If
' --> change to ElseIf (assuming conditions are mutually exclusive)
ElseIf Target.Value Like "*NOK*" Then
Me.Cells(Target.Row, 41).Value = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss") ' <---
'Else   <--- don't need this
End If
End If
Application.EnableEvents = True  ' <-- Add This
End Sub

类似于_BeforeSave-禁用事件以避免_Change触发

Answer 3

好的，我所做的是如下，我实现了Everyone解决方案，从创建一个新文件来清理它到编辑宏，这样它们就不会处理不必要的项目(尽管我有点不再使用它，但我仍然为将来复制它！)。

我把我的工作簿分成两本。一张一张的工作簿(我们一直使用的工作簿)。它包含VLOOKUPS、其他函数、按钮宏和2个条件格式。我删除了所有的转储表，并更改了宏的工作方式(按钮而不是onchange或beforesave)。

第二个文件变成了我的疑难解答文件。这包含我的所有工作表，但没有宏，没有条件格式。第一个工作簿中的一张工作表仅使用对单元格的引用显示。此文件仅用于疑难解答。

• 它似乎更有效率
• 加载时间更短
• 崩溃次数较少(仍然如此，但只是偶尔发生，而不是一直发生：P)

感谢大家的时间和努力，我希望这也能在未来帮助人们。：)