So erstellen Sie benutzerdefinierte Funktionen in Microsoft Excel

2024 Autor: Jason Gerald | [email protected]. Zuletzt bearbeitet: 2023-12-16 11:03

Obwohl Excel bereits über Hunderte von integrierten Funktionen wie SUM, SVERWEIS, LINKS usw. verfügt, reichen die verfügbaren integrierten Funktionen normalerweise nicht aus, um ziemlich komplexe Aufgaben zu erledigen. Aber keine Sorge, Sie müssen nur die benötigten Funktionen selbst erstellen.

Schritt

Schritt 1. Erstellen Sie eine neue Arbeitsmappe oder öffnen Sie die Arbeitsmappe, die Sie mit benutzerdefinierten Funktionen (UDF) verarbeiten möchten

Schritt 2. Öffnen Sie den Visual Basic Editor in Microsoft Excel über Extras->Makro->Visual Basic Editor (oder drücken Sie die Tastenkombination Alt+F11)

Schritt 3. Klicken Sie auf die Schaltfläche Modul, um Ihrem Arbeitsblatt ein neues Modul hinzuzufügen

Sie können eine UDF in einer Arbeitsmappe erstellen, ohne ein neues Modul hinzuzufügen, aber die Funktion funktioniert nicht in anderen Arbeitsblättern in derselben Arbeitsmappe.

Schritt 4. Erstellen Sie den "Kopf" oder "Prototyp" Ihrer Funktion

Der Funktionsprototyp muss folgendem Aufbau folgen:

öffentliche Funktion "Funktionsname" (Parameter1 als Typ1, Parameter2 als Typ2) als Ergebnistyp.

Prototypen können möglichst viele Funktionen haben und ihre Typen können alle Basisdatentypen oder Excel-Objekttypen in Form von Range sein. Sie können sich Parameter als „Operanten“(Operatoren) vorstellen, auf die die Funktion einwirkt. Wenn Sie beispielsweise SIN(45) schreiben, um den Sinus von 45 Grad zu berechnen, wird die Zahl 45 als Parameter verwendet. Dann verwendet der Funktionscode diese Werte, um Berechnungen durchzuführen und die Ergebnisse anzuzeigen.

Schritt 5. Fügen Sie den Funktionscode hinzu, um sicherzustellen, dass Sie: 1) den vom Parameter angegebenen Wert verwenden; 2) das Ergebnis an den Funktionsnamen übergeben; und 3) schließe die Funktion mit dem Satz "Endfunktion". Programmieren in VBA oder einer anderen Sprache zu lernen erfordert viel Zeit und eine detaillierte Anleitung. Glücklicherweise haben diese Funktionen normalerweise kleine Codeblöcke und nutzen die Funktionen der Programmiersprache nicht sehr. Hier sind einige Elemente der VBA-Sprache, die verwendet werden können:

1. Der If (if)-Block, mit dem Sie einen Teil des Codes nur dann ausführen können, wenn die Bedingung erfüllt ist. Als Beispiel:

Kursergebnis der öffentlichen Funktion (als Integer-Wert) als String

Wenn Wert >= 5 Dann

Kursergebnisse = "Akzeptiert"

Anders

Kursergebnisse = "Abgelehnt"

Ende Wenn

Endfunktion

Beachten Sie die Elemente im If-Codeblock:

IF Bedingung THEN Code ELSE Code END IF

2. . Sie können das Else-Schlüsselwort zusammen mit dem zweiten Teil des Codes weglassen, da es optional ist.
3. Der Do (do)-Block, der einen Teil des While- oder Until-Codes ausführt, wenn oder bis die Bedingung erfüllt ist. Als Beispiel:

Öffentliche Funktion BilPrima(value As Integer) As Boolean

Dim i As Integer

ich = 2

BilPrima = Wahr

Tun

Wenn Wert / i = Int(Wert / i) Dann

BilPrima = Falsch

Ende Wenn

ich = ich + 1

Loop While i < value And NumberPrima = True

Endfunktion

Schauen Sie sich die Elemente noch einmal an:

DO-Code LOOP WHILE/UNTIL Bedingung

4. . Beachten Sie auch die zweite Zeile, die die Variable "deklariert". Sie können Ihrem Code Variablen zur späteren Verwendung hinzufügen. Variablen fungieren als temporäre Werte im Code. Betrachten Sie schließlich die Funktionsdeklaration als BOOLEAN, einem Datentyp, der nur TRUE- oder FALSE-Werte zulässt. Diese Methode zur Bestimmung von Primzahlen ist alles andere als optimal, aber der Code wurde so geschrieben, dass er gut lesbar ist.
5. Für Block (to), die eine bestimmte Menge an Code ausführt. Als Beispiel:

Öffentliche Funktion Factorial(value As Integer) As Long

Dimm-Ergebnisse so lang

Dim i As Integer

Wenn Wert = 0 Dann

Ergebnis = 1

ElseIf Wert = 1 Then

Ergebnis = 1

Anders

Ergebnis = 1

Für i = 1 zum Wert

Ergebnis= Ergebnis * i

Nächste

Ende Wenn

Fakultät = Ergebnis

Endfunktion

Schauen Sie sich die Elemente noch einmal an:

FOR Variable = Untergrenze TO Obergrenze des Codes NEXT

. Beachten Sie auch das zusätzliche ElseIf-Element in der If-Anweisung, mit dem Sie dem ausgeführten Code weitere Optionen hinzufügen können. Betrachten Sie schließlich die Funktion "Ergebnis" und die Variable, die als Long deklariert sind. Der Datentyp Long erlaubt viel größere Werte als Integer.

Unten sehen Sie den Code für eine Funktion, die kleine Zahlen in Wörter umwandelt.



Schritt 6. Kehren Sie zur Arbeitsmappe zurück und verwenden Sie die Funktion, indem Sie das Gleichheitszeichen (=) gefolgt vom Namen der Funktion in die Zelle schreiben

Schreiben Sie die öffnenden Klammern („(“) nach dem Funktionsnamen mit dem Zeichen Koma um die Parameter zu trennen, und enden Sie mit schließenden Klammern (“)“). Als Beispiel:

=ZahlzuBuchstabe(A4)

. Sie können auch hausgemachte Formeln verwenden, indem Sie in Kategorien danach suchen Benutzerdefinierten innerhalb der Option Formel einfügen. Du klickst einfach auf den Button Fx links neben der Formelleiste. Es gibt drei Arten von Parameterformularen in Funktionen:

1. Ein konstanter Wert, der direkt in die Zellformel eingegeben wird. In diesem Fall muss der Text (String) in Anführungszeichen stehen.
2. Zellreferenzen zum Beispiel B6 oder reichen wie A1: C3 (Parameter muss vom Datentyp „Bereich“sein)

3. Eine andere Funktion, die in Ihre Funktion eingeschlossen ist (Ihre Funktion kann auch in eine andere Funktion eingeschlossen werden), zum Beispiel: =Factorial(MAX(D6:D8))

   

   Schritt 7. Stellen Sie sicher, dass die Ergebnisse korrekt sind

   Verwenden Sie es mehrmals, um sicherzustellen, dass die Funktion verschiedene Parameterwerte korrekt verarbeiten kann:

   Tipps

   • Achten Sie beim Schreiben von Codeblöcken in Kontrollstrukturen wie If, For, Do usw. Dadurch wird der Code leichter verständlich und Fehler werden viel einfacher zu finden sein. Außerdem wird die Erhöhung der Funktionalität einfacher zu machen.
   • Wenn Sie nicht wissen, wie Sie Code für Funktionen schreiben, lesen Sie den Artikel So schreiben Sie ein einfaches Makro in Microsoft Excel.
   • Manchmal benötigen Funktionen nicht alle Parameter, um das Ergebnis zu berechnen. In diesem Fall können Sie das Schlüsselwort Optional vor dem Parameternamen im Funktionsheader verwenden. Sie können die Funktion IsMissing(parameter_name) in Ihrem Code verwenden, um zu bestimmen, ob einem Parameter ein Wert zugewiesen wird oder nicht.
   • Verwenden Sie nicht verwendete Namen als Funktionen in Excel, damit keine Funktionen überschrieben und gelöscht werden.
   • Excel verfügt über viele integrierte Funktionen und die meisten Berechnungen können mit diesen integrierten Funktionen entweder einzeln oder auf einmal durchgeführt werden. Schauen Sie sich unbedingt die Liste der verfügbaren Funktionen an, bevor Sie mit dem Programmieren beginnen. Die Ausführung kann schneller erfolgen, wenn Sie integrierte Funktionen verwenden.

   Warnung

   • Aus Sicherheitsgründen deaktivieren viele Leute Makros. Stellen Sie sicher, dass Sie Ihre Arbeitsmappenempfänger darüber informieren, dass die übermittelte Arbeitsmappe Makros enthält und dass diese Makros ihre Computer nicht beschädigen.
   • Die in diesem Artikel verwendete Funktion ist nicht der beste Weg, um das damit verbundene Problem zu lösen. Anhand des Beispiels wird die Verwendung von Sprachkontrollstrukturen erläutert.
   • VBA hat wie andere Sprachen neben Do, If und For mehrere andere Kontrollstrukturen. Die hier besprochene Struktur beschreibt nur, was im Quellcode der Funktion möglich ist. Es gibt viele Anleitungen im Internet, die Ihnen beim Erlernen von VBA helfen können.