Neue Funktionen für Excel und Calc

Funktionen selber machen

von Mark Lubkowitz - 05.08.2011

Der folgende Abschnitt erklärt anhand eines Beispiels, wie Sie selbst eine Excel-Funktion erstellen. Die Funktion wird aus einem bekannten Bruttobetrag und einem bekannten Steuersatz den Nettobetrag berechnen.

Das Programm, das Sie dazu nutzen, ist der Visual-Basic-Editor. Er ist in Ihrem Excel enthalten. Bevor es ans Programmieren geht, müssen Sie noch ein paar Vorbereitungen treffen: Sie nehmen in Excel eine Sicherheitseinstellung vor, öffnen den Editor und bauen dann das Grundgerüst für Ihre neue Funktion.

Starten Sie zunächst Excel. Falls Excel ohne eine leere Arbeitsmappe namens „Mappe1“ startet, klicken Sie auf „Datei, Neu…“ und wählen „Leere Arbeitsmappe“ aus.

Makro-Sicherheit

Makro-Sicherheit: Stellen Sie die Sicherheitsstufe auf „Mittel“, sonst führt Excel Ihre Funktionen nicht aus.

Alle Funktionen sind Makros. Makros können grundsätzlich Schaden an Ihrem System verursachen. Deshalb schränkt Excel die Ausführung von Makros per Vorgabe auch so weit ein, dass lediglich Makros aus vertrauenswürdigen Quellen erlaubt sind. Damit Sie die neue Funktion „NETTO()“, die Sie im Lauf des Workshops programmieren, später auch selbst benutzen können, müssen Sie die Sicherheitsstufe von Excel herabsenken. Das geht so:

Klicken Sie in Excel auf „Extras, Makro, Sicherheit…“. Wechseln Sie auf die Registerkarte „Sicherheitsstufe“. Wählen Sie die Option „Mittel“ aus und klicken Sie auf „OK“.

Wenn Sie künftig eine Excel-Arbeitsmappe mit Makros öffnen, dann erscheint ein Dialogfenster. Falls es sich um eine Arbeitsmappe mit Funktionen handelt, die Sie selbst erstellt haben — denen Sie also vertrauen —, klicken Sie auf „Makros aktivieren“.

Visual-Basic-Editor

Der Visual-Basic-Editor ist in allen Versionen von Microsoft Office enthalten. Das Programm dient zur Entwicklung von Makros und Funktionen. Sie starten den Editor in Excel über die Tastenkombination [Alt F11].

Funktionen und Makros erstellen Sie in Excel mit dem Microsoft Visual Basic-Editor.

Bis einschließlich Excel 2003 erreichen Sie den Editor über das Hauptmenü unter „Extras, Makros, Visual Basic-Editor“. Unter Microsoft Excel 2007 und 2010 wechseln Sie auf die Registerkarte „Entwickler-Tools“ und klicken links auf die Schaltfläche „Visual Basic“.

Sie können den Visual-Basic-Editor übrigens auch mit [Alt F11] starten. Die Tastenkombination funktioniert in allen Programmversionen von Excel.

Der Visual-Basic-Editor startet in einem eigenen Programmfenster. Wie es aufgebaut ist, erklärt das Bild So geht’s: Visual-Basic-Editor.

Module

Visual-Basic-Editor: Wählen Sie den Typ „Function“ und den Gültigkeitsbereich „Public“. Variablen sollten nicht statisch sein.

Funktionen sammelt Excel in Modulen. Bevor Sie eine Funktion definieren, legen Sie also im Visual-Basic-Editor ein Modul an.

Klicken Sie dazu im Projektbaum den Eintrag „VBAProject (Mappe1)“ mit der rechten Maustaste an. Wählen Sie „Einfügen, Modul“. Ein neuer Zweig namens „Module“ erscheint im Projektbaum.

Wählen Sie dann aus dem Hauptmenü „Einfügen, Prozedur…“ aus. Geben Sie in das Feld „Name“ eine Bezeichnung für Ihre künftige „Netto“-Funktion ein, beispielsweise Netto.

Sie wollen eine Funktion programmieren. Wählen Sie aus der Gruppe „Typ“ also die Option „Function“ aus. Ändern Sie die anderen Optionen nicht: Der „Gültigkeitsbereich“ muss „Public“ sein, und bei „Alle lokalen Variablen statisch“ darf kein Häkchen sein. Klicken Sie auf „OK“.

Neue Funktion im Rohbau: Die neue Funktion „NETTO()“ wurde eingefügt, besteht aber zunächst nur aus einem Kopf und einem Ende.

Der Visual-Basic-Editor erstellt nun das Grundgerüst für die Funktion „NETTO()“. Dieses Gerüst besteht aus drei Anweisungen — „Public, Function, End Function“ — und dem zuvor eingegebenen Namen „Netto“.

Klicken Sie auf das Symbol „Speichern“ in der Werkzeugleiste. Legen Sie einen Speicherort und einen Namen für die Mappe fest.

Berechnungsmethode

Der Nettobetrag entspricht 100 Prozent. Der Bruttobetrag entspricht folglich 100 Prozent plus Steuersatz, bei Waren also meistens 119 Prozent.

Beispiel: Um vom Bruttobetrag 35,70 Euro bei 19 Prozent Umsatzsteuer den Nettobetrag zu errechnen, wird der Bruttobetrag mit 119 Prozent gleichgesetzt. Nun werden die 35,70 Euro durch 119 Prozent geteilt und mit 100 Prozent multipliziert. Das ergibt den Nettobetrag 30,00 Euro. Die mathematische Grundlage ist der Dreisatz.

Parameter

Wenn Sie die Funktion „NETTO()“ später verwenden, dann werden Sie einen Bruttobetrag und einen Steuersatz eingeben. Das sind die Parameter, mit denen die Funktion rechnen soll. Damit die Funktion mit den Parametern umgehen kann, werden sie in Variablen gespeichert. Praktischerweise nennen wir diese Variablen Bruttobetrag und Steuersatz.

Die erste Zeile der Funktion sieht so aus:

Public Function Netto (Bruttobetrag, Steuersatz)

Die Funktion ist nun so erweitert, dass sie als ersten Parameter den Bruttobetrag und als zweiten den Steuersatz erwartet. Mit diesen beiden Werten lässt sich nun einfach rechnen.

Rückgabewert

Das Ergebnis, das eine Funktion zurückliefert, wird Rückgabewert genannt.

Der Rückgabewert, in diesem Beispiel der Nettobetrag, berechnet sich so:

Netto = Bruttobetrag / (100 + Steuersatz) * 100

Die Klammern bedeuten, dass zuerst der Steuersatz zu 100 addiert wird. Danach wird der Bruttobetrag durch diese Summe geteilt und mit 100 multipliziert. Das ist die Umsetzung des weiter vorn beschriebenen Dreisatzes zur Berechnung des Nettobetrags.

Operatoren

Eine Berechnung wird in der Programmierung mit Operatoren durchgeführt. Für eine Addition wird etwa der Operator „+“ verwendet, für eine Subtraktion der Operator „—“. Eine Multiplikation erfolgt mit „*“ und eine Division mit „/“. Welche weiteren Operatoren Sie bei Berechnungen einsetzen können, zeigt der Tabelle im nächsten Abschnitt „Rechnen: Operatoren für Excel und Calc“.

Wie in der Mathematik stehen links und rechts des Operators Variablen oder Werte, mit denen gerechnet werden soll.

Rechnen: Operatoren für Excel und Calc

Operatoren sind spezielle Zeichen, die in Funktionen und in Tabellenzellen Berechnungen ermöglichen. Die Übersicht zeigt die wichtigsten Operatoren.

Operator	Rechenart	Beschreibung
+	Addieren	Addiert den linken und rechten Wert
-	Subtrahieren	Subtrahiert den rechten vom linken Wert
*	Multiplizieren	Multipliziert den rechten und linken Wert
/	Dividieren	Teilt den linken durch den rechten Wert
^	Potenzieren	Potenziert den linken mit dem rechten Wert
>	Größer als	Prüft, ob der linke Wert größer als der rechte ist
<	Kleiner als	Prüft, ob der linke Wert kleiner als der rechte ist
<>	Ungleich	Prüft, ob der linke und rechte Wert ungleich sind
>=	Größer oder gleich	Prüft, ob der linke Wert größer oder gleich dem rechten ist
<=	Kleiner oder gleich	Prüft, ob der linke Wert kleiner oder gleich dem rechten ist

Operatoren für Excel und Calc

Bedingungen

Ihre Funktion arbeitet bis hierher einwandfrei, es kann aber zu einem Problem kommen: Wenn als Steuersatz versehentlich ein negativer Wert übergeben wird, dann versucht die Funktion unter Umständen, den Bruttobetrag durch 0 zu teilen. Und das führt zu einem Fehler.

Um zu verhindern, dass dieses Problem überhaupt auftritt, und um gleichzeitig alle negativen Werte als Steuersatz auszuschließen, müssen Sie folgende Bedingung prüfen: Der Wert der Variablen Steuersatz muss größer als 0 sein.

Die um die Bedingungsprüfung erweiterte Funktion sieht nun etwas komplexer aus. Sie prüft in der Zeile „If Steuersatz > 0 Then“, ob der Steuersatz größer als 0 ist. Falls ja, führt sie die Berechnung in der danach folgenden Zeile aus.

Public Function Netto (Bruttobetrag, Steuersatz)
If Steuersatz > 0 Then
Netto = Bruttobetrag / (100 + Steuersatz) * 100
Else
Netto = CVErr(xlErrValue)
End If
End Function

Wenn der Steuersatz kleiner oder gleich 0 ist, dann wird die Zeile ausgeführt, die nach „Else“ folgt. Hier wird als Ergebnis der Funktion ein Fehler festgelegt. In der Excel-Zelle wird später der Hinweis „#WERT!“ erscheinen anstelle einer Zahl.

Einsetzen

Netto berechnen: Der Befehl „=NETTO(A4;B4)“ rechnet 19 Prozent MwSt. aus 43,41 heraus.

Um die Funktion in Excel zu verwenden, geben Sie etwa in Zelle „A4“ einen Bruttobetrag ein. Daneben in „B4“ schreiben Sie den Mehrwertsteuersatz 19. Geben Sie jetzt in Zelle „C4“ die Funktion =NETTO(A4;B4) ein und drücken Sie die Eingabetaste.

Ihre importierten und selbst erstellten Funktionen finden Sie auch über den Menübefehl „Einfügen, Funktion…“. Wählen Sie dort die Kategorie „Benutzerdefiniert“. Neue Funktionen wie „=NETTO()“ oder „OSTERSONNTAG()“ werden jetzt aufgelistet.

Als Add-in speichern

Add-in mit Excel erstellen: Geben Sie einen Titel und einen Kommentar ein. Beides wird beim Import im Add-in-Manager angezeigt.

Speichern Sie Ihre Arbeitsmappen mit den eigenen Funktionen immer zuerst als normale Excel-Datei. Falls Sie die Funktionen einmal an andere weitergeben möchten, dann speichern Sie die Arbeitsmappen zusätzlich im Add-in-Format mit der Erweiterung XLA.

Öffnen Sie die Arbeitsmappe. Klicken Sie zunächst auf „Datei, Eigenschaften”. Wechseln Sie auf die Registerkarte „Zusammenfassung”. Geben Sie in die Felder „Titel” und „Kommentare” passende Beschreibungen für die Excel-Funktionen und somit für das Add-in-Paket ein. Diese Informationen werden später im Add-in-Manager angezeigt. Klicken Sie auf „OK”.

Klicken Sie dann auch „Speichern unter…“. Wählen Sie als „Dateityp“ die Option „Microsoft Office Excel-Add-in (*.xla)“ aus. Klicken Sie auf „Speichern“.

Seite