Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SLOW VBA Execution (2003 SP2)

    I have a Word document (a table) that I've protected (read-only). A custom menu is available for the user to add a row to the table. This menu selection opens a form where the user types in the values to be entered in the table (the change events on the form fields are used for some format checking, etc.). When all fields are entered, the "Add" button becomes enabled. When clicked, the underlying VBA code unprotects the Word document, adds a new row to the table, transfers the form field values to the new row, re-sorts the table, and then reprotects the document.

    The problem is, the VBA code runs excrutiatingly slowly. If a monkey with it a little bit (e.g., unprotect it "manually" via the Word interface, go into the VBA code, and try again), it eventually runs through the routine very quickly, as I would expect it to. For the life of me, though, I can't figure out what I'm doing to "breaks it loose" (and how to keep it that way). If I save the document (after it starts behaving), close, and re-open, it's back to its sluggggggish behavior. Any ideas?

    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SLOW VBA Execution (2003 SP2)

    Have you tried turning off screen updating? At the beginning, insert a line

    Application.ScreenUpdating = False

    and at the end, turn it on again with

    Application.ScreenUpdating = True

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SLOW VBA Execution (2003 SP2)

    Hans,

    Thanks for the tip. I added the lines and voila! It ran quickly as it should. BUT -- I saved, closed, and re-opened, ran it again and it was back to it's sluggish self. It seems that if I go into VBA editor, change something (like this) or something else that's innocuous it starts running (and continues to run) fast, but reverts to its bad habits the next time I open the document. Per chance, does this provide any hint as to what might be going on?

    Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SLOW VBA Execution (2003 SP2)

    I'm afraid not. Would it be possible to attach a copy of the document, with sensitive information removed or altered, and zipped if necessary?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SLOW VBA Execution (2003 SP2)

    Hans,

    Here's the scrubbed file (passwords removed; names changed). I tried it here at home and it seems to work okay (reasonably quickly) when first opened. Now I'm wondering if it has anything to do with the file (at work) being located on a network share area... Nonetheless, if you see anything in the file that might cause this behavior, that would be a great help.

    Thanks.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SLOW VBA Execution (2003 SP2)

    I cannot detect a significant difference in execution speed between running it after modifying the code and running it after closing and reopening the document.
    On my PC, the code takes about 4.4 seconds, of which rearranging the assessment numbers and restoring them take up about 2 seconds each, the rest only of the code 0.4 seconds.
    Perhaps you can do something with the suggestions in Maximising the performance of Word tables.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SLOW VBA Execution (2003 SP2)

    Could your virus protection possibly be interfering with the execution somehow?
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •