Skapa VBA -funktion för att returnera matris

Som titeln antyder kommer vi att lära oss hur man skapar en användardefinierad funktion i Excel som returnerar en array. Vi har redan lärt oss hur man skapar en användardefinierad funktion i VBA. Så utan att slösa bort tid, låt oss komma igång med självstudien.

Vad är en Array -funktion?

Arrayfunktioner är de funktioner som returnerar en array när den används. Dessa funktioner används med tangenterna CTRL+SKIFT+ENTER och det är därför vi föredrar att anropa matrisfunktion eller formler som CSE -funktion och formler.

Excel -matrisfunktionen är ofta flercelliga matrisformler. Ett exempel är TRANSPOSE -funktionen.

Skapa en UDF -arrayfunktion i VBA

Så scenariot är att jag bara vill returnera de tre första jämna talen med funktionen ThreeEven ().

Koden kommer att se ut så här.

Funktion ThreeEven () Som heltal () 'definiera array Dim nummer (2) As Integer' Tilldela värden till array nummer (0) = 0 nummer (1) = 2 nummer (2) = 4 'returvärden ThreeEven = nummer Avsluta funktion 

Låt oss använda den här funktionen på kalkylbladet.

Du kan se det, vi väljer först tre celler (horisontellt, för vertikal måste vi använda tvådimensionell matris. Vi har det täckt nedan.). Sedan börjar vi skriva vår formel. Sedan slår vi CTRL+SKIFT+ENTER. Detta fyller de markerade cellerna med matrisvärdena.

Notera:

• I praktiken vet du inte hur många celler du behöver. Markera i så fall alltid fler celler än förväntad matrislängd. Funktionen fyller cellerna med array och extra celler visar #N/A -fel.
• Som standard returnerar denna array -funktion värden i en horisontell array. Om du försöker markera vertikala celler visar alla celler endast det första värdet på arrayen.

Hur det fungerar?

För att skapa en matrisfunktion måste du följa denna syntax.

Function functionName (variabler) Som returnType () dim resultArray (length) as dataType 'Tilldela värden till array här functionName = resultArray End Function 

Funktionsdeklarationen måste vara enligt ovan. Detta deklarerade att det är en array -funktion.
När du använder det på kalkylbladet måste du använda tangentkombinationen CTRL+SKIFT+ENTER. Annars returnerar det endast det första värdet för matrisen.

VBA Array -funktion för att returnera vertikal matris

För att din UDF -arrayfunktion ska fungera vertikalt behöver du inte göra så mycket. Förklara bara matriserna som en tvådimensionell matris. Lägg sedan till värdena i den första dimensionen och lämna den andra dimensionen tom. Så här gör du:

Funktion ThreeEven () som heltal () 'definiera matris Dim -nummer (2,0) som heltal' Tilldela värden till matrisnummer (0,0) = 0 nummer (1,0) = 2 nummer (2,0) = 4 ' returvärden ThreeEven = siffror Slutfunktion 

Så här använder du det på kalkylbladet.

UDF Array -funktion med argument i Excel

I exemplen ovan skrev vi helt enkelt ut summa statiska värden på arket. Låt oss säga att vi vill att vår funktion ska acceptera ett intervallargument, utföra några operationer på dem och returnera den resulterande matrisen.

Exempel Lägg till "-done" till varje värde i intervallet

Nu vet jag att detta kan göras enkelt men bara för att visa hur du kan använda användardefinierade VBA-arrayfunktioner för att lösa problem.

Så här vill jag ha en array-funktion som tar ett intervall som ett argument och lägger till "-done" till varje värde i intervallet. Detta kan enkelt göras med hjälp av sammanfogningsfunktionen men vi kommer att använda en matrisfunktion här.

Funktion CONCATDone (rng As Range) Som Variant () Dim resultArr () Som Variant 'Skapa en samling Dim col Som Ny samling' Lägga till värden i samlingen Vid fel Återuppta nästa för varje v I rng kol.Add v Nästa Vid fel Gå till 0 ' slutföra operationen för varje värde och lägga till dem i Array ReDim resultArr (col.Count - 1, 0) For i = 0 To col.Count - 1 resultArr (i, 0) = col (i + 1) & "-done" Next CONCATDone = resultArr End -funktion 

Förklaring:

Ovanstående funktion accepterar ett intervall som ett argument och det kommer att lägga till "-done" till varje värde i intervallet.

Du kan se att vi har använt en VBA-samling här för att hålla värdena på arrayen och sedan har vi gjort vår operation på varje värde och lagt tillbaka dem på en tvådimensionell array.

Så ja killar, så här kan du skapa en anpassad VBA -array -funktion som kan returnera en array. Jag hoppas att det var tillräckligt förklarande. Om du har några frågor angående den här artikeln, lägg den i kommentarfältet nedan.

Klicka på länken nedan för att ladda ner arbetsfilen:

Skapa VBA -funktion för att returnera matris

Matriser i Excel Formul | Lär dig vilka matriser som finns i excel.

Hur man skapar användardefinierad funktion via VBA | Lär dig hur du skapar användardefinierade funktioner i Excel

Använda en användardefinierad funktion (UDF) från en annan arbetsbok med VBA i Microsoft Excel | Använd den användardefinierade funktionen i en annan arbetsbok i Excel

Returnera felvärden från användardefinierade funktioner med VBA i Microsoft Excel | Lär dig hur du kan returnera felvärden från en användardefinierad funktion

Populära artiklar:

50 Excel -genvägar för att öka din produktivitet

VLOOKUP -funktionen i Excel

COUNTIF i Excel 2016

Hur man använder SUMIF -funktionen i Excel