暂停VBA并重新运行它会导致更快的执行速度

Question

我们正在使用一个宏进行一些分析，希望能够查看该过程，由于某种原因，我们不得不在宏中使用Activate和Select，我和我的同事都知道使用这种方法的缺点。与此同时，经过测试，显式编码、选择和激活并不是导致问题的主要原因。

在我下面发布的（伪）代码的一个子模块中，我们基本上从一个工作表获取数据并将其复制到另一个工作表中。

问题：

问题在于，该过程非常缓慢，但当我暂停宏（Esc），调试，逐步执行（F8）for-loop中的一两个步骤，然后再次运行（F5），它运行得更快。

这不会发生在我循环的特定步骤或特定工作表周围，因此与我的数据以及其结构无关。

问题：

有什么可能原因？暂停/步进运行是否会导致类似清除内存或任何其他可能使其运行更快的情况？如何修复这个问题（使它运行得像没有需要暂停等）？

重要说明：

如上所述，使用Select和Activate并不是减速过程的主要原因。很抱歉再次说一遍，但我知道如何使用显式选项、设置范围、设置值而不是复制等。我已经更改了我的代码以避免选择，看看是否能解决问题，但没有成功。直到暂停、逐步执行和再次运行，它才开始运行缓慢。如果您能仔细研究一下问题并描述导致问题的原因，我将不胜感激。或者至少，明确地让我知道为什么这个问题与Select/Activate有关。

这是一个更大的主模块的一部分，可以作为整个程序运行，但这是导致减速的部分。我已经在主模块中使用了一些优化技术。

Sub Copy_ModelInputs(RootDir, FileName, TranID, ModOutDir, Angle, x, y, Method, TypeN)
'For each 150 storms, step through model event tabs and copy into runup tabs
FileName = RootDir & "NWM\" & FileName
FileName_output = ModOutDir & TranID & "_Outputs.xlsm"
Workbooks.Open (FileName)
FileName = ActiveWorkbook.Name
Workbooks.Open (FileName_output)
Filename2 = ActiveWorkbook.Name

'copy the angle into the doc sheet
Windows(FileName).Activate
Sheets("doc").Select
Range("c12").Select
ActiveCell.value = Angle

'File Transect ID
Range("c6").Select
ActiveCell.value = TranID
ActiveCell.Offset(1, 0).Select
ActiveCell.value = FileName_output
Range("I4").Select
ActiveCell.value = Now
Range("d8").Select
ActiveCell.value = x
ActiveCell.Offset(0, 2).Select
ActiveCell.value = y


'copy model output to input into excel spreadsheets

For i = 1 To 150
    'input SWELs
    Windows(Filename2).Activate
    Sheets("Event" & i).Select
    Range("B2:B300").Select
    'Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
                    

    Windows(FileName).Activate
    Sheets("Event" & i).Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
                    
    'input H
    Windows(Filename2).Activate
    Range("C2:C300").Select
    'Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
                    
    'Open runup template spreadsheet, copy H0
    Windows(FileName).Activate
    Range("D7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

                
        'input T
        Windows(Filename2).Activate
        Range("D2:D300").Select
        'Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
                    
    'Open template
    Windows(FileName).Activate
    Range("G7").Select
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    If TypeN = 1 Or TypeN = 3 Then
    
        'input deep
        Windows(Filename2).Activate
        Range("E2:E300").Select
        'Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
                        
        'Open template
        Windows(FileName).Activate
        Range("H7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
    
    'input local
    Windows(Filename2).Activate
    'If Method = 2 Then
    If TypeN = 2 Then
        Range("G2:G300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("I7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        'input model
        Windows(Filename2).Activate
        Range("F2:F300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("H7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        'input length
        Windows(Filename2).Activate
        Range("J2:J300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("J7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        'input data
        Windows(Filename2).Activate
        Range("I2:I300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("K7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
    End If

    
    'input sheet
    Windows(Filename2).Activate
    If TypeN = 3 Then
        Range("H2:H300").Select
        Selection.Copy
            'Open template
            Windows(FileName).Activate
            Range("S7").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
    End If

    Windows(Filename2).Activate

    Application.StatusBar = "Model Output copied Event " & i
Next i

ActiveWorkbook.Save
ActiveWindow.Close
ActiveWorkbook.Save
ActiveWindow.Close

Sheets("Summary").Select
End Sub

附言：我想知道除了其他应用程序属性之外，Application.Cursor = xlWait是否会显著提高性能。

请注意：请不要提出Select、Activate和CopyPaste参数。已经在上面的行和注释中多次涉及 :)

- M--

---

你尝试过关闭屏幕更新，使用cursor = xlWait等方法吗？ - dwirony

@dwirony 基本上，我尝试了典型的优化技术。 - M--

我建议对代码进行分析以找出哪些部分最慢，以及在代码执行暂停后它们的时间如何变化。您还可以尝试检查调试器停止后设置的应用程序属性，因为我认为暂停可能会更改其中一些属性。 - Slai

@Masoud - 真的，避免选择、粘贴等操作会产生更快的结果，我不明白有什么反对的理由。无论如何，我建议在代码的各个地方放置 Debug.Print Now()，这样你就可以看到哪些部分占用了时间。 - Absinthe

你尝试过从立即窗口开始运行宏吗？（我只是想知道当VBE处于活动状态，例如当你按F5或从立即窗口运行它时，工作表/工作簿的选择和激活是否会被处理得不同于Excel本身处于活动状态时。） - YowE3K

Answer 1

我曾遇到与 Microsoft 365 相似的问题（64位 Windows 10 Pro，i5 9代处理器，16GB RAM。Excel 文件大小为4MB，大量使用VBA。64位 Excel）。

代码子程序长度约为770行，它调用了多个函数并分支到多个子程序。

问题在于，当在同一台机器上使用 Office 2010（32位 Excel），甚至是在我的旧机器上（规格明显较低）时，软件都可以正常工作。

在 MS365 机器上，在运行时，代码会在随机位置停顿30到300秒或更长时间，但在调试模式下永远不会出现这种情况。

我尝试了所有建议的提示，如在代码的各个位置插入 DoEvents 并添加一两秒钟的等待时间。甚至卸载了 MS365 并重新安装了32位。

长话短说，我将代码分成较小的部分，并从原始代码块中调用它们，就像以下示例一样...

Sub xyz()
.....
.....
Call SetDoCalcPages
Call SetDoCalcPages1(r, ACellAddr, errStr, NowStr, errAddr)
Call SetDoCalcPages2(temWatch, fYoung, temYoung, doneonce, fK4, ACellAddr, goQT)
.....
.....
End Sub

大约70行代码被转移到上述三个子程序中。

我不知道为什么，但这解决了问题。在运行时，计算速度非常快。

有一件事是确定的。该代码会激活工作表和单元格，并设置颜色和字体 - 这些都受到许多论坛建议的反对。我可以确认这些与症状无关 - 症状是宏代码在运行时随机长时间停顿。

- jdsouza

Answer 2

经过一段时间的研究，我想就这个问题进行报告；正如@Slai所建议的那样，我尝试通过打印每个进程之间的时间来找到代码的瓶颈。结果发现，在for循环的每个步骤之间存在延迟，然后在Debug/Continue之后消失。

此外，在Debug/Continue之前和之后，Application属性没有发生变化。 @YowE3K提出的从Immediate Window运行宏实际上解决了这个问题。不知何故，似乎激活VBE是解决方案。

我还尝试将主工作簿保存为* .xlsb，这解决了问题。但是，它会导致文件在开始时加载缓慢，但总体开销时间成本并不重要。

我知道立即窗口的作用域不同。如果没有运行任何内容，它会假定全局 (Public) 作用域。否则，它将在应用程序作用域中。我希望有人能详细解释一下激活 VBE 与从命令按钮运行宏有何不同。

供参考，我也想在答案中指出不禁用Application.ScreenUpdating可能会显著影响执行时间。顺便说一句，应尽可能避免使用select，activate和类似的方法（从编程角度来看，它们总是可避免的）。

- M--

---

有趣。对我来说更有意义的是，差异不取决于VBA编辑器，而是取决于Excel应用程序/工作簿/工作表是否被激活或可见。例如Application.WindowState = xlMinimized或更冒险的Application.Visible = False。xlsb通常使加载时间变得更快，所以这似乎有点令人困惑。 - Slai

@Slai 确实很奇怪，而且只在我的机器上发生（xlsb格式）。有时候打开工作簿时也会出现“找不到项目或库”错误。关于WindowState选项，我认为你是对的。我也会尝试这个选项，看看是否有效，但值得一提的是，我可以在第二个屏幕上看到工作簿，所以Visible属性可能更合适。我需要继续深入研究这个问题。谢谢。 - M--

我的猜测是，在VBE环境中运行时，所有的Activate和Select语句都不会像在Excel环境中那样起作用——也就是说，它们只是改变了活动工作表/单元格的指针，但它们并没有将响应任何用户发起的事件的控制权交给该工作表/单元格。因此，我怀疑简短的答案是（你可能不喜欢）……避免使用Select和Activate。 - YowE3K

@YowE3K 哈哈，我知道我不应该这样做。但是当你的高级工程师坚持保留宏，因为它已经运行了一段时间，那么你就必须想办法解决。感谢您的帮助和评论。 - M--