Opret VBA -funktion for at returnere matrix

Som titlen antyder, lærer vi, hvordan man opretter en brugerdefineret funktion i Excel, der returnerer en matrix. Vi har allerede lært, hvordan man opretter en brugerdefineret funktion i VBA. Så lad os komme i gang med selvstudiet uden at spilde tid.

Hvad er en Array -funktion?

Arrayfunktioner er de funktioner, der returnerer et array, når det bruges. Disse funktioner bruges sammen med CTRL+SHIFT+ENTER tastekombinationer, og det er derfor, vi foretrækker at kalde array -funktion eller formler som CSE -funktion og formler.

Excel -arrayfunktionen er ofte multicellearrayformler. Et eksempel er TRANSPOSE -funktionen.

Oprettelse af en UDF -array -funktion i VBA

Så scenariet er, at jeg bare vil returnere de første 3 lige tal ved hjælp af funktion ThreeEven () -funktion.

Koden vil se sådan ud.

Funktion ThreeEven () Som Integer () 'definerer array Dim -numre (2) As Integer' Tildel værdier til array -numre (0) = 0 tal (1) = 2 tal (2) = 4 'returværdier ThreeEven = tal Afslut funktion 

Lad os bruge denne funktion på regnearket.

Du kan se, at vi først vælger tre celler (vandret, for lodret skal vi bruge todimensionalt array. Vi har det dækket herunder.). Derefter begynder vi at skrive vores formel. Derefter rammer vi CTRL+SKIFT+ENTER. Dette fylder de udvalgte celler med matrixværdierne.

Bemærk:

• I praksis ved du ikke, hvor mange celler du får brug for. I så fald skal du altid vælge flere celler end forventet matrixlængde. Funktionen vil fylde cellerne med array, og ekstra celler viser #N/A fejl.
• Som standard returnerer denne array -funktion værdier i et vandret array. Hvis du prøver at markere lodrette celler, viser alle celler kun den første værdi af array.

Hvordan det virker?

For at oprette en array -funktion skal du følge denne syntaks.

Function functionName (variabler) Som returnType () dim resultArray (length) as dataType 'Tildel værdier til array her functionName = resultArray End Function 

Funktionserklæringen skal være som defineret ovenfor. Dette erklærede, at det er en array -funktion.
Mens du bruger det på regnearket, skal du bruge tastekombinationen CTRL+SKIFT+ENTER. Ellers returnerer den kun den første værdi af arrayet.

VBA Array -funktion til at returnere lodret array

For at få din UDF -array -funktion til at fungere lodret, behøver du ikke gøre meget. Bare deklarér arrays som et todimensionalt array. Tilføj derefter værdierne i den første dimension, og lad den anden dimension stå tom. Sådan gør du:

Funktion ThreeEven () Som Integer () 'definerer array Dim -numre (2,0) As Integer' Tildel værdier til array -numre (0,0) = 0 tal (1,0) = 2 tal (2,0) = 4 ' return værdier ThreeEven = tal Afslut funktion 

Sådan bruger du det på regnearket.

UDF Array -funktion med argumenter i Excel

I ovenstående eksempler har vi simpelthen trykt sumstatiske værdier på arket. Lad os sige, at vi vil have vores funktion til at acceptere et områdeargument, udføre nogle operationer på dem og returnere det resulterende array.

Eksempel Tilføj "-done" til hver værdi i intervallet

Nu ved jeg, at dette kan gøres let, men bare for at vise dig, hvordan du kan bruge brugerdefinerede VBA-array-funktioner til at løse problemer.

Så her vil jeg have en array-funktion, der tager et område som et argument og tilføjer "-done" til hver værdi i området. Dette kan let gøres ved hjælp af sammenkædningsfunktionen, men vi vil bruge en matrixfunktion her.

Funktion CONCATDone (rng As Range) Som Variant () Dim resultArr () Som Variant 'Opret en samling Dim col As New Collection' Tilføjelse af værdier til samlingen Ved fejl Genoptag Næste For hver v I rng kol. Tilføj v Næste Ved fejl Gå til 0 ' færdiggøre operationen for hver værdi og tilføje 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 Slutfunktion 

Forklaring:

Ovenstående funktion accepterer et område som et argument, og det tilføjer "-done" til hver værdi i området.

Du kan se, at vi har brugt en VBA-samling her til at holde værdierne i arrayet, og derefter har vi udført vores operation på hver værdi og tilføjet dem tilbage på et todimensionalt array.

Så ja fyre, sådan kan du oprette en brugerdefineret VBA -array -funktion, der kan returnere en array. Jeg håber, det var forklarende nok. Hvis du har spørgsmål til denne artikel, skal du skrive den i kommentarfeltet herunder.

Klik på linket herunder for at downloade arbejdsfilen:

Opret VBA -funktion for at returnere matrix

Arrays i Excel Formul | Lær, hvad arrays er i excel.

Sådan oprettes brugerdefineret funktion via VBA | Lær, hvordan du opretter brugerdefinerede funktioner i Excel

Brug af en brugerdefineret funktion (UDF) fra en anden projektmappe ved hjælp af VBA i Microsoft Excel | Brug den brugerdefinerede funktion i en anden Excel-projektmappe

Returner fejlværdier fra brugerdefinerede funktioner ved hjælp af VBA i Microsoft Excel | Lær, hvordan du kan returnere fejlværdier fra en brugerdefineret funktion

Populære artikler:

50 Excel -genveje til at øge din produktivitet

VLOOKUP -funktionen i Excel

COUNTIF i Excel 2016

Sådan bruges SUMIF -funktionen i Excel