16. Funktionen
Eine benutzerdefinierte
Funktion (bF) wird genauso angewendet wie eine in Excel bereits
integrierte Tabellenfunktion, z.B. SUMME oder MITTELWERT, wobei
man selbst bestimmt, welche Aufgaben die Funktion erfüllt.
Umfangreiche bzw. verschachtelte Tabellenformeln oder eine Gruppe
von Formeln können durch eine einzige bF ersetzt werden, welche
wesentlich einprägsamer und effizienter einzusetzen sind.
16.1
Wirkungsweise einer benutzerdefinierten Funktion
Erstellt werden bF in einem Visual Basic-Modul,
indem Sie mathematische Ausdrücke, integrierte Microsoft Excel-Funktionen
sowie Visual-Basic-Codes miteinander verbinden. Anschließend
stellt man der bF die erforderlichen Werte zur Verfügung. Die
Funktion führt die Berechnungen auf Basis dieser Werte durch und
liefert einen Wert.
Anmerkung: Eine bF kann nicht nur auf
Zahlen und mathematische Ausdrücke, sondern auch auf Text,
Datums- und sonstige Werte angewandt werden.
16.2. Unterschiede zum Makro
Aufgezeichnete Makros | Benutzerdefinierte Funktionen |
Führen eine Aktion aus, z.B. Erstellen eines Diagramms oder Bewegen von Zellen. | Liefert einen Rückgabewert an das Hauptprogramm ; führen i.d.R. keine Aktionen aus. |
Können aufgezeichnet werden. | Müssen in einem Visual Basic-Modul erstellt werden. |
Werden von den Schlüsselwörtern Sub und End Sub eingeschlossen. | Werden von den Schlüsselwörtern Function und End Function eingeschlossen. |
16.3 Die Bestandteile einer
benutzerdefinierten Funktion
Die Funktion Gewinn errechnet den Bruttogewinn aus der Anzahl verkaufter Produkte, den Herstellungskosten pro Stück und dem erzielten Stückpreis. Die Funktion enthält somit 3 Argumente (VerkaufteStückzahl, Kosten und Stückpreis) sowie einen mathematischen Ausdruck.
Bestandteile:
16.4. Verstehen der Bestandteile
einer benutzerdefinierten Funktion
Argumente
Argumente in benutzerdefinierten Funktionen sind
mit den Namen vergleichbar, mit denen Sie Zellen in Tabellen
definieren. Ein Argument ist ein Variablentyp (Erklärung folgt
später). Eine Variable ist ein Name, der einen Wert darstellt.
Argumente und Variablen sind die Grundlage für leicht verständliche
Ausdrücke in benutzerdefinierten Funktionen.
Mit der folgenden
Funktion können Sie den Gewinn nach Abzug der Steuern (Reingewinn)
berechnen.
Die Argumente der Funktion sind: VerkaufteStückzahl, Kosten, Stückpreis
sowie Einkommensteuersatz. Die
Variablen sind: Bruttogewinn und Reingewinn, wobei es sich bei
Reingewinn um eine besondere Variable handelt, da sie den Rückgabewert
enthält.
Function Reingewinn(VerkaufteStückzahl,
Kosten, Stückpreis, Steuersatz)
Bruttogewinn = VerkaufteStückzahl * (Stückpreis - Kosten)
Reingewinn = Bruttogewinn * (1 - Steuersatz)
End Function
Der Ausdruck, der den Bruttogewinn berechnet, verwendet 3
Argumente der Funktion. Das Ergebnis des Ausdrucks wird in der
Variablen Bruttogewinn gespeichert. Der den Reingewinn
berechnende Ausdruck verwendet ein Argument sowie die Variable
Bruttogewinn; dieses Ergebnis wird in Reingewinn gespeichert.
Sie können auch benutzerdefinierte Funktionen erstellen, die
keine Argumente benötigen, beispielsweise einen Wert einer Zelle
oder die Tageszeit als Ausgangsdaten. Die beiden integrierten
Tabellenfunktionen ZUFALLSZAHL und JETZT benötigen
beispielsweise keine Argumente.
Visual Basic-Ausdrücke
Ausdrücke sind mit Formeln vergleichbar, die man
in Tabellen eingibt.
Tabellenformeln werden in Zellen eingegeben | Ausdrücke werden in ein Visual Basic-Modul eingegeben |
Tabellenformeln beginnen mit einem Gleichheitszeichen, und das Ergebnis wird in die Zelle übertragen, die die Formel enthält | Visual Basic-Ausdrücken werden eine Variable und ein Gleichheitszeichen vorangestellt. Die Variable auf der linken Seite der Gleichung ist der Bestimmungs- bzw. Speicherort des auf der rechten Gleichungsseite errechneten Wertes = Wertzuweisung |
Visual Basic-Anweisungen, z.B. Wenn, Für und Durchlaufe können verwendet werden. |
Diese nächste
Funktion errechnet die Provision, die ein Wertpapierhändler
einer Bank beim Verkauf von Aktien erhält, wobei die
Provisionsrate je nach gesamtem Verkaufspreis variiert. Die
normale Provisionsrate beträgt 25 € plus 0,30 € pro Aktie. Bei
Transaktionen, die 15.000 € übersteigen, wird jedoch ein 10%iger
Mengenrabatt gewährt, so dass lediglich 90 % der verkauften
Aktien in die Provisionsberechnung einfließen.
Function MaklerProvision(VerkaufteAktien,
PreisJeAktie)
GesamtVerkaufspreis = VerkaufteAktien * PreisJeAktie
If GesamtVerkaufspreis <=
15000 Then MaklerProvision = 25 + 0.3
* VerkaufteAktien
Else: MaklerProvision = 25 + 0.3
* (0.9 * VerkaufteAktien)
End If
End Function
Rückgabewerte
Ein Rückgabewert wird angegeben, indem Sie hinter
dem Namen einer benutzerdefinierten Funktion einen Wert oder
einen mathematischen Ausdruck eingeben. Mindestens eine Zeile in
der Funktion muss die Form NameDerFunktion = Ausdruck
aufweisen.
16.5. Erstellen einer benutzerdefinierten Funktion
Visual-Basic-Editor aufrufen
16.6. Verwenden einer
benutzerdefinierten Funktion
Eine bF wird genauso verwendet wie eine integrierte Microsoft Excel-Funktion.
16.7. Gültigkeitsbereich von Prozeduren und Funktionen
Prozeduren und Funktionen sind standardmäßig Öffentlich, wenn sie nicht explizit mit Privat festgelegt werden. Wird (zusätzlich) Statisch angegeben, so bleiben die Werte lokaler Variablen zwischen den Aufrufen der Funktion oder Prozedur erhalten.
Teil | Beschreibung |
Public Function oder Sub | Die Funktion/Prozedur kann in jedem Modulblatt eines Projektes angesprochen werden. |
Private | Auf die Funktions/Prozedur kann nur durch andere Prozeduren aus dem Modul zugegriffen werden, in dem sie selbst steht. |
Static | Alle innerhalb der Funktion/Prozedur geschaffenen (lokalen) Variablen behalten nach Abarbeiten der Funktion ihren Wert bei |
Eine Anweisung ist die kleinste Einheit von Code und Operatoren, die eine Aktion, Definition oder Deklaration ermöglichen. Beispiele dafür sind:
Wert1=5 |
Zuweisung eines Wertes an eine Variable |
Produkt=Wert1*Wert2 |
Multiplikation zweiter Werte mit Operators "*"; Zuweisung des Ergebnisses an eine Variable |
x=Application.Max(5,12) |
Aufruf der EXCEL-Funktion "Max"; Übergabe zweier Werte; Zuweisung des Ergebnisses an eine Variable |
Open "TESTDAT.XLS" |
Anweisung, die Datei zu öffnen |
Unterprozedur Wert1, Wert2 |
Starten einer Prozedur und Übergabe von zwei Werten an diese |
y=MeineFunktion(a,b,c) |
Aufrufen einer Funktion und Übergabe von drei Werten an diese; Zuweisen des Ergebnisses der Berechnungen innerhalb der Funktion an eine Variable |
DIM MeineVariable As Integer |
Deklaration eines Variablennamens und Zuweisen eines Datentyps an die Variable |
Mehrere Anweisungen pro Zeile macht Code unübersichtlich, es ist aber möglich, wenn Sie nach jeder Anweisung einen Doppelpunkt setzen.
Wert1 = 5: Wert2 = 8: Produkt = Wert1 * Wert2
Anweisungen, die wegen ihrer Länge mehr als eine Zeile Code brauchen am Ende ein Leerzeichen gefolgt von einem Unterstrich.
Unterprozedur Wert1, _ Wert2
Function Bruttogewinn(VerkaufteStückzahl,
Kosten, Stückpreis)
Bruttogewinn = VerkaufteStückzahl * (Stückpreis - Kosten)
End Function
Function Reingewinn(VerkaufteStückzahl,
Kosten, Stückpreis, Steuersatz)
Bruttogewinn = VerkaufteStückzahl * (Stückpreis - Kosten)
Reingewinn = Bruttogewinn * (1 - Steuersatz)
End Function
Function MaklerProvision(VerkaufteAktien,
PreisJeAktie)
GesamtVerkaufspreis = VerkaufteAktien * PreisJeAktie
If GesamtVerkaufspreis <=
15000 Then MaklerProvision = 25 + 0.3
* VerkaufteAktien
Else: MaklerProvision = 25 + 0.3
* (0.9 * VerkaufteAktien)
End If
End Function
Function Provision(Aktienanzahl,
Aktienpreis)
Gesamtpreis = Aktienanzahl * Aktienpreis
If Gesamtpreis <= 4000 Then
Provision = Gesamtpreis * 0.2
Else: Provision = Gesamtpreis *
0.3
End If
End Function
Function SichereWurzel(Number)
TempWert = Abs(Number)
SichereWurzel = Sqr(TempWert)
End Function
5 | 3 | 4 |