Optimize Long Word Report with VBA by waiting to update fields until it is done

[rcurtice]

So I have a macro that pulls in over 900 charts and adds a caption to each one. Basicly it reads a text file that has the location of the picture and the caption and puts them in the document. I have already turned off Pagination and that helped to speed things up. But basiclly every time it adds a new page, it updates all the fields in the ENTIRE document. The longer the document gets, the longer each update takes. As far as I can tell, the fields I use are page numbers (in a header), heading levels, figure numbers and table numbers. Is there a way to stop these fields from being updated until the very end?

[Frosty]

You may be hitting the upward limit of Word's capability to display information well. The fields and the pagination may have less to do with your problem than actually having 900 charts (are they pictures, embedded excel spreadsheets, embedded chart objects, etc).

I'm betting that you would see a significant increase in performance if you were simply to have 900 tables with fields labelling each table.

That said... there are a couple of other ways to stop the fields from being updated *as* frequently.

1. Use normal view instead of page layout view.
2. Tools > Options > View (in 2003... in 2007/2010 different location, but same setting)... you could show your field codes (ALT+F9 will accomplish the same thing-- this will stop all updating until you manually turn it back on, since there's nothing to update when you show the actual code).
3. Picture placeholders (whether to show or not may help... I seem to recall that telling word not to display any activeX/embedded items.
4. Tools > Options > Print (in 2003... in 2007/2010 it's in a different location, but the setting exists), you can turn off Update Fields (forcing you to manually reset... I *believe* this also affects what happens when you're in page layout view and it wants to re-paginate (which you can't really control, despite a VBA setting which seems to allow you to turn automatic pagination off.
5. Zoom level will actually change the display speed (i.e., "repainting").

In general, you should play with the various settings above, as the best bang for your buck will be highly dependent on your actual document. But to summarize-- I doubt that it's predominatily the fault of the field codes, and probably more to do with 900 activeX objects in your document.

[Paul_Hossler]

One thing to try is to put UnDoClear into your macro and call it when you load each chart ( or every 10??)



......

ActiveDocument.UndoClear

......



Paul

[macropod]

Re:

every time it adds a new page, it updates all the fields in the ENTIRE document
That should only occur if your code is telling it to. You should only need to do that once - after the chart processing is done.

A couple of performance improvement hints (which may or may not be relevant as you haven't posted any code):
1. Use ranges instead of selections; and
2. Make sure you turn off screen updating (ie Application.ScreenUpdating = False) until the very end. Doing so also obviates the need for the workarounds suggested by Frosty.

[Frosty]

Those excellent points, Paul. I forgot about how useful ScreenUpdating could be in this kind of scenario.

I'd just add that the Application.ScreenUpdating = False is not always reliable, depending on the scenario (especially if it's not entirely possible to get rid of use of a selection object, although I'm guessing that it should be possible in this case). But even though most screen updating will disappear, even in the simplest scenario (like my code below), there are still *some* changes to the screen, even with screenupdating turned off. This may cause slowness despite your best efforts.

It can also be distressing to an end-user to have the application appear to "freeze" for a long time, if the end-user isn't expecting it (and I think, if you're pulling in 900 charts, it's going to take some amount of time to accomplish the process no matter what you do). There are a number of ways to inform the user of your progress...

Just as a proof of concept, in terms of settings, here's a quick sample bit of code to try out. Uncomment some of the options at the top to see the difference in timing.

If you're familiar with the immediate window, you can comment out the msgbox line as well.

Public Sub TestStatusBar()
Dim i As Long
Dim si As Single
Dim rngWhere As Range
Dim sInfo As String

Set rngWhere = Selection.Range
'ActiveWindow.View = wdPrintView
'ActiveWindow.View = wdNormalView
'Application.ScreenUpdating = False
'Application.Options.Pagination = False
'set our timer
si = Timer
'set our range
'do our test
For i = 1 To 1000
rngWhere.InsertAfter "Hello"
Application.StatusBar = "Processing: " & i
Next
rngWhere.text = ""
sInfo = "Accomplished in: " & Timer - si & " seconds. " & vbCr & _
" Paginating: " & Application.Options.Pagination & vbCr & _
" Screen Updating: " & Application.ScreenUpdating
MsgBox Replace(sInfo, vbCr & " ", vbCr)
Debug.Print Replace(sInfo, vbCr & " ", "")
Application.ScreenUpdating = True
Application.Options.Pagination = True
End Sub

[Paul_Hossler]

So I have a macro that pulls in over 900 charts and adds a caption to each one. Basicly it reads a text file that has the location of the picture and the caption and puts them in the document.



1. Do you insert each of the 900 charts at the end of the document each time, or insert them in various places all over the document?

2. Just wondering if the ActiveDocument.UndoClear made any difference at all. I've noticed that the UnDo stack can slow you down if it gets too full of large items (subjective of course)

3. Another way to provide feedback to the user is via Application.Statusbar. It doesn't change the screen, but it is easy and does work

Paul

[macropod]

I'd just add that the Application.ScreenUpdating = False is not always reliable, depending on the scenario (especially if it's not entirely possible to get rid of use of a selection object, although I'm guessing that it should be possible in this case). But even though most screen updating will disappear, even in the simplest scenario (like my code below), there are still *some* changes to the screen, even with screenupdating turned off. This may cause slowness despite your best efforts.
If I un-comment the commented-out 'Application.ScreenUpdating = False' in your demo code, I don't get any screen updating/changes till the code has finished executing ...

[Frosty]

Well, that's because my code is simple (and perfect). Grin.

I should have clarified in my post: the purpose of that code was to test the different timings of some settings. As I was writing it, I noticed that occasionally, on my system... if I'm in Normal View and running it on an empty document, I would sometimes see the horizontal line below the cursor "bounce" a bit, even with .ScreenUpdating false. I suspect it has something to do with available memory, how long word has been open, etc. With a "fresh" Word process, my screen doesn't refresh at all in that simple code (as when I just tried it).

But my comment about .ScreenUpdating not being reliable was meant more in the broader sense-- that even if you turn screenupdating off in Word, there are other things you can do in code which will turn screenupdating back on without alerting you (use the of selection object in anyway will cause word to trigger a "repaint," even with .ScreenUpdating = False, as an example).

So that, broadly speaking, you can't simply put .ScreenUpdating = False at the top of any procedure, and .ScreenUpdating = True at the bottom of that same procedure, and have your screen not refresh during the processing 100% of the time. At least, that has been my experience.

[macropod]

broadly speaking, you can't simply put .ScreenUpdating = False at the top of any procedure, and .ScreenUpdating = True at the bottom of that same procedure, and have your screen not refresh during the processing 100% of the time.
True, but that's usually because you're using selections instead of range objects or because you're opening/closing documents. A major advantage of turning screenupdating off, though is to expedite processing - and isn't that what this thread's about?

[Frosty]

A major advantage of turning screenupdating off, though is to expedite processing - and isn't that what this thread's about?
Well, yes. I was simply trying to add to the discussion.

As the OP had mentioned inserting charts, I was simply bringing up .ScreenUpdating not necessarily being a panacea because charts may indicate the use of OLEObjects, some of which will cause .ScreenUpdating not to be as useful (inserting an Excel Chart object triggers another application, which causes a repaint regardless of whether you have .ScreenUpdating = False and no use of the Selection object).

It was, admittedly, a bit of a shotgun approach to answering.

Not trying to get into some kind of information competition. Simply trying to help.