Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, all. I don't imagine my problems will actually be solved here, but I do hope to gather any "best practices" I may not be aware of, in hopes of minimizing them.

    I developed a template, which is stored on a network share/folder, which is set on users' machines as the "Workgroup Templates" location. It uses an Excel spreadshet that is also stored there, and which contains the values for the various picklists. (I decided it would be easier for the system owner to maintain a spreadsheet than to keep modifying code when a new product type, etc. is added.) I will note that the "initialization" of the dialog box on Word (checking for and instantiating Excel and obtaining the values from the specified ranges) goes off without a hitch. It opens the spreadsheet and collects the values it needs for the array with no problem.

    The DOTM template has a very traditional dialog box, parts of which are populated by the spreadsheet mentioned above) that gather information from text boxes and list-box choices. It then plugs these values into the document a specific locations (bookmarks) using a function I wrote that maintains the bookmark after the text is inserted (by "re-bookmarking" the range.) As always happens when developing, it performs fine on my machine. But during testing, the first user reported a number of errors that I simply can't duplicate nor predict.

    For example, she initially reported that choices from the picklists threw "5941" errors. (There is some validation going on when a choice is made, i.e. some choices are mututally exclusive.) I couldn't duplicate any of them, nor can she 24 hours later. But, today presented a different set of oddities: namely, when certain choices are made, some boilerplate (Building Block/autotext) is inserted at a named range, and a second dialog should then appear to prompt for two additional pieces of information. When testing the template this morning on her machine, I noticed that the second dialog box wasn't appearing at all. When I started the process over, I got an error about not being able to modify a range. I chose to debug, and I saw it stopped on this line:

    If blnECTD = False Then Activedocument.Bookmarks("bmkeCTDText").Range.Para graphs(1).Range.Delete
    (The variable blnECTD indicates whether the user chose to keep a certain paragraph via an option button in the dialog box.)

    However, after verifying that the bookmark existed, I simply resumed the paused code and it indeed deleted the paragraph associated with that bookmark as if it never stopped at all. This still happens most of the times when the macro is called! (And to cloud the issue further, it started showing the second dialog box that wasn't being triggered when I ran it a second time!)

    It's almost as if the code is stumbling over itself. Does anyone have any similar problems? I am using the ActiveDocument object, but during testing, it was the only Word file open! Should I use a reference/variable instead of the ActiveDocument object? (I didn't think it necessary, though I usually work that way, since I've had problems in the past where Word "loses track" of which document to work with, especially if the "Show All Windows In Taskbar" option is turned on.) Are there any other "habits" one should get into when working with sketchy environments? (We can sometimes have painful latency issues.)

    It seems the better I get at automating Word, the more unpredictable VBA reveals itself to be!
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I suspect code editing debris is fooling you.
    Export all modules and forms, then remove them. Save the file (use a backup copy!). Close the file, open the file again and import the modules and forms.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ahh... I used to have a utility (VBCodeCleaner or something like that) which would do exactly that.

    Does anyone know if it's supported under Word 2007? (I'll do it manually for now, but it was a nice utility. Reduced file size by a LOT, too!)

    Added: I think i found it here - VBA Code Cleaner (I'm sorry if i'm violating any advertising rules.) Although iti hasn't been upgraded per se, I tested it and it worked just fine under Word 2007 (except that you need to access it from the Add Ins tab and not some cool ribbon button.)
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  5. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by Mesaka View Post
    When I started the process over, I got an error about not being able to modify a range. I chose to debug, and I saw it stopped on this line:

    If blnECTD = False Then Activedocument.Bookmarks("bmkeCTDText").Range.Para graphs(1).Range.Delete
    (The variable blnECTD indicates whether the user chose to keep a certain paragraph via an option button in the dialog box.)

    However, after verifying that the bookmark existed, I simply resumed the paused code and it indeed deleted the paragraph associated with that bookmark as if it never stopped at all. This still happens most of the times when the macro is called!
    We've had a few "works in Debug but not in real life" problems posted here over the years. Usually, operations in a document proceed sequentially, but occasionally and rather unpredictably it seems as though the code needs to pause to let Word "catch up." I don't know of an official way to do that in Word, but I have used the Windows API Sleep function on occasion.

  6. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Spokane, WA USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jscher2000 View Post
    We've had a few "works in Debug but not in real life" problems posted here over the years. Usually, operations in a document proceed sequentially, but occasionally and rather unpredictably it seems as though the code needs to pause to let Word "catch up." I don't know of an official way to do that in Word, but I have used the Windows API Sleep function on occasion.
    Would DoEvents do what you want? That function returns control to the operating system (and presumably Word) to allow it to catch up on events.

  7. #6
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK... after a day of trying various ways of humoring Word so that it would let me delete a range of text, I came up with a kludgy workaround which I'll share here in case anyone else is faced with the same problem.

    But first, to recap: I needed to have my code delete the paragraph in a document that contained a particular bookmark. The code would always work when I stepped through it and when I ran it in real time, but would almost inevitably throw a bogus VB error ("Can't edit range") for our users. Oddly, clicking on Debug and then simply resuming the code served as a sort of "jiggle the handle" solution.

    The only significant difference in environments is that most users have "locked down" machines, while I and another tester (for whom it worked fine) have admin privileges. I can't believe that could be the reason, though! I even added an "Autonew" routine that would temporarily turn off the "Show all windows in taskbar" option, since past code I have written showed that Word would sometimes get confused about just what was the "active document." But that didn't help in this instance.

    So, in the end, I found that the only thing that worked was to use the Selection object (which I've always tried to avoid.) Thus, replacing the line
    docCoverLetter.Bookmarks("bmkeCTDText").Range.Para graphs(1).Range.Delete
    with
    Set rngKillText = docCoverLetter.Bookmarks("bmkeCTDText").Range.Para graphs(1).Range
    rngKillText.Select
    Selection.Delete
    was the only thing that actually worked.

    Go figure...
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

Posting Permissions

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