VBA Excel

Benutzerdefinierte Funktionen

Oft gibt es in Kalkulationsblättern Berechnungen welche immer wieder identisch durchgeführt werden müssen. Eine spezielle Umsatzberechnung, eine Provisionsberechnung, eine Zeitumrechnung und gerade für Ihre Berechnung kennt Excel keine Funktion. In solch einem Fall kann eine benutzerdefinierte Funktion hergestellt werden.

Die Grundliegende Syntax ist:

Function Funktionsname(Übergabewert)
   Anweisungen
   Funktionsname=Variable
End Function

Konkret:

• Wählen Sie in Excel im Menü Extras Makros->Visual Basic Editor
• Wählen Sie im Menü Einfügen-->Modul
• Schreiben Sie nun folgende Programmcode in Ihr Modul (Vergleiche auch den Punkt Entwicklungsumgebung).

function MeineMultiplikation(Basis,Faktor)
   MeineMultiplikation=Basis*Faktor
End Function

Damit ist eine erste benutzerdefinierte Funktion geschrieben.

In Excel werden Sie nun mit dem Funktionsassistenten in der Kategorie Benutzerdefiniert die Funktion MeineMultiplikation finden welche zwei Werte als Eingabe erwartet.
Die Erstellung eines Hilfetextes für die Funktion ist weiter unten beschrieben.

Die Beschriftung der beiden Parameter "Basis" und "Faktor" erscheint aufgrund der Angaben in den Klammern der Funktion.
Das Erstellen eines Hilfstextes für diese Parameter ist nur mit dem Einsatz eines Help-Files möglich.

Dieses einfachste erste Funktion soll zeigen wie die Datenrückgabe aus einer Funktion aufgebaut ist. Der Name der Funktion bildet den Rückgabewert der Funktion.

Aufruf einer Funktion

Benutzerdefinierte Funktionen können wie folgt aufgerufen werden:

• Von einer Zelle als Funktion, z.B. mit dem Funktionsassistenten (oben beschrieben)
• Als Unterfunktion von einer anderen Funktion
• Als Unterfunktion von einer Prozedur

Rückgabewert = Funktionsname(Parameter 1[,Parameter n])

Dokumentieren der Funktion

Um für die Funktion einen Hilfetext zu erstellen gehen Sie wie folgt vor:

1. Klicken Sie auf das Symbol Objekt-Katalog
2. Wählen Sie Ihr VBA-Projekt um die benutzerdefinierten Funktionen aufgelistet zu bekommen.
3. Klicken Sie mit der rechten Maustaste auf Ihre Funktion und wählen den Kontext "Eigenschaften".
4. Erfassen Sie nun den Hilfetext für die Funktion wie er im Funktionsassistenen erscheinen soll.

Die Erstellung weiterer Hilfe zu den Variabeln benötigt den Hilfecompiler (HC31.EXE) von Microsoft welcher beispielsweise in VB enthalten ist.
Bequemere Versionen des HC31 werden zum Beispiel mit der Software Doc2Help im Internet angeboten. Eine Demoversion ist verfügbar.

Übung Funktion Bruttogewinn

Funktion Bruttogewinn(VerkaufteStückzahl;Kosten;Stückpreis)
   Bruttogewinn=VerkaufteStückzahl*(Stückpreis-Kosten)
Ende Funktion

Übung Funktion Reingewinn

Funktion Reingewinn(VerkaufteStückzahl;Kosten;Stückpreis;Steuersatz)
   Bruttogewinn=VerkaufteStückzahl*(Stückpreis-Kosten)
   Reingewinn=Bruttogewinn*(1-Steuersatz)
Ende Funktion

Übung Funktion Provision

Function Provision(VerkaufteAktien;PreisJeAktie)
   GesamtVerkaufspreis=VerkaufteAktien*PreisJeAktie
   If GesamtVerkaufspreis<=1500 then
      Provision=25+0.3*VerkaufteAktien
   else
      Provision=25+0.3*(0.9*VerkaufteAktien)
   End if
End Function

Übung Funktion Steuer

' Deklaration von Konstanten
const KEHRSTEUERSATZ=0.28
const ZUSCHLAGSGRENZE=1000000
const STEUERZUSCHLAG=0.1

Function Steuer(Stückzahl;KaufpreisAktie;VerkaufspreisAktie)
   Gewinn=Stückzahl*(VerkaufspreisAktie-KaufpreisAktie)
   If Gewinn<=0 then
      Steuer=0
   ElseIf Gewinn>ZUSCHLAGSGRENZE then
      Steuer=(KEHRSTEUERSATZ*Gewinn)+_
      (Gewinn-ZUSCHALGSGRENZE)*STEUERZUSCHLAG
   else
      Steuer=Gewinn+KEHRSTEUERSATZ
   end If
End Function

Übung Funktion Aktienverkauf

Function Aktienverkauf(Kaufpreis;AnzahlDerAktien;Verkaufspreis)
   Bruttogewinn=AnzahlDerAktien*(Verkaufspreis-Kaufpreis)
   MeineProvision=Provision(AnzahlDerAktien;Verkaufspreis)
   MeineSteuer=Steuer(AnzahlDerAktien;Kaufpreis;Verkaufspreis)
   Aktienverkauf=Bruttogewinn-MeineProvision-MeineSteuer
End Function

Beachten Sie dass Funktionen und Variablen keine Redundanten Namen bekommen dürfen. Dies ist in obigen Beispielen nicht gewährleistet!