Lag VBA -funksjon for å returnere matrise

Som tittelen antyder, lærer vi hvordan du oppretter en brukerdefinert funksjon i Excel som returnerer en matrise. Vi har allerede lært hvordan du oppretter en brukerdefinert funksjon i VBA. Så uten å kaste bort tid, la oss komme i gang med opplæringen.

Hva er en matrisefunksjon?

Arrayfunksjoner er funksjonene som returnerer en matrise når den brukes. Disse funksjonene brukes med CTRL+SKIFT+ENTER tastekombinasjoner, og det er derfor vi foretrekker å kalle matrisefunksjon eller formler som CSE -funksjon og formler.

Excel -matrisefunksjonen er ofte flercellede matriseformler. Et eksempel er TRANSPOSE -funksjonen.

Opprette en UDF -matrisefunksjon i VBA

Så scenariet er at jeg bare vil returnere de tre første partallene med funksjonen ThreeEven ().

Koden vil se slik ut.

Funksjon ThreeEven () Som heltall () 'definere array Dim tall (2) As Integer' Tilordne verdier til array tall (0) = 0 tall (1) = 2 tall (2) = 4 'returverdier ThreeEven = tall Avslutt funksjon 

La oss bruke denne funksjonen på regnearket.

Du kan se at vi først velger tre celler (horisontalt, for vertikal må vi bruke todimensjonal matrise. Vi har det dekket nedenfor.). Så begynner vi å skrive formelen vår. Deretter trykker vi på CTRL+SKIFT+ENTER. Dette fyller de utvalgte cellene med matrisverdiene.

Merk:

• I praksis vet du ikke hvor mange celler du trenger. Velg i så fall alltid flere celler enn forventet matriselengde. Funksjonen vil fylle cellene med matrise og ekstra celler vil vise #N/A -feil.
• Som standard returnerer denne matrisefunksjonen verdier i en horisontal matrise. Hvis du prøver å velge vertikale celler, vil alle cellene bare vise den første verdien av matrisen.

Hvordan det fungerer?

For å lage en matrisefunksjon må du følge denne syntaksen.

Function functionName (variabler) Som returnType () dim resultArray (length) as dataType 'Tilordne verdier til array her functionName = resultArray Sluttfunksjon 

Funksjonserklæringen må være som definert ovenfor. Dette erklærte at det er en matrisefunksjon.
Når du bruker det på regnearket, må du bruke tastekombinasjonen CTRL+SKIFT+ENTER. Ellers vil den bare returnere den første verdien av matrisen.

VBA Array -funksjon for å returnere vertikal matrise

For å få UDF -matrisefunksjonen til å fungere vertikalt, trenger du ikke gjøre mye. Bare deklar matrisene som en todimensjonal matrise. Deretter legger du til verdiene i den første dimensjonen og lar den andre dimensjonen stå tom. Slik gjør du:

Funksjon ThreeEven () Som heltall () 'definere array Dim tall (2,0) As Integer' Tilordne verdier til array tall (0,0) = 0 tall (1,0) = 2 tall (2,0) = 4 ' returverdier ThreeEven = tall Avslutt funksjon 

Slik bruker du det på regnearket.

UDF Array -funksjon med argumenter i Excel

I eksemplene ovenfor trykte vi ganske enkelt summen av statiske verdier på arket. La oss si at vi vil at funksjonen vår skal godta et områdeargument, utføre noen operasjoner på dem og returnere den resulterende matrisen.

Eksempel Legg til "-done" i hver verdi i området

Nå vet jeg at dette kan gjøres enkelt, men bare for å vise deg hvordan du kan bruke brukerdefinerte VBA-arrayfunksjoner til å løse problemer.

Så her vil jeg ha en matrisefunksjon som tar et område som et argument og legger til "-done" til hver verdi i området. Dette kan enkelt gjøres ved hjelp av sammenkoplingsfunksjonen, men vi vil bruke en matrisefunksjon her.

Funksjon CONCATDone (rng As Range) As Variant () Dim resultArr () As Variant 'Create a collection Dim col As New Collection' Legge til verdier i samlingen På Feil Gjenoppta Neste For Hver v I rng kol. Legg til v Neste Ved Feil Gå til 0 ' fullføre operasjonen på hver verdi og legge dem til Array ReDim resulArr (col.Count - 1, 0) For i = 0 To col.Count - 1 resultArr (i, 0) = col (i + 1) & "-done" Next CONCATDone = resultArr Sluttfunksjon 

Forklaring:

Funksjonen ovenfor godtar et område som et argument, og det vil legge til "-done" til hver verdi i området.

Du kan se at vi har brukt en VBA-samling her for å holde verdiene til matrisen, og så har vi utført operasjonen på hver verdi og lagt dem tilbake på en todimensjonal matrise.

Så ja gutta, slik kan du lage en tilpasset VBA -matrisefunksjon som kan returnere en matrise. Jeg håper det var forklarende nok. Hvis du har spørsmål angående denne artikkelen, kan du skrive den i kommentarfeltet nedenfor.

Klikk på lenken nedenfor for å laste ned arbeidsfilen:

Lag VBA -funksjon for å returnere matrise

Matriser i Excel Formul | Finn ut hvilke matriser som er i excel.

Hvordan lage brukerdefinert funksjon gjennom VBA | Lær hvordan du oppretter brukerdefinerte funksjoner i Excel

Bruke en brukerdefinert funksjon (UDF) fra en annen arbeidsbok ved hjelp av VBA i Microsoft Excel | Bruk den brukerdefinerte funksjonen i en annen arbeidsbok i Excel

Returner feilverdier fra brukerdefinerte funksjoner ved hjelp av VBA i Microsoft Excel | Lær hvordan du kan returnere feilverdier fra en brukerdefinert funksjon

Populære artikler:

50 Excel -snarveier for å øke produktiviteten

VLOOKUP -funksjonen i Excel

COUNTIF i Excel 2016

Slik bruker du SUMIF -funksjonen i Excel