为什么VBA代码从标准模块（而不是用户窗体）调用时运行速度更快？ [英] Why does VBA code run faster when called from a standard module (instead of a userform)?

问题描述

替代标题：为什么按 Esc 使我的MS-Word宏运行更快

等待一些代码运行我偶然发现一些有趣的东西。

代码运行缓慢...按 Esc 代码运行速度快。例如：

• 执行后立即按 Esc - 2秒完成
• 
  
• 不要按 Esc ，直到 30秒完成

对我来说这没有意义。事实证明，其他人注意到类似的行为，例如：

• 中的代码执行时间

  
  
  

  由于计时器与调用堆栈的运行方式，我不得不测试代码 getStyleElement ，以确保它对于额外的时间没有显着贡献。我通过直接计时代码来做到这一点，并且能够确认它一直很快运行。

  
  
  

  检查其余代码证实问题是与 applyStyleFormat （调用 getStyleElement ）。

  
  
  

  样式应用于文档 - 代码结构包括With块和For循环;如下所示：

    For i = 1 to Styles.Count 
   With aDocument.Styles（i）
  。不要=东西
  '或.Paragraph。 =某事
  结束
  下一个我
    

  我不清楚为什么代码从userform外部运行得更快，或者按下 Esc 之后，它似乎与修改样式有关。

  解决方案

  只需在@Florent Bs评论中拉线程，您是否尝试在点击事件中运行宏之前查看可以禁用的内容？像

    Application.EnableEvents = False 
   Application.ScreenUpdating = False 
   Application.Calculation = xlCalculationManual 
   
  '代码
   Module1.Macro1 
   Module1.Macro2 
   Module1.Macro3 
   
   Application.EnableEvents = True 
   Application.ScreenUpdating = True 
   Application.Calculation = xlCalculationAutomatic 
    

  只是看看事情是否更快？可能还有其他命令可以取消人们可以添加的其他内容。

  
  

  Alternate Title: Why does pressing Esc make my MS-Word macro run faster

  While waiting for some code to run I stumbled across something interesting.

  Code runs slow... press Esc code runs fast. Example:

  • press Esc immediately after execution - 2 sec to complete
  • don't press Esc at all - up to 30 sec to complete

  To me this makes no sense. It turns out other people have noticed similar behavior, for example:

  • Pausing VBA and re-running it causes faster execution

  • http://www.vbforums.com/showthread.php?490446-ESC-key-while-running-macros

  • https://www.excelforum.com/excel-programming-vba-macros/617300-vba-code-runs-slow-until-i-push-esc.html

  And they have found various solutions or workarounds. However, these posts are for MS-Excel; which it seems has different behavior for the Esc key.

  In MS-Excel pressing the Esc key can (depending on the setting of Application.EnableCancleKey) interrupt code or raise an error (Err 18), or do nothing. On the other hand in MS Word no such thing happens, instead Application.EnableCancleKey alters the behavior of Ctrl + Pause. Yet despite this, pressing the Esc speeds up the code execution dramatically.

  That difference aside, my question relates more to the placement of code in a userform. For example in my Userform:

  Private Sub Cmd_Click()
  
      Module1.Macro1
      Module1.Macro2
      Module1.Macro3
  
  End Sub
  

  Tested on a 64 bit version of Word 2010, using the structure above is significantly slower than this:

  Userform:

  Private Sub Cmd_Click()
  
      Module1.RunMacro123
  
  End Sub
  

  Standard Module:

  Private Sub RunMacro123()
  
      Module1.Macro1
      Module1.Macro2
      Module1.Macro3
  
  End Sub
  

  I should note the following:

  1. This is most apparent in the 64 bit version of MS Word, the 32 bit version seems to run quite fast with the original code (I'm yet to test with the revised code)
  2. Like the author in the first link I am not using the selection object etc.
  3. I'm more interested any insights as to why the speed of code execution is so influenced by:
     • pressing Esc
     • moving the calls from the userform to a standard module
  4. Macro1, Macro2 & Macro3 create and edit document styles and (FWIW) involve reading INI files many times

  On a side note, in a hair-brained attempt at being clever I tried using sendKeys to send the Esc key but it has no affect.

  Edit - Results of timing the code:

  I ended up using the timer function to time the code, I had already implemented a stack class which was adapted from here: http://www.tek-tips.com/viewthread.cfm?qid=1468970

  I added an 'absolute' timer (debug.print timer - startTime) to the call stack such that the time between each push was recorded and the timer was reset (startTime = timer) at each pop. Doing this made comparing times easier in NotePad++

  This allowed me to determine that a sub that applies styles to a document was taking ~0.04 seconds to apply the style (NB value returned by the timer = seconds past midnight).

  The image below shows an example of the code timing results. Basically, as far as I can tell, the delay in code execution comes from many incremental delays all associated with the same basic task.

  Because of the way the timer works with the call stack I had to test the code getStyleElement to make sure it was not contributing significantly to the extra time. I did this by timing the code directly and was able to confirm that it was consistently fast to run.

  Examining the rest of the code confirmed that the issue was with applyStyleFormat (which calls getStyleElement).

  The styles are applied to the document - the code structure includes a With block and For Loop; something like this:

  For i = 1 to Styles.Count
      With aDocument.Styles(i)
          .Font.??? = Something
          ' or .Paragraph.??? = Something
      End With
  Next i
  

  I'm no clearer as to why the code runs faster from outside of the userform, or after pressing Esc but it does and it seems to have something to do with modifying styles...

  解决方案

  Just pulling the thread on @Florent Bs comment, have you tried seeing what you can disable prior to running the macros in the click event? Things like

  Application.EnableEvents = False
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  
  'code
  Module1.Macro1
  Module1.Macro2
  Module1.Macro3
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  

  Just to see if things are any quicker? There might be other commands to cancel other things that people can add.

  这篇关于为什么VBA代码从标准模块（而不是用户窗体）调用时运行速度更快？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！