I'm writing this in an effort to alert others to this stupid undocumented "glitch" in Microsoft Excel. If you don't work with Excel and you don't use any Excel workbooks that contain macros, feel free to hit your back button and go on with life. Clearly this won't affect you.
I recently took over a project from a programmer who was laid off (fired with advanced notice giving him time to pass off his work.) This particular Excel add-in has been the bane of my existence for 2 weeks. The macro reads in a plain-text file (actually a pst file but it's just text inside) and formats it so that each page is represented on a single Excel worksheet and it does a bunch of stuff to make it more readable and generally pretty to look at.
When I took over the project, there were 2 outstanding requests that needed to be implemented. I added those features and delivered the add-in to the assigned testers.
In addition to my own testing, there were 6 people who would eventually be end-users who were also testing it. Myself and three of the testers were able to run the add-in every time without error. The other 3 testers would have some success, but often the program would just hang. Other times, it would stop running and fail to produce any output. In either case, it wouldn't display an error of any kind.
The programmers who were experiencing the problems often reported that Excel was taking up 99% of the processor resources after the macro appeared to hang.
Since I was unable to produce the same symptoms, I had to rely on the three people who were experiencing the problems. I added a ton of additional error-trapping code and distributed it for testing, again. When I say "additional error-trapping", I mean "In place of the 'On Error Resume Next' that was in there presently."
The myriad problems that were being reported occurred throughout the macro. The only commonality that I could find was that each error was related to a With-statement and that the With-statements were all acting upon the worksheet.pagesetup class.
Included in one of the error-message alert emails sent by one of the testers, he tossed in a casual comment regarding an observation that he made. He said, "It seems like only the Toledo testers are cursed." This eventually lead me in the right direction because it triggered a memory about Excel 95 needing to actually communicate with the default printer when doing page setup stuff. If the printer was busy printing, Excel would "wait" until the printer could say, "Yup, I'm here!"
In my searches on Google attempting to find an answer to my problem, I looked for "microsoft excel 2003 with statement pagesetup errors problems hanging" and any/every combination of those terms. I kept coming across pages saying that Excel couldn't do page setup related VBA code unless there was a printer installed.
Questions came to mind:
- Why would that be?
- How would Excel know if you had a printer installed?
- Would it behave differently based on the type/brand/style printer that you had set as your default printer?
Then, the answers follwed shortly after:
- Because it's a Microsoft product
- It would check the printer driver?
- Perhaps it would!
Of course, each of these people were working right here in the Toledo office and I knew each of them had a printer installed. Their jobs require that they print things all the time. I asked the primary tester to map to the same printer that I use as my default printer and run the program again. Here's his email reply
Congrats Pete! Great job figuring out the problem.
I've run the Liz tables through a few times and it's taken 2.5 - 3 mins each time, with no problems. Ran the Matt tables through twice with no problems in about 11 mins each time. John has run the Liz tables 3 times in 3-4 mins each with no probs. Jeff ran Matt's through in about 15 mins. Lanny ran Liz's through a few times w/no prob in 3-4 mins. I changed my default printer to a different one we have over here (an HP printer) and it still ran fine.
An HP driver! My printer uses an HP driver even though it's a Dell printer. The printer that they had been using was utilizing a Dell driver.
Some more investigation revealed that Excel must maintain a connection with the printer driver while it's executing the page setup methods. Unfortunately, Excel has a hard time maintaining that connection. There are several solutions to this problem. They include but are certainly not limited to:
- Use Application.ExecuteExcel4Macro to do page setup stuff because it does it all in one action.
- Don't use With-statements
- Make sure you're pointing to a printer that Excel can hang onto
- Use Fineprint or some other intermediating printer software.
So, if you're ever running an Excel macro and it's running slowly, try pointing to a different printer. Try switching to a different printer driver. Or, don't rely on Excel to format your printouts.
Total Members: 4939