Arbeider med VBA-funksjoner som returnerer et array i Excel 2016 - dummies

Array formler er en av Excels kraftigste funksjoner. Hvis du er kjent med matriseformler, vil du gjerne vite at du kan opprette VBA-funksjoner som returnerer en matrise.

Tilbakestill en rekke måneders navn

La oss starte med et enkelt eksempel. MonthNames-funksjonen returnerer en 12-elements rekkevidde av - du gjettet det - månedens navn.

Funksjon Månednavn () MonthNames = Array ("Januar", "Februar", "Mars", "April", "Mai", "Juni", "Juli", "August", "September" Oktober "," november "," desember ") Sluttfunksjon

For å bruke MonthNames-funksjonen i et regneark må du skrive det inn som en 12-celletabellformel. For eksempel, velg område A2: L2 og skriv inn = Månednavn () . Trykk deretter Ctrl + Shift + Enter for å angi matriseformelen i alle 12 valgte celler. Sjekk ut resultatet.

Bruk funksjonen MonthNames til å returnere en 12-elements array.

Hvis du vil at månedens navn skal vises i en kolonne, velg 12 celler i en kolonne og bruk denne arrayformelen. (Ikke glem å skrive inn det ved å trykke Ctrl + Shift + Enter.)

= TRANSPOSE (MonthNames ())

Du kan også velge en enkelt måned fra gruppen. Her er en formel (ikke en matriseformel) som viser det fjerde elementet i arrayet: april.

= INDEX (MonthNames (), 4)

Retur en sortert liste

Anta at du har en liste over navn du vil vise i sortert rekkefølge i et annet rekke celler. Ville det ikke vært fint å ha et regneark-funksjon, gjør det for deg?

Denne tilpassede funksjonen gjør nettopp det: Det tar en enkeltkolonne rekke celler som argumentet og returnerer deretter en rekke av disse cellene sortert. Område A2: A13 inneholder noen navn. Range C2: C13 inneholder denne multicell array-formelen. (Husk at du må skrive inn formelen ved å trykke Ctrl + Shift + Enter.)

Bruk en egendefinert funksjon for å returnere et sortert område.

= Sortert (A2: A13)

Her er koden for Sortert funksjon:

Funksjon sortert (Rng Som Range) Dim SortedData () Som Variant Dim Cell Som Range Dim Temp Som Variant, j Så lang dim Ikke gyldig så lenge 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. Verdi End Hvis Next Cell 'Sorter array For i = 1 Til NonEmpty For j = i + 1 Til NonEmpty Hvis SorteredData (i)> SorteredData (j) Så Temp = SortedData (j) SortedData (j) = Sorteredata (i) SorteredData (i) = Temp End Hvis Neste j Neste jeg 'Transponere arrayet og returnere det Sorted = Application.Transpose (SortedData) End Function

Sorteringsfunksjonen starter ved å opprette en array som heter SorteredData. Denne gruppen inneholder alle nonblank-verdiene i argumentområdet. Deretter sorteres SorteredData-oppsettet ved hjelp av en boble-sorteringsalgoritme. Fordi arrayet er et horisontalt array, må det transponeres før det returneres av funksjonen.

Sorteringsfunksjonen fungerer med en rekkevidde av enhver størrelse, så lenge den er i en enkelt kolonne eller rad. Hvis de usorterte dataene er på rad, må du bruke Excels TRANSPOSE-funksjon for å vise de sorterte dataene horisontalt. For eksempel:

= TRANSPOSE (Sortert (A16: L16))