debugging 暂停VBA并重新执行可加快执行速度

iovurdzv  于 2022-11-14  发布在  其他
关注(0)|答案(2)|浏览(144)

我们正在使用宏进行一些分析,希望能够查看过程,出于超出此处范围的原因,我们被迫在宏中使用ActivateSelect。我和同事都意识到使用这种方法的缺点。同时,已经测试表明显式编码、选择和激活不是此问题的主要原因。
在其中一个***子模块***中,我在下面发布了它的(伪)代码,我们基本上从一个工作表中获取数据,并将其复制到另一个工作表中。

问题

问题是这个过程真的很慢,但是当我暂停宏(Esc),点击调试,单步执行(F8)一两步for-loop,然后再次运行(F5)时,它运行得更快。
这不会发生在for循环的特定步骤或特定工作表周围,因此与我的数据及其结构无关。

**问题:**可能的原因是什么?暂停/单步运行是否会导致内存清除之类的问题,或者导致任何其他可能的情况,使其运行得更快?如何解决此问题(使其运行得更快,而无需暂停等)?

重要说明

如上所述,使用SelectActivate并不是导致进程变慢的主要原因。我很抱歉再次声明,但我知道如何使用显式选项、设置范围、设置值而不是复制等。我已经更改了代码,以避免选择,看看是否解决了问题。它仍然运行缓慢,直到暂停。如果您能更仔细地了解这个问题并描述问题背后的原因,我将不胜感激。或者至少,明确地让我知道为什么这个问题与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

**P.S.**我想知道Application.Cursor = xlWait是否会在其他应用程序属性之外显著提高性能。
**注意:**请不要使用SelectActivateCopyPaste参数。在上面的行和注解中已经多次涉及到它:)

h7appiyu

h7appiyu1#

我在微软365(64位Windows 10 Pro,i5第9代处理器,16 GB RAM。Excel文件大小4 MB,大量使用VBA。64位Excel)上也遇到了类似的问题。
代码子长度-大约770行,它调用了几个函数,并分支到几个子。
麻烦的是,当使用Office 2010(在同一台机器上使用32位Excel--就这一点而言,在我以前的规格相当低的机器上)时,该软件工作正常。
在MS 365计算机上。在运行时,代码将在代码中的任意位置暂停30到300秒或更长时间,但如果处于调试模式,则不会出现这种情况。
我尝试了所有建议的技巧,比如在代码中的不同位置插入DoEvents和添加一两秒的等待。甚至卸载了MS 365并重新安装了32位。
长话短说,我将代码分成了更小的部分,并从原始代码块中调用它们,如下面的示例所示...

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

大约70行代码被转移到上面提到的三个子程序中。
我不能说为什么,但这解决了这个问题。在运行时,计算以很快的速度完成。
有一件事是肯定的。代码激活工作表和单元格,设置颜色和字体-所有这些都是不赞成的建议,在许多论坛。我可以确认,这些没有任何关系的症状-这是随机长时间暂停执行宏代码在运行时。

kiayqfof

kiayqfof2#

在花了相当多的时间在这个问题上之后,我想就这个问题进行汇报;
正如@Slai所建议的,我试图通过打印每个进程之间的时间来找到代码的瓶颈,结果发现for循环的每一步之间都有一个滞后,然后在Debug/Continue之后消失。
此外,Application属性在Debug/Continue之前和之后均未更改。
@YowE3K提出的从 * 即时窗口 * 运行宏的建议实际上解决了这个问题。不知何故,似乎激活的VBE是解决方案
我也试着将我的主工作簿保存为 * .xlsb,这解决了这个问题。但是,它导致文件在开始时加载速度较慢,但总的开销时间成本并不可观。
我知道即时窗口在作用域上是不同的。如果没有运行任何东西,它假定是全局(Public)作用域。否则,它将在应用程序作用域中。如果有人能详细解释激活的VBE与从命令按钮运行宏的不同之处,我将不胜感激。
作为参考,我还想在答案中包括不禁用Application.ScreenUpdating会显著影响执行时间。如果可能,应该避免FWIW、selectactivate和类似的做法(从编程Angular 来说,它们总是可以避免的)。

相关问题