VBA-Funktion erstellen, um Array zurückzugeben

Wie der Titel schon sagt, lernen wir, wie man eine benutzerdefinierte Funktion in Excel erstellt, die ein Array zurückgibt. Wir haben bereits gelernt, wie man eine benutzerdefinierte Funktion in VBA erstellt. Beginnen wir also ohne Zeitverlust mit dem Tutorial.

Was ist eine Array-Funktion?

Array-Funktionen sind die Funktionen, die bei Verwendung ein Array zurückgeben. Diese Funktionen werden mit Tastenkombinationen STRG+UMSCHALT+EINGABE verwendet und deshalb bevorzugen wir den Aufruf von Array-Funktionen oder Formeln als CSE-Funktion und -Formeln.

Die Excel-Array-Funktion besteht oft aus mehrzelligen Array-Formeln. Ein Beispiel ist die TRANSPOSE-Funktion.

Erstellen einer UDF-Array-Funktion in VBA

Das Szenario ist also, dass ich nur die ersten 3 geraden Zahlen mit der Funktion ThreeEven() zurückgeben möchte.

Der Code wird so aussehen.

Funktion ThreeEven() As Integer() 'Array definieren Dim-Zahlen(2) As Integer 'Werte dem Array zuweisen zahlen(0) = 0 Zahlen(1) = 2 Zahlen(2) = 4 'Werte zurückgeben ThreeEven = Zahlen End Function 

Lassen Sie uns diese Funktion auf dem Arbeitsblatt verwenden.

Sie können sehen, dass wir zuerst drei Zellen auswählen (horizontal, für vertikal müssen wir ein zweidimensionales Array verwenden. Wir haben es unten behandelt.). Dann fangen wir an, unsere Formel zu schreiben. Dann drücken wir STRG+UMSCHALT+EINGABE. Dadurch werden die ausgewählten Zellen mit den Array-Werten gefüllt.

Notiz:

• In der Praxis wissen Sie nicht, wie viele Zellen Sie benötigen. Wählen Sie in diesem Fall immer mehr Zellen als erwartete Arraylänge aus. Die Funktion füllt die Zellen mit Arrays und zusätzliche Zellen zeigen den Fehler #N/A an.
• Standardmäßig gibt diese Array-Funktion Werte in einem horizontalen Array zurück. Wenn Sie versuchen, vertikale Zellen auszuwählen, zeigen alle Zellen nur den ersten Wert von Array an.

Wie es funktioniert?

Um eine Array-Funktion zu erstellen, müssen Sie dieser Syntax folgen.

Funktion functionName(variables) As returnType() dim resultArray(length) as dataType 'Hier dem Array Werte zuweisen functionName =resultArray End Function 

Die Funktionsdeklaration muss wie oben definiert sein. Dies deklariert, dass es sich um eine Array-Funktion handelt.
Während Sie es auf dem Arbeitsblatt verwenden, müssen Sie die Tastenkombination STRG+UMSCHALT+EINGABETASTE verwenden. Andernfalls wird nur der erste Wert des Arrays zurückgegeben.

VBA-Array-Funktion zum Zurückgeben eines vertikalen Arrays

Damit Ihre UDF-Array-Funktion vertikal funktioniert, müssen Sie nicht viel tun. Deklarieren Sie die Arrays einfach als zweidimensionales Array. Fügen Sie dann in der ersten Dimension die Werte hinzu und lassen Sie die andere Dimension leer. Das ist wie man es macht:

Funktion ThreeEven() As Integer() 'array definieren Dim numbers(2,0) As Integer 'Werte dem Array zuweisen Zahlen(0,0) = 0 Zahlen(1,0) = 2 Zahlen(2,0) = 4 ' Rückgabewerte ThreeEven = Zahlen End Function 

So verwenden Sie es auf dem Arbeitsblatt.

UDF-Array-Funktion mit Argumenten in Excel

In den obigen Beispielen haben wir einfach statische Summenwerte auf das Blatt gedruckt. Nehmen wir an, unsere Funktion soll ein Bereichsargument akzeptieren, einige Operationen damit ausführen und das resultierende Array zurückgeben.

Beispiel Füge "-done" zu jedem Wert im Bereich hinzu

Nun, ich weiß, dass dies leicht möglich ist, aber nur um Ihnen zu zeigen, wie Sie benutzerdefinierte VBA-Array-Funktionen verwenden können, um Probleme zu lösen.

Hier möchte ich also eine Array-Funktion, die einen Bereich als Argument verwendet und jedem Wert im Bereich "-done" hinzufügt. Dies kann leicht mit der Verkettungsfunktion erfolgen, aber wir verwenden hier eine Array-Funktion.

Funktion CONCATDone(rng As Range) As Variant() Dim resulArr() As Variant 'Erzeuge eine Sammlung Dim col As New Collection 'Hinzufügen von Werten zur Sammlung bei Fehler Resume Next For Each v In rng col.Add v Next On Error GoTo 0 ' Abschließen der Operation für jeden Wert und Hinzufügen zu Array ReDim resulArr(col.Count - 1, 0) For i = 0 To col.Count - 1 resulArr(i, 0) = col(i + 1) & "-done" Next CONCATDone = resulArr End Function 

Erläuterung:

Die obige Funktion akzeptiert einen Bereich als Argument und fügt jedem Wert im Bereich "-done" hinzu.

Sie können sehen, dass wir hier eine VBA-Sammlung verwendet haben, um die Werte des Arrays zu speichern, und dann haben wir unsere Operation für jeden Wert ausgeführt und sie wieder zu einem zweidimensionalen Array hinzugefügt.

Also ja, Leute, so können Sie eine benutzerdefinierte VBA-Array-Funktion erstellen, die ein Array zurückgeben kann. Ich hoffe, es war erklärend genug. Wenn Sie Fragen zu diesem Artikel haben, schreiben Sie ihn in den Kommentarbereich unten.

Klicken Sie auf den folgenden Link, um die Arbeitsdatei herunterzuladen:

VBA-Funktion erstellen, um Array zurückzugeben

Arrays in Excel Formul|Erfahren Sie, was Arrays in Excel sind.

So erstellen Sie eine benutzerdefinierte Funktion über VBA | Erfahren Sie, wie Sie benutzerdefinierte Funktionen in Excel erstellen

Verwenden einer benutzerdefinierten Funktion (UDF) aus einer anderen Arbeitsmappe mit VBA in Microsoft Excel | Verwenden Sie die benutzerdefinierte Funktion in einer anderen Arbeitsmappe von Excel

Fehlerwerte von benutzerdefinierten Funktionen mit VBA in Microsoft Excel zurückgeben | Erfahren Sie, wie Sie Fehlerwerte von einer benutzerdefinierten Funktion zurückgeben können

Populäre Artikel:

50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität

Die SVERWEIS-Funktion in Excel

ZÄHLENWENN in Excel 2016

So verwenden Sie die SUMIF-Funktion in Excel