Oprettelse af en brugerdefineret funktion (UDF) i Excel VBA (Ultimate Guide)

Med VBA kan du oprette en brugerdefineret funktion (også kaldet en brugerdefineret funktion), der kan bruges i regnearkene ligesom almindelige funktioner.

Disse er nyttige, når de eksisterende Excel -funktioner ikke er nok. I sådanne tilfælde kan du oprette din egen brugerdefinerede brugerdefinerede funktion (UDF) for at imødekomme dine specifikke behov.

I denne vejledning dækker jeg alt om oprettelse og brug af brugerdefinerede funktioner i VBA.

Hvis du er interesseret i at lære VBA på den nemme måde, så tjek min Online Excel VBA -træning.

Hvad er en funktionsprocedure i VBA?

En funktionsprocedure er en VBA -kode, der udfører beregninger og returnerer en værdi (eller en række værdier).

Ved hjælp af en funktionsprocedure kan du oprette en funktion, som du kan bruge i regnearket (ligesom enhver almindelig Excel -funktion, f.eks. SUM eller VLOOKUP).

Når du har oprettet en funktionsprocedure ved hjælp af VBA, kan du bruge den på tre måder:

1. Som en formel i regnearket, hvor den kan tage argumenter som input og returnerer en værdi eller en række værdier.
2. Som en del af din VBA -underprogramkode eller en anden funktionskode.
3. I betinget formatering.

Selvom der allerede er 450+ indbyggede Excel -funktioner tilgængelige i regnearket, har du muligvis brug for en brugerdefineret funktion, hvis:

• De indbyggede funktioner kan ikke gøre, hvad du ønsker at få gjort. I dette tilfælde kan du oprette en brugerdefineret funktion baseret på dine krav.
• De indbyggede funktioner kan få arbejdet gjort, men formlen er lang og kompliceret. I dette tilfælde kan du oprette en brugerdefineret funktion, der er let at læse og bruge.

Bemærk, at brugerdefinerede funktioner, der er oprettet ved hjælp af VBA, kan være betydeligt langsommere end de indbyggede funktioner. Derfor er disse bedst egnet til situationer, hvor du ikke kan få resultatet ved hjælp af de indbyggede funktioner.

Funktion vs. Underprogram i VBA

En 'Underrutine' giver dig mulighed for at udføre et sæt kode, mens en 'Funktion' returnerer en værdi (eller en række værdier).

For at give dig et eksempel, hvis du har en liste med tal (både positive og negative), og du vil identificere de negative tal, er det her, hvad du kan gøre med en funktion og en underprogram.

En underrutine kan gå gennem hver celle i området og kan fremhæve alle de celler, der har en negativ værdi i den. I dette tilfælde ender underprogrammet med at ændre egenskaberne for områdeobjektet (ved at ændre cellens farve).

Med en brugerdefineret funktion kan du bruge den i en separat kolonne, og den kan returnere en SAND, hvis værdien i en celle er negativ og FALSK, hvis den er positiv. Med en funktion kan du ikke ændre objektets egenskaber. Det betyder, at du ikke kan ændre cellens farve med en funktion i sig selv (du kan dog gøre det ved hjælp af betinget formatering med den brugerdefinerede funktion).

Når du opretter en brugerdefineret funktion (UDF) ved hjælp af VBA, kan du bruge den funktion i regnearket ligesom enhver anden funktion. Jeg vil dække mere om dette i afsnittet 'Forskellige måder at bruge en brugerdefineret funktion i Excel' på.

Oprettelse af en simpel brugerdefineret funktion i VBA

Lad mig oprette en simpel brugerdefineret funktion i VBA og vise dig, hvordan det fungerer.

Nedenstående kode opretter en funktion, der udtrækker de numeriske dele fra en alfanumerisk streng.

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)) Herefter Resultat = Resultat & Mid (CellRef, i, 1) Næste i GetNumeric = Resultatafslutningsfunktion

Når du har ovenstående kode i modulet, kan du bruge denne funktion i projektmappen.

Nedenfor er hvordan denne funktion - GetNumeric - kan bruges i Excel.

Nu før jeg fortæller dig, hvordan denne funktion oprettes i VBA, og hvordan den fungerer, er der et par ting, du bør vide:

• Når du opretter en funktion i VBA, bliver den tilgængelig i hele projektmappen ligesom alle andre almindelige funktioner.
• Når du skriver funktionsnavnet efterfulgt af lig med tegn, viser Excel navnet på funktionen på listen over matchende funktioner. I ovenstående eksempel, da jeg indtastede = Get, viste Excel mig en liste, der havde min tilpassede funktion.

Jeg mener, at dette er et godt eksempel, når du kan bruge VBA til at oprette en brugervenlig funktion i Excel. Du kan også gøre det samme med en formel (som vist i denne vejledning), men det bliver kompliceret og svært at forstå. Med denne UDF behøver du kun at passere et argument, og du får resultatet.

Anatomi af en brugerdefineret funktion i VBA

I ovenstående afsnit gav jeg dig koden og viste dig, hvordan UDF -funktionen fungerer i et regneark.

Lad os nu dykke dybt og se, hvordan denne funktion er skabt. Du skal placere nedenstående kode i et modul i VB Editor. Jeg dækker dette emne i afsnittet - 'Hvor skal man lægge VBA -koden til en brugerdefineret funktion'.

Funktion GetNumeric (CellRef As String) så lang 'Denne funktion udtrækker den numeriske del fra strengen Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Resultat & midten (CellRef, i, 1) Næste i GetNumeric = Resultatafslutningsfunktion

Den første linje i koden starter med ordet - Funktion.

Dette ord fortæller VBA, at vores kode er en funktion (og ikke en underprogram). Ordet Funktion efterfølges af funktionens navn - GetNumeric. Dette er det navn, vi vil bruge i regnearket til at bruge denne funktion.

• Funktionens navn må ikke have mellemrum i den. Du kan heller ikke navngive en funktion, hvis den kolliderer med navnet på en cellereference. For eksempel kan du ikke navngive funktionen ABC123, da den også refererer til en celle i Excel -regneark.
• Du bør ikke give din funktion det samme navn som en eksisterende funktion. Hvis du gør dette, vil Excel foretrække den indbyggede funktion.
• Du kan bruge en understregning, hvis du vil adskille ord. For eksempel er Get_Numeric et acceptabelt navn.

Funktionsnavnet efterfølges af nogle argumenter i parentes. Dette er de argumenter, som vores funktion ville have brug for fra brugeren. Det er ligesom de argumenter, vi skal levere til Excels indbyggede funktioner. For eksempel i en COUNTIF -funktion er der to argumenter (område og kriterier)

Inden for parentesen skal du angive argumenterne.

I vores eksempel er der kun et argument - CellRef.

Det er også en god praksis at angive, hvilken slags argument funktionen forventer. I dette eksempel, da vi vil fodre funktionen med en cellereference, kan vi specificere argumentet som en "Range" -type. Hvis du ikke angiver en datatype, ville VBA betragte den som en variant (hvilket betyder, at du kan bruge enhver datatype).

Hvis du har mere end et argument, kan du angive dem i samme parentes - adskilt med et komma. Vi vil senere se i denne vejledning om, hvordan du bruger flere argumenter i en brugerdefineret funktion.

Bemærk, at funktionen er angivet som datatypen 'streng'. Dette ville fortælle VBA, at resultatet af formlen ville være af strengdatatypen.

Selvom jeg kan bruge en numerisk datatype her (f.eks. Lang eller dobbelt), ville det begrænse rækkevidden af ​​numre, den kan returnere. Hvis jeg har en 20 nummer lang streng, som jeg skal udtrække fra den overordnede streng, ville erklæring af funktionen som en lang eller dobbelt give en fejl (da tallet ville være uden for sit område). Derfor har jeg beholdt funktionsoutputdatatypen som streng.

Den anden linje i koden - den i grønt, der starter med en apostrof - er en kommentar. Når man læser koden, ignorerer VBA denne linje. Du kan bruge dette til at tilføje en beskrivelse eller en detalje om koden.

Den tredje linje i koden erklærer en variabel 'StringLength' som en heltal datatype. Dette er variablen, hvor vi gemmer værdien af ​​længden af ​​strengen, der analyseres ved hjælp af formlen.

Den fjerde linje erklærer variablen Resultat som en strengdatatype. Dette er variablen, hvor vi vil udtrække tallene fra den alfanumeriske streng.

Den femte linje tildeler længden af ​​strengen i inputargumentet til varianten 'StringLength'. Bemærk, at 'CellRef' refererer til det argument, der vil blive givet af brugeren, mens du bruger formlen i regnearket (eller bruger det i VBA - som vi vil se senere i denne vejledning).

Sjette, syvende og ottende linje er en del af For Next -løkken. Sløjfen kører lige så mange gange, som mange tegn er der i inputargumentet. Dette nummer er givet af LEN -funktionen og er tildelt variablen 'StringLength'.

Så løkken løber fra '1 til strenglængde'.

Inden for løkken analyserer IF -sætningen hvert tegn i strengen, og hvis det er numerisk, tilføjer det det numeriske tegn til resultatvariablen. Det bruger MID -funktionen i VBA til at gøre dette.

Den næstsidste linje i koden tildeler værdien af ​​resultatet til funktionen. Det er denne kodelinje, der sikrer, at funktionen returnerer 'Resultat' -værdien tilbage i cellen (hvorfra den kaldes).

Den sidste linje i koden er Slutfunktion. Dette er en obligatorisk kodelinje, der fortæller VBA, at funktionskoden ender her.

Ovenstående kode forklarer de forskellige dele af en typisk brugerdefineret funktion, der er oprettet i VBA. I de følgende afsnit dykker vi dybt ned i disse elementer og ser også de forskellige måder at udføre VBA -funktionen på i Excel.

Argumenter i en brugerdefineret funktion i VBA

I ovenstående eksempler, hvor vi oprettede en brugerdefineret funktion for at få den numeriske del fra en alfanumerisk streng (GetNumeric), blev funktionen designet til at tage et enkelt argument.

I dette afsnit vil jeg dække, hvordan man opretter funktioner, der ikke tager noget argument til dem, der tager flere argumenter (påkrævet samt valgfrie argumenter).

Oprettelse af en funktion i VBA uden argumenter

I Excel -regneark har vi flere funktioner, der ikke tager nogen argumenter (såsom RAND, TODAY, NU).

Disse funktioner er ikke afhængige af input input argumenter. For eksempel returnerer TODAY -funktionen den aktuelle dato, og RAND -funktionen returnerer et tilfældigt tal mellem 0 og 1.

Du kan også oprette en lignende funktion i VBA.

Nedenfor er koden, der giver dig navnet på filen. Det tager ingen argumenter, da det resultat, det skal returnere, ikke er afhængigt af noget argument.

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

Ovenstående kode angiver funktionens resultat som en strengdatatype (da det resultat, vi ønsker, er filnavnet - som er en streng).

Denne funktion tildeler værdien af ​​'ThisWorkbook.Name' til funktionen, som returneres, når funktionen bruges i regnearket.

Hvis filen er gemt, returnerer den navnet med filtypen, ellers giver den ganske enkelt navnet.

Ovenstående har dog et problem.

Hvis filnavnet ændres, opdateres det ikke automatisk. Normalt opdateres en funktion, når der er en ændring i inputargumenterne. Men da der ikke er argumenter i denne funktion, genberegnes funktionen ikke (selvom du ændrer navnet på projektmappen, skal du lukke den og derefter genåbne igen).

Hvis du vil, kan du tvinge en genberegning ved at bruge tastaturgenvejen - Control + Alt + F9.

For at få formlen til at genberegne, når der er en ændring i regnearket, skal du en linje med kode til den.

Nedenstående kode får funktionen til at genberegne, når der er en ændring i regnearket (ligesom andre lignende regnearksfunktioner som TODAY eller RAND -funktion).

Funktion WorkbookName () Som String Application.Volatile True WorkbookName = ThisWorkbook.Name Afslut funktion

Hvis du nu ændrer projektmappens navn, opdateres denne funktion, når der er ændringer i regnearket, eller når du åbner denne projektmappe igen.

Oprettelse af en funktion i VBA med et argument

I et af afsnittene ovenfor har vi allerede set, hvordan man opretter en funktion, der kun tager ét argument (GetNumeric -funktionen dækket ovenfor).

Lad os oprette en anden enkel funktion, der kun tager et argument.

Funktion oprettet med nedenstående kode ville konvertere den refererede tekst til store bogstaver. Nu har vi allerede en funktion til det i Excel, og denne funktion er bare for at vise dig, hvordan det fungerer. Hvis du skal gøre dette, er det bedre at bruge den indbyggede UPPER -funktion.

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

Denne funktion bruger UCase -funktionen i VBA til at ændre værdien af ​​CellRef -variablen. Det tildeler derefter værdien til funktionen ConvertToUpperCase.

Da denne funktion tager et argument, behøver vi ikke at bruge Application.Volatile -delen her. Så snart argumentet ændres, opdateres funktionen automatisk.

Oprettelse af en funktion i VBA med flere argumenter

Ligesom regnearkfunktioner kan du oprette funktioner i VBA, der tager flere argumenter.

Nedenstående kode ville oprette en funktion, der vil udtrække teksten før den angivne afgrænser. Det kræver to argumenter - cellereferencen, der har tekststrengen, og afgrænsningen.

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

Når du skal bruge mere end et argument i en brugerdefineret funktion, kan du have alle argumenterne i parentesen adskilt med et komma.

Bemærk, at du for hvert argument kan angive en datatype. I ovenstående eksempel er 'CellRef' blevet erklæret som en datatype for område, og 'Delim' er blevet erklæret som en strengdatatype. Hvis du ikke angiver nogen datatype, mener VBA, at disse er en variantdatatype.

Når du bruger ovenstående funktion i regnearket, skal du angive cellehenvisningen, der har teksten som det første argument, og afgrænsningstegn (erne) i dobbelte anførselstegn som det andet argument.

Det kontrollerer derefter placeringen af ​​afgrænseren ved hjælp af INSTR -funktionen i VBA. Denne position bruges derefter til at udtrække alle tegnene før afgrænsningen (ved hjælp af VENSTRE -funktionen).

Endelig tildeler det funktionen resultatet.

Denne formel er langt fra perfekt. Hvis du f.eks. Indtaster en afgrænsning, der ikke findes i teksten, vil det give en fejl. Nu kan du bruge IFERROR -funktionen i regnearket til at slippe af med fejlene, eller du kan bruge nedenstående kode, der returnerer hele teksten, når den ikke kan finde afgrænsningen.

Funktion GetDataBeforeDelimiter (CellRef As Range, Delim As String) som String Dim Resultat Som String Dim DelimPosition As Integer DelimPosition = InStr (1, CellRef, Delim, vbBinaryCompare) - 1 If DelimPosition <0 Then DelimPosition = Len (CellRef) Resultat = Venstre ( CellRef, DelimPosition) GetDataBeforeDelimiter = Resultatafslutningsfunktion

Vi kan yderligere optimere denne funktion.

Hvis du indtaster teksten (hvorfra du vil udtrække delen før afgrænsningen) direkte i funktionen, ville det give dig en fejl. Fortsæt… prøv det!

Dette sker, da vi har specificeret 'CellRef' som en datatype for område.

Eller hvis du vil have afgrænsningen til at være i en celle og bruge cellereferencen i stedet for at kode den hårdt i formlen, kan du ikke gøre det med ovenstående kode. Det er fordi Delim er blevet erklæret som en strengdatatype.

Hvis du vil have, at funktionen har fleksibiliteten til at acceptere direkte tekstinput eller cellereferencer fra brugeren, skal du fjerne datatypedeklarationen. Dette ville ende med at gøre argumentet som en variant datatype, som kan tage enhver form for argument og behandle det.

Nedenstående kode ville gøre dette:

Funktion GetDataBeforeDelimiter (CellRef, Delim) As String Dim Result 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 = Resultatafslutningsfunktion

Oprettelse af en funktion i VBA med valgfrie argumenter

Der er mange funktioner i Excel, hvor nogle af argumenterne er valgfrie.

For eksempel har den legendariske VLOOKUP -funktion 3 obligatoriske argumenter og et valgfrit argument.

Et valgfrit argument, som navnet antyder, er valgfrit at angive. Hvis du ikke angiver et af de obligatoriske argumenter, giver din funktion dig en fejl, men hvis du ikke angiver det valgfrie argument, fungerer din funktion.

Men valgfrie argumenter er ikke ubrugelige. De giver dig mulighed for at vælge mellem en række muligheder.

For eksempel, i VLOOKUP -funktionen, hvis du ikke angiver det fjerde argument, foretager VLOOKUP et omtrentligt opslag, og hvis du angiver det sidste argument som FALSE (eller 0), gør det et eksakt match.

Husk, at de valgfrie argumenter altid skal komme efter alle de krævede argumenter. Du kan ikke have valgfrie argumenter i begyndelsen.

Lad os nu se, hvordan du opretter en funktion i VBA med valgfrie argumenter.

Funktion med kun et valgfrit argument

Så vidt jeg ved, er der ingen indbygget funktion, der kun tager valgfrie argumenter (jeg kan tage fejl her, men jeg kan ikke tænke på nogen sådan funktion).

Men vi kan oprette en med VBA.

Nedenfor er koden for funktionen, der giver dig den aktuelle dato i dd-mm-åååå-formatet, hvis du ikke indtaster noget argument (dvs. lad det stå tomt) og i "dd mmmm, åååå" -format, hvis du indtaster noget som argumentet (dvs. alt, så argumentet ikke er tomt).

Funktion CurrDate (Valgfri fmt som variant) Dim Resultat Hvis IsMissing (fmt) Så CurrDate = Format (dato, "dd-mm-åååå") Ellers CurrDate = Format (dato, "dd mmmm, åååå") Afslut hvis slut funktion

Bemærk, at ovenstående funktion bruger 'IsMissing' til at kontrollere, om argumentet mangler eller ej. For at bruge IsMissing -funktionen skal dit valgfrie argument være af variantdatatypen.

Ovenstående funktion fungerer, uanset hvad du indtaster som argumentet. I koden kontrollerer vi kun, om det valgfrie argument er leveret eller ej.

Du kan gøre dette mere robust ved kun at tage specifikke værdier som argumenter og vise en fejl i resten af ​​sagerne (som vist i nedenstående kode).

Funktion CurrDate (Valgfri fmt som variant) Dim Resultat Hvis IsMissing (fmt) Så CurrDate = Format (dato, "dd-mm-åååå") ElseIf fmt = 1 Så CurrDate = Format (dato, "dd mmmm, åååå") Ellers CurrDate = CVErr (xlErrValue) End If End -funktion

Ovenstående kode opretter en funktion, der viser datoen i "dd-mm-åååå" -formatet, hvis der ikke leveres noget argument, og i "dd mmmm, åååå" -format, når argumentet er 1. Det giver en fejl i alle andre tilfælde.

Funktion med påkrævede såvel som valgfrie argumenter

Vi har allerede set en kode, der udtrækker den numeriske del fra en streng.

Lad os nu se på et lignende eksempel, der tager både krævede såvel som valgfrie argumenter.

Nedenstående kode opretter en funktion, der udtrækker tekstdelen fra en streng. Hvis det valgfrie argument er SAND, giver det resultatet i store bogstaver, og hvis det valgfrie argument er FALSKT eller udelades, giver det resultatet som det er.

Funktion GetText (CellRef As Range, Valgfri 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 & Midt (CellRef, i, 1) Næste i Hvis TextCase = True Så Resultat = UCase (Resultat) GetText = Resultatafslutningsfunktion

Bemærk, at i ovenstående kode har vi initialiseret værdien af ​​'TextCase' som Falsk (se inden for parentesen i den første linje).

Ved at gøre dette har vi sikret, at det valgfrie argument starter med standardværdien, som er FALSK. Hvis brugeren angiver værdien som SAND, returnerer funktionen teksten med store bogstaver, og hvis brugeren angiver det valgfrie argument som FALSKT eller udelader det, er teksten, der returneres, som den er.

Oprettelse af en funktion i VBA med et array som argument

Hidtil har vi set eksempler på oprettelse af en funktion med valgfrie/påkrævede argumenter - hvor disse argumenter var en enkelt værdi.

Du kan også oprette en funktion, der kan tage et array som argument. I Excel -regnearksfunktioner er der mange funktioner, der tager array -argumenter, såsom SUM, VLOOKUP, SUMIF, COUNTIF osv.

Nedenfor er koden, der skaber en funktion, der giver summen af ​​alle lige numre i det angivne område af cellerne.

Funktion AddEven (CellRef som Range) Dim Cell som Range for hver celle i CellRef If IsNumeric (Cell.Value) Then If Cell.Value Mod 2 = 0 Then Result = Result + Cell.Value End If End If Next Cell AddEven = Result End Fungere

Du kan bruge denne funktion i regnearket og angive celleområdet, der har tallene som argument. Funktionen returnerer en enkelt værdi - summen af ​​alle lige tal (som vist nedenfor).

I ovenstående funktion har vi i stedet for en enkelt værdi leveret en matrix (A1: A10). For at dette skal fungere, skal du sikre dig, at din datatype i argumentet kan acceptere en matrix.

I koden ovenfor angav jeg argumentet CellRef som Range (som kan tage et array som input). Du kan også bruge variantdatatypen her.

I koden er der en for hver sløjfe, der går gennem hver celle og kontrollerer, om det er et antal ikke. Hvis det ikke er det, sker der ikke noget, og det flytter til den næste celle. Hvis det er et tal, kontrollerer det, om det er lige eller ej (ved hjælp af MOD -funktionen).

Til sidst tilføjes alle lige numre, og summen tildeles funktionen.

Oprettelse af en funktion med et ubestemt antal argumenter

Mens du opretter nogle funktioner i VBA, kender du muligvis ikke det nøjagtige antal argumenter, som en bruger ønsker at levere. Så behovet er at oprette en funktion, der kan acceptere, da mange argumenter leveres, og bruge disse til at returnere resultatet.

Et eksempel på en sådan regnearksfunktion er SUM -funktionen. Du kan levere flere argumenter til det (f.eks. Dette):

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

Ovenstående funktion tilføjer værdierne i alle disse argumenter. Bemærk også, at disse kan være en enkelt celle eller en række celler.

Du kan oprette en sådan funktion i VBA ved at have det sidste argument (eller det kan være det eneste argument) som valgfrit. Dette valgfrie argument bør også gå forud for søgeordet 'ParamArray'.

'ParamArray' er en modifikator, der giver dig mulighed for at acceptere så mange argumenter, du vil. Bemærk, at brugen af ​​ordet ParamArray før et argument gør argumentet valgfrit. Du behøver dog ikke at bruge ordet Valgfrit her.

Lad os nu oprette en funktion, der kan acceptere et vilkårligt antal argumenter og ville tilføje alle numrene i de angivne argumenter:

Funktion AddArguments (ParamArray arglist () som variant) For hver arg I arglist AddArguments = AddArguments + arg Næste arg Slutfunktion

Ovenstående funktion kan tage et vilkårligt antal argumenter og tilføje disse argumenter for at give resultatet.

Bemærk, at du kun kan bruge en enkelt værdi, en cellereference, en boolean eller et udtryk som argumentet. Du kan ikke levere et array som argument. For eksempel, hvis et af dine argumenter er D8: D10, ville denne formel give dig en fejl.

Hvis du vil kunne bruge begge flercellede argumenter, skal du bruge nedenstående kode:

Funktion AddArguments (ParamArray arglist () som variant) For hver arg In arglist For hver celle i arg AddArguments = AddArguments + Cell Næste celle Næste arg Næste arg End funktion

Bemærk, at denne formel fungerer med flere celler og matrixreferencer, men den kan ikke behandle hårdkodede værdier eller udtryk. Du kan oprette en mere robust funktion ved at kontrollere og behandle disse forhold, men det er ikke hensigten her.

Hensigten her er at vise dig, hvordan ParamArray fungerer, så du kan tillade et ubestemt antal argumenter i funktionen. Hvis du vil have en bedre funktion end den, der er oprettet af ovenstående kode, skal du bruge funktionen SUM i regnearket.

Oprettelse af en funktion, der returnerer et array

Hidtil har vi set funktioner, der returnerer en enkelt værdi.

Med VBA kan du oprette en funktion, der returnerer en variant, der kan indeholde en hel række værdier.

Arrayformler er også tilgængelige som indbyggede funktioner i Excel -regneark. Hvis du er bekendt med matrixformler i Excel, ved du, at disse indtastes ved hjælp af Control + Shift + Enter (i stedet for bare Enter). Du kan læse mere om matrixformler her. Hvis du ikke kender til matrixformler, skal du ikke bekymre dig, fortsæt med at læse.

Lad os oprette en formel, der returnerer en matrix med tre tal (1,2,3).

Nedenstående kode ville gøre dette.

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

I ovenstående kode har vi specificeret funktionen ‘ThreeNumbers’ som en variant. Dette gør det muligt at holde en række værdier.

Variablen 'NumberValue' erklæres som en matrix med 3 elementer. Den har de tre værdier og tildeler den til ‘ThreeNumbers’ funktionen.

Du kan bruge denne funktion i regnearket ved at indtaste funktionen og trykke på Ctrl + Shift + Enter -tasten (hold Ctrl- og Shift -tasterne nede, og tryk derefter på Enter).

Når du gør dette, returnerer det 1 i cellen, men i virkeligheden rummer det alle de tre værdier. For at kontrollere dette skal du bruge nedenstående formel:

= MAX (ThreeNumbers ())

Brug ovenstående funktion med Control + Shift + Enter. Du vil bemærke, at resultatet nu er 3, da det er de største værdier i arrayet returneret af Max -funktionen, som får de tre tal som et resultat af vores brugerdefinerede funktion - ThreeNumbers.

Du kan bruge den samme teknik til at oprette en funktion, der returnerer en matrix med månedsnavne som vist med nedenstående kode:

Funktion Måneder () Som Variant Dim Månedsnavn (1 Til 12) Månedsnavn (1) = "Januar" Månedsnavn (2) = "Februar" Månedsnavn (3) = "Marts" Månedsnavn (4) = "April" Månedsnavn (5) = "Maj" Månedsnavn (6) = "Juni" Månedsnavn (7) = "Juli" Månedsnavn (8) = "August" Månedsnavn (9) = "September" Månedsnavn (10) = "Oktober" Månedsnavn (11) = "November "MonthName (12) =" December "Months = MonthName Slutfunktion

Når du nu indtaster funktionen = Måneder () i Excel -regneark og bruger Ctrl + Skift + Enter, returnerer det hele rækken af ​​månedsnavne. Bemærk, at du kun ser januar i cellen, da det er den første værdi i arrayet. Dette betyder ikke, at matrixen kun returnerer en værdi.

For at vise dig det faktum, at det returnerer alle værdierne, skal du gøre dette - vælg cellen med formlen, gå til formellinjen, vælg hele formlen og tryk på F9. Dette viser dig alle de værdier, som funktionen returnerer.

Du kan bruge dette ved at bruge nedenstående INDEX -formel til at få en liste over alle månedsnavne på én gang.

= INDEKS (Måneder (), RÆKKE ())

Hvis du nu har mange værdier, er det ikke en god praksis at tildele disse værdier en efter en (som vi har gjort ovenfor). I stedet kan du bruge Array -funktionen i VBA.

Så den samme kode, hvor vi opretter funktionen 'Måneder', ville blive kortere som vist herunder:

Funktionsmåneder () Som variantmåneder = Array ("januar", "februar", "marts", "april", "maj", "juni", _ "juli", "august", "september", "oktober" , "November", "December") Afslut funktion

Ovenstående funktion bruger Array -funktionen til at tildele værdierne direkte til funktionen.

Bemærk, at alle de funktioner, der er oprettet ovenfor, returnerer et vandret array af værdier. Det betyder, at hvis du vælger 12 vandrette celler (lad os sige A1: L1), og indtaster = måneder () formlen i celle A1, vil det give dig alle månedens navne.

Men hvad nu hvis du vil have disse værdier i et lodret celleområde.

Du kan gøre dette ved at bruge TRANSPOSE -formlen i regnearket.

Vælg blot 12 lodrette celler (sammenhængende), og indtast nedenstående formel.

Forstå omfanget af en brugerdefineret funktion i Excel

En funktion kan have to omfang - Offentlig eller Privat.

• EN Offentligt omfang betyder, at funktionen er tilgængelig for alle arkene i projektmappen samt alle procedurer (Sub og funktion) på tværs af alle moduler i projektmappen. Dette er nyttigt, når du vil kalde en funktion fra en underprogram (vi vil se, hvordan dette gøres i det næste afsnit).
• EN Privat anvendelsesområde betyder, at funktionen kun er tilgængelig i det modul, hvor den findes. Du kan ikke bruge det i andre moduler. Du vil heller ikke se det på listen over funktioner i regnearket. For eksempel, hvis dit funktionsnavn er 'Måneder ()', og du skriver funktion i Excel (efter = -tegnet), viser det dig ikke funktionsnavnet. Du kan dog stadig bruge det, hvis du indtaster formelnavnet.

Hvis du ikke angiver noget, er funktionen som standard en offentlig funktion.

Nedenfor er en funktion, der er en privat funktion:

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

Du kan bruge denne funktion i underrutiner og procedurer i de samme moduler, men kan ikke bruge den i andre moduler. Denne funktion vises heller ikke i regnearket.

Nedenstående kode ville gøre denne funktion offentlig. Det vises også i regnearket.

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

Forskellige måder at bruge en brugerdefineret funktion i Excel

Når du har oprettet en brugerdefineret funktion i VBA, kan du bruge den på mange forskellige måder.

Lad os først dække, hvordan du bruger funktionerne i regnearket.

Brug af UDF'er i regneark

Vi har allerede set eksempler på at bruge en funktion oprettet i VBA i regnearket.

Alt du skal gøre er at indtaste funktionsnavnet, og det vises i intellisense.

Bemærk, at for at funktionen skal vises i regnearket, skal den være en offentlig funktion (som forklaret i afsnittet ovenfor).

Du kan også bruge dialogboksen Indsæt funktion til at indsætte den brugerdefinerede funktion (ved hjælp af trinene herunder). Dette fungerer kun for offentlige funktioner.

• Gå til fanen Data.
• Klik på indstillingen 'Indsæt funktion'.
• Vælg Brugerdefineret som kategori i dialogboksen Indsæt funktion. Denne indstilling vises kun, når du har en funktion i VB -editoren (og funktionen er offentlig).
• Vælg funktionen på listen over alle de offentlige brugerdefinerede funktioner.
• Klik på knappen Ok.

Ovenstående trin indsætter funktionen i regnearket. Det viser også en dialogboks med funktionsargumenter, der giver dig detaljer om argumenterne og resultatet.

Du kan bruge en brugerdefineret funktion ligesom enhver anden funktion i Excel. Dette betyder også, at du kan bruge den med andre indbyggede Excel -funktioner. For eksempel. nedenstående formel ville give navnet på projektmappen i store bogstaver:

= OVER (WorkbookName ())

Brug af brugerdefinerede funktioner i VBA -procedurer og funktioner

Når du har oprettet en funktion, kan du også bruge den i andre underprocedurer.

Hvis funktionen er offentlig, kan den bruges i enhver procedure i det samme eller forskellige modul. Hvis det er privat, kan det kun bruges i det samme modul.

Nedenfor er en funktion, der returnerer navnet på projektmappen.

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

Nedenstående procedure kalder funktionen og viser derefter navnet i en meddelelsesboks.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Du kan også kalde en funktion fra en anden funktion.

I nedenstående koder returnerer den første kode navnet på projektmappen, og den anden returnerer navnet med store bogstaver ved at kalde den første funktion.

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

Funktion WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Slutfunktion

Opkald til en brugerdefineret funktion fra andre projektmapper

Hvis du har en funktion i en projektmappe, kan du også kalde denne funktion i andre projektmapper.

Der er flere måder at gøre dette på:

1. Oprettelse af et tilføjelsesprogram
2. Gemningsfunktion i den personlige makro -projektmappe
3. Henvisning til funktionen fra en anden projektmappe.

Oprettelse af et tilføjelsesprogram

Ved at oprette og installere et tilføjelsesprogram har du den tilpassede funktion i det tilgængeligt i alle projektmapper.

Antag, at du har oprettet en brugerdefineret funktion - 'GetNumeric', og du vil have den i alle projektmapper. For at gøre dette skal du oprette en ny projektmappe og have funktionskoden i et modul i denne nye projektmappe.

Følg nu trinene herunder for at gemme det som et tilføjelsesprogram og derefter installere det i Excel.

• Gå til fanen Filer, og klik på Gem som.
• I dialogboksen Gem som skal du ændre typen ‘Gem som’ til .xlam. Navnet, du tildeler filen, ville være navnet på dit tilføjelsesprogram. I dette eksempel gemmes filen med navnet GetNumeric.
  • Du vil bemærke, at stien til filen, hvor den gemmes, automatisk ændres. Du kan bruge standardindstillingen eller ændre den, hvis du vil.
• Åbn en ny Excel -projektmappe, og gå til fanen Udvikler.
• Klik på indstillingen Excel-tilføjelser.
• Gennemse og find den fil, du har gemt, i dialogboksen Tilføjelser, og klik på OK.

Nu er tilføjelsesprogrammet aktiveret.

Nu kan du bruge den tilpassede funktion i alle projektmapper.

Gem funktionen i Personal Macro Workbook

En personlig makro -projektmappe er en skjult projektmappe i dit system, der åbnes, når du åbner Excel -applikationen.

Det er et sted, hvor du kan gemme makrokoder og derefter få adgang til disse makroer fra enhver projektmappe. Det er et godt sted at gemme de makroer, du ofte vil bruge.

Som standard er der ingen personlig makro -projektmappe i din Excel. Du skal oprette den ved at optage en makro og gemme den i den personlige makro -projektmappe.

Du kan finde de detaljerede trin om, hvordan du opretter og gemmer makroer i den personlige makro -projektmappe her.

Henvisning til funktionen fra en anden projektmappe

Mens de to første metoder (oprettelse af et tilføjelsesprogram og brug af personlig makro-projektmappe) ville fungere i alle situationer, skal denne projektmappe være åben, hvis du vil henvise til funktionen fra en anden projektmappe.

Antag, at du har en projektmappe med navnet 'Arbejdsbog med formel ', og den har funktionen med navnet 'GetNumeric ’.

For at bruge denne funktion i en anden projektmappe (mens Arbejdsbog med formel er åben), kan du bruge nedenstående formel:

= 'Workbook with Formula'! GetNumeric (A1)

Ovenstående formel bruger den brugerdefinerede funktion i Arbejdsbog med formel fil og give dig resultatet.

Bemærk, at da projektmappens navn har mellemrum, skal du vedlægge det i enkelte anførselstegn.

Brug af Exit Function Statement VBA

Hvis du vil afslutte en funktion, mens koden kører, kan du gøre det ved at bruge erklæringen 'Afslut funktion'.

Nedenstående kode ville udtrække de første tre numeriske tegn fra en alfanumerisk tekststreng. Så snart den får de tre tegn, slutter funktionen og returnerer resultatet.

Funktion GetNumericFirstThree (CellRef As Range) Så længe Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 Til StringLength Hvis J = 3 Derefter afslut funktion Hvis IsNumeric (Mid (CellRef, i, 1)) Så J = J + 1 Resultat = Resultat og midt (CellRef, i, 1) GetNumericFirstThree = Resultat slut Hvis næste i slut funktion

Ovenstående funktion kontrollerer antallet af tegn, der er numeriske, og når den får 3 numeriske tegn, forlader den funktionen i den næste sløjfe.

Fejlfinding af en brugerdefineret funktion

Der er et par teknikker, du kan bruge, når du debugger en brugerdefineret funktion i VBA:

Debugging af en brugerdefineret funktion ved hjælp af meddelelsesboksen

Brug MsgBox -funktionen til at vise en meddelelsesboks med en bestemt værdi.

Den værdi, du viser, kan være baseret på, hvad du vil teste. For eksempel, hvis du vil kontrollere, om koden bliver udført eller ej, ville enhver meddelelse fungere, og hvis du vil kontrollere, om sløjferne fungerer eller ej, kan du vise en bestemt værdi eller sløjfe -tælleren.

Debugging af en brugerdefineret funktion ved at indstille breakpoint

Indstil et breakpoint for at kunne gå gennem hver linje en ad gangen. For at indstille et brudpunkt skal du vælge den linje, du vil have den, og trykke på F9, eller klikke på det grå lodrette område, der er tilbage til kodelinjerne. Enhver af disse metoder ville indsætte et brudpunkt (du vil se en rød prik i det grå område).

Når du har indstillet brydepunktet, og du udfører funktionen, går det indtil brudpunktslinjen og stopper derefter. Nu kan du gå gennem koden ved hjælp af F8 -tasten. Et tryk på F8 går én gang til den næste linje i koden.

Debugging af en brugerdefineret funktion ved hjælp af Debug.Print i koden

Du kan bruge Debug.Print -sætning i din kode til at få værdierne for de angivne variabler/argumenter i det umiddelbare vindue.

For eksempel i nedenstående kode har jeg brugt Debug.Print til at få værdien af ​​to variabler - 'j' og 'Resultat'

Funktion GetNumericFirstThree (CellRef As Range) Så længe Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 Til StringLength Hvis J = 3 Derefter afslut funktion Hvis IsNumeric (Mid (CellRef, i, 1)) Så J = J + 1 Resultat = Resultat & midt (CellRef, i, 1) Debug.Print J, Resultat GetNumericFirstThree = Resultat slut Hvis næste i slut funktion

Når denne kode udføres, viser den følgende i det umiddelbare vindue.

Excel indbyggede funktioner vs. VBA brugerdefineret funktion

Der er få stærke fordele ved at bruge Excel-indbyggede funktioner i forhold til brugerdefinerede funktioner, der er oprettet i VBA.

• Indbyggede funktioner er meget hurtigere end VBA -funktionerne.
• Når du opretter en rapport/dashboard ved hjælp af VBA -funktioner, og du sender den til en klient/kollega, behøver de ikke bekymre sig om, hvorvidt makroerne er aktiveret eller ej. I nogle tilfælde bliver klienter/kunder bange ved at se en advarsel i den gule bjælke (som simpelthen beder dem om at aktivere makroer).
• Med indbyggede Excel -funktioner behøver du ikke bekymre dig om filudvidelser. Hvis du har makroer eller brugerdefinerede funktioner i projektmappen, skal du gemme den i .xlsm.

Selvom der er mange stærke grunde til at bruge Excel-indbyggede funktioner, er det i nogle få tilfælde bedre at bruge en brugerdefineret funktion.

• Det er bedre at bruge en brugerdefineret funktion, hvis din indbyggede formel er enorm og kompliceret. Dette bliver endnu mere relevant, når du har brug for en anden til at opdatere formlerne. For eksempel, hvis du har en enorm formel, der består af mange forskellige funktioner, kan selv ændring af en henvisning til en celle være kedelig og udsat for fejl. I stedet kan du oprette en brugerdefineret funktion, der kun tager et eller to argumenter og gør alt det tunge løfte backend.
• Når du skal få gjort noget, der ikke kan udføres af Excel indbyggede funktioner. Et eksempel på dette kan være, når du vil udtrække alle de numeriske tegn fra en streng. I sådanne tilfælde opvejer fordelen ved at bruge en brugerdefineret funktion gar dens negative.

Hvor placeres VBA-koden for en brugerdefineret funktion

Når du opretter en brugerdefineret funktion, skal du sætte koden i kodevinduet for den projektmappe, hvor du vil have funktionen.

Nedenfor er trinene til at sætte koden til funktionen ‘GetNumeric’ i projektmappen.

1. Gå til fanen Udvikler.
2. Klik på indstillingen Visual Basic. Dette åbner VB -editoren i backend.
3. Højreklik på et objekt til projektmappen, hvor du vil indsætte koden, i ruden Project Explorer i VB Editor. Hvis du ikke kan se Project Explorer, skal du gå til fanen View og klikke på Project Explorer.
4. Gå til Indsæt og klik på Modul. Dette vil indsætte et modulobjekt til din projektmappe.
5. Kopier og indsæt koden i modulvinduet.

Du kan også lide følgende Excel VBA -vejledninger:

• Arbejde med celler og områder i Excel VBA.
• Arbejde med regneark i Excel VBA.
• Arbejde med projektmapper ved hjælp af VBA.
• Sådan bruges Loops i Excel VBA.
• Excel VBA Events - En nem (og komplet) vejledning
• Brug af IF Then Else Statements i VBA.
• Sådan optages en makro i Excel.
• Sådan køres en makro i Excel.
• Sådan sorteres data i Excel ved hjælp af VBA (en trinvis vejledning).
• Excel VBA InStr -funktion - forklaret med eksempler.