Print Performance in VBA

[ISS6]

I have developed an EXCEL 2003 VBA application that works like a charm -
except for printing various reports.

The user selects a report to be produced and it is built to a new sheet.
Watching the process, I can see that this happens very quickly.

However, once the command Sheet2.Print is executed, processing drops to a
crawl. It takes up to a full minute to get the sheet sent to the printer.
What's going on? I can perform the same function from the spreadsheet in a
blink of an eye, but within VBA, it takes forever.

Am I forgetting to set something?

 

[JLGWhiz]

It there are several factors. Is the printer on a network? Is the printer
busy? Was the primary printer specified? VBA will automatically step
through these, which takes very little time, but if the printer is not ready,
and depending on the type of printer, it can take a couple of seconds. Still
it should indicate that the file was sent to the printer pretty quickly.

 

[JLGWhiz]

I just noticed your syntax, Sheet2.Print.

This is not the normal syntax for printing sheets. I would use:

Sheet2.PrintOut

 

[ISS6]

I believe it is indeed Sheet2.printout. I'm typing from memory!

This is a stand-alone system - a P4 PC with an attached printer. No
network. In this particular configuration, it is printing to an HP LaserJet
1400 that has a 8 meg buffer. The entire application is less than 1 meg!

While the PrintOut command is executing, the screen locks up - that is, the
hourglass replaces the cursor and nothing else can happen in the application
until it finishes. Naturally, I can go to another application - Word,
Solitaire - while waiting for Excel to execute the print, but I can't
understand why this bogs down so heavily. After all, the sheet has been
completely formatted and all I'm doing is sending it to the printer!

 

[JRForm]

ISS6,

Does it ever finish? Perhaps you could post the code you are using to get
your printout.

 

[ISS6]

Aye, it finishes, just slower than molasses.

The code is:

Sheet2.PrintOut

That's it. The sheet is built from data selected rom Sheet1. This happens
very quickly (I stepped through the code to isolate the problem). I can
Print Preview the report and it's perfect. Then I step onto Sheet2.PrintOut
and wait up to a minute. The report is printed and then control returns to
the menu.

 

[JLGWhiz]

Just guessing, but it sounds like a communication problem with the printer.
Maybe the printer driver. But you said that manually it zips right out, so
probably not the driver. I am not really a techie, so I don't know what the
difference between a VBA print command and a click event command amount to.
I would think, very little. I do not think it is VBA per se that is the
problem.

 

[ISS6]

The report is formatted to Sheet very quickly. However, the actual command
PrintOut is extremely slow in executing. It just sits there on this line for
up to a minute, do what I have no idea. Maybe waiting for the printer to
power up? (The printer is on all the time, but I think it goes into a
standby mode to conserve energy) Whatever happened to the Windows print
spooler? I thought that the spooler was an automatic part of Windows, but
maybe I need to manage it programatically?

If I'm just waiting for the printer to that is the case, can the printer be
"poked" to wake it up when the Reports Menu is entered - say in the
UserForm_Activate module - without anything actually being printed?

Oh, what a tangled web.......

 

[JP]

Guys,

Isn't the syntax supposed to be

Sheets(2).PrintOut

?


--JP

 

[Dave Peterson]

Probably not.

Next time you're in the VBE, look at the project explorer window.

If you expand the objects, you'll see something like:

Sheet2 (Sheet2)
or
Sheet2 (SheetnameYouSeeOnTheTabInExcel)

The name on the left is called the Codename. The name in parentheses is the
name (what's seen on the tab in excel).

My bet is the original poster was refering to the Codename of the worksheet.
This codename is usually much more difficult for the average user to change--and
isn't susceptible to any sheet name changes that the user may make.

Sheets("sheetnamehere").printout
would fail if the user renamed "sheetnamehere" to something else.

And if the user reordered the tabs, then
sheets(2).printout
may not print the sheet that the developer wanted.
This will print the second sheet (starting on the left).

Using codenames in your macros will make your code more robust.

=====
The original poster could have declared a variable and named it Sheet2, but
that's pretty unlikely--and with a variable with a name like that would confuse
the heck out of me!

 

[ISS6]

OK - Here's the entire routine - although I left out the formatting of
several print columns as they aren't the problem. I know how hard it is to
debug someone's code without seeing all of it!

**********************************************
intAdults = 0
y = 2
Sheet2.Cells.Clear

For X = 1 To LastRow
Cell1 = "A" & X
If Sheet1.Range(Cell1) > "" Then
Cell2 = "H" & X

intAdults = intAdults + Sheet1.Range(Cell2).Value
Sheet2.Range("A" & y) = Sheet1.Range("A" & X)
Sheet2.Range("A" & y).Font.Size = 9
Sheet2.Range("B" & y) = Sheet1.Range("B" & X)
Sheet2.Range("B" & y).Font.Size = 9
Sheet2.Range("C" & y) = Sheet1.Range("C" & X)
Sheet2.Range("C" & y).Font.Size = 9
y = y + 1
Else
Exit For
End If
Next

With Worksheets(2).PageSetup
.Orientation = xlLandscape
.LeftMargin = 0
.RightMargin = 0
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.75)
.CenterHeader = "&B &16 Report of Adult " & _
"Registrations &B"
.RightHeader = "&B &12 " & Format(Date, "mm/dd/yyyy")
.RightFooter = "&B &12 Page &P"
.PrintHeadings = False
End With

Sheet2.Range("A1") = "Name"
Sheet2.Range("A1").Font.Size = 10
Sheet2.Range("A1").Font.Bold = True
Sheet2.Range("A1").Font.Underline = True
Sheet2.Columns("A").ColumnWidth = 23

Sheet2.Range("B1") = "Address"
Sheet2.Range("B1").Font.Size = 10
Sheet2.Range("B1").Font.Bold = True
Sheet2.Range("B1").Font.Underline = True
Sheet2.Columns("b").ColumnWidth = 25

Sheet2.Range("C1") = "City"
Sheet2.Range("C1").Font.Size = 10
Sheet2.Range("C1").Font.Bold = True
Sheet2.Range("C1").Font.Underline = True
Sheet2.Columns("C").ColumnWidth = 14

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
'------> Execution Point A
Sheet2.Printout
'------> Execution Point B
End Select
**************************************

Now execution to "Execution Point A" is lickety-split. But the single
command Sheet2.PrintOut is the hang up.

 

[Dave Peterson]

Can you test on a different printer?

OK - Here's the entire routine - although I left out the formatting of
several print columns as they aren't the problem. I know how hard it is to
debug someone's code without seeing all of it!

**********************************************
intAdults = 0
y = 2
Sheet2.Cells.Clear

For X = 1 To LastRow
Cell1 = "A" & X
If Sheet1.Range(Cell1) > "" Then
Cell2 = "H" & X

intAdults = intAdults + Sheet1.Range(Cell2).Value
Sheet2.Range("A" & y) = Sheet1.Range("A" & X)
Sheet2.Range("A" & y).Font.Size = 9
Sheet2.Range("B" & y) = Sheet1.Range("B" & X)
Sheet2.Range("B" & y).Font.Size = 9
Sheet2.Range("C" & y) = Sheet1.Range("C" & X)
Sheet2.Range("C" & y).Font.Size = 9
y = y + 1
Else
Exit For
End If
Next

With Worksheets(2).PageSetup
.Orientation = xlLandscape
.LeftMargin = 0
.RightMargin = 0
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.75)
.CenterHeader = "&B &16 Report of Adult " & _
"Registrations &B"
.RightHeader = "&B &12 " & Format(Date, "mm/dd/yyyy")
.RightFooter = "&B &12 Page &P"
.PrintHeadings = False
End With

Sheet2.Range("A1") = "Name"
Sheet2.Range("A1").Font.Size = 10
Sheet2.Range("A1").Font.Bold = True
Sheet2.Range("A1").Font.Underline = True
Sheet2.Columns("A").ColumnWidth = 23

Sheet2.Range("B1") = "Address"
Sheet2.Range("B1").Font.Size = 10
Sheet2.Range("B1").Font.Bold = True
Sheet2.Range("B1").Font.Underline = True
Sheet2.Columns("b").ColumnWidth = 25

Sheet2.Range("C1") = "City"
Sheet2.Range("C1").Font.Size = 10
Sheet2.Range("C1").Font.Bold = True
Sheet2.Range("C1").Font.Underline = True
Sheet2.Columns("C").ColumnWidth = 14

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
'------> Execution Point A
Sheet2.Printout
'------> Execution Point B
End Select
**************************************

Now execution to "Execution Point A" is lickety-split. But the single
command Sheet2.PrintOut is the hang up.

 

[JRForm]

ISS6,

Try -ActiveSheet.PrintOut

OK - Here's the entire routine - although I left out the formatting of
several print columns as they aren't the problem. I know how hard it is to
debug someone's code without seeing all of it!

**********************************************
intAdults = 0
y = 2
Sheet2.Cells.Clear

For X = 1 To LastRow
Cell1 = "A" & X
If Sheet1.Range(Cell1) > "" Then
Cell2 = "H" & X

intAdults = intAdults + Sheet1.Range(Cell2).Value
Sheet2.Range("A" & y) = Sheet1.Range("A" & X)
Sheet2.Range("A" & y).Font.Size = 9
Sheet2.Range("B" & y) = Sheet1.Range("B" & X)
Sheet2.Range("B" & y).Font.Size = 9
Sheet2.Range("C" & y) = Sheet1.Range("C" & X)
Sheet2.Range("C" & y).Font.Size = 9
y = y + 1
Else
Exit For
End If
Next

With Worksheets(2).PageSetup
.Orientation = xlLandscape
.LeftMargin = 0
.RightMargin = 0
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.75)
.CenterHeader = "&B &16 Report of Adult " & _
"Registrations &B"
.RightHeader = "&B &12 " & Format(Date, "mm/dd/yyyy")
.RightFooter = "&B &12 Page &P"
.PrintHeadings = False
End With

Sheet2.Range("A1") = "Name"
Sheet2.Range("A1").Font.Size = 10
Sheet2.Range("A1").Font.Bold = True
Sheet2.Range("A1").Font.Underline = True
Sheet2.Columns("A").ColumnWidth = 23

Sheet2.Range("B1") = "Address"
Sheet2.Range("B1").Font.Size = 10
Sheet2.Range("B1").Font.Bold = True
Sheet2.Range("B1").Font.Underline = True
Sheet2.Columns("b").ColumnWidth = 25

Sheet2.Range("C1") = "City"
Sheet2.Range("C1").Font.Size = 10
Sheet2.Range("C1").Font.Bold = True
Sheet2.Range("C1").Font.Underline = True
Sheet2.Columns("C").ColumnWidth = 14

With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
'------> Execution Point A
Sheet2.Printout
'------> Execution Point B
End Select
**************************************

Now execution to "Execution Point A" is lickety-split. But the single
command Sheet2.PrintOut is the hang up.

 

[JLGWhiz]

You know, looking at the page setup data, I got to thinking about the old HP
laserjet that we had in the office a few years ago. It seemed to get a
little cranky when you sent it some parameter changes for headers, footers,
margins, etc. I would have thought that there would be enough technological
advance to get around those quirks by now, but maybe not. It might be that
the printer is recalculating every time you send it those print parameters.
They don't load into the printer until you give it the PrintOut command.

 

[ISS6]

I decided to try your advice - I took the app to another machine with a
different printer and no more problems.

I guess I need to update my printer driver (or maybe invest in a new printer).


Thanks for all your patience and suggestions!