Solved: Debugger anomaly

[jwise]

I was using "single step" mode of the debugger to trace the execution of my program. It reads a spreadsheet and puts numbers in a Word template, and Word prints the single page. For each spreadsheet row, I get one page of print.

It took some time to get this to work...lots of people on VBAX contributed. I assumed the problem was fixed when I was able to successfully go through the file- in the debugger. When I execute the code outside the debugger, it prints nothing. When I run it in the debugger, it works. What gives? Could this be memory related? I would assume that the debugger uses considerably more storage than without the debugger, so it doesn't make much sense.

TIA

[jwise]

I brought this code to my second PC. It has more memory and runs XL2003, versus XL2000.

The code works on this machine- in the debugger or stand-alone. I don't know of anything I could have done that would make the code XL release dependent.

[Jacob Hilderbrand]

Can you post the relavent code or an attachment so we can see what you are talking about?

[jwise]

Hi-

I do not know how to direct you to the code. This is my ignorance. I have another open issue under "XL VBA Invokes Word". A version of the code is there. You can see the code plus download the workbook. You must also look at the next message because it has the download for a Word document.

Basically this Word document is a template with bookmarks, and the XL spreadsheet populates these bookmarks and then instructs Word to print this document. This version of the code only prints one line (the line that is selected) before you invoke the macro. I have another version that prints the entire file (one for each row (minus the first two). This also fails, i.e. it will work when I single step through the debugger but not when I just execute it. There is no indication of an error. If you set Word visible, you can go to its screen and see that it is populated properly.

The two versions were designed to be used together, i.e. you print them all, and if you need to print one later, you can do that too.

If I can get this to work, I think I know how to turn it into a very nice utility, i.e. good for other documents besides my stuff.

Thanks

[Charlize]

Posted a solution at office automation http://www.vbaexpress.com/forum/showpost.php?p=97887&postcount=31.

Charlize

[jwise]

I haven't tried your suggestion yet, but it makes sense! I will put this in the code immediately. It also occurred to me that if I put a 10 second wait after each row, the execution time will be greatly elongated. The real data file is around 40 rows. I think I need to put one of those progress bars as suggested by Jon Walkenbach in his book "Excel 2003 Power Programming with VBA". But wouldn't it be better if I could just "wait" on the spooler? You can do that on the mainframe, synchronously or asynchronously.

I like your "double click" suggestion very much. My users will like that. I do not understand your other suggestion about "normal module", but I will look into that as well.

You have been most helpful... Thanks

[lucas]

Hi Jeff,

I do not know how to direct you to the code.
Just copy the url from your browser and past it into a reply as Charlize did. It's ugly and there are better ways but at least you can give a link that way.....

[Charlize]

I haven't tried your suggestion yet, but it makes sense! I will put this in the code immediately. It also occurred to me that if I put a 10 second wait after each row, the execution time will be greatly elongated --- Time to fetch a cup of coffee ---. The real data file is around 40 rows. I think I need to put one of those progress bars as suggested by Jon Walkenbach in his book "Excel 2003 Power Programming with VBA". But wouldn't it be better if I could just "wait" on the spooler? You can do that on the mainframe, synchronously or asynchronously. --- You can show a form acting as a progressbar with userform.show 0This means show form and continue to run the code.

I like your "double click" suggestion very much. My users will like that. I do not understand your other suggestion about "normal module" --- Code that doesn't belong to a worksheet event, is better placed in a normal module. I had to look for you coding in your file because I couldn't see a normal module. If you place your code in a module you can see that it must be accessible for whole your project (I think) ---, but I will look into that as well.

You have been most helpful... Thanks

[jwise]

I really appreciate all the replies. The "WAIT" suggestion was exactly the problem. It made perfect sense once I read Charlize's post.

I really do appeciate the replies because you not only gave me a fish, you taught me to fish.

To recap the problem and solution, my macro invoked Word for each data row, and inserted cells from that row in several fields. Then it printed the Word document, looping to the next row.

The logic after the print was to destoy the word object so it would not stay in memory when the next invocation of Word came along. The problem was I did not allow any time for the Word thread to complete its task. In the debugger in step mode (press F8 for a single statement's execution), there was enough time to get this print going.

Thanks again

[lucas]

Don't forget to mark your thread solved....

[lucas]

oops, you must have been marking it solved while I was posting....takes forever in the forum these days....