Tips: Seitenwechsel und Zwischensummen per Makro ermitteln (CHIP 04/2002)

Seitenwechsel und Zwischensummen per Makro ermitteln

Sie arbeiten mit einer lΣngeren Kalkulationstabelle. Dort m÷chten Sie die Seitenwechsel fⁿr den Ausdruck ermitteln und fⁿr die einzelnen Druckseiten eine Teilsumme berechnen. Die Tabelle soll dabei aber flexibel bleiben und auch nach dem Einfⁿgen von Daten oder ─nderungen der Formatierung wieder die aktuellen Summen auf der gedruckten Seite ausweisen.

Hauptaufgabe ist zunΣchst, die Seitenumbrⁿche automatisch mit einem Makro zu bestimmen. Bei bekannten Seitenumbrⁿchen k÷nnen Sie dann die Summenbildung recht einfach ergΣnzen.
Mittels VBA k÷nnen Sie diese Aufgabe l÷sen. Das Objekt ╗HPageBreaks½ liefert Ihnen die Informationen zum Bestimmen der Umbrⁿche. In den so ermittelten Zeilen k÷nnen Sie die Summenformeln fⁿr die einzelnen Seiten einfⁿgen.
╓ffnen Sie Ihr Arbeitsblatt in Excel und fⁿhren Sie ╗Extras | Makro | Makros½ aus. Geben Sie einen Namen wie ╗Seitenwechsel½ ein und klicken Sie auf ╗Erstellen½. Im VBA-Editor geben Sie dann den folgenden Programmcode ein oder Sie kopieren ihn von der Heft-CD unter CHIP-Code Wechsel:

Sub Seitenwechsel()
Dim Zeile(100) As Integer
Dim i As Integer
Dim XStr As String
Anzahl = Worksheets(1).
HPageBreaks.Count
If Anzahl = 0 Then
  Anzahl = 1
  Cells(Rows.Count, 1) = 1
  Zeile(1) = Worksheets(1).
  HPageBreaks(1).Location.
  Row - 1
  Cells(Rows.Count, 1).
  ClearContents
End If

For i = 1 To CInt(Anzahl)
Zeile(i) = Worksheets(1).
HPageBreaks(i).Location.Row - 1
Next i

Zeile(0) = 0
For i = 1 To Anzahl
  Cells(Zeile(i), 5).Select
  æSelection.EntireRow.Insert
  XStr = ô=sum(Aö & CStr(Zeile(i - 1) + 1) & ô:Aö & CStr(Zeile(i)) & ô)ö
  ActiveCell.Formula = XStr
Next i
End Sub

Und so arbeitet das Listing: Nach der Definition der n÷tigen Variablen ermittelt das Makro die Zahl der vorhandenen Seitenwechsel und speichert sie in der Variablen ╗Anzahl½. Falls die Tabelle noch keinen Umbruch hat, weist das Makro einer Zelle am Tabellenende temporΣr einen Wert zu und ermittelt den ersten Seitenumbruch. Die nΣchste Schleife bestimmt die Zeilennummern aller Seitenumbrⁿche der Tabelle und speichert Sie im Feld ╗Zeile½. Eine weitere Schleife durchlΣuft alle gefundenen Umbrⁿche, um dort die Teilsummen fⁿr die einzelnen Seiten einzufⁿgen. In unserem Beispiel benutzt das Makro die fⁿnfte Spalte (E) der jeweils letzten Zeile auf einer Seite.
Der folgende Befehl setzt aus der vorhergehenden und der aktuellen Zeilennummer des Seitenwechsels den Summenbefehl zusammen und speichert ihn in der Variablen ╗XStr½. Im nΣchsten Schritt schreibt das Makro diese Formel in die aktive Zelle. Die Summenberechnung erfolgt in der Schleife fⁿr sΣmtliche Seitenwechsel.
Mit ╗Selection.EntireRow.Insert½ k÷nnen Sie fⁿr die Summenberechnung auch eine neue Zeile am Seitenende einfⁿgen. Die Gefahr dabei: Die Tabelle wird bei wiederholter Ausfⁿhrung der Makros durch Leerzeilen zerstⁿckelt.