none
VBA slow RRS feed

  • Question

  • We have many macros in a project originally written for Word 2003. They are all optimized for speed as far as we are able. We are now trying to use the same macros in Word 2010. Typically they take about 5 times longer to execute in Word 2010. We think that most of the slowing down is happening during searches.

    A typical macro is as follows (designed to correct errors where a space should not, for example, be superscipt)

    Find each space and decide, by looking at the characters before and after the space, if the space should be superscipt/subscript or not.

    Executes very fast in Word 2003, very much slower in Word 2010.

    My question is "Why is VBA slower to execute in Word 2010 than it is in Word 2003".

    Wednesday, March 7, 2012 2:25 PM

All replies

  • Hi keytecstaff2012,
    Thank you for posting. 
    I will help you involve others to help you. There might be some delay about the response. Appreciate your patience.
    Best Regards,

    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us

    Friday, March 9, 2012 7:47 AM
  • Greetings,

    It might be helpful for you to do a bit of testing for some of your code.  Please try this.

    • In Office 2003, place the following code at the beginning of a procedure and at the end:  Debug.print Date
    • Open the immediate window (View menu, Immediate Window)
    • Run the code
    • Observe the start and end times in the Immediate Window.

    Your code might look something like this:

    Sub TestMyCode()
        Debug.Print Date

        'Your code goes here
       
        Debug.Print Date
    End Sub

    Repeat the procedure in Office 2010.  Observe the numbers.  Now we have a way to measure progress.

    Next, you can try turning off screen updating.  When your code interacts with the Word user interface it has to redraw the screen.  If you are making a lot of trips between VBA and the Word UI this can be very noticeable.  Try the following and see if it improves your time.

    Sub TestMyCode()
        Debug.Print Date

        Application.ScreenUpdating=False

        'Your code goes here
       
       'Don't forget to turn screen updating back on

        Application.ScreenUpdating=False

        Debug.Print Date
    End Sub

    This might help but it may not be enough.  There are a lot of ways to improve code efficiency.  We may need to see some of the code to help further.

    Please let me know if this helps.

    If you found this helpful please consider voting as helpful or marking the post as an answer.  Thanks.

    Mike Corkery, MCT, MCPD, MCITP, MSF, MCSD, MCAD, etc.

    Friday, March 9, 2012 1:06 PM
  • We have already tried the various ways of speeding up the code with some limited success.

    My question was

    "Why is VBA slower to execute in Word 2010 than it is in Word 2003"

    Surely identical code should execute at the same speed in both applications?

    If not then why not? Is Word 2010 doing lots of stuff in the background that wasn't being done in Word 2003 perhaps?

    We have done tests and, on average, Word 2010 code takes about 5 times longer than the same code in Word 2003. (Also we are using Word 2010 on a faster computer than Word 2003, so the speed problem is actually worse than 5 times)

    I have seen several posts about this problem when Googling and no satisfactory answer has yet been provided as to why the newer application is so much slower than the older one.

    Tuesday, March 20, 2012 12:30 PM
  • "Surely identical code should execute at the same speed in both applications? "

    Ah,you would think so but they surely do not.  I can not answer why, other than that are more objects and methods in the 2010 object model, but I doubt that alone makes the difference.  Sloppy programming?

    One thing you may want to try, if you have not done so yet.  Make a NEW file and copy all your procedures into it, and then save it.  VBA can get, like most of MS, bloated under the table.  Most of the time it is not a serious issue...except when:

    1.  you have done a LOT of rewrites (adding new procedures, editing existing procedures)

    2.  you have a LOT of procedures.

    I suggest this because of your statement "many macros in a project originally written for Word 2003".  Question:  is a .DOT file?  is it loaded as a global?

    Monday, April 9, 2012 7:12 AM
  • Thanks for the reply. We have tried the various things you suggest with no increase in speed.

    Since my first posting we have done another experiment using the commercial software "MathType" (also written in VB) which, among other things, can be used to convert equations from one format to another e.g. from Equation editor format to Latex.

    We have a document with 480 equations in it. Converting these equations to another format took 2 mins 55 seconds using MathType with Word 2010 and took just 20 seconds using MathType with Word2003. This proves that the problem is not just to do with our project but is also experienced by others.

    Actually the speed difference is probably worse than we thnk becuase the computer running Word 2010 has a faster processor than the one running Word 2003. Both are using Windows XP.

    Wednesday, April 18, 2012 9:40 AM
  • More information about slow execution speeds.

    Any subroutine which uses the "selection" is much slower.  We have been able to re-write some routines to avoid making any selections and this has helped but is not the complete solution.

    Monday, May 28, 2012 12:19 PM
  • <<"Why is VBA slower to execute in Word 2010 than it is in Word 2003"

    Surely identical code should execute at the same speed in both applications?

    If not then why not? Is Word 2010 doing lots of stuff in the background that wasn't being done in Word 2003 perhaps? >>

    You aren't the first person to notice and complain about this. As far as I know, no one has been given an answer as to why.


    Cindy Meister, VSTO/Word MVP

    Monday, May 28, 2012 2:01 PM
    Moderator
  • I do have experiencing the same problems\

    VBA is toooooooooooo slow

    The code that is executed in Excel 2003 is tooooo slowed down in Excel 2010

    This problem is quite common in all of my work sheets

    Once the file is converted to .xlsm this has started

    Please believe us and help us

    Thursday, June 20, 2013 12:08 PM