VBA 2007 Performance Issues?

Topic

New Reply

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.Paragraphs(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!

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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
[indent]docCoverLetter.Bookmarks(“bmkeCTDText”).Range.Paragraphs(1).Range.Delete
[/indent]with
[indent] Set rngKillText = docCoverLetter.Bookmarks(“bmkeCTDText”).Range.Paragraphs(1).Range
rngKillText.Select
Selection.Delete
[/indent]was the only thing that actually worked.

Go figure…

Reply | Quote