CODEWITHSUNDEEP
excelvbams-word
SlowLearner
SlowLearner

Reputation: 3294

Why does VBA code run faster when called from a standard module (instead of a userform)?

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:

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

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. Comparing code execution times in NotePad++

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...

Upvotes: 4

Views: 2893

Answers (1)

Terry Field
Terry Field

Reputation: 105

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.

Upvotes: 0

Related Questions