Werken met VBA-functies die een array retourneren in Excel 2016 - dummies

Array-formules zijn een van de krachtigste functies van Excel. Als u bekend bent met arrayformules, weet u graag dat u VBA-functies kunt maken die een array retourneren.

Een reeks namen voor een maand retourneren

Laten we beginnen met een eenvoudig voorbeeld. De functie MonthNames retourneert een matrix met 12 elementen van - u raadt het al - maandnamen.

Functie MonthNames () MonthNames = Array ("Januari", "Februari", "Maart", _ "April", "Mei", "Juni", "Juli", "Augustus", _ "September", " Oktober "," november "," december ") Eindfunctie

Om de MonthNames-functie in een werkblad te gebruiken, moet u deze invoeren als een matrixformule met 12 cellen. Selecteer bijvoorbeeld bereik A2: L2 en voer = MonthNames () in. Druk vervolgens op Ctrl + Shift + Enter om de matrixformule in alle 12 geselecteerde cellen in te voeren. Bekijk het resultaat.

De MonthNames-functie gebruiken om een ​​array met 12 elementen terug te zetten.

Als u de maandnamen in een kolom wilt weergeven, selecteert u 12 cellen in een kolom en gebruikt u deze matrixformule. (Vergeet niet om het in te voeren door op Ctrl + Shift + Enter te drukken.)

= TRANSPOSE (MonthNames ())

U kunt ook een enkele maand uit de array kiezen. Hier is een formule (geen matrixformule) die het vierde element van de array weergeeft: april.

= INDEX (MonthNames (), 4)

Een gesorteerde lijst retourneren

Stel dat u een lijst met namen hebt die u in een andere reeks cellen in gesorteerde volgorde wilt weergeven. Zou het niet leuk zijn om een ​​werkbladfunctie voor u te hebben?

Deze aangepaste functie doet precies dat: het neemt een celbereik met één kolom als argument en retourneert vervolgens een array van die cellen gesorteerd. Bereik A2: A13 bevat enkele namen. Bereik C2: C13 bevat deze formule met meerdere cellen. (Vergeet niet dat u de formule moet invoeren door op Ctrl + Shift + Enter te drukken.)

Een aangepaste functie gebruiken om een ​​gesorteerd bereik te retourneren.

= Gesorteerd (A2: A13)

Hier is de code voor de gesorteerde functie:

Functie gesorteerd (Rng als bereik) Dim SortedData () als variant Dim-cel als bereik Dim-temperatuur als variant, i As Long, j As Long Dim NonEmpty As Long 'Gegevens overbrengen naar gesorteerde gegevens voor elke cel in Rng zo niet is leeg (cel) vervolgens niet-leeg = niet leeg + 1 ReDim behouden gesorteerde gegevens (1 tot niet-leeg) gesorteerde gegevens (niet leeg) = cel. Value End If Next Cell 'Sorteer de array Voor i = 1 Aan NonEmpty Voor j = i + 1 Aan NonEmpty If SortedData (i)> SortedData (j) Then Temp = SortedData (j) SortedData (j) = SortedData (i) SortedData (i) = Temp End If Next j Next i 'Transponeer de array en retourneer deze Sorted = Application.Transpose (SortedData) Eindfunctie

De gesorteerde functie begint met het creëren van een array genaamd SortedData. Deze array bevat alle niet-lege waarden in het argumentbereik. Vervolgens wordt de SortedData-array gesorteerd met behulp van een bellen-sorteeralgoritme. Omdat de array een horizontale array is, moet deze worden getransponeerd voordat deze door de functie wordt geretourneerd.

De gesorteerde functie werkt met een bereik van elke grootte, zolang het maar in een enkele kolom of rij staat. Als de ongesorteerde gegevens op een rij staan, moet uw formule de TRANSPOSE-functie van Excel gebruiken om de gesorteerde gegevens horizontaal weer te geven. Bijvoorbeeld:

= TRANSPOSE (gesorteerd (A16: L16))