Excel Seitenumbrüche per VBA
Als Teil einer überarbeitung der report-generator habe ich gesehen, was ich glaubte zu sein, ineffizienten code. Dieser Teil des Codes ausgeführt wird, nachdem die Haupt-Bericht generiert wird, um die Seitenumbrüche im logischen Positionen. Die Kriterien ist diese:
- Jeder Website beginnt auf einer neuen Seite.
- Gruppe dürfen nicht gebrochen, mehrere Seiten.
Der code folgt dem obigen format: 2-loops-diese jobs.
Dies ist der ursprüngliche code (sorry für die Länge):
Public Sub PageBreak(ByRef wstWorksheet As Excel.Worksheet, ByVal pctProgress As ProgressCtl.ProgressControl)
Dim breaksMoved As Integer
Dim p As HPageBreak
Dim i As Integer
'Used as a control value
breaksMoved = 1
' Marks that no rows/columns are to be repeated on each page
wstWorksheet.Activate
wstWorksheet.PageSetup.PrintTitleRows = ""
wstWorksheet.PageSetup.PrintTitleColumns = ""
'If this isn't performed beforehand, then the HPageBreaks object isn't available
Range("A3").Select
ActiveWindow.View = xlPageBreakPreview
'Defaults the print area to be the entire sheet
wstWorksheet.DisplayPageBreaks = False
wstWorksheet.PageSetup.PrintArea = ""
Range("$B$4").Select
' add breaks after each site
Do While ActiveCell.Row <= wstWorksheet.UsedRange.Rows.Count
If ActiveCell.FormulaR1C1 = "Site ID" Then
ActiveCell.PageBreak = xlPageBreakManual
End If
ActiveCell.Offset(1, 0).Activate
pctProgress.ProgressText = "Row " & CStr(ActiveCell.Row) & " of " & CStr(wstWorksheet.UsedRange.Rows.Count)
Loop
Dim passes As Long
Do While breaksMoved = 1
passes = passes + 1
breaksMoved = 0
For i = 1 To wstWorksheet.HPageBreaks.Count - 1
Set p = wstWorksheet.HPageBreaks.Item(i)
'Selects the first page break
Range(p.Location.Address).Select
'Sets the ActiveCell to 1 row above the page break
ActiveCell.Offset(-1, 0).Activate
'Move the intended break point up to the first blank section
Do While Not ActiveCell.FormulaR1C1 = ""
ActiveCell.Offset(-1, 0).Activate
breaksMoved = 1
Loop
'Add the page break
If ActiveCell.FormulaR1C1 <> "Site ID" Then
ActiveCell.Offset(1, 0).Activate
wstWorksheet.HPageBreaks.Add ActiveCell
End If
pctProgress.ProgressText = "Set break point " & CStr(passes) & "." & CStr(i)
Next
Loop
'Reset the view to normal
wstWorksheet.DisplayPageBreaks = True
ActiveWindow.View = xlNormalView
Range("A3").Select
End Sub
Sehen Raum für Verbesserung, die ich zu ändern. Als eine der neuen Anforderungen, die die Menschen wollen den Bericht wurden manuell entfernen von Seiten vor dem drucken. Also ich habe Checkboxen auf einer anderen Seite und kopiert die markierten Elemente über. Zu lindern, die ich verwendet benannte Bereiche. Ich habe diese benannte Bereiche zur Erfüllung der ersten Anforderung:
' add breaks after each site
For Each RangeName In ActiveWorkbook.Names
If Mid(RangeName.Name, 1, 1) = "P" Then
Range(RangeName).Activate
ActiveCell.Offset(Range(RangeName).Rows.Count - 1, 0).Select
ActiveCell.PageBreak = xlPageBreakManual
End If
Next RangeName
Alle Bereiche sind mit dem Präfix P_ (für die Eltern). Mit dem lame Jetzt() Stil der grobe timing-dies ist 1 Sekunde langsamer auf meine kurze 4 Seiten Bericht und anspruchsvoller 15-site berichten. Diese haben 606 und 1600 Zeilen jeweils.
1 Sekunde gar nicht so schlecht. Schauen wir uns die nächsten Kriterien.
Jede logische Gruppe ist geteilt durch eine leere Zeile, also der einfachste Weg ist, um die nächste Seite zu brechen, einen Schritt zurück, bis Sie die nächste leere Zeile, und legen Sie die neue Pause. Spülen und wiederholen.
Also warum nicht das original durch mehrere Zeiten? Wir verbessern kann, dass zu (den Textbaustein außerhalb des loops ist die gleiche).
Dim i As Long
Dim oPageBreak As HPageBreak
Do While i < shtDeliveryVariance.HPageBreaks.Count - 1
i = i + 1
pctProgress.ProgressText = "Setting Page Break " & CStr(i) & " of " & CStr(shtDeliveryVariance.HPageBreaks.Count)
Set oPageBreak = shtDeliveryVariance.HPageBreaks.Item(i)
' select the page break
Range(oPageBreak.Location.Address).Select
ActiveCell.Offset(-1, 0).Activate
' move up to a free row
Do While Not ActiveCell.FormulaR1C1 = ""
ActiveCell.Offset(-1, 0).Activate
Loop
'Add the page break
If ActiveCell.FormulaR1C1 <> "Site ID" Then
ActiveCell.Offset(1, 0).Activate
shtDeliveryVariance.HPageBreaks.Add ActiveCell
End If
Loop
Einen pass und eleganter zu. Aber wie viel schneller ist es? Auf der kleinen test dauert 54 Sekunden im Vergleich zu den ursprünglichen 45 Sekunden, und auf den größeren test mein code ist langsamer wieder auf 153 130 Sekunden. Und das ist gemittelt über 3 runs zu.
Also meine Fragen sind: Warum ist mein neuer code so viel langsamer als das original, trotz mine suchen, schneller und was kann ich tun um die Beschleunigung der Langsamkeit, die code?
Hinweis: Den Bildschirm.Aktualisierung, etc. ist bereits aus, wie ist die Berechnung etc.
jpinto, sollten die Dinge so einfach wie möglich gemacht... Aber nicht einfacher:) Wenn er will posten im detail, mehr Leistung zu ihm. Zumindest war es nicht so etwas wie "Warum ist excel so langsam?";)
ja, ich weiß, was du meinst... dieser Ort entpuppt sich mehr um subjektive Umfrage als wirkliche spezifische Programmierung probs. (einfach mal die 10 oder 20 meisten Stimmen Fragen) wie auch immer, mein Kommentar war mehr gedacht als ein Kompliment für den avatar.
Ich sollte wirklich ändern - der Eigentümer der Tölpel hat, verstarb leider (Anna Nicole Smith), aber ich habe mit dem avatar für 10 Jahre jetzt.
InformationsquelleAutor graham.reeds | 2009-06-12
Du musst angemeldet sein, um einen Kommentar abzugeben.
Sehe ich Raum für Verbesserung in ein paar stellen in Ihrem code:
Ich umgestaltet werden, die original code geben Sie ein Beispiel für einige dieser Ideen. Ohne zu wissen, Ihre Daten-layout, ist es schwer sicher zu sein, ob dieser code ist 100% gültig, so würde ich überprüfen es für die Logik-Fehler. Aber es sollte Ihnen den Einstieg.
Wenn ich könnte Ihnen 2 upvotes würde ich - das ist eine Menge guter Informationen.
Wusste nie, dass über 2). Das ist ein guter link.
Der Beweis, Sie sagen, ist in den pudding. Die ursprüngliche Methode auf eine 4883 Zeilen Uhren mit 830 Sekunden. Ihre überarbeitete Methode Uhren in an einem atemberaubenden 131. Müssen Sie nehmen, was ich hier gelernt hast und anwenden, um die anderen Funktionen sind langsam (ie: Die Funktionen, die das 4883 Zeilen...)
Glücklich zu helfen. In Bezug auf Eigenschaften: es ist nicht immer, die viel gewinnen, es hängt davon ab, wie der Autor der Klasse implementiert die Eigenschaft:) Es ist einfach eines dieser Dinge, die Sie tun können, um zu lösen die Zweifel zu Ihren Gunsten. msdn.microsoft.com/en-us/library/sk5e8eth(VS.80).aspx
InformationsquelleAutor Oorang
Die einfache Antwort ist, dass Sie
ActiveCell
undSelect
undActivate
. Excel tatsächlich wählt die Zellen aus, wie Ihr code ausgeführt wird, so dass der code langsamer ausgeführt werden (wie Sie bemerkt haben).Ich würde empfehlen, mit einem
Range
als Referenz und alles tun, die tests, die "in memory".Dim Bereich für tracking (
dim rngCurrentCell as range
) und verwenden, anstatt die Auswahl der Zellen.So, für den ersten Auftritt von
Select
im codeRange("A3").Select
würden Sie "Set", es alsSet rngCurrentCell = Range("A3")
. Das gleiche für die Nächsten B4 Linie.Dann:
Und so weiter.
Nun, um Werte zu testen verwenden die gleiche syntax wie die
ActiveCell
.Wenn Sie irgendwelche Fragen haben, lassen Sie es mich wissen.
InformationsquelleAutor JustPlainBill
Ich warf einen kurzen Blick auf den code und mein Erster Gedanke ist, dass diese Zeile:
pctProgress.ProgressText = "Einstellung Seitenumbruch" & CStr(i) & "" & CStr(shtDeliveryVariance.HPageBreaks.Count)
kann eine Ursache für einige der Verzögerungen. Die Lage dieser code bedeutet, dass das system zu gehen und neu berechnen .Count-Wert, da kommt es am Anfang der Schleife in deinem code, aber diese Neuberechnung geschieht nicht im original.
Andere Gedanken:
Je nach Tabelle Größe, raus gehen und nachmessen kann dieser Wert Verlangsamung Dinge nach unten. Warum nicht einfach manuell erhöhen eine Pausen zählen tracking-variable, wenn Sie tatsächlich die Ergänzung des neuen zu brechen, anstatt das system zu gehen und zu zählen, oder loszuwerden, die zählen in der Schleife (da bist du nicht die Anzeige aktualisiert wird sowieso während dieses Prozesses) und setzen die Zählung der Seite bricht in seinen eigenen code-segment, läuft durch die Inhalte am Ende die ganze Formatierung, wenn eine endgültige Zahl der Seitenumbrüche kann leicht bestimmt werden mit einem einzigen Aufruf?
InformationsquelleAutor Ron McMahon