In diesem Tutorial lernen wir die Excel VBA-Funktion {leer} kennen. 1) Was ist Visual Basic in Excel?

{leer} 2) Wie verwende ich VBA in Excel?

{leer} 3) Wie erstelle ich eine benutzerdefinierte Funktion?

{leer} 4) Wie schreibe ich ein Makro?

So schreiben Sie VBAcode Excel bietet dem Benutzer eine große Sammlung vorgefertigter Funktionen, die mehr als genug sind, um den durchschnittlichen Benutzer zufrieden zu stellen. Viele weitere können hinzugefügt werden, indem die verschiedenen verfügbaren Add-Ins installiert werden. Die meisten Berechnungen können mit dem, was bereitgestellt wird, erreicht werden, aber es dauert nicht lange, bis Sie sich wünschen, dass es eine Funktion gibt, die einen bestimmten Job erledigt hat, und Sie können nichts Passendes in der Liste finden. Sie benötigen eine UDF. Eine UDF (User Defined Function) ist einfach eine Funktion, die Sie selbst mit VBA erstellen. UDFs werden häufig als „Benutzerdefinierte Funktionen“ bezeichnet. Eine UDF kann in einem Codemodul verbleiben, das an eine Arbeitsmappe angehängt ist. 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 über Codemodule zugegriffen werden. Oft 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 so komplex sein, wie Sie möchten. Beginnen wir mit einem einfachen …​

Eine Funktion zur Berechnung der Fläche eines Rechtecks ​​Ja, ich weiß, dass Sie dies in Ihrem Kopf tun können! Das Konzept ist sehr einfach, sodass Sie sich auf die Technik konzentrieren können. Angenommen, Sie benötigen eine Funktion zum Berechnen der Fläche eines Rechtecks. Sie sehen sich die Funktionssammlung von Excel an, aber es gibt keine geeignete. Dies ist die durchzuführende Berechnung:

BEREICH = 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. Wählen Sie also Einfügen> Modul. Geben Sie in das leere Modul Folgendes ein: Funktionsbereich und drücken Sie die EINGABETASTE. Der Visual Basic-Editor vervollständigt die Zeile für Sie und fügt eine Endfunktionszeile hinzu, als würden Sie eine Unterroutine erstellen. Bisher sieht es so aus …​

Function Area()

End Function

Platzieren Sie den Cursor zwischen den Klammern nach „Bereich“. Wenn Sie sich jemals gefragt haben, wozu die Klammern dienen, werden Sie es gleich herausfinden! Wir werden die „Argumente“ angeben, die unsere Funktion annehmen wird (ein argument ist eine Information, die für die Berechnung benötigt wird). Geben Sie Length as double, Width as double ein und klicken Sie in die leere Zeile darunter. Beachten Sie, dass während der Eingabe ein Bildlauffeld mit allen für Ihre Eingabe geeigneten Elementen angezeigt wird.

img2

Diese Funktion wird als Mitglieder der automatischen Liste bezeichnet. Wenn es nicht angezeigt wird, ist es entweder ausgeschaltet (aktivieren Sie es unter Extras> Optionen> Editor) oder Sie haben möglicherweise früher einen Tippfehler gemacht. Dies 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. Ihr Code sieht jetzt so aus …​

Function Area(Length As Double, Width As Double)

End Function

Die Angabe des Datentyps der Argumente ist nicht obligatorisch, aber sinnvoll. Sie hätten Length, Width eingeben und so belassen können, aber wenn Sie Excel warnen, welcher Datentyp zu erwarten ist, wird Ihr Code schneller ausgeführt und Fehler bei der Eingabe werden erkannt. Der Datentyp double bezieht sich auf die 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 (um das Lesen zu erleichtern) und geben Sie Area = Length * Width. ein. Hier ist der fertige Code …​

Function Area(Length As Double, Width As Double)

Area = Length * Width

End Function

Während der Eingabe wird eine weitere Hilfefunktion des Visual Basic-Editors angezeigt: Auto Quick Info …​

img31

Es ist hier nicht relevant. Der Zweck besteht darin, Ihnen beim Schreiben von Funktionen in VBA zu helfen, indem Sie angeben, welche Argumente erforderlich sind. Sie können Ihre Funktion sofort testen. Wechseln Sie zum Excel-Fenster und geben Sie 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 _ = Fläche (A1, B1) _ eingegeben und die neue Funktion hat die Fläche (110,5) berechnet …​

img3

Manchmal können die Argumente einer Funktion optional sein. In diesem Beispiel könnten wir das Width-Argument optional machen. Angenommen, das Rechteck ist zufällig ein Quadrat mit gleicher Länge und Breite. Um zu vermeiden, dass der Benutzer zwei Argumente eingeben muss, können 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 eine IF-Anweisung hinzufügen, um sie bei der Entscheidung zu unterstützen. Ändern Sie den Code so, dass er so aussieht …​

Function Area(Length As Double, Optional Width As Variant)

If IsMissing(Width) Then

Area = Length * Length

Else

Area = Length * Width

End If

End Function

Beachten Sie, dass der Datentyp für Breite in Variant geändert wurde, um Nullwerte zuzulassen. Die Funktion ermöglicht es dem Benutzer nun, nur ein Argument einzugeben, z. _ = area (A1) _. Die IF-Anweisung in der Funktion prüft, ob das Width-Argument angegeben wurde, und berechnet entsprechend …​

img4

Eine Funktion zur Berechnung des Kraftstoffverbrauchs Ich möchte den Kraftstoffverbrauch meines Autos überprüfen. Wenn ich Kraftstoff kaufe, notiere ich mir den Kilometerstand und die Menge an Kraftstoff, die zum Befüllen des Tanks benötigt wird.

Hier in Großbritannien wird Kraftstoff in Litern verkauft. Der Kilometerzähler des Autos (OK, es ist also ein Kilometerzähler) zeichnet die Entfernung in Meilen auf. Und weil ich zu alt und dumm bin, um mich zu ändern, verstehe ich nur MPG (Meilen pro Gallone). 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 die MPG für mich berechnet und die Leistung des Autos aufzeichnet. Die Berechnung ist die Anzahl der Meilen, die das Auto seit dem letzten Auffüllen zurückgelegt hat, geteilt durch die Anzahl der verbrauchten Gallonen Kraftstoff …​

MPG = (MEILEN DIESE FÜLLUNG – MEILEN LETZTE FÜLLUNG) / GALLONEN KRAFTSTOFF, aber weil der Kraftstoff in Litern kommt und es 4,546 Liter in einer Gallone gibt ..

MPG = (MEILEN DIESE FÜLLUNG – MEILEN LETZTE FÜLLUNG) / LITERS OF FUEL x 4.546 So habe ich die Funktion geschrieben …​

Function MPG(StartMiles As Integer, FinishMiles As Integer, Litres As Single)

MPG = (FinishMiles - StartMiles) / Litres * 4.546

End Function

und so sieht es auf dem Arbeitsblatt aus …​

img5

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 angibt (z. B. Montag). Die Antwort ist nein, aber es ist ziemlich einfach, eine 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 Funktion WOCHENTAG, die den Wochentag als Zahl von 1 bis 7 zurückgibt. Sie können auswählen, welcher Tag 1 ist, wenn Sie die Standardeinstellung (Sonntag) nicht mögen. Im folgenden Beispiel gibt die Funktion „5“ zurück, was zufällig „Donnerstag“ bedeutet.

img6

Aber ich möchte keine Nummer sehen, ich möchte „Donnerstag“ sehen. Ich könnte die Berechnung ändern, indem ich eine VLOOKUP-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 …​

Function 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"

Case 5

DayName = "Thursday"

Case 6

DayName = "Friday"

Case 7

DayName = "Saturday"

End Select

End Function

Ich habe meine Funktion „DayName“ genannt und es wird ein einzelnes Argument verwendet, das ich „InputDate“ nenne, was (natürlich) ein Datum sein muss. So funktioniert es …​

  • Die erste Zeile der Funktion deklariert eine Variable, die ich „DayNumber“ genannt habe und die eine Ganzzahl (d. H. Eine ganze Zahl) ist.

  • In der nächsten Zeile der Funktion wird dieser Variablen mit der WEEKDAY-Funktion von Excel ein Wert zugewiesen. Der Wert ist eine Zahl zwischen 1 und 7.

Obwohl der Standardwert 1 = Sonntag ist, habe ich ihn aus Gründen der Übersichtlichkeit trotzdem eingefügt.

Schließlich untersucht eine Case Statement * den Wert der Variablen und gibt den entsprechenden Text zurück.

So sieht es auf dem Arbeitsblatt aus …​

img7

=== === Zugreifen auf Ihre benutzerdefinierten Funktionen Wenn an eine Arbeitsmappe ein VBA-Codemodul angehängt ist, das benutzerdefinierte Funktionen enthält, können diese Funktionen problemlos in derselben Arbeitsmappe behandelt werden, wie in den obigen Beispielen gezeigt. Sie verwenden den Funktionsnamen so, als wäre er eine der in Excel integrierten Funktionen.

Sie finden die Funktionen auch im Funktionsassistenten (manchmal auch als Einfügefunktionstool bezeichnet). Verwenden Sie den Assistenten, um eine Funktion auf normale Weise einzufügen (Einfügen> Funktion).

Scrollen Sie durch die Liste der Funktionskategorien, um Benutzerdefiniert zu finden, und wählen Sie sie aus, um eine Liste der verfügbaren UDFs anzuzeigen …​

img8

img9

Sie können sehen, dass den benutzerdefinierten Funktionen eine andere Beschreibung als die nicht hilfreiche Meldung „Keine Hilfe verfügbar“ fehlt. Sie können jedoch eine kurze Beschreibung hinzufügen …​

Stellen Sie sicher, dass Sie sich in der Arbeitsmappe befinden, die die Funktionen enthält. Gehen Sie zu Extras> Makro> Makros. Sie werden Ihre Funktionen hier nicht sehen, aber Excel kennt sie! Geben Sie im Feld Makroname oben im Dialogfeld den Namen der Funktion ein und klicken Sie auf die Schaltfläche Optionen des Dialogfelds. 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! Dies öffnet einen weiteren Dialog, in den Sie eine kurze Beschreibung der Funktion eingeben können. Klicken Sie auf OK, um die Beschreibung zu speichern, und (hier ist das verwirrende Bit) auf 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, hat Ihre UDF eine Beschreibung …​

img10

Wie Makros können benutzerdefinierte Funktionen in jeder anderen Arbeitsmappe verwendet werden, solange die sie enthaltende Arbeitsmappe geöffnet ist. Es ist jedoch keine gute Praxis, dies zu tun. Die Eingabe 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 umständlich manuell zu schreiben. Der Funktionsassistent zeigt die vollständigen Namen aller UDFs in anderen Arbeitsmappen an …​

img11

Wenn Sie die Arbeitsmappe, in der Sie die Funktion verwendet haben, zu einem Zeitpunkt öffnen, zu dem 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 sie neu, und alles ist wieder in Ordnung. Zum Glück gibt es einen besseren Weg.

Wenn Sie benutzerdefinierte Funktionen zur Verwendung in mehr als einer Arbeitsmappe schreiben möchten, erstellen Sie am besten ein Excel-Add-In. Wie das geht, erfahren Sie im Tutorial Erstellen eines Excel-Add-Ins.

Nachtrag Ich sollte es wirklich besser wissen! Sag niemals niemals! Nachdem ich Ihnen gesagt habe, dass es keine Funktion gibt, die den Namen des Tages angibt, habe ich mich jetzt an die erinnert, die dies kann. Schauen Sie sich dieses Beispiel an …​

img12

Die TEXT-Funktion gibt den Wert einer Zelle als Text in einem bestimmten Zahlenformat zurück. In dem Beispiel hätte ich also = TEXT (A1, „ddd“) wählen können, um „Do“ zurückzugeben, = TEXT (A1, „mmmm“), um „September“ zurückzugeben usw. Die Hilfe von Excel enthält einige weitere Beispiele für Verwendungsmöglichkeiten diese Funktion.

__ Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie Ihren Freunden auf Facebook mit. Sie können uns auch auf Twitter und Facebook folgen.

Wir würden gerne von Ihnen hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder innovieren und für Sie verbessern können. Schreiben Sie uns an [email protected]