Arbejder med VBA-funktioner, der returnerer et array i Excel 2016 - dummies

Array formler er et af Excels mest kraftfulde funktioner. Hvis du er bekendt med matrixformler, vil du være glad for at vide, at du kan oprette VBA-funktioner, der returnerer en matrix.

Gendannelse af en række månedlige navne

Lad os starte med et simpelt eksempel. Funktionen MonthNames returnerer et 12-elements array af - du gættede det - månedens navne.

Funktion MonthNames () MonthNames = Array ("Januar", "Februar", "Marts", "April", "Maj", "Juni", "Juli", "August", "September" Oktober "," november "," december ") Afslut Funktion

For at bruge MonthNames funktionen i et regneark skal du indtaste det som en 12-celle array formel. Vælg for eksempel område A2: L2 og indtast = MånedNavne () . Tryk derefter på Ctrl + Shift + Enter for at indtaste arrayformlen i alle 12 udvalgte celler. Tjek resultatet.

Brug funktionen MonthNames til at returnere et 12-element array.

Hvis du vil have månedenavnene vist i en kolonne, skal du vælge 12 celler i en kolonne og bruge denne arrayformel. (Glem ikke at indtaste det ved at trykke på Ctrl + Skift + Enter.)

= TRANSPOSE (MonthNames ())

Du kan også vælge en enkelt måned fra arrayet. Her er en formel (ikke en matrixformel), der viser det fjerde element i arrayet: April.

= INDEX (MonthNames (), 4)

Retur en sorteret liste

Antag, at du har en liste over navne, du vil vise i rækkefølge i en anden række celler. Ville det ikke være rart at have et regneark-funktion, gør det for dig?

Denne brugerdefinerede funktion gør netop det: Det tager en enkeltkolonne række celler som argument og returnerer derefter en række af de sorterede celler. Område A2: A13 indeholder nogle navne. Område C2: C13 indeholder denne multicell array-formel. (Husk at du skal indtaste formlen ved at trykke på Ctrl + Skift + Enter.)

Brug en brugerdefineret funktion til at returnere et sorteret interval.

= Sorteret (A2: A13)

Her er koden for Sorteret funktion:

Funktion sorteret (Rng Som Range) Dim SortedData () Som Variant Dim Cell Som Range Dim Temp Som Variant, j Så lang Dim NonEmpty As Long 'Overfør data til SortedData for hver celle i Rng Hvis ikke erEmpty (Cell) Then NonEmpty = NonEmpty + 1 ReDim Bevar SortedData (1 til NonEmpty) SortedData (NonEmpty) = Cell. Værdi ende Hvis næste celle 'Sorter array For i = 1 Til NonEmpty For j = i + 1 Til NonEmpty Hvis SortedData (i)> SortedData (j) Så Temp = SortedData (j) SortedData (j) = SorteretData (i) SorteretData (i) = Temp End Hvis Næste j Næste Jeg 'Transponér arrayet og returner det Sorted = Application.Transpose (SortedData) End Function

Sorteret funktion starter ved at oprette en array med navnet SortedData. Dette array indeholder alle nonblank-værdierne i argumentområdet. Derefter sorteres SortedData-arrayet ved hjælp af en bubble-sort-algoritme. Fordi arrayet er et vandret array, skal det transponeres, før det returneres af funktionen.

Den sorterede funktion fungerer med en rækkevidde af enhver størrelse, så længe den er i en enkelt kolonne eller række. Hvis de usorterede data er på række, skal din formel bruge Excels TRANSPOSE-funktion til at vise de sorterede data vandret. For eksempel:

= TRANSPOSE (Sorteret (A16: L16))