Maak een VBA-functie om array terug te geven

Zoals de titel al doet vermoeden, zullen we leren hoe we een door de gebruiker gedefinieerde functie in Excel kunnen maken die een array retourneert. We hebben al geleerd hoe we een door de gebruiker gedefinieerde functie in VBA kunnen maken. Dus laten we zonder tijd te verspillen beginnen met de tutorial.

Wat is een matrixfunctie?

Matrixfuncties zijn de functies die bij gebruik een array retourneren. Deze functies worden gebruikt met CTRL+SHIFT+ENTER toetscombinaties en daarom noemen we matrixfunctie of formules liever als CSE-functie en formules.

De Excel-arrayfunctie is vaak matrixformules met meerdere cellen. Een voorbeeld is de TRANSPOSE-functie.

Een UDF-arrayfunctie maken in VBA

Het scenario is dus dat ik alleen de eerste 3 even getallen wil retourneren met de functie ThreeEven() functie.

De code zal er als volgt uitzien.

Functie ThreeEven() As Integer() 'definieer array Dim getallen(2) As Integer 'Wijs waarden toe aan array getallen(0) = 0 getallen(1) = 2 getallen(2) = 4 'retour waarden ThreeEven = getallen End Function 

Laten we deze functie op het werkblad gebruiken.

Je kunt zien dat we eerst drie cellen selecteren (horizontaal, voor verticaal moeten we een tweedimensionale array gebruiken. We hebben het hieronder behandeld.). Dan beginnen we met het schrijven van onze formule. Daarna drukken we op CTRL+SHIFT+ENTER. Dit vult de geselecteerde cellen met de matrixwaarden.

Opmerking:

• In de praktijk weet je niet hoeveel cellen je nodig hebt. Selecteer in dat geval altijd meer cellen dan de verwachte arraylengte. De functie vult de cellen met array en extra cellen zullen een #N/A-fout tonen.
• Standaard retourneert deze arrayfunctie waarden in een horizontale array. Als u verticale cellen probeert te selecteren, tonen alle cellen alleen de eerste waarde van de matrix.

Hoe het werkt?

Om een ​​array-functie te maken, moet u deze syntaxis volgen.

Functie functionName(variabelen) As returnType() dim resultArray(length) as dataType 'Wijs hier waarden toe aan array functionName =resultArray End Function 

De functiedeclaratie moet zijn zoals hierboven gedefinieerd. Dit verklaarde dat het een array-functie is.
Als u het op het werkblad gebruikt, moet u de toetsencombinatie CTRL+SHIFT+ENTER gebruiken. Anders wordt alleen de eerste waarde van de array geretourneerd.

VBA-arrayfunctie om verticale array te retourneren

Om uw UDF-arrayfunctie verticaal te laten werken, hoeft u niet veel te doen. Declareer de arrays gewoon als een tweedimensionale array. Voeg vervolgens in de eerste dimensie de waarden toe en laat de andere dimensie leeg. Dit is hoe je het doet:

Functie ThreeEven() As Integer() 'define array Dim numbers(2,0) As Integer 'Wijs waarden toe aan array getallen(0,0) = 0 getallen(1,0) = 2 getallen(2,0) = 4 ' retourneert waarden ThreeEven = getallen End Function 

Zo gebruik je het op het werkblad.

UDF-arrayfunctie met argumenten in Excel

In de bovenstaande voorbeelden hebben we eenvoudig statische somwaarden op het blad afgedrukt. Laten we zeggen dat we willen dat onze functie een bereikargument accepteert, er enkele bewerkingen op uitvoert en de resulterende array retourneert.

Voorbeeld Voeg "-done" toe aan elke waarde in het bereik

Nu weet ik dat dit eenvoudig kan, maar alleen om u te laten zien hoe u door de gebruiker gedefinieerde VBA-arrayfuncties kunt gebruiken om problemen op te lossen.

Dus hier wil ik een array-functie die een bereik als argument neemt en "-done" toevoegt aan elke waarde in het bereik. Dit kan eenvoudig worden gedaan met behulp van de aaneenschakelingsfunctie, maar we zullen hier een array-functie gebruiken.

Functie CONCATDone(rng As Range) As Variant() Dim resulArr() As Variant 'Maak een verzameling Dim col As New Collection 'Waarden toevoegen aan verzameling Bij fout Hervatten volgende voor elke v In rng col.Toevoegen v Volgende bij fout Ga naar 0 ' bewerking op elke waarde voltooien en toevoegen aan Array ReDim resulArr(col.Count - 1, 0) For i = 0 To col.Count - 1 resulArr(i, 0) = col(i + 1) & "-done" Volgende CONCATDone = resulArr End Function 

Uitleg:

De bovenstaande functie accepteert een bereik als argument en voegt "-done" toe aan elke waarde in het bereik.

Je kunt zien dat we hier een VBA-verzameling hebben gebruikt om de waarden van de array vast te houden en dat we onze bewerking op elke waarde hebben uitgevoerd en ze weer hebben toegevoegd aan een tweedimensionale array.

Dus ja jongens, dit is hoe je een aangepaste VBA-arrayfunctie kunt maken die een array kan retourneren. Ik hoop dat het voldoende verhelderend was. Als je vragen hebt over dit artikel, plaats het dan in de opmerkingen hieronder.

Klik op de onderstaande link om het werkbestand te downloaden:

Maak een VBA-functie om array terug te geven

Arrays in Excel Formul|Leer wat arrays zijn in Excel.

Hoe een door de gebruiker gedefinieerde functie te creëren via VBA | Leer hoe u door de gebruiker gedefinieerde functies in Excel kunt maken

Een door de gebruiker gedefinieerde functie (UDF) uit een andere werkmap gebruiken met VBA in Microsoft Excel | Gebruik de door de gebruiker gedefinieerde functie in een andere werkmap van Excel

Retourneer foutwaarden van door de gebruiker gedefinieerde functies met VBA in Microsoft Excel | Leer hoe u foutwaarden kunt retourneren vanuit een door de gebruiker gedefinieerde functie

Populaire artikels:

50 Excel-snelkoppelingen om uw productiviteit te verhogen

De VERT.ZOEKEN-functie in Excel

AANTAL.ALS in Excel 2016

Hoe de SUMIF-functie in Excel te gebruiken?