Thread: Printing Slow Down On VB Repetitive Use
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
RSnyder RSnyder is offline
external usenet poster
  
Posts: 2
Default Printing Slow Down On VB Repetitive Use
Here's the cod

Dim NextLine As Rang
Dim CurrLine As Rang
Dim RptKey As Intege
Dim NumCopies As Intege


With Applicatio
.Calculation = xlManua
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals

RptKey = InputBox(
Prompt:="Choose Sale Rpt Base Type; 1-Net Whse Sales, 2-Net Total Sales, 3-Period Variance Rpt, 4-Quarter Variance Rpt",
Title:="Report Sales Base Type",
Default:=1
NumCopies =
NumCopies = InputBox(
Prompt:="How Many Copies Do You Need Printed? (Maximum of 5)",
Title:="NUMBER OF REPORTS TO PRINT",
Default:=1
If NumCopies 5 The
MsgBox ("The number of copies to print is excessive, 1 will be printed. Use the copier for multiples."
NumCopies =
End I
If RptKey < "1" Or RptKey "4" The
MsgBox ("Your slection must be 1, 2, 3 or 4; you have selected an invalid key!"
ElseIf RptKey = "1" Then 'Report based on net Whse sale
Range("FLD_CHK_TOPRINT_WNS").Selec
ActiveSheet.Unprotec
Set CurrLine = Range("FLD_CHK_TOPRINT_WNS"
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
D
If CurrLine.Value = "N" The
Selection.EntireRow.Hidden = Tru
End I
Set CurrLine = NextLin
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
Loop Until CurrLine.Value = "XX
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies, Collate:=Tru
Cells.Selec
Selection.EntireRow.Hidden = Fals
Range("FLD_DATACENTER").Selec
With Applicatio
.Calculation = xlAutomati
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals
ActiveSheet.Protect "", True, True, , Tru
ElseIf RptKey = "2" Then 'Report based on net Total sale
Range("FLD_CHK_TOPRINT_TNS").Selec
ActiveSheet.Unprotec
Set CurrLine = Range("FLD_CHK_TOPRINT_TNS"
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
D
If CurrLine.Value = "N" The
Selection.EntireRow.Hidden = Tru
End I
Set CurrLine = NextLin
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
Loop Until CurrLine.Value = "XX
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies, Collate:=Tru
Cells.Selec
Selection.EntireRow.Hidden = Fals
Range("FLD_DATACENTER").Selec
With Applicatio
.Calculation = xlAutomati
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals
ActiveSheet.Protect "", True, True, , Tru
ElseIf RptKey = "3" Then 'Period Variance Repor
Range("FLD_CHK_TOPRINT_PVRSRow").Selec
ActiveSheet.Unprotec
Set CurrLine = Range("FLD_CHK_TOPRINT_PVRSRow"
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
D
If CurrLine.Value = "N" The
Selection.EntireRow.Hidden = Tru
End I
Set CurrLine = NextLin
Set NextLine = CurrLine.Offset(1, 0
CurrLine.Selec
Loop Until CurrLine.Value = "XX
Set CurrLine = Range("FLD_CHK_TOPRINT_PVRSRow"
Set NextLine = CurrLine.Offset(1, 0
Range("FLD_CHK_TOPRINT_PVRSCol").Selec
Set CurrLine = Range("FLD_CHK_TOPRINT_PVRSCol"
Set NextLine = CurrLine.Offset(0, 1
CurrLine.Selec
D
If CurrLine.Value = "N" The
Selection.EntireColumn.Hidden = Tru
End I
Set CurrLine = NextLin
Set NextLine = CurrLine.Offset(0, 1
CurrLine.Selec
Loop Until CurrLine.Value = "XX
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies, Collate:=Tru
Cells.Selec
Selection.EntireRow.Hidden = Fals
Cells.Selec
Selection.EntireColumn.Hidden = Fals
Range("FLD_DATACENTER").Selec
With Applicatio
.Calculation = xlAutomati
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals
ActiveSheet.Protect "", True, True, , Tru
ElseIf RptKey = "4" Then 'Quarter Variance Repor
ActiveWorkbook.Sheets("VR Qtr Stmt").Select
ActiveSheet.Unprotect
Range("FLD_CHK_TOPRINT_QVRSRow").Select
Set CurrLine = Range("FLD_CHK_TOPRINT_QVRSRow")
Set NextLine = CurrLine.Offset(1, 0)
CurrLine.Select
Do
If CurrLine.Value = "N" Then
Selection.EntireRow.Hidden = True
End If
Set CurrLine = NextLine
Set NextLine = CurrLine.Offset(1, 0)
CurrLine.Select
Loop Until CurrLine.Value = "XX"
Set CurrLine = Range("FLD_CHK_TOPRINT_QVRSRow")
Set NextLine = CurrLine.Offset(1, 0)
Range("FLD_CHK_TOPRINT_QVRSCol").Select
Set CurrLine = Range("FLD_CHK_TOPRINT_QVRSCol")
Set NextLine = CurrLine.Offset(0, 1)
CurrLine.Select
Do
If CurrLine.Value = "N" Then
Selection.EntireColumn.Hidden = True
End If
Set CurrLine = NextLine
Set NextLine = CurrLine.Offset(0, 1)
CurrLine.Select
Loop Until CurrLine.Value = "XX"
ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies, Collate:=True
Cells.Select
Selection.EntireRow.Hidden = False
Cells.Select
Selection.EntireColumn.Hidden = False
Range("FLD_DATACENTER").Select
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
ActiveSheet.Protect "", True, True, , True
End If
End Sub


----- Dick Kusleika wrote: -----

R

Post the code.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"RSnyder" wrote in message
...
I have a VB instruction set (macro) that hides unnecessary lines on a
worksheet before printing out the worksheet and it works fine except for
when I repeat the macro each time after that. It's not that the macro
instructions aren't carried out it's that the time it takes to execute slows
down dramatically after the 1st execution of the macro. Typically, the code
executes and does the print the 1st time in roughly 3-4 seconds. Its
roughly 270 lines for the IF statements to determine the hide status on a
line and to do the hide or proceed to the next line. All executions after
the initial execution the time jumps up dramatically to 15 seconds. The
speed on each successive execution looks to be about the same. Now that's
not a big deal either however me being a speed demon I'm searching for speed
and not "that's acceptable".
Help me out here. Is this a possible variable issue not being released
after the 1st execution is done even though I'm completely out of the macro
or is it something else maybe in the operating environment? If I closed out
of the workbook and came back in each time I get the same result when
running the macro.

Reply With QuoteReply With Quote