VBA/Excel 在没有代码更改的情况下突然运行缓慢

VBA/Excel suddenly running slow with no code changes

提问by LazyBear

I came to work this morning and began work on the same file I left from yesterday. Now Excel slows way down whenever a macro is assigning values to cells or clearing them. It was lightning quick before I left yesterday, now it takes 2-3 seconds at a time to assign a value to a cell.

我今天早上上班，开始处理我昨天离开的同一个文件。现在，只要宏为单元格分配值或清除它们，Excel 就会减慢速度。在我昨天离开之前闪电般快速，现在每次需要 2-3 秒为一个单元格赋值。

Right now, other files with similar macros run fine. This happened to another file a while back, so I reverted to a previous version of the file and it worked fine, while the slow file continued to work slow. I guess I can do that now, but in this case that would take a whole lot of work to bring that file to where I have this version. I was wondering if anyone knows what might be going on. Is this one of the things Excel files just do from time to time (like a kind of file corruption), or is there a fix?

现在，具有类似宏的其他文件运行良好。不久前，另一个文件发生了这种情况，因此我恢复到该文件的先前版本并且它运行良好，而慢速文件继续运行缓慢。我想我现在可以做到这一点，但在这种情况下，需要做大量的工作才能将该文件带到我拥有此版本的地方。我想知道是否有人知道可能会发生什么。这是 Excel 文件不时做的事情之一（例如一种文件损坏），还是有修复方法？

I have provided the macro below, although it happens throughout the file whenever values are assigned to cells. I've marked the trouble areas by denoting <---SLOW HERE.

我提供了下面的宏，尽管每当将值分配给单元格时它都会发生在整个文件中。我用 标记了问题区域<---SLOW HERE。

I know this all sounds very vague, and I know I am giving very little to go on. Perhaps insufficient info, but this is all I have. There is no reason (that I can see) that this should be happening. I've even restarted the computer...just in case the problem was external to Excel. No change.

我知道这一切听起来很模糊，而且我知道我没有什么可以继续下去。也许信息不足，但这就是我所拥有的。没有理由（我可以看到）这应该发生。我什至重新启动了计算机……以防万一问题出在 Excel 之外。没变。

If you need more info I will do my best to expound if I can. Thanks for your help and understanding.

如果您需要更多信息，我会尽力说明。感谢您的帮助和理解。

Example Macro:

示例宏：

Sub DeleteButton1_Click()
   Call UnlockSettingsWorksheet

   Sheet24.Range("C18:E18").Value = ""  <---SLOW HERE 
   Dim i As Long
   For i = 18 To 21
     Sheet24.Range("C" & i & ":E" & i).Value = Sheet24.Range("C" & i + 1 & ":E" & i + 1).Value  <---SLOW HERE  
   Next
   Sheet24.Range("C22:E22").Value = ""  <---SLOW HERE 

   Call LockSettingsWorksheet
End Sub

回答by paul bica

As mentioned in the comments, there might be a lot of factors contributing to this change:

正如评论中提到的，可能有很多因素导致了这种变化：

• any actions triggered in these events:

  • Worksheet_Calculate()
  • Worksheet_Change()
  • Worksheet_FollowHyperlink()
  • Worksheet_SelectionChange()
  • Workbook_SheetCalculate()
  • Workbook_SheetChange()
  • Workbook_SheetFollowHyperlink()

• external links, and the external file(s) moved or deleted

• database connections (check Data Tab -> Connections) (doubtful)
• Invalid Named Ranges (Formula Tab -> Name Manager; any Refs?)
• Data validation rules (Data Tab -> Data Validation -> clear all)
• Are you opening the file from a network location - this will make it much slower
• Conditional Formatting rules? - remove all
• Any hidden objects? (Alt+ F10- delete all)
• Hidden formatting (what is the last used cell with data?); this may not be relevant for your case
• Corrupt file

• 在这些事件中触发的任何操作：

  • Worksheet_Calculate()
  • Worksheet_Change()
  • Worksheet_FollowHyperlink()
  • Worksheet_SelectionChange()
  • Workbook_SheetCalculate()
  • Workbook_SheetChange()
  • Workbook_SheetFollowHyperlink()

• 外部链接，以及移动或删除的外部文件

• 数据库连接（检查数据选项卡-> 连接）（可疑）
• 无效的命名范围（公式选项卡 -> 名称管理器；任何参考？）
• 数据验证规则（Data Tab -> Data Validation -> clear all）
• 您是否从网络位置打开文件 - 这会使速度变慢
• 条件格式规则？- 移除所有
• 有什么隐藏物品吗？( Alt+ F10- 全部删除)
• 隐藏格式（最后使用的带有数据的单元格是什么？）；这可能与您的情况无关
• 损坏的文件

If the file is corrupt, and it's feasible, try re-creating it from scratch, and run this function first

如果文件已损坏，并且可行，请尝试从头开始重新创建，并先运行此功能

If it's not corrupt, one of the first things I'd try is to disable all Excel functionality before the macro:

如果它没有损坏，我会尝试的第一件事就是在宏之前禁用所有 Excel 功能：

---

---

Sub DeleteButton1_Click()

    'UnlockSettingsWorksheet

    FastWB          '<--- Disables all Application and Worksheet level settings

    With ThisWorkbook.Worksheets("Sheet24")   'Fully qualified worksheet

        .Range("C18:E18").Value2 = vbNullString

        Dim i As Long
        For i = 18 To 21
            .Range("C" & i & ":E" & i).Value2 = .Range("C" & (i + 1) & ":E" & (i + 1) ).Value2
        Next

        .Range("C22:E22").Value2 = vbNullString

    End With

    XlResetSettings '<--- Restores all Excel settings to defaults

    'LockSettingsWorksheet

End Sub

---

---

.

.

---

---

Public Sub FastWB(Optional ByVal opt As Boolean = True)
    With Application
        .Calculation = IIf(opt, xlCalculationManual, xlCalculationAutomatic)
        .DisplayAlerts = Not opt
        .DisplayStatusBar = Not opt
        .EnableAnimations = Not opt
        .EnableEvents = Not opt
        .ScreenUpdating = Not opt
    End With
    FastWS , opt
End Sub

Public Sub FastWS(Optional ByVal ws As Worksheet, Optional ByVal opt As Boolean = True)
    If ws Is Nothing Then
        For Each ws In Application.ThisWorkbook.Sheets
            OptimiseWS ws, opt
        Next
    Else
        OptimiseWS ws, opt
    End If
End Sub

Public Sub OptimiseWS(ByVal ws As Worksheet, ByVal opt As Boolean)
    With ws
        .DisplayPageBreaks = False
        .EnableCalculation = Not opt
        .EnableFormatConditionsCalculation = Not opt
        .EnablePivotTable = Not opt
    End With
End Sub

---

---

Public Sub XlResetSettings()    'default Excel settings
    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .DisplayStatusBar = True
        .EnableAnimations = False
        .EnableEvents = True
        .ScreenUpdating = True
        Dim ws As Worksheet
        For Each ws In Application.ThisWorkbook.Sheets
            With ws
                .DisplayPageBreaks = False
                .EnableCalculation = True
                .EnableFormatConditionsCalculation = True
                .EnablePivotTable = True
            End With
        Next
    End With
End Sub

---

---

Maybe this will eliminate some VBA causes

也许这会消除一些 VBA 原因

回答by Drew Chapin

I started noticing Range.Value = ...operations being significantly slow once upgraded to a 64 bit edition. Aside form the normal stuff like .ScreenUpdatingand .Calculation, two things I've discovered significantly improve the speed are:

Range.Value = ...升级到 64 位版本后，我开始注意到操作速度明显变慢。除了像.ScreenUpdating和这样的正常东西之外.Calculation，我发现可以显着提高速度的两件事是：

1. Change the cursor to xlWaitprior to your operation, and back to xlDefaultonce done.

   Public Sub MyMacro()
       On Error Goto Exception
       Application.Cursor = xlWait
       ' Do something here.
   Exception:
       Application.Cursor = xlDefault
   End Sub
   

2. Use .Value2instead of .Value

   Sheet1.Range("A1").Value2 = "The quick brown fox jumps over the lazy dog."
   

1. 将光标更改为xlWait操作前，xlDefault完成后返回。

   Public Sub MyMacro()
       On Error Goto Exception
       Application.Cursor = xlWait
       ' Do something here.
   Exception:
       Application.Cursor = xlDefault
   End Sub
   

2. 使用.Value2代替.Value

   Sheet1.Range("A1").Value2 = "The quick brown fox jumps over the lazy dog."