Link to home
Start Free TrialLog in
Avatar of JohnRobinAllen
JohnRobinAllenFlag for Canada

asked on

Display informaton in the status bar using VBA in MS Word

I have a VBA macro that takes about ten or fifteen minutes to finish its work. I would like to inform the user that the program is really doing something when it otherwise would appear to be dead.

The obvious solution is to display progress in the status bar. That works for about four seconds and then VBA goes on without displaying anything. The screen is not updated, nor is the status bar.

I suspect the solution is insolvable, but EE readers continually to surprise me with solutions to bugs or "features" in MS Office.

Thanks for any help.

    john robin allen
    Priddis, Alberta, Canada
Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Progress display requires some sort of milestones to increment the the display.

Assuming that these are correct, you probably also need to put some DoEvents statements in strategic places in the code in order to allow some CPU time to the display update process which is likely to be asynchronous.
Avatar of JohnRobinAllen


I'll try out the suggestion and get back to you next year. (It's still 2010 here.)
Put the single line of code
after the statusbar line (ot more if you need it). The macros run too fast to allow screen update. This code stops the macro for a fraction of a second to allow it to happen.
The DoEvents solution is a band-aid that works only part of the time. I am still working on the problem. The basic cause of the problem is that Word keeps wanting to indicate "Page 1 of 42" (or whatever) in the status bar, and that appears to override my requests.

A factor complicating my problem is that I use Application.ScreenUpdating = False (or True) frequently to speed up the process, and perhaps that has some effect on my status bar.

The macro in which I need to control the status bar is in a routine that takes about ten minutes to accomplish its task. The routine has three major steps:
     1. Go through the active document -- a book or story -- looking for marked items and then memorizing certain things (location, context, etc.)
     2. Go through another document that consists of a table with more information about the marked words.
     3. Generate a new document with all the information from the two steps above.

In step 2, things work well. the Statusbar performs perfectly. In step 3, the computer works so quickly there is no need to use the status bar. The third document is generated in about three seconds.

My problem is with the first step that takes about five minutes. Even though I specify that the screen should not be updated, it often does so anyway as the cursor jumps all around the document picking up an earlier page number before the marked word(s), then picking up the contexts before and after the marked words.  I would like to give some indication to the user about how long this will all take, by saying we are on page x of y number of pages in the document.

If anyone would want to see it and try it, I can easily attach the document and macro to one of these messages so you can see the problem.

If I make a breakthrough with the DoEvents command, I'll post it here.

Thanks for your interest

         John Robin (Allen)
I have tried several variations on the DoEvents solution, but at certain places, particularly when the computer is working rather hard, it just does not work. I can easily show anyone the problem if you will allow me to upload the document in question.
You are welcome to upload the document - and the macro. The problem is usually with questioners who might have worries about data confidentiality data.
My files are currently *.docm" files. I will change them to the old format *.doc, but the files (obviously) have macros, which may prevent EE from accepting them. We'll see. I have to convert them anyway to run on Word 2003 *.doc files. When ready, I'll upload them and we can see if EE will let them pass.
Here are the files that give me problems
1.      Le Neveu de Rameau.doc
2.      Le Neveu de Rameau data.doc
3.      Vocab Tools Data Template.doc

To see the problem, load the first file into Word. It should be the active document, but the other two files should also be in the current directory. The macro will not run without the third file in the directory, and the second file provides information needed by the macro.

The macro that causes the problem is xMakeTable which is in the VocabWork module of the active document. One can either run it in the VBA editor, or a shortcut to run it is Alt + Shift + a quotation mark (i.e, a shift apostrophe).

At first the status bar shows what is happening, as the macro works. Then Word takes over and supplies its own information that overwrites what we want to have appear in the status bar.

The coding is not finished, but works well now in Word 2010. Eventually I’ll add code to make it work in earlier versions. If anyone would like an earlier version now, I can generate that as soon as I can load an earlier version of Word onto my computer. The major difference is that in the earlier versions of Word there is nothing that limits the width of MsgBox messages. I have a Function that can artificially insert CR/LF into long strings.

The first document above is just the first five pages of a forty-two-page document. I have reduced its size  to cut down the time to go through the three steps of the macro:
    1. Get information from the first file;
    2. Get more information from the second file;
    3. Create a new version of the second file using the template as a base.

Any suggestions about how to make the status bar work, or about anything else, are most appreciated.


The point value on this question is too low. I'm pushing it up to 400 for a partial solution. If I get a real solution, I'll push the value up to 500.
Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Actually, I have a bit of a problem with this line:

    ActiveDocument.Bookmarks.Add Range:=Selection.Range, Name:="zzzMakeTable"

It bookmarks the Selection, but I can't see how the Selection has been set, so the bookmark could be anywhere and any size.
There is always the option of a stand alone progress bar, instead of in the status bar, that can be found here:
I certainly will try the progress bar. That could solve all my problems. I'll also work on the bookmark setting.
On the comment about the bookmark (Comment 3447532), I put the bookmark in before I test whether the macro should be run. It does not matter where the selection is. When my code tests whether the document is valid, it will move the cursor away from its current position as the program looks for marked words in that initial test.
     If the test then fails and the program stops, I want to return the cursor to wherever it was before the user launched the program. The user will see why the test failed and will see the cursor has apparently not moved.
     If the test succeeds, then we move the cursor to the top of the document and begin working on making the flat file database. The bookmark is no longer relevant.
     In other words, that initial setting of a bookmark is not really important unless the template document is not in the current directory or the user is working with a faulty initial document.
     The odd name for the bookmark is (a) to move the bookmark away from all the other bookmarks and (b) to tell me in which module I set the bookmark, in case I accidentally forget to delete the bookmark after the module finishes its work.

     On the comment about selection, comment 34477493, that might lead to an ideal solution. I should try to see if I can substitute "Range" for "Selection" so that the cursor does not appear to move. I'm not sure whether that is possible, but it would certainly solve the problem about the cursor jumping around so much.

     I am very grateful for all your advice.
Thanks to the help of Graham Skan, I think I'm nearer to a solution of the problem that the status bar does not show status bar messages. In the document I uploaded, the problem macro has three tasks and works with three different documents. The status bar is defective in the first task but works well with the last two documents and tasks.

The major difference with the first task is that the cursor is jumping around a lot. It finds a word, then searches backward to find the page number of the word. Then forward back to the original word, then backward to pick off the context before the word, back to the word, then it jumps forward to get the following context. Then it jumps down to a new word and starts all over again.

I suspect that each time the cursor changes the display, Word decides to display a status bar message that we are on page x of y pages, and that wipes out my messages. The “Application.ScreenUpdating = False” statement will freeze displaying the screen but not the status bar, so Word will let me know which page we are on that I cannot see.

My work, then, is to try to replace all my “Selection” moves to “Range” moves. I don’t know if that is possible or if it will work, or even if that is the cause of my problem, but it will be interesting to find out. I also presume that the macro, which already takes ten or fifteen minutes to run, will run much more quickly if I can make it work with Range objects.

One of my screens gave up this morning, so I've been sourcing a replacement. This means that I haven't made much much progress in looking at improving the code.

Coming back to it, I have encountered an error on the .Execute in the Function vocGetPageNo:

'The Find What text contains a range that is not valid.'

This is because the MatchWildCards argument is true. It isn't set in the function, but since there is only one Selection, there is only one Selection.Find object, and the argument is remembered from the last time that it was used.

Incidentally, there is no need to Select bookmarks before deleting them, so you only need the second line here.

            Selection.GoTo What:=wdGoToBookmark, Name:="zzzMakeTable"
            ActiveDocument.Bookmarks("zzzMakeTable").Delete     '   Clean up bookmark no longer needed
These suggestions are worth gold.
     I'm puzzled by the WildCards argument which I usually never use. Perhaps I should ensure that it is always set to False.

As for the two lines dealing with a bookmark, the purpose of the particular bookmark is to move the cursor back to its original location before my macro mixed things up. That is the reason for the GoTo. Once I get back, then I can delete the bookmark, whose purpose is no longer needed.

I'm working on revising the code to use Range objects rather than Selection, but I have to be out of town Friday morning until Tuesday morning. I may not get finished with the coding before I return. If I can move totally to using Ranges, then many of the bookmarks I made no longer serve any purpose and I can delete them.

The acid test is when I finish the coding whether the status bar will display correctly. I'm a bit disquieted by the fact that the program is running more slowly with Range objects. However, I've also had the system do a lot more in checking whether the documents are in place and ready for the macro. That extra work is probably what is making the program run so slowly.

I'll get back to you when the rewriting is done and works.

Duh. I didn't check back to the main procedure to see where the wildcards had been set. I had been doing some wildcard searches myself in the user interface.

However it does illustrate another disadvantage of using Selection.Find, in that you have to set all the default values in case they have been modified earlier. If you use a new range object, you only have to set  parameters that differ from the default values.

Normally it is quicker not to use the Selection because range objects do not have to consider where on the page  text is found.

Good luck.
I am making progress on the problem, and I have been able to run the complex macro and display statusbar messages. However, I have not yet determined what steps suggested by Graham Skan actually do the trick. Unfortunately I have to be out of town until next Tuesday, at which point I believe I can publish what works and give full credit to Graham. Here are the three things Graham suggested which, in combination, let the messages be shown, but I also suspect that I can eliminate one or two of the following.

       1. Insert the statement "Application.DisplayAlerts = False" in the code. That was a suggestion Graham had made in answer to another query, but it applies well to this problem.
       2. When the cursor has to jump around a document a lot, using Range rather than Selection. That has a few other advantages: (a) the code runs faster; (b) one does not have to use Application.ScreenUpdating = False. The major disadvantage is that one has to figure out how to implement certain Selection statements that do not exist with Range objects.  It is not a picnic using the invisible Range objects, but when it is done, the code works like lightning.
       3. Insert the statement DoEvent in different places in the code.

       I suspect that #3 above is not necessary, but I cannot prove that yet.
       When I get all my Selections turned into Ranges, then I will try deleting #3 and, if possible, #1.

       However I will not be able to do that before five days from now. I hope EE will not remove this question in the interim. Things are looking up.
I am working toward a solution by using Range instead of Selection to stop the cursor from jumping around. Meanwhile I have posted a new question, how to sort a table with Range rather than Selection. Please give me a few more hours with the problem. I think I am almost done evaluating Graham Skan's solution. Meanwhile I am finding that use of Range instead of Selection does not always speed up the processes. Details to follow
I did not succeed in banishing all "Selection" statements from the program, but the few that remain did not interfere with the Status bar messages. I can now state definitively what works and what does not work in ensuring that status bar messages are not wiped out by MS Word announcements.
     In Comment 34499578 I listed what I thought were the required steps:

     1. The statement "Application.DisplayAlerts = False" should be in the code near the start
     2. The use of Range rather that Selection
     3. DoEvent statements sprinkled in the code.

     I now know that item 1 has NO EFFECT on the problem.
     Items 2 and 3 are BOTH NECESSARY to ensure display of status bar messages.
     Full credit goes to Graham Skan for his suggestions that led to the solution. I am most grateful.

     --John Robin Allen
        Priddis, Alberta, Canada
I'm increasing the point value of the question. The information it gives merits the highest point value.
This suggestion is part of the solution. I have a comment below in item 34548368 that gives a summary of all the factors suggested that result in a solution.
     If anyone wants to see the revised code, I will upload it to EE.
     Many thanks for the help