Excel-Functions mit VBA @ PS-Trainer

Excel bietet ihnen eine grosse Anzahl nützliche Funktionen. Gelegentlich benötigen sie jedoch Funktionen, die Excel nicht oder nicht genauso enthält, wie sie es brauchen. Dann können sie mit VBA eigene Functions programmieren.
Das erfordert allerdings einige grundlegende Programmier-Kenntnisse.

Visual Basic for Applications (VBA) ist eine Adaptierung der Programmiersprache von Microsoft. Gegenüber VB sind hier die Objekte und Methoden der Tabellenkalkulation enthalten - Sie können daher Tabellen, Bereiche, Zellen und deren Werte verwenden und nach Belieben beeinflussen.

Das Ziel: BenutzerInnen-Definierte Funktionen:
Sie sollten die Verwendung der Standard-Funktionen beherrschen, bevor sie beginnen, eigene Functions zu programmieren. Es ist immer noch schneller und einfacher, eine vorhandene Funktion anzuwenden, als eine eigene zu programmieren und einzusetzen.

Stellen sie sich vor, dass sie den Befehl Einfügen / Funktion anwenden und unter Funktionskategorie=BenutzerInnenDefiniert ihre eigenen Functions angeführt finden. Das ist das Ziel dieser Webseite.

Planung der eigenen Function myMWSt:
Die Mehrwertsteuer bezahlt niemand gerne. Die Funktionen zur MWSt sind jedoch besonders einfach zu programmieren und eignen sich gut zum Training. Machen sie nie den Fehler, die MWSt. als fixen %Satz zu programmieren - der MWSt-Satz kann und wird sich ändern !

Der MWSt-Satz von 16% beträgt 0,16, jener von 20% beträgt 0,2.
Für 0<MWSt-Satz<1 gilt:
MWSt = Nettopreis * MWStSatz
Bruttopreis = Nettopreis * (1+MWStSatz)
Nettopreis = Bruttopreis / (1+MWStSatz)
Ziel: Programmieren sie die Funktionen myMWSt, myBrutto, myNetto.
In dieser Darstellung steht das Ergebnis links vom = , rechts finden sie die Argumente (Nettopreis, MWStSatz bzw. Bruttopreis,MWStSatz) und die Anleitung zur Berechnung.

Aus der Erfahrung mit den Standard-Funktionen wissen sie, dass man den Funktionen Werte zur Berechnung übergeben kann, sog. Argumente. Sie werden daher die benötigten Argumente (MWStSatz, Nettopreis, Bruttopreis, ...) an die Function übergeben.

Wenn die Argumente bekannt sind, wissen sie, was auszurechnen ist. Der nächste Schritt ihrer Funktionen ist daher die Berechnung laut angeführter Formel.

Durch Berechnung erhalten sie ein Ergebnis. Dieses müssen sie an Excel zurückgeben, damit der "Funktonswert" im Tabellenblatt angezeigt werden kann.

Programmierung der eigenen Function myMWSt :

Zuerst benötigen sie ein VBA (Visual Basi for Applications)-Project, in das sie ihre eigenen Funktionen eintragen können, zunächst nur zum Test. Das VBA-Fenster sehen sie u.a. mit Befehl Alt-F11 aus Excel.
Ein Project erzeugen sie z.B. beim Mitschreiben eines Macros. Typische Project-Namen sind VBAProject(Mappe 1), wenn ihr Arbeitsblatt noch nicht gespeichrt wurde.

Eine eigene Function beginnen sie durch Eingabe der Zeile

Function myMWSt(NettoPreis as Double,MWStSatz as Double)

Function erzeugt nach endgültiger Eingabe praktischerweise auch gleich End Function. Bis dorthin reicht ihre Function.
myMWSt ist der Name ihrer eigenen Function.
NettoPreis und MWStSatz sind die Argumente, die von Excel an ihre VBA-Function übergeben werden - die werden zur Berechnung benötigt !
Mehrere Argumente werden durch Beistriche getrennt.
Double ist eine bestimmte Type von Argument, nämlich Gleitkommazahlen hoher Genauigkeit. (Anm.: es gibt auch andere Typen, wie z.B. Ganze Zahlen, Texte, Zellen und Bereiche, ...)

Nun können sie die MWSt berechnen:

Function myMWSt(NettoPreis as Double, MWStSatz as Double)
x = NettoPreis * MWStSatz
End Function

x ist eine Variable: ein Behälter, der im Laufe der Berechnung unterschiedliche Werte speichern kann.

Die Berechnung ist fertig, jetzt muss nur noch das Ergebnis x an Excel zurückgegeben werden:

Function myMWSt(NettoPreis as Double, MWStSatz as Double)
x = NettoPreis * MWStSatz
myMWSt = x
End Function

Mit der "Zuweisung" eines Wertes an die Function wird der jeweilige Wert an Excel zurückgegeben und im Tabellenblatt als Ergebnis ihrer Function angezeigt.
Ihre Function ist damit komplett und kann getestet werden:

Test der programmierten Function:

Test ihrer Function myMWSt:
Rechts sehen sie als Illustration screenshots von einem kleinen Test-Beispiel. Geben sie in Zellen ihres Arbeitsblatts beliebige Zahlen für den Nettopreis (z.B. 123,00 in B5) und für die MWSt (z.B. 0,2 oder 20% in C2) ein.
Achtung: der Text "MWSt-Satz=" in B2 ist nur eine Überschrift für das menschliche Verständnis, wirkt sich auf die Berechnung nicht aus !
Markieren sie die Zelle rechts neben dem obersten Nettopreis (z.B. C5) und fügen sie ihre eigene Function ein: Befehl
Einfügen / Funktion / Funktionskategorie=Benutzerdefiniert / myMWSt.
Der eingebaute Funktions-Assistent öffnet sein Hilfsfenster und zeigt ihnen die Eingabefelder für Nettopreis und MWStSatz. Klicken sie die Zellen, in denen sich die betreffenden Werte befinden (z.B. B5 und C2).
Bei Abschluss der Eingabe wird das Ergebnis berechnet und angezeigt. Bei jeder Änderung des Arbeitsblattes wird der Funktionswert erneut berechnet. - Ihre erste VBA-Funktion ist programmiert und in Betrieb !

Programmieren sie analog zu ihrer ersten VBA-Funktion myMWSt die weiteren Funktionen myBrutto und myNetto.
Das Zeichen für "Division" ist ein Schrägstrich /.
Testen sie alle Funktionen sorgfältig. Die Berechnung von Netto aus Brutto und MWSt führen sie rechts neben der Brutto-Spalte durch. Sie muss in jeder Zeile die gleichen Netto-Werte berechnen, wie von ihnen eingegeben.

Meistens wenden sie die MWSt-Funktionen auf eine ganze Spalte von Nettopreisen an. Der MWSt-Satz dagegen wird nur in eine einzige Zelle geschrieben. In diesem Fall müssem sie die Zellen-Adresse des MWSt-Satzes absolut adressieren (d.h. $C$2 an Stelle von C2), damit der MWSt-Satz immer aus dieser Zelle entnommen wird, nicht aus anderen Zellen, die sich durch Ausfüllen nach unten oder nach rechts ergeben.
Beispiel: Das fertige Kalkulationsblatt sieht z.B. so aus:
Normalansicht der Lösung

Mit Befehl Extras / Optionen / Ansicht / Formeln sehen sie die Funktionen:
Functions-Ansicht der Lösung

Erstellen einer Funktionsbibliothek:
Ihre selbst erstellten Functions werden zwar zusammen mit dem Arbeitsblatt gespeichert, sind jedoch in anderen Arbeitsblättern nicht verfügbar. Damit sie selbst erstellte Functions in jedem Arbeitsblatt verwenden können, müssen sie diese in eine Funktionsbibliothek integrieren, die bei Excel Add-In heisst:

Kommentieren: Öffnen sie das VBA-Arbeitsblatt (z.B. mit Alt-F11) und bringen sie das von ihnen programmierte Modul nach vorne. Tragen sie zu Beginn als Kommentar ein: Titel, Name, Datum, Version.
Kommentare beginnen mit einem einfachen Hochkomma (single quote). Sie dienen der Lesbarkeit durch Menschen, und wirken sich auf die Programmierung nicht aus. (Kontrolle: Kommentare sind im VBA-Fenster farblich vom Programm abgehoben)

Speichern sie den Quelltext (source code) ihrer VBA-Programme mit Befehl Datei / Daten exportieren als *.bas . Dieser Schritt ist nicht für die Anwendung, sondern "nur" für die Dokumentation notwendig. Wenn sie Erfahrungen zur VBA-Programmierung austauschen (z.B. in Internet-Foren) , dann brauchen sie diesen Code als Text.

Klicken sie in ihr Excel-Arbeitsblatt. Die Bibliothek wird hier als Excel-verwendbares Add-In gespeichert. Befehl
Datei / Speichern unter / Dateityp=Excel Add-In (*.xla) .
Sichern (!) und schliessen sie nun ihr Entwicklungs-Arbeitsblatt und öffnen sie zum Test ein neues, leeres Arbeitsblatt.

Installation und Test einer Funktionsbibliothek:
Ihre selbst erstellte Add-In Bibliothek wird genauso installiert wie jedes andere der unzähligen erhältlichen Add-In Module:

Sichern und schliessen sie ihr Entwicklungs-Arbeitsblatt und öffnen sie zum Test ein neues, leeres Arbeitsblatt.

Mit Hilfe des Add-In Managers bauen sie ihre eigene Function-Library in das Programm Excel ein: Befehl
Extras / Add-Ins Manager : sie sehen die Liste der bekannten Add-Ins, die momentan verwendeten sind angekreuzt. Mit Klick auf Durchsuchen öffnen sie das übliche Datei-Suchfenster. Suchen sie ihre *.vba-Datei.
Schliessen sie den Add-In Manager, wenn ihre Bibliothek angekreuzt in der Liste aufscheint.
Ab nun ist das Add-In Modul nicht nur in diesem Arbeitsblatt, sondern in jedem (alten und neuen) Arbeitsblatt verfügbar, das sie öffnen.

Tragen sie die MWSt. und einige Nettopreise in ihr Test-Arbeitsblatt ein, dann kommt ihre eigene Function: Befehl Einfügen / Funktion / Funktionskategorie=Benutzerdefiniert / Name der Funktion=myMWSt
Geben sie die Argumente für MWSt-Satz und Nettopreis ein und bestätigen sie die Eingabe:
Ihre eigene Function-Bibliothek ist fertig und verwendbar.

Handhabung von Add-Ins: Im Add-In Manager können sie ihre Function-Bibliothek wieder abschalten ("wegklicken"). Damit kann man ganze Bibliotheken mit vielen Functions rasch ein- und abschalten. Sie können daher je nach Zweck der Excel-Anwendung spezielle Bibliotheken dazu- oder wegschalten.

Es gibt sehr viele solcher Add-Ins für eine grosse Anzahl von Arbeitsgebieten, viele davon kostenlos im Internet. Diese Add-Ins können sie mit dem Manager genauso hinzufügen wie ihre eigenen Bibliotheken. Umgekehrt können sie ihre selbst programmierten Bibliotheken via mail als Anlage-Datei versenden.

Aktuelle Daten dieser Seite	Letzte Änderung: