Skapa en användardefinierad funktion (UDF) i Excel VBA (Ultimate Guide)

Med VBA kan du skapa en anpassad funktion (även kallad en användardefinierad funktion) som kan användas i kalkylbladet precis som vanliga funktioner.

Dessa är användbara när de befintliga Excel -funktionerna inte räcker. I sådana fall kan du skapa din egen anpassade användardefinierade funktion (UDF) för att tillgodose dina specifika behov.

I den här självstudien kommer jag att täcka allt om att skapa och använda anpassade funktioner i VBA.

Om du är intresserad av att lära dig VBA på det enkla sättet, kolla in min Online Excel VBA -utbildning.

Vad är ett funktionsförfarande i VBA?

Ett funktionsförfarande är en VBA -kod som utför beräkningar och returnerar ett värde (eller en rad värden).

Med hjälp av ett funktionsförfarande kan du skapa en funktion som du kan använda i kalkylbladet (precis som alla vanliga Excel -funktioner som SUMMA eller VLOOKUP).

När du har skapat en funktionsprocedur med VBA kan du använda den på tre sätt:

1. Som en formel i kalkylbladet, där den kan ta argument som inmatningar och returnerar ett värde eller en rad värden.
2. Som en del av din VBA -underrutinkod eller en annan funktionskod.
3. I villkorlig formatering.

Även om det redan finns 450+ inbyggda Excel -funktioner tillgängliga i kalkylbladet, kan du behöva en anpassad funktion om:

• De inbyggda funktionerna kan inte göra det du vill få gjort. I det här fallet kan du skapa en anpassad funktion baserad på dina krav.
• De inbyggda funktionerna kan få jobbet gjort men formeln är lång och komplicerad. I det här fallet kan du skapa en anpassad funktion som är lätt att läsa och använda.

Observera att anpassade funktioner som skapats med VBA kan vara betydligt långsammare än de inbyggda funktionerna. Därför är dessa bäst lämpade för situationer där du inte kan få resultatet med de inbyggda funktionerna.

Funktion Vs. Subrutin i VBA

Med en "underrutin" kan du köra en uppsättning kod medan en "funktion" returnerar ett värde (eller en rad värden).

För att ge dig ett exempel, om du har en lista med siffror (både positiva och negativa) och du vill identifiera de negativa talen, är det här du kan göra med en funktion och en subrutin.

En subrutin kan gå igenom varje cell i intervallet och kan markera alla celler som har ett negativt värde i den. I det här fallet slutar subrutinen att ändra egenskaperna för intervallobjektet (genom att ändra cellens färg).

Med en anpassad funktion kan du använda den i en separat kolumn och den kan returnera en SANN om värdet i en cell är negativt och FALSKT om det är positivt. Med en funktion kan du inte ändra objektets egenskaper. Det betyder att du inte kan ändra cellens färg med en funktion i sig (du kan dock göra det med villkorlig formatering med den anpassade funktionen).

När du skapar en användardefinierad funktion (UDF) med VBA kan du använda den funktionen i kalkylbladet precis som alla andra funktioner. Jag kommer att täcka mer om detta i avsnittet 'Olika sätt att använda en användardefinierad funktion i Excel'.

Skapa en enkel användardefinierad funktion i VBA

Låt mig skapa en enkel användardefinierad funktion i VBA och visa hur det fungerar.

Koden nedan skapar en funktion som extraherar de numeriska delarna från en alfanumerisk sträng.

Funktion GetNumeric (CellRef As String) som Long Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Result & Mid (CellRef, i, 1) Nästa i GetNumeric = Resultatavslutningsfunktion

När du har ovanstående kod i modulen kan du använda den här funktionen i arbetsboken.

Nedan är hur denna funktion - GetNumeric - kan användas i Excel.

Nu innan jag berättar hur denna funktion skapas i VBA och hur den fungerar, finns det några saker du bör veta:

• När du skapar en funktion i VBA blir den tillgänglig i hela arbetsboken precis som alla andra vanliga funktioner.
• När du skriver funktionsnamnet följt av lika med tecknet visar Excel namnet på funktionen i listan över matchande funktioner. I exemplet ovan, när jag angav = Get, visade Excel mig en lista som hade min anpassade funktion.

Jag tror att detta är ett bra exempel när du kan använda VBA för att skapa en enkel att använda funktion i Excel. Du kan också göra samma sak med en formel (som visas i den här självstudien), men det blir komplicerat och svårt att förstå. Med denna UDF behöver du bara skicka ett argument och du får resultatet.

Anatomi för en användardefinierad funktion i VBA

I avsnittet ovan gav jag dig koden och visade dig hur UDF -funktionen fungerar i ett kalkylblad.

Låt oss nu dyka djupt och se hur denna funktion skapas. Du måste placera koden nedan i en modul i VB Editor. Jag täcker detta ämne i avsnittet - 'Var ska man lägga VBA -koden för en användardefinierad funktion'.

Funktion GetNumeric (CellRef As String) som Long 'Denna funktion extraherar den numeriska delen från strängen Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Resultat & mitten (CellRef, i, 1) Nästa i GetNumeric = Resultatavslutningsfunktion

Den första raden i koden börjar med ordet - Funktion.

Detta ord berättar för VBA att vår kod är en funktion (och inte en underrutin). Ordet Funktion följs av namnet på funktionen - GetNumeric. Detta är namnet som vi kommer att använda i kalkylbladet för att använda den här funktionen.

• Funktionens namn får inte ha mellanslag. Du kan inte heller namnge en funktion om den krockar med namnet på en cellreferens. Till exempel kan du inte namnge funktionen ABC123 eftersom den också refererar till en cell i Excel -kalkylblad.
• Du bör inte ge din funktion samma namn som för en befintlig funktion. Om du gör detta skulle Excel ge företräde åt den inbyggda funktionen.
• Du kan använda en understrykning om du vill skilja ord. Till exempel är Get_Numeric ett acceptabelt namn.

Funktionsnamnet följs av några argument inom parentes. Detta är de argument som vår funktion skulle behöva från användaren. Det här är precis som de argument som vi behöver leverera till Excels inbyggda funktioner. Till exempel i en COUNTIF -funktion finns det två argument (intervall och kriterier)

Inom parentesen måste du ange argumenten.

I vårt exempel finns det bara ett argument - CellRef.

Det är också bra att ange vilken typ av argument funktionen förväntar sig. I det här exemplet, eftersom vi kommer att mata funktionen med en cellreferens, kan vi ange argumentet som en "Range" -typ. Om du inte anger en datatyp skulle VBA betrakta det som en variant (vilket innebär att du kan använda vilken datatyp som helst).

Om du har mer än ett argument kan du ange dem i samma parentes - separerade med ett kommatecken. Vi kommer att se senare i denna handledning om hur du använder flera argument i en användardefinierad funktion.

Observera att funktionen har angetts som datatypen "String". Detta skulle berätta för VBA att resultatet av formeln skulle vara av strängdatatypen.

Även om jag kan använda en numerisk datatyp här (till exempel lång eller dubbel), skulle det begränsa antalet siffror som den kan returnera. Om jag har en 20 -tal lång sträng som jag behöver extrahera från den övergripande strängen, skulle deklarera funktionen som en lång eller dubbel ge ett fel (eftersom antalet skulle vara utanför dess intervall). Därför har jag behållit funktionens utdatatyp som sträng.

Den andra raden i koden - den i grönt som börjar med en apostrof - är en kommentar. När du läser koden ignorerar VBA denna rad. Du kan använda detta för att lägga till en beskrivning eller en detalj om koden.

Den tredje raden i koden deklarerar en variabel 'StringLength' som en heltal datatyp. Detta är variabeln där vi lagrar värdet på längden på strängen som analyseras med formeln.

Den fjärde raden deklarerar variabeln Resultat som en strängdatatyp. Detta är variabeln där vi kommer att extrahera siffrorna från den alfanumeriska strängen.

Den femte raden tilldelar strängens längd i inmatningsargumentet till variabeln "StringLength". Observera att "CellRef" hänvisar till argumentet som kommer att ges av användaren när du använder formeln i kalkylbladet (eller använder det i VBA - som vi kommer att se senare i den här självstudien).

Sjätte, sjunde och åttonde raden är delen av For Next -slingan. Slingan körs så många gånger som många tecken finns i inmatningsargumentet. Detta nummer ges av LEN -funktionen och tilldelas variabeln "StringLength".

Så slingan går från ‘1 till stränglängd’.

Inom slingan analyserar IF -satsen varje tecken i strängen och om det är numeriskt lägger det till det numeriska tecknet i resultatvariabeln. Den använder MID -funktionen i VBA för att göra detta.

Den näst sista raden i koden tilldelar funktionen värdet av resultatet. Det är den här kodraden som säkerställer att funktionen returnerar "Resultat" -värdet tillbaka i cellen (varifrån den kallas).

Den sista raden i koden är Slutfunktion. Detta är en obligatorisk kodrad som berättar för VBA att funktionskoden slutar här.

Ovanstående kod förklarar de olika delarna av en typisk anpassad funktion som skapats i VBA. I följande avsnitt kommer vi att fördjupa oss i dessa element och också se de olika sätten att köra VBA -funktionen i Excel.

Argument i en användardefinierad funktion i VBA

I exemplen ovan, där vi skapade en användardefinierad funktion för att få den numeriska delen från en alfanumerisk sträng (GetNumeric), var funktionen utformad för att ta ett enda argument.

I det här avsnittet kommer jag att täcka hur man skapar funktioner som inte tar något argument till de som tar flera argument (krävs såväl som valfria argument).

Skapa en funktion i VBA utan några argument

I Excel -kalkylbladet har vi flera funktioner som inte tar några argument (t.ex. RAND, TODAY, NU).

Dessa funktioner är inte beroende av några ingångsargument. Till exempel returnerar TODAY -funktionen det aktuella datumet och RAND -funktionen returnerar ett slumpmässigt tal mellan 0 och 1.

Du kan också skapa en liknande funktion i VBA.

Nedan finns koden som ger dig namnet på filen. Det tar inga argument eftersom resultatet det behöver returnera inte är beroende av något argument.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name Slutfunktion

Koden ovan anger funktionens resultat som en strängdatatyp (eftersom resultatet vi vill ha är filnamnet - vilket är en sträng).

Denna funktion tilldelar värdet "ThisWorkbook.Name" till funktionen, som returneras när funktionen används i kalkylbladet.

Om filen har sparats returnerar den namnet med filtillägget, annars ger det helt enkelt namnet.

Ovanstående har dock en fråga.

Om filnamnet ändras uppdateras det inte automatiskt. Normalt uppdateras en funktion när det finns en ändring av inmatningsargumenten. Men eftersom det inte finns några argument i den här funktionen beräknas inte funktionen igen (även om du ändrar arbetsbokens namn, stäng den och öppna den igen).

Om du vill kan du tvinga fram en ny beräkning med hjälp av kortkommandot - Ctrl + Alt + F9.

För att göra formeln omberäknad när det finns en ändring i kalkylbladet måste du en rad med kod till den.

Koden nedan gör att funktionen omberäknas när det finns en ändring i kalkylbladet (precis som andra liknande kalkylbladsfunktioner som TODAY eller RAND -funktionen).

Function WorkbookName () Som String Application.Volatile True WorkbookName = ThisWorkbook.Name Slutfunktion

Om du nu ändrar arbetsbokens namn uppdateras den här funktionen när det finns någon ändring i kalkylbladet eller när du öppnar arbetsboken igen.

Skapa en funktion i VBA med ett argument

I ett av avsnitten ovan har vi redan sett hur man skapar en funktion som bara tar ett argument (GetNumeric -funktionen som täcks ovan).

Låt oss skapa en annan enkel funktion som bara tar ett argument.

Funktion skapad med nedanstående kod skulle konvertera den refererade texten till versaler. Nu har vi redan en funktion för det i Excel, och den här funktionen är bara för att visa dig hur det fungerar. Om du måste göra detta är det bättre att använda den inbyggda UPPER -funktionen.

Funktion ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) Slutfunktion

Denna funktion använder UCase -funktionen i VBA för att ändra värdet på CellRef -variabeln. Det tilldelar sedan värdet till funktionen ConvertToUpperCase.

Eftersom denna funktion tar ett argument behöver vi inte använda Application.Volatile -delen här. Så snart argumentet ändras uppdateras funktionen automatiskt.

Skapa en funktion i VBA med flera argument

Precis som kalkylbladets funktioner kan du skapa funktioner i VBA som tar flera argument.

Koden nedan skapar en funktion som extraherar texten före den angivna avgränsaren. Det krävs två argument - cellreferensen som har textsträngen och avgränsaren.

Funktion GetDataBeforeDelimiter (CellRef As Range, Delim As String) som String Dim Resultat As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 Result = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Result End Function

När du behöver använda mer än ett argument i en användardefinierad funktion kan du ha alla argument inom parentesen åtskilda med ett kommatecken.

Observera att för varje argument kan du ange en datatyp. I exemplet ovan har "CellRef" deklarerats som en intervalldatatyp och "Delim" har deklarerats som en strängdatatyp. Om du inte anger någon datatyp anser VBA att dessa är en variantdatatyp.

När du använder ovanstående funktion i kalkylbladet måste du ange cellreferensen som har texten som det första argumentet och avgränsningstecknen i dubbla citattecken som det andra argumentet.

Den kontrollerar sedan avgränsarens position med hjälp av INSTR -funktionen i VBA. Denna position används sedan för att extrahera alla tecken före avgränsaren (med VÄNSTER -funktionen).

Slutligen tilldelar den funktionen resultatet.

Denna formel är långt ifrån perfekt. Om du till exempel anger en avgränsare som inte finns i texten skulle det ge ett fel. Nu kan du använda IFERROR -funktionen i kalkylbladet för att bli av med felen, eller så kan du använda koden nedan som returnerar hela texten när den inte kan hitta avgränsaren.

Funktion GetDataBeforeDelimiter (CellRef As Range, Delim As String) som String Dim Resultat As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len (CellRef) Resultat = Vänster ( CellRef, DelimPosition) GetDataBeforeDelimiter = Resultat slutfunktion

Vi kan ytterligare optimera denna funktion.

Om du anger texten (från vilken du vill extrahera delen före avgränsaren) direkt i funktionen, skulle det ge dig ett fel. Prova!

Detta händer när vi har angett "CellRef" som en datatyp för intervall.

Eller, om du vill att avgränsaren ska vara i en cell och använda cellreferensen istället för att hårdkoda den i formeln, kan du inte göra det med ovanstående kod. Det beror på att avgränsningen har deklarerats som en strängdatatyp.

Om du vill att funktionen ska ha flexibiliteten att acceptera direkt textinmatning eller cellreferenser från användaren måste du ta bort datatypdeklarationen. Detta skulle sluta göra argumentet som en variant datatyp, som kan ta vilken typ av argument som helst och bearbeta det.

Koden nedan skulle göra detta:

Funktion GetDataBeforeDelimiter (CellRef, Delim) As String Dim Resultat As String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len (CellRef) Result = Left (CellRef, DelimPosition) GetDataBeforeDelimiter = Resultat slutfunktion

Skapa en funktion i VBA med valfria argument

Det finns många funktioner i Excel där några av argumenten är valfria.

Till exempel har den legendariska VLOOKUP -funktionen tre obligatoriska argument och ett valfritt argument.

Ett valfritt argument, som namnet antyder, är valfritt att ange. Om du inte anger något av de obligatoriska argumenten kommer din funktion att ge dig ett fel, men om du inte anger det valfria argumentet fungerar din funktion.

Men valfria argument är inte värdelösa. De låter dig välja mellan en rad alternativ.

Till exempel, i VLOOKUP -funktionen, om du inte anger det fjärde argumentet, gör VLOOKUP en ungefärlig sökning och om du anger det sista argumentet som FALSE (eller 0), gör det en exakt matchning.

Kom ihåg att de valfria argumenten alltid måste komma efter alla nödvändiga argument. Du kan inte ha valfria argument i början.

Låt oss nu se hur du skapar en funktion i VBA med valfria argument.

Funktion med endast ett valfritt argument

Så vitt jag vet finns det ingen inbyggd funktion som bara tar valfria argument (jag kan ha fel här, men jag kan inte tänka mig någon sådan funktion).

Men vi kan skapa en med VBA.

Nedan finns koden för funktionen som ger dig det aktuella datumet i dd-mm-åååå-formatet om du inte anger något argument (dvs lämna det tomt) och i "dd mmmm, åååå" -format om du anger något som argumentet (dvs. allt så att argumentet inte är tomt).

Funktion CurrDate (Valfritt fmt som variant) Dim Resultat Om IsMissing (fmt) Sedan CurrDate = Format (datum, "dd-mm-åååå") Annat CurrDate = Format (datum, "dd mmmm, åååå") Slut om slutfunktion

Observera att funktionen ovan använder "IsMissing" för att kontrollera om argumentet saknas eller inte. För att använda IsMissing -funktionen måste ditt valfria argument vara av datatypen variant.

Ovanstående funktion fungerar oavsett vad du anger som argument. I koden kontrollerar vi bara om det valfria argumentet tillhandahålls eller inte.

Du kan göra detta mer robust genom att bara ta specifika värden som argument och visa ett fel i resten av fallen (som visas i koden nedan).

Funktion CurrDate (Valfritt fmt som variant) Dim Resultat If IsMissing (fmt) Sedan CurrDate = Format (datum, "dd-mm-åååå") ElseIf fmt = 1 Sedan CurrDate = Format (datum, "dd mmmm, åååå") Annat CurrDate = CVErr (xlErrValue) End If End -funktion

Ovanstående kod skapar en funktion som visar datumet i "dd-mm-åååå" -formatet om inget argument tillhandahålls och i "dd mmmm, åååå" -formatet när argumentet är 1. Det ger ett fel i alla andra fall.

Funktion med både obligatoriska och valfria argument

Vi har redan sett en kod som extraherar den numeriska delen från en sträng.

Låt oss nu titta på ett liknande exempel som tar både nödvändiga och valfria argument.

Koden nedan skapar en funktion som extraherar textdelen från en sträng. Om det valfria argumentet är SANT, ger det resultatet i versaler, och om det valfria argumentet är FALSKT eller utelämnas, ger det resultatet som det är.

Funktion GetText (CellRef As Range, Valfritt TextCase = False) As String Dim StringLength As Integer Dim Result As String StringLength = Len (CellRef) For i = 1 To StringLength If Not (IsNumeric (Mid (CellRef, i, 1))) Then Resultat = Resultat & Mitt (CellRef, i, 1) Nästa i Om TextCase = True Då är Resultat = UCase (Resultat) GetText = Resultatavslutningsfunktion

Observera att i koden ovan har vi initialiserat värdet för 'TextCase' som falskt (se inom parentesen på den första raden).

Genom att göra detta har vi säkerställt att det valfria argumentet börjar med standardvärdet, vilket är FALSKT. Om användaren anger värdet som SANT, returnerar funktionen texten med stora bokstäver, och om användaren anger det valfria argumentet som FALSKT eller utelämnar det, så returneras texten som den är.

Skapa en funktion i VBA med en matris som argument

Hittills har vi sett exempel på att skapa en funktion med valfria/obligatoriska argument - där dessa argument var ett enda värde.

Du kan också skapa en funktion som kan ta en array som argument. I Excel -kalkylbladets funktioner finns det många funktioner som tar array -argument, till exempel SUMMA, VLOOKUP, SUMIF, COUNTIF, etc.

Nedan finns koden som skapar en funktion som ger summan av alla jämna tal i det angivna intervallet för cellerna.

Funktion AddEven (CellRef som intervall) Dim Cell som intervall för varje cell i CellRef If IsNumeric (Cell.Value) Then If Cell.Value Mod 2 = 0 Då Resultat = Resultat + Cell.Value End Om End Om Next Cell AddEven = Result End Fungera

Du kan använda den här funktionen i kalkylbladet och ange cellintervallet som har siffrorna som argument. Funktionen skulle returnera ett enda värde - summan av alla jämna tal (som visas nedan).

I ovanstående funktion, i stället för ett enda värde, har vi levererat en array (A1: A10). För att detta ska fungera måste du se till att datatypen för argumentet kan acceptera en array.

I koden ovan specificerade jag argumentet CellRef som Range (som kan ta en array som ingång). Du kan också använda variantdatatypen här.

I koden finns det en för varje slinga som går igenom varje cell och kontrollerar om det är ett antal inte. Om det inte är det händer ingenting och det flyttar till nästa cell. Om det är ett tal kontrollerar det om det är jämnt eller inte (med hjälp av MOD -funktionen).

I slutändan läggs alla jämna tal till och summan tilldelas tillbaka till funktionen.

Skapa en funktion med obegränsat antal argument

När du skapar vissa funktioner i VBA kanske du inte vet det exakta antalet argument som en användare vill tillhandahålla. Så behovet är att skapa en funktion som kan acceptera så många argument som tillhandahålls och använda dessa för att returnera resultatet.

Ett exempel på en sådan kalkylbladsfunktion är funktionen SUMMA. Du kan ange flera argument för det (som detta):

= SUMMA (A1, A2: A4, B1: B20)

Ovanstående funktion skulle lägga till värdena i alla dessa argument. Observera också att dessa kan vara en enda cell eller en rad celler.

Du kan skapa en sådan funktion i VBA genom att ha det sista argumentet (eller det kan vara det enda argumentet) som valfritt. Detta valfria argument bör också föregås av sökordet 'ParamArray'.

'ParamArray' är en modifierare som låter dig acceptera så många argument du vill. Observera att argumentet är valfritt om du använder ordet ParamArray före ett argument. Du behöver dock inte använda ordet valfritt här.

Låt oss nu skapa en funktion som kan acceptera ett godtyckligt antal argument och skulle lägga till alla siffror i de angivna argumenten:

Funktion AddArguments (ParamArray arglist () som variant) För varje arg I arglist AddArguments = AddArguments + arg Nästa arg Slutfunktion

Ovanstående funktion kan ta valfritt antal argument och lägga till dessa argument för att ge resultatet.

Observera att du bara kan använda ett enda värde, en cellreferens, en boolean eller ett uttryck som argument. Du kan inte tillhandahålla en array som argument. Till exempel, om ett av dina argument är D8: D10, skulle denna formel ge dig ett fel.

Om du vill kunna använda båda flercelliga argumenten måste du använda koden nedan:

Funktion AddArguments (ParamArray arglist () som variant) För varje arg I arglist För varje cell i arg AddArguments = AddArguments + Cell Nästa cell Nästa arg Slutfunktion

Observera att denna formel fungerar med flera celler och matrisreferenser, men den kan inte bearbeta hårdkodade värden eller uttryck. Du kan skapa en mer robust funktion genom att kontrollera och behandla dessa tillstånd, men det är inte avsikten här.

Avsikten här är att visa dig hur ParamArray fungerar så att du kan tillåta ett obegränsat antal argument i funktionen. Om du vill ha en bättre funktion än den som skapats av ovanstående kod, använd SUM -funktionen i kalkylbladet.

Skapa en funktion som returnerar en matris

Hittills har vi sett funktioner som returnerar ett enda värde.

Med VBA kan du skapa en funktion som returnerar en variant som kan innehålla en hel rad värden.

Arrayformler är också tillgängliga som inbyggda funktioner i Excel -kalkylblad. Om du är bekant med matrisformler i Excel vet du att dessa matas in med Ctrl + Skift + Retur (istället för bara Enter). Du kan läsa mer om matrisformler här. Om du inte känner till matrisformler, oroa dig inte, fortsätt läsa.

Låt oss skapa en formel som returnerar en array med tre nummer (1,2,3).

Koden nedan skulle göra detta.

Funktion ThreeNumbers () Som variant Dim NumberValue (1 till 3) NumberValue (1) = 1 NumberValue (2) = 2 NumberValue (3) = 3 ThreeNumbers = NumberValue End Function

I koden ovan har vi specificerat funktionen "Tre nummer" som en variant. Detta gör att den kan hålla en rad värden.

Variabeln 'NumberValue' deklareras som en array med tre element. Den håller de tre värdena och tilldelar funktionen "Tre nummer".

Du kan använda den här funktionen i kalkylbladet genom att gå in i funktionen och trycka på Ctrl + Shift + Enter -tangenten (håll ned Ctrl och Shift -tangenterna och tryck sedan på Enter).

När du gör detta kommer den att returnera 1 i cellen, men i verkligheten håller den alla tre värdena. För att kontrollera detta, använd följande formel:

= MAX (ThreeNumbers ())

Använd funktionen ovan med Control + Shift + Enter. Du kommer att märka att resultatet nu är 3, eftersom det är de största värdena i arrayen som returneras av Max -funktionen, som får de tre siffrorna som ett resultat av vår användardefinierade funktion - ThreeNumbers.

Du kan använda samma teknik för att skapa en funktion som returnerar en uppsättning månadnamn som visas med koden nedan:

Funktion Månader () Som variant Dim Månadsnamn (1 till 12) Månadsnamn (1) = "Januari" Månadsnamn (2) = "Februari" Månadsnamn (3) = "Mars" Månadsnamn (4) = "April" Månadsnamn (5) = "May" MonthName (6) = "June" MonthName (7) = "July" MonthName (8) = "Augusti" MonthName (9) = "September" MonthName (10) = "October" MonthName (11) = "November "MonthName (12) =" December "Months = MonthName Slutfunktion

Nu när du anger funktionen = Månader () i Excel -kalkylbladet och använder Ctrl + Skift + Retur, returnerar det hela uppsättningen månadnamn. Observera att du bara ser januari i cellen eftersom det är det första värdet i matrisen. Detta betyder inte att matrisen bara returnerar ett värde.

För att visa dig det faktum att det returnerar alla värden gör du detta - markera cellen med formeln, gå till formelfältet, välj hela formeln och tryck på F9. Detta visar dig alla värden som funktionen returnerar.

Du kan använda detta genom att använda nedanstående INDEX -formel för att få en lista över alla månadens namn på en gång.

= INDEX (månader (), RAD ())

Om du nu har många värden är det inte bra att tilldela dessa värden en efter en (som vi har gjort ovan). Istället kan du använda Array -funktionen i VBA.

Så samma kod där vi skapar funktionen "Månader" skulle bli kortare som visas nedan:

Funktionsmånader () som variantmånader = Array ("januari", "februari", "mars", "april", "maj", "juni", _ "juli", "augusti", "september", "oktober" , "November", "December") Slutfunktion

Ovanstående funktion använder Array -funktionen för att tilldela funktionen direkt värdena.

Observera att alla funktioner som skapats ovan returnerar en horisontell uppsättning värden. Det betyder att om du väljer 12 horisontella celler (låt oss säga A1: L1) och anger = månaders () formel i cell A1, kommer det att ge dig alla månadens namn.

Men vad händer om du vill ha dessa värden i ett vertikalt cellområde.

Du kan göra detta genom att använda TRANSPOSE -formeln i kalkylbladet.

Välj helt enkelt 12 vertikala celler (sammanhängande) och ange formeln nedan.

Förstå omfattningen av en användardefinierad funktion i Excel

En funktion kan ha två omfattningar - offentlig eller Privat.

• A Allmän omfattning betyder att funktionen är tillgänglig för alla blad i arbetsboken samt alla procedurer (sub och funktion) för alla moduler i arbetsboken. Detta är användbart när du vill anropa en funktion från en delrutin (vi får se hur detta görs i nästa avsnitt).
• A Privat omfattning betyder att funktionen endast är tillgänglig i modulen där den finns. Du kan inte använda den i andra moduler. Du kommer inte heller att se det i listan över funktioner i kalkylbladet. Till exempel, om ditt funktionsnamn är "Månader ()" och du skriver funktion i Excel (efter = -tecknet), kommer det inte att visa dig funktionsnamnet. Du kan dock fortfarande använda det om du anger formelnamnet.

Om du inte anger något är funktionen som standard en offentlig funktion.

Nedan visas en funktion som är en privat funktion:

Private Function WorkbookName () As String WorkbookName = ThisWorkbook.Name Slutfunktion

Du kan använda den här funktionen i delrutinerna och procedurerna i samma moduler, men kan inte använda den i andra moduler. Den här funktionen visas inte heller i kalkylbladet.

Koden nedan skulle göra denna funktion offentlig. Det kommer också att visas i kalkylbladet.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name Slutfunktion

Olika sätt att använda en användardefinierad funktion i Excel

När du har skapat en användardefinierad funktion i VBA kan du använda den på många olika sätt.

Låt oss först täcka hur du använder funktionerna i kalkylbladet.

Använda UDF i kalkylblad

Vi har redan sett exempel på att använda en funktion som skapats i VBA i kalkylbladet.

Allt du behöver göra är att ange funktionsnamnet, och det visas i intellisense.

Observera att för att funktionen ska visas i kalkylbladet måste den vara en offentlig funktion (som förklaras i avsnittet ovan).

Du kan också använda dialogrutan Infoga funktion för att infoga den användardefinierade funktionen (med hjälp av stegen nedan). Detta fungerar bara för funktioner som är offentliga.

• Gå till fliken Data.
• Klicka på alternativet "Infoga funktion".
• I dialogrutan Infoga funktion väljer du Användardefinierad som kategori. Det här alternativet visas bara när du har en funktion i VB -redigeraren (och funktionen är offentlig).
• Välj funktionen från listan över alla de offentliga användardefinierade funktionerna.
• Klicka på OK -knappen.

Stegen ovan skulle infoga funktionen i kalkylbladet. Det visar också en dialogruta med funktionsargument som ger dig information om argumenten och resultatet.

Du kan använda en användardefinierad funktion precis som alla andra funktioner i Excel. Detta innebär också att du kan använda den med andra inbyggda Excel -funktioner. Till exempel. nedanstående formel skulle ge arbetsbokens namn i versaler:

= UPPER (WorkbookName ())

Använda användardefinierade funktioner i VBA -procedurer och funktioner

När du har skapat en funktion kan du också använda den i andra delprocedurer.

Om funktionen är offentlig kan den användas i valfri procedur i samma eller olika modul. Om det är privat kan det bara användas i samma modul.

Nedan visas en funktion som returnerar arbetsbokens namn.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name Slutfunktion

Nedanstående procedur kallar funktionen och visar sedan namnet i en meddelanderuta.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Du kan också ringa en funktion från en annan funktion.

I nedanstående koder returnerar den första koden arbetsbokens namn, och den andra returnerar namnet i versaler genom att anropa den första funktionen.

Function WorkbookName () As String WorkbookName = ThisWorkbook.Name Slutfunktion

Function WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Slutfunktion

Anropa en användardefinierad funktion från andra arbetsböcker

Om du har en funktion i en arbetsbok kan du också kalla den här funktionen i andra arbetsböcker.

Det finns flera sätt att göra detta:

1. Skapa ett tillägg
2. Sparfunktion i Personal Macro Workbook
3. Hänvisning till funktionen från en annan arbetsbok.

Skapa ett tillägg

Genom att skapa och installera ett tillägg har du den anpassade funktionen i den tillgänglig i alla arbetsböcker.

Antag att du har skapat en anpassad funktion - ‘GetNumeric’ och du vill ha den i alla arbetsböcker. För att göra detta, skapa en ny arbetsbok och ha funktionskoden i en modul i den här nya arbetsboken.

Följ nu stegen nedan för att spara det som ett tillägg och sedan installera det i Excel.

• Gå till fliken Arkiv och klicka på Spara som.
• I dialogrutan Spara som ändrar du typen "Spara som" till .xlam. Namnet du tilldelar filen skulle vara namnet på ditt tillägg. I det här exemplet sparas filen med namnet GetNumeric.
  • Du kommer att märka att sökvägen till filen där den sparas automatiskt ändras. Du kan använda standard eller ändra den om du vill.
• Öppna en ny Excel -arbetsbok och gå till fliken Utvecklare.
• Klicka på alternativet Excel-tillägg.
• I dialogrutan Tillägg bläddrar du till och hittar filen du sparade och klickar på OK.

Nu har tillägget aktiverats.

Nu kan du använda den anpassade funktionen i alla arbetsböcker.

Spara funktionen i Personal Macro Workbook

En personlig makro arbetsbok är en dold arbetsbok i ditt system som öppnas när du öppnar Excel -programmet.

Det är en plats där du kan lagra makrokoder och sedan komma åt dessa makron från valfri arbetsbok. Det är ett bra ställe att lagra de makron som du vill använda ofta.

Som standard finns det ingen personlig makro -arbetsbok i Excel. Du måste skapa det genom att spela in ett makro och spara det i den personliga makro -arbetsboken.

Du hittar de detaljerade stegen om hur du skapar och sparar makron i den personliga makro -arbetsboken här.

Hänvisning till funktionen från en annan arbetsbok

Medan de två första metoderna (att skapa ett tillägg och använda personlig makro-arbetsbok) skulle fungera i alla situationer, om du vill referera till funktionen från en annan arbetsbok, måste arbetsboken vara öppen.

Antag att du har en arbetsbok med namnet 'Arbetsbok med formel, och den har funktionen med namnet 'GetNumeric ’.

För att använda den här funktionen i en annan arbetsbok (medan Arbetsbok med formel är öppen) kan du använda följande formel:

= 'Arbetsbok med formel'! GetNumeric (A1)

Ovanstående formel använder den användardefinierade funktionen i Arbetsbok med formel fil och ge dig resultatet.

Observera att eftersom arbetsbokens namn har mellanslag måste du bifoga det i enstaka citattecken.

Använda Exit Function Statement VBA

Om du vill avsluta en funktion medan koden körs kan du göra det med hjälp av uttalandet "Avsluta funktion".

Koden nedan skulle extrahera de tre första numeriska tecknen från en alfanumerisk textsträng. Så snart den får de tre tecknen avslutas funktionen och returnerar resultatet.

Funktion GetNumericFirstThree (CellRef As Range) Så länge Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Avsluta sedan funktionen if IsNumeric (Mid (CellRef, i, 1)) Då J = J + 1 Resultat = Resultat & mitten (CellRef, i, 1) GetNumericFirstThree = Resultat slut Om nästa i slut funktion

Ovanstående funktion söker efter antalet tecken som är numeriska, och när den får 3 numeriska tecken avslutar den funktionen i nästa slinga.

Felsöka en användardefinierad funktion

Det finns några tekniker du kan använda när du felsöker en användardefinierad funktion i VBA:

Felsöka en anpassad funktion med hjälp av meddelanderutan

Använd MsgBox -funktionen för att visa en meddelanderuta med ett visst värde.

Värdet du visar kan baseras på vad du vill testa. Till exempel, om du vill kontrollera om koden körs eller inte, skulle alla meddelanden fungera, och om du vill kontrollera om slingorna fungerar eller inte kan du visa ett specifikt värde eller loop -räknaren.

Felsöka en anpassad funktion genom att ställa in brytpunkten

Ställ in en brytpunkt för att kunna gå igenom varje rad en i taget. För att ställa in en brytpunkt, välj raden där du vill ha den och tryck på F9, eller klicka på det grå vertikala området som är kvar till kodraderna. Någon av dessa metoder skulle infoga en brytpunkt (du kommer att se en röd prick i det grå området).

När du har ställt in brytpunkten och du utför funktionen går den till brytpunktslinjen och stannar sedan. Nu kan du gå igenom koden med F8 -tangenten. Om du trycker på F8 går du till nästa rad i koden.

Felsöka en anpassad funktion med hjälp av Debug.Print i koden

Du kan använda Debug.Print -satsen i din kod för att få värdena för de angivna variablerna/argumenten i det omedelbara fönstret.

Till exempel i koden nedan har jag använt Debug.Print för att få värdet på två variabler - 'j' och 'Resultat'

Funktion GetNumericFirstThree (CellRef As Range) Så länge Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Avsluta sedan funktionen if IsNumeric (Mid (CellRef, i, 1)) Då J = J + 1 Resultat = Resultat & mitten (CellRef, i, 1) Debug.Print J, Resultat GetNumericFirstThree = Resultat slut Om nästa i slut funktion

När den här koden körs visar den följande i det omedelbara fönstret.

Excel Inbyggda funktioner vs. VBA användardefinierad funktion

Det finns få starka fördelar med att använda inbyggda Excel-funktioner jämfört med anpassade funktioner som skapats i VBA.

• Inbyggda funktioner är mycket snabbare än VBA -funktionerna.
• När du skapar en rapport/instrumentpanel med VBA -funktioner och skickar den till en klient/kollega behöver de inte oroa sig för om makron är aktiverade eller inte. I vissa fall blir klienter/kunder rädda av att se en varning i det gula fältet (som helt enkelt ber dem att aktivera makron).
• Med inbyggda Excel -funktioner behöver du inte oroa dig för filtillägg. Om du har makron eller användardefinierade funktioner i arbetsboken måste du spara den i .xlsm.

Även om det finns många starka skäl att använda inbyggda Excel-funktioner, är det i några fall bättre att använda en användardefinierad funktion.

• Det är bättre att använda en användardefinierad funktion om din inbyggda formel är enorm och komplicerad. Detta blir ännu mer relevant när du behöver någon annan för att uppdatera formlerna. Om du till exempel har en enorm formel som består av många olika funktioner kan till och med ändra en referens till en cell vara tråkigt och felaktigt. Istället kan du skapa en anpassad funktion som bara tar ett eller två argument och gör allt för att lyfta backend.
• När du måste få något gjort som inte kan göras med Excel inbyggda funktioner. Ett exempel på detta kan vara när du vill extrahera alla numeriska tecken från en sträng. I sådana fall uppväger fördelen med att använda en användardefinierad funktionsgaranti dess negativa.

Var ska man lägga VBA-koden för en användardefinierad funktion

När du skapar en anpassad funktion måste du lägga koden i kodfönstret för den arbetsbok där du vill ha funktionen.

Nedan följer stegen för att sätta koden för funktionen ‘GetNumeric’ i arbetsboken.

1. Gå till fliken Utvecklare.
2. Klicka på alternativet Visual Basic. Detta öppnar VB -redigeraren i backend.
3. I fönstret Projektutforskare i VB-redigeraren högerklickar du på valfritt objekt för arbetsboken där du vill infoga koden. Om du inte ser Project Explorer går du till fliken View och klickar på Project Explorer.
4. Gå till Infoga och klicka på Modul. Detta kommer att infoga ett modulobjekt för din arbetsbok.
5. Kopiera och klistra in koden i modulfönstret.

Du kanske också gillar följande Excel VBA -självstudier:

• Arbeta med celler och intervall i Excel VBA.
• Arbeta med kalkylblad i Excel VBA.
• Arbeta med arbetsböcker med VBA.
• Hur man använder loopar i Excel VBA.
• Excel VBA -evenemang - En enkel (och komplett) guide
• Använda IF Then Else Statements i VBA.
• Hur man spelar in ett makro i Excel.
• Hur man kör ett makro i Excel.
• Så här sorterar du data i Excel med VBA (en steg-för-steg-guide).
• Excel VBA InStr -funktion - förklaras med exempel.