In diesem Tutorial lernen wir die Excel VBA-Funktion kennen
1) Was ist Visual Basic in Excel?
2) Wie verwende ich VBA in Excel?
3) Wie erstelle ich eine benutzerdefinierte Funktion?
4) Wie schreibe ich ein Makro?
Wie schreibe ich VBA-Code?
Excel bietet dem Benutzer eine große Sammlung vorgefertigter Funktionen, mehr als genug, um den durchschnittlichen Benutzer zufrieden zu stellen. Viele weitere können hinzugefügt werden, indem Sie die verschiedenen verfügbaren Add-Ins installieren. Die meisten Berechnungen können mit den bereitgestellten Mitteln durchgeführt werden, aber es dauert nicht lange, bis Sie sich wünschen, dass es eine Funktion gibt, die eine bestimmte Aufgabe erfüllt, und Sie können in der Liste nichts Passendes finden. Sie benötigen eine UDF. Eine UDF (User Defined Function) ist einfach eine Funktion, die Sie mit VBA selbst erstellen. UDFs werden oft als "Custom Functions" bezeichnet. Eine UDF kann in einem an eine Arbeitsmappe angehängten Codemodul verbleiben. In diesem Fall ist sie immer verfügbar, wenn diese Arbeitsmappe geöffnet ist. Alternativ können Sie ein eigenes Add-In erstellen, das eine oder mehrere Funktionen enthält, die Sie wie ein kommerzielles Add-In in Excel installieren können. Auf UDFs kann auch von Codemodulen zugegriffen werden. Häufig werden UDFs von Entwicklern erstellt, um ausschließlich innerhalb des Codes einer VBA-Prozedur zu arbeiten, und der Benutzer ist sich ihrer Existenz nie bewusst. Wie jede Funktion kann die UDF so einfach oder komplex sein, wie Sie möchten. Fangen wir mit einem einfachen an…
Eine Funktion zur Berechnung der Fläche eines Rechtecks
Ja, ich weiß, du könntest das in deinem Kopf tun! Das Konzept ist sehr einfach, sodass Sie sich auf die Technik konzentrieren können. Angenommen, Sie benötigen eine Funktion, um die Fläche eines Rechtecks zu berechnen. Sie durchsuchen die Funktionssammlung von Excel, aber es gibt keine passende. Dies ist die durchzuführende Berechnung:
FLÄCHE = LÄNGE x BREITE
Öffnen Sie eine neue Arbeitsmappe und öffnen Sie dann den Visual Basic-Editor (Extras > Makro > Visual Basic-Editor oder ALT+F11).
Sie benötigen ein Modul, in das Sie Ihre Funktion schreiben können, also wählen Sie Einfügen > Modul. In den leeren Modultyp: Funktionsbereich und drücke EINTRETEN.Der Visual Basic Editor vervollständigt die Zeile für Sie und fügt eine End Function-Zeile hinzu, als ob Sie eine Subroutine erstellen würden. Bisher sieht es so aus…
Funktionsbereich() Funktion beenden
Platzieren Sie Ihren Cursor zwischen den Klammern hinter "Bereich". Wenn Sie sich jemals gefragt haben, wozu die Klammern dienen, werden Sie es gleich herausfinden! Wir werden die "Argumente" spezifizieren, die unsere Funktion annehmen wird (und Streit ist eine Information, die für die Berechnung benötigt wird). Typ Länge doppelt, Breite doppelt und klicken Sie in die leere Zeile darunter. Beachten Sie, dass während der Eingabe ein Bildlauffeld angezeigt wird, in dem alle für Ihre Eingabe geeigneten Dinge aufgelistet sind.
Diese Funktion heißt Mitglieder automatisch auflisten. Wenn es auch nicht erscheint, ist es ausgeschaltet (schalten Sie es ein um Extras > Optionen > Editor) oder Sie haben sich möglicherweise zuvor einen Tippfehler gemacht. Es ist eine sehr nützliche Überprüfung Ihrer Syntax. Suchen Sie das gewünschte Element und doppelklicken Sie darauf, um es in Ihren Code einzufügen. Sie können es ignorieren und einfach eingeben, wenn Sie möchten. Dein Code sieht jetzt so aus…
Funktionsbereich (Länge als Doppelt, Breite als Doppelt) Endfunktion
Die Angabe des Datentyps der Argumente ist nicht obligatorisch, aber sinnvoll. Du hättest tippen können Länge Breite und belassen Sie es so, aber wenn Sie Excel darüber informieren, welcher Datentyp zu erwarten ist, wird Ihr Code schneller ausgeführt und Fehler bei der Eingabe werden erkannt. Die doppelt Datentyp bezieht sich auf Zahl (die sehr groß sein kann) und erlaubt Brüche. Nun zur Berechnung selbst. Drücken Sie in der leeren Zeile zuerst die TAB Taste, um Ihren Code einzurücken (erleichtert das Lesen) und eingeben Fläche = Länge * Breite. Hier ist der fertige Code…
Funktionsbereich (Länge als Doppelt, Breite als Doppelt) Bereich = Länge * Breite Endfunktion
Sie werden feststellen, dass während der Eingabe eine weitere Hilfefunktion des Visual Basic-Editors angezeigt wird. Auto-Schnellinfo…
Es ist hier nicht relevant. Sein Zweck besteht darin, Ihnen beim Schreiben von Funktionen in VBA zu helfen, indem er Ihnen mitteilt, welche Argumente erforderlich sind. Sie können Ihre Funktion gleich testen. Wechseln Sie in das Excel-Fenster und geben Sie die Zahlen für Länge und Breite in separate Zellen ein. Geben Sie in einer dritten Zelle Ihre Funktion ein, als wäre es eine der integrierten. In diesem Beispiel enthält Zelle A1 die Länge (17) und Zelle B1 die Breite (6.5). In C1 habe ich getippt =Bereich(A1,B1) und die neue Funktion berechnete die Fläche (110.5)…
Manchmal können die Argumente einer Funktion optional sein. In diesem Beispiel könnten wir die Breite Argument optional. Angenommen, das Rechteck ist ein Quadrat mit gleicher Länge und Breite. Um dem Benutzer die Eingabe von zwei Argumenten zu ersparen, könnten wir ihn nur die Länge eingeben lassen und die Funktion diesen Wert zweimal verwenden lassen (d. h. Länge x Länge multiplizieren). Damit die Funktion weiß, wann sie dies tun kann, müssen wir ein IF-Anweisung bei der Entscheidung helfen. Ändern Sie den Code so, dass er so aussieht…
Funktionsfläche (Länge als Double, optionale Breite als Variante) If IsMissing(Breite) Then Fläche = Länge * Länge Sonst Fläche = Länge * Breite Ende Wenn Endfunktion
Beachten Sie, dass der Datentyp für Breite geändert wurde in Variante um Nullwerte zuzulassen. Die Funktion ermöglicht es dem Benutzer nun, nur ein Argument einzugeben, z.B. =Fläche(A1).Die IF-Anweisung in der Funktion prüft, ob das Argument Width angegeben wurde und berechnet entsprechend…
Eine Funktion zur Berechnung des Kraftstoffverbrauchs
Ich kontrolliere gerne den Kraftstoffverbrauch meines Autos. Wenn ich Kraftstoff kaufe, notiere ich mir den Kilometerstand und die Kraftstoffmenge, die zum Auffüllen des Tanks benötigt wird. Hier in Großbritannien wird Kraftstoff in Litern verkauft. Der Kilometerzähler des Autos (OK, also ein Kilometerzähler) zeichnet die Entfernung in Meilen auf. Und weil ich zu alt und zu dumm zum Wechseln bin, verstehe ich nur MPG (Meilen pro Gallone). Nun, wenn Sie denken, dass das alles ein bisschen traurig ist, wie wäre es damit. Wenn ich nach Hause komme, öffne ich Excel und gebe die Daten in ein Arbeitsblatt ein, das den MPG für mich berechnet und die Leistung des Autos aufzeichnet. Die Berechnung ist die Anzahl der Kilometer, die das Auto seit dem letzten Tanken zurückgelegt hat, geteilt durch die Anzahl der Liter Kraftstoff, die verbraucht wurden…
MPG = (MEILEN DIESER FÜLLUNG - MEILEN LETZTE FÜLLUNG) / GALLONS KRAFTSTOFF
aber weil der Kraftstoff in Litern kommt und es 4,546 Liter in einer Gallone sind…
MPG = (MEILEN DIESE FÜLLUNG - MEILEN LETZTE FÜLLUNG) / LITER KRAFTSTOFF x 4.546
So habe ich die Funktion geschrieben…
Funktion MPG (StartMiles As Integer, FinishMiles As Integer, Liters As Single) MPG = (FinishMiles - StartMiles) / Liter * 4.546 End Function
und so sieht es auf dem Arbeitsblatt aus…
Nicht alle Funktionen führen mathematische Berechnungen durch. Hier ist eine, die Informationen liefert…
Eine Funktion, die den Namen des Tages angibt
Ich werde oft gefragt, ob es eine Datumsfunktion gibt, die den Wochentag als Text ausgibt (z.B. Montag). Die Antwort ist nein*, aber es ist ganz einfach, einen zu erstellen. (*Nachtrag: Habe ich nein gesagt? Überprüfen Sie den Hinweis unten, um die Funktion zu sehen, die ich vergessen habe!). Excel verfügt über die WOCHENTAG-Funktion, die den Wochentag als Zahl von 1 bis 7 zurückgibt. Sie können wählen, welcher Tag 1 ist, wenn Ihnen die Vorgabe (Sonntag) nicht gefällt. Im folgenden Beispiel gibt die Funktion "5" zurück, von der ich weiß, dass sie "Donnerstag" bedeutet.
Aber ich will keine Zahl sehen, ich will "Donnerstag" sehen. Ich könnte die Berechnung ändern, indem ich eine SVERWEIS-Funktion hinzufüge, die auf eine Tabelle verweist, die irgendwo eine Liste von Zahlen und eine entsprechende Liste von Tagesnamen enthält. Oder ich könnte das Ganze mit mehreren verschachtelten IF-Anweisungen in sich geschlossen haben. Zu kompliziert! Die Antwort ist eine benutzerdefinierte Funktion…
Funktion DayName(InputDate As Date) Dim DayNumber As Integer DayNumber = Weekday(InputDate, vbSunday) Select Case DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Fall 5 DayName = "Donnerstag" Fall 6 DayName = "Freitag" Fall 7 DayName = "Samstag" Ende Auswählen Ende Funktion
Ich habe meine Funktion "DayName" aufgerufen und sie benötigt ein einzelnes Argument, das ich "InputDate" nenne, das (natürlich) ein Datum sein muss. So funktioniert das…
- Die erste Zeile der Funktion deklariert eine Variable, die ich "DayNumber" genannt habe und die eine ganze Zahl (d. h. eine ganze Zahl) sein wird.
- Die nächste Zeile der Funktion weist dieser Variablen mithilfe der Excel-Funktion WEEKDAY einen Wert zu. Der Wert ist eine Zahl zwischen 1 und 7. Obwohl der Standardwert 1=Sonntag ist, habe ich ihn der Übersichtlichkeit halber trotzdem eingefügt.
- Endlich a Fallerklärung prüft den Wert der Variablen und gibt den entsprechenden Text zurück.
So sieht es auf dem Arbeitsblatt aus…
Zugriff auf Ihre benutzerdefinierten Funktionen
Wenn an eine Arbeitsmappe ein VBA-Codemodul angehängt ist, das benutzerdefinierte Funktionen enthält, können diese Funktionen problemlos innerhalb derselben Arbeitsmappe angesprochen werden, wie in den obigen Beispielen gezeigt. Sie verwenden den Funktionsnamen, als ob er eine der integrierten Excel-Funktionen wäre.
Sie finden die Funktionen auch im Funktionsassistenten (manchmal auch als Werkzeug zum Einfügen von Funktionen bezeichnet). Verwenden Sie den Assistenten, um eine Funktion wie gewohnt einzufügen (Einfügen > Funktion).
Scrollen Sie in der Liste der Funktionskategorien nach unten, um zu finden Benutzerdefinierten und wählen Sie es aus, um eine Liste der verfügbaren UDFs anzuzeigen…
Sie können sehen, dass den benutzerdefinierten Funktionen keine Beschreibung außer der nicht hilfreichen Meldung "Keine Hilfe verfügbar" fehlt, aber Sie können eine kurze Beschreibung hinzufügen …
Stellen Sie sicher, dass Sie sich in der Arbeitsmappe befinden, die die Funktionen enthält. Gehe zu Extras > Makro > Makros. Ihre Funktionen werden hier nicht aufgelistet, aber Excel kennt sie! In dem Makroname oben im Dialogfeld, geben Sie den Namen der Funktion ein und klicken Sie dann auf das Dialogfeld Optionen Taste. Wenn die Schaltfläche ausgegraut ist, haben Sie entweder den Funktionsnamen falsch geschrieben oder Sie befinden sich in der falschen Arbeitsmappe oder sie existiert nicht! Es öffnet sich ein weiterer Dialog, in den Sie eine kurze Beschreibung der Funktion eingeben können. Klicken OK um die Beschreibung zu speichern und (hier ist der verwirrende Teil) klicken Abbrechen , um das Dialogfeld Makro zu schließen. Denken Sie daran, die Arbeitsmappe mit der Funktion zu speichern. Wenn Sie das nächste Mal zum Funktionsassistenten gehen, enthält Ihr UDF eine Beschreibung…
Wie Makros können benutzerdefinierte Funktionen in jeder anderen Arbeitsmappe verwendet werden, solange die Arbeitsmappe, die sie enthält, geöffnet ist. Es ist jedoch keine gute Praxis, dies zu tun. Das Eingeben der Funktion in eine andere Arbeitsmappe ist nicht einfach. Sie müssen den Namen der Host-Arbeitsmappe zum Funktionsnamen hinzufügen. Dies ist nicht schwierig, wenn Sie sich auf den Funktionsassistenten verlassen, aber es ist umständlich, manuell zu schreiben. Der Funktionsassistent zeigt die vollständigen Namen aller UDFs in anderen Arbeitsmappen an…
Wenn Sie die Arbeitsmappe, in der Sie die Funktion verwendet haben, öffnen, während die Arbeitsmappe mit der Funktion geschlossen ist, wird in der Zelle, in der Sie die Funktion verwendet haben, eine Fehlermeldung angezeigt. Excel hat es vergessen! Öffnen Sie die Host-Arbeitsmappe der Funktion, berechnen Sie neu, und alles ist wieder in Ordnung. Zum Glück gibt es einen besseren Weg.
Wenn Sie benutzerdefinierte Funktionen für die Verwendung in mehr als einer Arbeitsmappe schreiben möchten, erstellen Sie am besten eine Excel-Datei Add-In. Wie das geht, erfahren Sie im Tutorial Erstellen eines Excel-Add-Ins.
Nachtrag
Ich sollte es wirklich besser wissen! Sag niemals nie! Nachdem ich Ihnen gesagt habe, dass es keine Funktion gibt, die den Namen des Tages liefert, habe ich mich jetzt an diejenige erinnert, die dies kann. Schauen Sie sich dieses Beispiel an…
Die Funktion TEXT gibt den Wert einer Zelle als Text in einem bestimmten Zahlenformat zurück. Also im Beispiel hätte ich wählen können =TEXT(A1,"ddd") "Do" zurückgeben, =TEXT(A1,"mmmm") um "September" zurückzugeben usw. Die Excel-Hilfe enthält weitere Beispiele für die Verwendung dieser Funktion.
Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen.
Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns an E-Mail-Site