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

alternate title: why pressing esc make ms-word macro run faster

while waiting code run stumbled across interesting.

code runs slow... press esc code runs fast. example:

• press esc after execution - 2 sec complete
• don't press esc @ - 30 sec complete

to me makes no sense. turns out other people have noticed similar behavior, example:

• pausing vba , re-running 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 have found various solutions or workarounds. however, these posts ms-excel; seems has different behavior esc key.

in ms-excel pressing esc key can (depending on setting of application.enablecanclekey) interrupt code or raise error (err 18), or nothing. on other hand in ms word no such thing happens, instead application.enablecanclekey alters behavior of ctrl + pause. yet despite this, pressing esc speeds code execution dramatically.

that difference aside, question relates more placement of code in userform. example in userform:

private sub cmd_click()      module1.macro1     module1.macro2     module1.macro3  end sub 

tested on 64 bit version of word 2010, using structure above slower 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 following:

1. this apparent in 64 bit version of ms word, 32 bit version seems run quite fast original code (i'm yet test revised code)
2. like author in first link not using selection object etc.
3. i'm more interested insights why speed of code execution influenced by:
   • pressing esc
   • moving calls userform standard module
4. macro1, macro2 & macro3 create , edit document styles , (fwiw) involve reading ini files many times

on side note, in hair-brained attempt @ being clever tried using sendkeys send esc key has no affect.

edit - results of timing code:

i ended using timer function time code, had implemented stack class adapted here: http://www.tek-tips.com/viewthread.cfm?qid=1468970

i added 'absolute' timer (debug.print timer - starttime) call stack such time between each push recorded , timer reset (starttime = timer) @ each pop. doing made comparing times easier in notepad++

this allowed me determine sub applies styles document taking ~0.04 seconds apply style (nb value returned timer = seconds past midnight).

the image below shows example of code timing results. basically, far can tell, delay in code execution comes many incremental delays associated same basic task.

because of way timer works call stack had test code getstyleelement make sure not contributing time. did timing code directly , able confirm consistently fast run.

examining rest of code confirmed issue applystyleformat (which calls getstyleelement).

the styles applied document - code structure includes block , loop; this:

for = 1 styles.count     adocument.styles(i)         .font.??? =         ' or .paragraph.??? =     end next 

i'm no clearer why code runs faster outside of userform, or after pressing esc , seems have modifying styles...

just pulling thread on @florent bs comment, have tried seeing can disable prior running macros in 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 see if things quicker? there might other commands cancel other things people can add.