我们正在使用宏进行一些分析,希望能够查看过程,出于超出此处范围的原因,我们被迫在宏中使用Activate
和Select
。我和同事都意识到使用这种方法的缺点。同时,已经测试表明显式编码、选择和激活不是此问题的主要原因。
在其中一个***子模块***中,我在下面发布了它的(伪)代码,我们基本上从一个工作表中获取数据,并将其复制到另一个工作表中。
问题
问题是这个过程真的很慢,但是当我暂停宏(Esc),点击调试,单步执行(F8)一两步for-loop
,然后再次运行(F5)时,它运行得更快。
这不会发生在for循环的特定步骤或特定工作表周围,因此与我的数据及其结构无关。
**问题:**可能的原因是什么?暂停/单步运行是否会导致内存清除之类的问题,或者导致任何其他可能的情况,使其运行得更快?如何解决此问题(使其运行得更快,而无需暂停等)?
重要说明
如上所述,使用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
**P.S.**我想知道Application.Cursor = xlWait
是否会在其他应用程序属性之外显著提高性能。
**注意:**请不要使用Select
、Activate
和Copy
Paste
参数。在上面的行和注解中已经多次涉及到它:)
2条答案
按热度按时间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位。
长话短说,我将代码分成了更小的部分,并从原始代码块中调用它们,如下面的示例所示...
大约70行代码被转移到上面提到的三个子程序中。
我不能说为什么,但这解决了这个问题。在运行时,计算以很快的速度完成。
有一件事是肯定的。代码激活工作表和单元格,设置颜色和字体-所有这些都是不赞成的建议,在许多论坛。我可以确认,这些没有任何关系的症状-这是随机长时间暂停执行宏代码在运行时。
kiayqfof2#
在花了相当多的时间在这个问题上之后,我想就这个问题进行汇报;
正如@Slai所建议的,我试图通过打印每个进程之间的时间来找到代码的瓶颈,结果发现
for
循环的每一步之间都有一个滞后,然后在Debug/Continue
之后消失。此外,
Application
属性在Debug/Continue
之前和之后均未更改。@YowE3K提出的从 * 即时窗口 * 运行宏的建议实际上解决了这个问题。不知何故,似乎激活的VBE是解决方案。
我也试着将我的主工作簿保存为 * .xlsb,这解决了这个问题。但是,它导致文件在开始时加载速度较慢,但总的开销时间成本并不可观。
我知道即时窗口在作用域上是不同的。如果没有运行任何东西,它假定是全局(
Public
)作用域。否则,它将在应用程序作用域中。如果有人能详细解释激活的VBE与从命令按钮运行宏的不同之处,我将不胜感激。作为参考,我还想在答案中包括不禁用
Application.ScreenUpdating
会显著影响执行时间。如果可能,应该避免FWIW、select
、activate
和类似的做法(从编程Angular 来说,它们总是可以避免的)。