22.4 Makros für Rechnungen 

Häufig kann eine Aufgabe durch ein paar kleine Makros sehr erleichtert werden, auch wenn an den Aufzeichnungen noch kleine Korrekturen erforderlich sind. Ein Beispiel ist das Schreiben von Rechnungen. Zwar bietet Ihnen Excel 2007 dafür eine fertige Tabellenvorlage, die ganz gut aussieht. Das Problem ist nur, es sind zwar einige Anpassungsmöglichkeiten eingebaut, aber in der Praxis werden oft noch andere Änderungen notwendig. Deshalb wird hier gezeigt, wie Sie sich selbstständig eine solche Vorlage aufbauen können, die beliebig angepasst werden kann.

Ein halbautomatisches Rechnungsformular

Angenommen, Sie haben für Ihre Rechnungen ein Formular mit Rechnungskopf etc. als Mustervorlage aufgebaut, in der schon die wesentlichen Einträge für die Rechnung enthalten sind. Das leere Formular könnte etwa so aussehen:

Formular für Rechnungen

Wenn Sie mit einem derartigen Formular arbeiten, sind folgende Aktionen nötig, von denen sich ein Teil automatisieren lässt:

• Adressat, Datum, Auftragsdatum und Rechnungsnummer werden eingetragen.

• Die Rechnungspositionen mit Menge und Einzelpreis werden zeilenweise eingegeben.

• Zum Abschluss kommen die Gesamtsumme, Ausweisung der Mehrwertsteuer, evtl. Zahlungsbedingungen etc.

Ein Makro für das Tagesdatum 

Für die Eingabe des Datums kann ein kleines Makro verwendet werden. Die Zelle E11 wird ausgewählt. Das Datum wird mit der Datumsfunktion =HEUTE() vom Systemdatum übernommen. Der Zellinhalt wird in die Zwischenablage kopiert und dann als Wert wieder eingefügt. (Würden Sie die Funktion HEUTE() in der Zelle stehen lassen, würde die Rechnung jedes Mal das Datum aktualisieren, wenn die Datei geöffnet wird.) Anschließend wird die Zelle E12 ausgewählt, sodass das Auftragsdatum eingegeben werden kann.

Aufzeichnung für das Datum

Obwohl das Makro so korrekt arbeitet, ist der von der Makroaufzeichnung erzeugte Code etwas umständlich. Eine kleine manuelle Korrektur vereinfacht das Makro. Zwischen den beiden .Select-Anweisungen genügt eine einzige Zeile, die mit der VBA-Funktion Date arbeitet:

ActiveCell.R1C1Formula = Date

Durch diese Anweisung wird immer das aktuelle Datum des Tages in die Zelle geschrieben, an dem das Makro aufgerufen wird, also bei der Rechnungserstellung.

Ein Makro für die Rechnungspositionen 

Manuell würde nach dem Eintrag des Datums etc. das Ausfüllen des eigentlichen Rechnungsteils so vor sich gehen, dass der Reihe nach die Positionsnummern, Mengen, Bezeichnungen und Einzelpreise eingetragen werden. Dann käme in jede Zelle, die einen Gesamtpreis erhalten soll, unter »Gesamt« die Formel

= Bx*Dx

wobei für x die jeweilige Zeilennummer steht. Der Eintrag der Formeln lässt sich wieder durch ein Makro erledigen, das aber erst aufgerufen wird, wenn alle Positionen erfasst sind. Dabei ist das Problem zu lösen, dass die Formel nur in Zeilen eingegeben werden sollte, in denen tatsächlich Zahlen für Menge und Einzelpreis stehen. Steht die Formel auch in einer leeren Zeile, wird der Wert 0 ausgegeben, der bei Rechnungen mit leeren Zeilen natürlich sehr störend wirkt. Als Formel wird deshalb gewählt

=WENN(ISTZAHL(Bx);Bx*Dx;" ")

mit der Bedeutung: Wenn Bx eine Zahl enthält, dann das Produkt Bx*Dx, sonst ein Leerzeichen (x ist wieder die Zeilennummer). Anschließend muss diese Formel in alle Zellen kopiert werden, die dafür in Frage kommen.

Als Ausgangspunkt für die Aufzeichnung soll die letzte Zeile dienen, in der eine Mengenangabe und ein Einzelpreis eingetragen wurden. Der Zellzeiger wird also zunächst in die Spalte E ein Stück weiter unten (z. B. E30) gesetzt. Am besten schreiben Sie sich in zwei Zellen für Menge und für Einzelpreis je einen Testwert, um zu sehen, ob alles richtig funktioniert. Dann wird die Makroaufzeichnung aktiviert. Als Namen kann »Gesamt« eingetragen werden.

Formel-Makro aufzeichnen 

In der Tabelle werden folgende Schritte vollzogen:

2 Anschließend wird der Bereich von der Zelle, in die die Formel eingetragen wurde, bis zu E17 (im Beispiel also E30:E17) von unten nach oben markiert, sodass die Zelle mit der Formel den Zellzeiger behält.

3 Der letzte Schritt besteht darin, die Formel in alle markierten Zellen zu kopieren. Hierzu dient der Befehl Start/Bearbeiten/Füllbereich mit der Option Oben. Dann wird die Aufzeichnung beendet.

Das aufgezeichnete Makro sieht so aus:

Makro zum Eintragen der Formeln

Das Makro schreibt zunächst die gewünschte Formel in die aktive Zelle, wobei die Formel etwas ungewöhnlich aussieht: Statt der erwarteten (und beim Aufzeichnen geschriebenen) Formel hat Visual Basic mit relativen Adressen in der R1C1-Schreibweise gearbeitet.

Ein Blick auf die Aufzeichnung lehrt, dass das Makro nicht ganz so funktionieren kann, wie es soll. Es enthält für den Bereich, der ausgewählt wird, feste Adressen ("E17:E30") und für die Zelle, in der die anfängliche Formel steht, ebenfalls eine feste Adresse ("E30"). Hier schafft eine kleine Nachbesserung Abhilfe.

Zunächst wird eine neue Zeile eingefügt, die die aktuelle Zeilennummer (wo die Formel eingetragen wurde) ausliest. Diese Nummer wird auch benutzt, um die Zeilennummer festzulegen, in die der Zellzeiger nach der ganzen Aktion gesetzt werden soll.

azeile = Selection.Row 
nzeile = azeile + 2

Die Variable azeile (aktuelle Zeile) speichert die Zeilennummer der aktuellen Auswahl. Dann wird diese Zeilennummer in die Bereichsbezeichnungen so eingebaut, wie es die folgende Abbildung zeigt (dazu wurde in diesem Beispiel ein neues Makro mit dem Namen Gesamta geschrieben). Zusätzlich wird an das Makro noch eine Zeile angehängt, die den Zellzeiger zwei Zellen tiefer setzt:

Range("E" & nzeile).Select

In beiden Fällen wird die Tatsache genutzt, dass die Bereichsadressen in Visual Basic als Zeichenketten eingegeben werden, sodass sie auch aus Variablen zusammensetzbar sind. Die übrigen Befehle können aus der Aufzeichnung durch Kopieren und Einfügen übernommen werden.

Das überarbeitete Makro

Damit ist die Arbeit mit der Rechnung fast schon zu Ende. Es fehlen jetzt lediglich noch die Endsumme mit dem gesonderten Ausweis der Mehrwertsteuer und eventuelle zusätzliche Einträge.

Makro für den Rechnungsabschluss 

Um das Schlussmakro zu erstellen, ist es sinnvoll, direkt an das vorherige Makro anzuschließen. Hierfür wird der Zellzeiger wieder in die Position gebracht, in der er nach Ablauf des vorhergehenden Makros steht. Für die Aufzeichnungsart wird diesmal Relative Aufzeichnung gewählt, damit das Makro später beim Aufruf alle Aktionen relativ zu der letzten Markierung durchführt. Als Name wird »Abschluss« eingetragen. Nach dem Start der Aufzeichnung werden folgende Aktionen durchgeführt:

2 Der Zellzeiger wird um zwei Zellen nach links versetzt. Hierher kommt der Text »Summe Netto:«.

3 Anschließend rückt der Zellzeiger eine Zelle nach unten für den Eintrag »Mehrwertsteuer:«.

4 Nun wieder zwei Zellen nach links und dort die Formel: =E32*0,19.

5 Jetzt eine Zelle nach unten und die Formel =SUMME(E32:E33).

6 Wieder zwei Zellen nach links für den Eintrag »Summe brutto:«.

Hiermit ist das Makro »Abschluss« im Prinzip fertig gestellt. Sie können aber jetzt noch, bevor Sie die Aufzeichnung beenden, einige Formatierungen vornehmen: die Zellen mit den Texteinträgen (E32:E35) markieren und den Text rechtsbündig formatieren, Zellrahmen (immer nur unten) setzen, um Zwischenstriche zu erhalten usw.

Im Beispiel wurde lediglich die Textausrichtung vollzogen. Die Aufzeichnung sieht dann so aus:

Aufgezeichnetes Makro Abschluss

Auch hier ist eine kurze Nachbearbeitung erforderlich. Die zweite Zeile des Makros soll die Formel für die Nettosumme eintragen. Die eingetragene Formel lautet im Makro:

"=SUMME(R(-15)C:R(-2)C)"

würde also die Summe des Bereichs fünfzehn Zellen bis zwei Zellen oberhalb bilden. Benötigt wird aber die Summe von Zelle E17 bis zwei Zellen oberhalb. Das lässt sich einfach erreichen: die Zeile wird so korrigiert, dass die eingetragene Formel lautet:

"=SUMME(R17C:R(-2)C)"

Die relative Adresse R(-15)C wird also durch die absolute Adresse R17C ersetzt. Das überarbeitete Makro erhält den Namen »Abschlussa«.

Zusammenfassen von Makros 

Nun gibt es eigentlich keinen Grund, warum die beiden Makros »Gesamta« und »Abschlussa« getrennt sein müssten. Zwischen ihnen muss an der Rechnung ja nicht mehr gearbeitet werden.

Es spricht also nichts dagegen, sie miteinander zu verbinden. Hierzu werden die beiden Makros hintereinander kopiert, der Titel von »Gesamta« abgeändert in »Schluss«, das End Sub des ersten Makros und das Sub Abschluss() des zweiten Makros können gelöscht werden.

Stattdessen können Sie beide Makros aber auch in einem gemeinsamen zusammenfassen. Das Resultat sieht so aus:

Zwei Prozeduren werden verklammert.

Schließlich werden noch die Bedienungselemente eingerichtet: eine Schaltfläche mit der Beschriftung »Datum« und eine mit der Beschriftung »Abschluss«, die mit den entsprechenden Makros verknüpft werden.

Das fertige Rechnungsformular mit Beispieldaten

Speichern des Formulars als Mustervorlage 

Mit diesen Makros lässt sich die Arbeit mit dem Rechnungsformular leicht erledigen. Sobald Sie die Makros ausgiebig getestet haben, können Sie das Rechnungsformular als Mustervorlage speichern. Alle Einträge aus der Rechnung, die im Lauf der Makroaufzeichnungen vorgenommen wurden, werden entfernt, auch die Formate aus den rechtsbündig formatierten Textstellen werden gelöscht.

Schließlich ist dafür zu sorgen, dass die Zellen unter Einzel und Gesamt mit dem Währungsformat formatiert werden (am besten zunächst die ganzen Spalten D und E, dann die beiden Datumszellen mit einem Datumsformat und die Zelle für die Rechnungsnummer mit dem Zahlenformat »0«).

Dann sollten alle nicht benutzten Tabellen entfernt werden, sodass nur noch das Formular übrig bleibt. Die Arbeitsmappe wird mit Speichern unter mit dem Namen »Rechnungsformular« und dem Dateityp Excel-Vorlage mit Makros im Vorlagenordner gespeichert.

Arbeit mit dem Rechnungsformular 

Wenn Sie mit dem Rechnungsformular arbeiten wollen, wählen Sie über das Office-Menü den Befehl Neu und im Dialogfeld Neue Arbeitsmappe das gewünschte Formular. Anschließend haben Sie eine Arbeitsmappe mit dem Namen »Rechnungsformular1« zur Verfügung, in die Sie mit Hilfe der Makros zunächst das Tagesdatum und nach Abschluss der einzelnen Positionen die Schlussberechnungen einfügen können.

Die Rechnungsvorlage im Dialog Neue Arbeitsmappe

Wenn Sie noch weitere Veränderungen an der Mustervorlage vornehmen wollen, können Sie die Mustervorlage direkt aus dem Vorlagenordner öffnen, also nicht über Neu, sondern über Öffnen.

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.