Autor: Roman

Rechnungsvorlage für KMU

Roman Allgemein , , ,

Administrative Arbeiten sind für Kleinstunternehmer oft mühsam und zeitraubend - man hat genügend andere Arbeiten zu erledigen. Ich habe hier eine Excel-Vorlage zur Erstellung von Rechnungen, die ich gerne zur Verfügung stelle. Es soll die jeweiligen Eingaben möglichst erleichtern und diverse Textbausteine werden automatisch ermittelt.

Die Datei kann unter folgendem Link aufgerufen werden:
Invoice_TEMPLATE

Um die Vorlage entsprechend zu nutzen, müsstest Du noch vereinzelte Anpassungen vornehmen. Füge beispielsweise Dein Firmenlogo ein und erfasse die wichtigsten Stammdaten auf der rechten Seite (Spalte M) auf dem Tabellenblatt "Invoice". Auf der Rechnung selbst müssen danach beim Erstellen neuer Rechnungen nur noch einzelne Änderungen vorgenommen werden, beispielsweise zu den effektiven Leistungen. Die einzelnen Texte wie Anrede oder die Einleitung kannst Du selbst auf Deine Bedürfnisse zuschneiden - die Vorlage kann beliebig umgestaltet und benutzt werden. Es steht zudem ein Tabellenblatt "Details" zur Verfügung, wo über die einzelnen Leistungen weitere Informationen offengelegt werden können - je nach Bedarf und Wunsch.

Die Spalte "Rabatt" kann, falls sie nicht benötigt wird, "entfernt" werden. Lösche die Inhalte oder wechsle die Schriftfarbe "weiss" - das ist wohl der einfachste Weg. So erhältst Du zudem mehr Platz für die Leistungsbeschreibung (z.B. durch "Zellen verbinden"). Bitte beachte, dass Du womöglich den Total-Betrag rechts von dieser Spalte neu ermitteln musst, da die Formelbezüge verloren gehen (einfach die Anzahl mit dem Preis multiplizieren).

Du kannst jederzeit weitere Positionen einfügen oder entfernen, achte einfach darauf, dass Deine Zwischensumme netto unterhalb noch den korrekten Bereich hat, damit auch alles richtig aufsummiert wird.

Sollte sich ein MWST-Satz ändern, kannst Du diese Anpassung im Hintergrund auf dem Blatt "Parameter" vornehmen.

Gruppierung reduzieren / erweitern auf gesperrtem Tabellenblatt

Roman Allgemein, VBA (Makros) , , , ,

Gruppierungen werden in Excel-Berichten eingesetzt, um bestimmte Inhalte transparent anzeigen oder ausblenden zu lassen. Es zeigt dem Leser an, dass noch weitere Informationen in der Tabelle vorhanden sind, die möglicherweise eingesehen werden sollten. Im Unterschied dazu gibt es die Möglichkeit, Zeilen oder Spalten komplett auszublenden. Dort erfordert es vom Leser mehr Aufmerksamkeit, um zu bemerken, dass Inhalte verborgen sind. Man bemerkt möglicherweise einen sprunghaften Anstieg in den Zeilennummerierungen oder den Spalten. Für welche Variante man sich entscheidet, ist natürlich situativ.

Worum es allerdings in diesem Blog im Spezifischen geht: Hast Du einmal versucht, Gruppierungen in einem geschützten und gesperrten Tabellenblatt auf- oder einzuklappen? Das ist nicht möglich und die Rechte können nicht entsprechend vergeben werden. Es sei denn, man wendet zwei Zeilen VBA-Code an. Es wird die nachfolgende Fehlermeldung angezeigt:

ErrorGroupings

Hierfür gibt es zwei Möglichkeiten, die ich nachfolgend vorstelle: Eine Schaltfläche, mit welcher der Benutzer die +/- Symbole "aktiviert" oder dass das Makro direkt beim Öffnen der Datei ausgeführt wird. Für beide Lösungsansätze musst Du zunächst in die Makro-Umgebung wechseln (ALT + F11 oder mittels Rechtsklick auf ein Tabellenblatt → "Code anzeigen"). Auf der linken Seite findest Du Deine Tabelle wieder. Mit einem Rechtsklick auf kannst Du via Einfügen → Modul alles soweit vorbereiten, um Dein Makro einzurichten. Füge dann folgenden Code ein:

Sub GruppierungenErlauben()

Sheets("MeinTabellenblatt").Protect Password:="MeinPasswort", UserInterfaceOnly:=True
Sheets("MeinTabellenblatt").EnableOutlining = True

End Sub

Ersetze bitte "MeinTabellenblatt" mit dem Namen Deines Excel-Sheets und vergebe ein Passwort, mit dem der Schutz aufzuheben ist. Mit "UserInterfaceOnly" wird lediglich die Benutzereingabe eingeschränkt; ein Makro könnte nach wie vor Änderungen in den gesperrten Bereichen vornehmen. Durch die weitere Angabe "EnableOutlining" wird die Berechtigung erteilt, auf die Gruppierungen zuzugreifen - trotz Blattschutz. Um dem Benutzer im Tabellenblatt die Möglichkeit zu geben, die Funktion zu aktivieren, füge beispielsweise ein Textfeld ein. Mit einem Rechtsklick auf dieses Objekt findest Du die Option "Makro zuweisen", woraufhin Du das Makro "GruppierungenErlauben" dem "Button" zuweisen kannst.

Makro beim Öffnen der Datei ausführen

Meiner Meinung nach schöner gelöst ist es, wenn Du die Codezeilen direkt ausführst, wenn die Datei geöffnet wird. Um dies zu bewerkstelligen, füge nicht ein neues Modul ein, sondern navigiere in der VBA-Umgebung auf "DieseArbeitsmappe" (ThisWorkbook in Englisch). Füge dort den nachfolgenden Code ein (achte bitte auf die angepasste 1. Code-Zeile):

Private Sub Workbook_Open()

Sheets("MeinTabellenblatt").Protect Password:="MeinPasswort", UserInterfaceOnly:=True
Sheets("MeinTabellenblatt").EnableOutlining = True

End Sub

So wird der Code entsprechend ausgeführt, wenn das Excel-File geöffnet wird.

Bitte beachte, dass Du die Excel-Datei als Makrodatei (.xlsm) oder Binärdatei (.xlsb) abspeichern musst, damit die Makros enthalten bleiben.

Collections in VBA

Roman VBA (Makros) , , , , , ,

Wer Daten von einer Quelle in eine Zieltabelle überträgt, kann mit den .Copy und .Paste-Methoden ein gewünschtes Resultat erzielen. Ist man jedoch häufig dazu gezwungen, zwischen Tabellenblättern oder Excel-Dateien hin und her zu wechseln, hat das Implikationen auf die Laufzeit des Makros. Eine performante Variante, um mehrere Datensätze zu übertragen, bietet der Einsatz von Collections. Beim einleitend beschriebenen Fall werden zuerst alle Datensätze der Collection hinzugefügt und daraufhin auf der Zieltabelle eingetragen. In diesem Blog wird dieses Beispiel illustriert und abschliessend eine weitere Möglichkeit aufgezeigt, wie Collections zusätzlich genutzt werden können.

Die nachfolgenden Tagesumsätze sollen auf eine zweite Tabelle übertragen werden:

CollectionUmsaetze

Natürlich könnte man alle Daten selektieren und mittels .Copy und .Paste fortschreiben, wir verwenden aber zur Übung und Illustration eine Collection dazu. Wir definieren demnach die Variable "UmsatzColl". Zudem ist die Collection zu initiieren. Verwende hierzu diese beiden Codezeilen:

Dim UmsatzColl As Collection
Set UmsatzColl = New Collection

Um beispielsweise den Wert in Zelle "C4" Deiner Collection hinzuzufügen, verwende:

UmsatzColl.Add Range("C4").Value

Man führt hinter der Collection-Variable also die Methode "Add" hinzu, gefolgt vom Bezug auf den Wert, den man zwischenspeichern möchte. Zusätzlich zum Wert könnte ein "Key" angegeben werden mit dem jederzeit auf den effektiven Wert referenziert werden kann. Hier könnte beispielsweise das Datum des Umsatzes hinzugefügt werden. Desweiteren kann man den Wert sortieren oder einordnen lassen, indem man die fakultativen "Before" oder "After"-Angaben mitgibt.

CollectionProperties

Neben "Add" stehen weitere Methoden zur Verfügung, sie werden Dir angezeigt, sobald Du "UmsatzColl." schreibst:

CollectionMethods

Man fügt Werte zur Sammlung hinzu, kann mit "Count" ausgeben, wie viele Werte in der Collection sind und mit "Remove" spezifische Einträge entfernen. Mit "Item" kann ein Wert ausgegeben werden (es ist jedoch die Standardeigenschaft und muss nicht zwingend angegeben werden) - ich werde das weiter unten aufführen.

Wir haben oben einen spezifischen Wert (Zelle "C4") hinzugefügt. Mit einer For-Schleife werden wir alle Umsätze durchgehen (Zeile 2 bis 24), die Integer-Variable "i" unterstützt diesen Loop:

For i = 2 To 24
UmsatzColl.Add Range("C" & i).Value
Next

 Um nun die Daten auf einer anderen Tabelle abzurufen, verwende ich wiederum einen For-Loop. Dabei wird wieder "i" verwendet, beginnend bei 1 bis hin zur Anzahl Items, die in der Collection sind (UmsatzColl.Count). Die Werte werden in eine Spalte A geschrieben:

For i = 1 To UmsatzColl.Count
Range("A" & i) = UmsatzColl(i)
Next

Alternativ könntest Du auch UmsatzColl.Item(i) verwenden, wie oben erwähnt - das Resultat ist dasselbe.

In der Praxis verwende ich teilweise auch mehrere Collections in Kombination. Eine "StartCopyRowColl" und "EndCopyRowColl" beispielsweise, wenn mehrere auseinanderliegende und unterschiedlich grosse Bereiche zu kopieren sind. Zusammen gibt es spannende und sehr performante Lösungen, da der Index ("i") in beiden Collections korreliert.

Ein weiterer Tipp: Du musst nicht alle Items mit "Remove" entfernen, wenn Du die Collection in der gleichen Prozedur erneut verwenden möchtest. Führe einfach die Codezeile "Set UmsatzColl = New Collection" erneut auf, dann beginnst Du mit Deiner Sammlung wieder von vorne.