✅ Opprette en brukerdefinert funksjon (UDF) i Excel VBA (Ultimate Guide)

Innholdsfortegnelse

Hva er en funksjonsprosedyre i VBA?
Opprette en enkel brukerdefinert funksjon i VBA
Anatomi av en brukerdefinert funksjon i VBA
Argumenter i en brukerdefinert funksjon i VBA
Opprette en funksjon som returnerer en matrise
Forstå omfanget av en brukerdefinert funksjon i Excel
Ulike måter å bruke en brukerdefinert funksjon i Excel
Bruke Exit Function Statement VBA
Feilsøking av en brukerdefinert funksjon
Excel innebygde funksjoner vs. VBA brukerdefinert funksjon
Hvor du skal sette VBA-koden for en brukerdefinert funksjon

Med VBA kan du opprette en tilpasset funksjon (også kalt en brukerdefinert funksjon) som kan brukes i regnearkene akkurat som vanlige funksjoner.

Disse er nyttige når de eksisterende Excel -funksjonene ikke er nok. I slike tilfeller kan du opprette din egen tilpassede brukerdefinerte funksjon (UDF) for å dekke dine spesifikke behov.

I denne opplæringen vil jeg dekke alt om å lage og bruke tilpassede funksjoner i VBA.

Hvis du er interessert i å lære VBA på den enkle måten, sjekk min Online Excel VBA -opplæring.

Hva er en funksjonsprosedyre i VBA?

En funksjonsprosedyre er en VBA -kode som utfører beregninger og returnerer en verdi (eller en rekke verdier).

Ved å bruke en funksjonsprosedyre kan du opprette en funksjon som du kan bruke i regnearket (akkurat som alle vanlige Excel -funksjoner som SUM eller VLOOKUP).

Når du har opprettet en funksjonsprosedyre ved hjelp av VBA, kan du bruke den på tre måter:

Som en formel i regnearket, hvor den kan ta argumenter som innganger og returnerer en verdi eller en rekke verdier.
Som en del av din VBA -underrutinkode eller en annen funksjonskode.
I betinget formatering.

Selv om det allerede er 450+ innebygde Excel -funksjoner tilgjengelig i regnearket, kan det hende du trenger en egendefinert funksjon hvis:

De innebygde funksjonene kan ikke gjøre det du vil få gjort. I dette tilfellet kan du opprette en tilpasset funksjon basert på dine krav.
De innebygde funksjonene kan få jobben gjort, men formelen er lang og komplisert. I dette tilfellet kan du opprette en egendefinert funksjon som er lett å lese og bruke.

Vær oppmerksom på at egendefinerte funksjoner som er opprettet ved hjelp av VBA, kan være betydelig tregere enn de innebygde funksjonene. Derfor er disse best egnet for situasjoner der du ikke kan få resultatet ved hjelp av de innebygde funksjonene.

Funksjon vs. Subrutine i VBA

En 'Subroutine' lar deg utføre et sett med kode mens en 'Funksjon' returnerer en verdi (eller en rekke verdier).

For å gi deg et eksempel, hvis du har en liste med tall (både positive og negative), og du vil identifisere de negative tallene, er det du kan gjøre med en funksjon og en underprogram.

En subrutine kan gå gjennom hver celle i området og kan markere alle cellene som har en negativ verdi i den. I dette tilfellet ender subrutinen med å endre egenskapene til områdeobjektet (ved å endre fargen på cellene).

Med en egendefinert funksjon kan du bruke den i en egen kolonne, og den kan returnere en SANN hvis verdien i en celle er negativ og FALSK hvis den er positiv. Med en funksjon kan du ikke endre objektets egenskaper. Dette betyr at du ikke kan endre fargen på cellen med en funksjon selv (du kan imidlertid gjøre det ved å bruke betinget formatering med den egendefinerte funksjonen).

Når du oppretter en brukerdefinert funksjon (UDF) ved hjelp av VBA, kan du bruke den funksjonen i regnearket akkurat som alle andre funksjoner. Jeg vil dekke mer om dette i delen "Ulike måter å bruke en brukerdefinert funksjon i Excel".

Opprette en enkel brukerdefinert funksjon i VBA

La meg lage en enkel brukerdefinert funksjon i VBA og vise deg hvordan det fungerer.

Koden nedenfor oppretter en funksjon som trekker ut de numeriske delene fra en alfanumerisk streng.

Funksjon 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) Neste i GetNumeric = Resultatsluttfunksjon

Når du har koden ovenfor i modulen, kan du bruke denne funksjonen i arbeidsboken.

Nedenfor er hvordan denne funksjonen - GetNumeric - kan brukes i Excel.

Nå før jeg forteller deg hvordan denne funksjonen er opprettet i VBA og hvordan den fungerer, er det noen ting du bør vite:

Når du oppretter en funksjon i VBA, blir den tilgjengelig i hele arbeidsboken akkurat som alle andre vanlige funksjoner.
Når du skriver funksjonsnavnet etterfulgt av likhetstegnet, viser Excel navnet på funksjonen i listen over matchende funksjoner. I eksemplet ovenfor, da jeg skrev = Get, viste Excel meg en liste som hadde min egendefinerte funksjon.

Jeg tror dette er et godt eksempel når du kan bruke VBA til å lage en brukervennlig funksjon i Excel. Du kan også gjøre det samme med en formel (som vist i denne opplæringen), men det blir komplisert og vanskelig å forstå. Med denne UDF trenger du bare å passere ett argument, og du får resultatet.

Anatomi av en brukerdefinert funksjon i VBA

I delen ovenfor ga jeg deg koden og viste deg hvordan UDF -funksjonen fungerer i et regneark.

La oss nå dykke dypt og se hvordan denne funksjonen er opprettet. Du må plassere koden nedenfor i en modul i VB Editor. Jeg dekker dette emnet i avsnittet - 'Hvor skal jeg sette VBA -koden for en brukerdefinert funksjon'.

Funksjon GetNumeric (CellRef As String) as Long 'Denne funksjonen trekker ut den numeriske delen fra strengen Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If IsNumeric (Mid (CellRef, i, 1)) Then Result = Resultat og midten (CellRef, i, 1) Neste i GetNumeric = Resultatsluttfunksjon

Den første linjen i koden starter med ordet - Funksjon.

Dette ordet forteller VBA at koden vår er en funksjon (og ikke en subrutine). Ordet funksjon etterfølges av navnet på funksjonen - GetNumeric. Dette er navnet vi skal bruke i regnearket for å bruke denne funksjonen.

Navnet på funksjonen kan ikke ha mellomrom. Du kan heller ikke navngi en funksjon hvis den kolliderer med navnet på en cellereferanse. For eksempel kan du ikke navngi funksjonen ABC123, ettersom den også refererer til en celle i Excel -regnearket.
Du bør ikke gi funksjonen din samme navn som for en eksisterende funksjon. Hvis du gjør dette, vil Excel foretrekke den innebygde funksjonen.
Du kan bruke en understreking hvis du vil skille ord. For eksempel er Get_Numeric et akseptabelt navn.

Funksjonsnavnet etterfølges av noen argumenter i parentes. Dette er argumentene som funksjonen vår trenger fra brukeren. Dette er akkurat som argumentene vi må levere til Excels innebygde funksjoner. For eksempel i en COUNTIF -funksjon er det to argumenter (område og kriterier)

Innenfor parentes må du spesifisere argumentene.

I vårt eksempel er det bare ett argument - CellRef.

Det er også en god praksis å spesifisere hva slags argument funksjonen forventer. I dette eksemplet, siden vi vil mate funksjonen med en cellereferanse, kan vi spesifisere argumentet som en "Range" -type. Hvis du ikke angir en datatype, vil VBA betrakte den som en variant (som betyr at du kan bruke hvilken som helst datatype).

Hvis du har mer enn ett argument, kan du angi dem i samme parentes - atskilt med komma. Vi vil se senere i denne opplæringen om hvordan du bruker flere argumenter i en brukerdefinert funksjon.

Vær oppmerksom på at funksjonen er spesifisert som datatypen "streng". Dette vil fortelle VBA at resultatet av formelen ville være av strengdatatypen.

Selv om jeg kan bruke en numerisk datatype her (for eksempel Long eller Double), ville det begrense rekkevidden av tall den kan returnere. Hvis jeg har en 20 tall lang streng som jeg trenger å trekke ut fra den generelle strengen, ville deklarering av funksjonen som en lang eller dobbel gi en feil (ettersom tallet ville være utenfor området). Derfor har jeg beholdt funksjonsutdatatypen som streng.

Den andre linjen i koden - den i grønt som starter med en apostrof - er en kommentar. Når du leser koden, ignorerer VBA denne linjen. Du kan bruke dette til å legge til en beskrivelse eller en detalj om koden.

Den tredje linjen i koden deklarerer en variabel ‘StringLength’ som en heltall datatype. Dette er variabelen der vi lagrer verdien av lengden på strengen som blir analysert av formelen.

Den fjerde linjen deklarerer variabelen Resultat som en strengdatatype. Dette er variabelen der vi vil trekke ut tallene fra den alfanumeriske strengen.

Den femte linjen tilordner lengden på strengen i inputargumentet til varianten ‘StringLength’. Vær oppmerksom på at 'CellRef' refererer til argumentet som vil bli gitt av brukeren mens du bruker formelen i regnearket (eller bruker det i VBA - som vi vil se senere i denne opplæringen).

Sjette, syvende og åttende linje er delen av For Next -løkken. Sløyfen går så mange ganger så mange tegn som er der i input -argumentet. Dette nummeret er gitt av LEN -funksjonen og er tilordnet varianten "StringLength".

Så løkken går fra ‘1 til strenglengde’.

I løkken analyserer IF -setningen hvert tegn i strengen, og hvis det er numerisk, legger det det numeriske tegnet til resultatvariabelen. Den bruker MID -funksjonen i VBA for å gjøre dette.

Den nest siste linjen i koden tildeler verdien av resultatet til funksjonen. Det er denne kodelinjen som sikrer at funksjonen returnerer 'Resultat' -verdien tilbake i cellen (hvorfra den kalles).

Den siste linjen i koden er Sluttfunksjon. Dette er en obligatorisk kodelinje som forteller VBA at funksjonskoden ender her.

Koden ovenfor forklarer de forskjellige delene av en typisk tilpasset funksjon opprettet i VBA. I de følgende avsnittene vil vi dykke dypt ned i disse elementene og også se de forskjellige måtene å utføre VBA -funksjonen i Excel.

Argumenter i en brukerdefinert funksjon i VBA

I eksemplene ovenfor, hvor vi opprettet en brukerdefinert funksjon for å hente den numeriske delen fra en alfanumerisk streng (GetNumeric), ble funksjonen designet for å ta ett enkelt argument.

I denne delen vil jeg dekke hvordan du oppretter funksjoner som ikke tar noen argumenter til de som tar flere argumenter (påkrevd så vel som valgfrie argumenter).

Opprette en funksjon i VBA uten argumenter

I Excel -regneark har vi flere funksjoner som ikke tar noen argumenter (for eksempel RAND, TODAY, NOW).

Disse funksjonene er ikke avhengig av inngangsargumenter. For eksempel vil TODAY -funksjonen returnere gjeldende dato og RAND -funksjonen vil returnere et tilfeldig tall mellom 0 og 1.

Du kan også lage en lignende funksjon i VBA.

Nedenfor er koden som vil gi deg navnet på filen. Det tar ingen argumenter, ettersom resultatet det trenger å returnere ikke er avhengig av noe argument.

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

Koden ovenfor angir funksjonens resultat som en strengdatatype (ettersom resultatet vi ønsker er filnavnet - som er en streng).

Denne funksjonen tildeler verdien av 'ThisWorkbook.Name' til funksjonen, som returneres når funksjonen brukes i regnearket.

Hvis filen er lagret, returnerer den navnet med filtypen, ellers gir den ganske enkelt navnet.

Ovenstående har imidlertid et problem.

Hvis filnavnet endres, oppdateres det ikke automatisk. Normalt oppdateres en funksjon når det er en endring i input -argumentene. Men siden det ikke er argumenter i denne funksjonen, beregnes ikke funksjonen på nytt (selv om du endrer navnet på arbeidsboken, lukker du den og åpner den igjen).

Hvis du vil, kan du tvinge til en ny beregning ved å bruke hurtigtasten - Ctrl + Alt + F9.

For å få formelen til å beregne på nytt når det er en endring i regnearket, må du skrive en linje med kode.

Koden nedenfor gjør at funksjonen beregnes på nytt når det er en endring i regnearket (akkurat som andre lignende regnearkfunksjoner som TODAY eller RAND -funksjonen).

Funksjon WorkbookName () Som String Application.Volatile True WorkbookName = ThisWorkbook.Name Sluttfunksjon

Hvis du endrer arbeidsboknavnet, oppdateres denne funksjonen når det er noen endringer i regnearket eller når du åpner denne arbeidsboken igjen.

Opprette en funksjon i VBA med ett argument

I en av seksjonene ovenfor har vi allerede sett hvordan vi lager en funksjon som bare tar ett argument (GetNumeric -funksjonen som er dekket ovenfor).

La oss lage en annen enkel funksjon som bare tar ett argument.

Funksjon som er opprettet med koden nedenfor, vil konvertere teksten som refereres til til store bokstaver. Nå har vi allerede en funksjon for det i Excel, og denne funksjonen er bare for å vise deg hvordan det fungerer. Hvis du må gjøre dette, er det bedre å bruke den innebygde OPPER -funksjonen.

Funksjon ConvertToUpperCase (CellRef As Range) ConvertToUpperCase = UCase (CellRef) Sluttfunksjon

Denne funksjonen bruker UCase -funksjonen i VBA til å endre verdien til CellRef -variabelen. Den tildeler deretter verdien til funksjonen ConvertToUpperCase.

Siden denne funksjonen tar et argument, trenger vi ikke å bruke Application.Volatile -delen her. Så snart argumentet endres, oppdateres funksjonen automatisk.

Opprette en funksjon i VBA med flere argumenter

På samme måte som regnearkfunksjoner, kan du opprette funksjoner i VBA som tar flere argumenter.

Koden nedenfor vil opprette en funksjon som vil trekke ut teksten før den angitte skilletegnet. Det krever to argumenter - cellereferansen som har tekststrengen, og skilletegnet.

Funksjon 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 trenger å bruke mer enn ett argument i en brukerdefinert funksjon, kan du ha alle argumentene i parentesen atskilt med et komma.

Vær oppmerksom på at for hvert argument kan du angi en datatype. I eksemplet ovenfor har 'CellRef' blitt deklarert som en datatype for område og 'Delim' har blitt deklarert som en strengdatatype. Hvis du ikke angir noen datatype, anser VBA at dette er en variantdatatype.

Når du bruker funksjonen ovenfor i regnearket, må du oppgi cellereferansen som har teksten som det første argumentet og skilletegn (er) i doble anførselstegn som det andre argumentet.

Den kontrollerer deretter plasseringen til skilletegn ved å bruke INSTR -funksjonen i VBA. Denne posisjonen brukes deretter til å trekke ut alle tegnene før skilletegnet (ved å bruke VENSTRE -funksjonen).

Til slutt tilordner det resultatet til funksjonen.

Denne formelen er langt fra perfekt. For eksempel, hvis du skriver inn en skilletegn som ikke finnes i teksten, vil det gi en feil. Nå kan du bruke IFERROR -funksjonen i regnearket for å kvitte seg med feilene, eller du kan bruke koden nedenfor som returnerer hele teksten når den ikke finner skilletegnet.

Funksjon 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 = Resultatsluttfunksjon

Vi kan optimalisere denne funksjonen ytterligere.

Hvis du skriver inn teksten (som du vil trekke ut delen før skilletegnet) direkte i funksjonen, vil det gi deg en feil. Fortsett … prøv det!

Dette skjer når vi har spesifisert 'CellRef' som en datatype for område.

Eller, hvis du vil at skilletegnet skal være i en celle og bruke cellereferansen i stedet for å kode den hardt i formelen, kan du ikke gjøre det med koden ovenfor. Det er fordi Delim er erklært som en strengdatatype.

Hvis du vil at funksjonen skal ha fleksibilitet til å godta direkte tekstinngang eller cellereferanser fra brukeren, må du fjerne datatypedeklarasjonen. Dette vil ende opp med å lage argumentet som en variant datatype, som kan ta hvilken som helst type argument og behandle det.

Koden nedenfor vil gjøre dette:

Funksjon 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 = Resultatsluttfunksjon

Opprette en funksjon i VBA med valgfrie argumenter

Det er mange funksjoner i Excel der noen av argumentene er valgfrie.

For eksempel har den legendariske VLOOKUP -funksjonen 3 obligatoriske argumenter og ett valgfritt argument.

Et valgfritt argument, som navnet antyder, er valgfritt å spesifisere. Hvis du ikke angir et av de obligatoriske argumentene, vil funksjonen gi deg en feil, men hvis du ikke spesifiserer det valgfrie argumentet, fungerer funksjonen din.

Men valgfrie argumenter er ikke ubrukelige. De lar deg velge mellom en rekke alternativer.

For eksempel, i VLOOKUP -funksjonen, hvis du ikke spesifiserer det fjerde argumentet, gjør VLOOKUP et omtrentlig oppslag, og hvis du angir det siste argumentet som FALSE (eller 0), gjør det en eksakt samsvar.

Husk at de valgfrie argumentene alltid må komme etter alle de nødvendige argumentene. Du kan ikke ha valgfrie argumenter i begynnelsen.

La oss nå se hvordan du oppretter en funksjon i VBA med valgfrie argumenter.

Funksjon med bare et valgfritt argument

Så vidt jeg vet, er det ingen innebygd funksjon som bare tar valgfrie argumenter (jeg kan ta feil her, men jeg kan ikke tenke meg noen slik funksjon).

Men vi kan lage en med VBA.

Nedenfor er koden for funksjonen som vil gi deg den gjeldende datoen i dd-mm-åååå-formatet hvis du ikke angir noe argument (dvs. la det stå tomt), og i "dd mmmm, åååå" -format hvis du skriver inn noe som argumentet (dvs. alt slik at argumentet ikke er tomt).

Funksjon CurrDate (Valgfri fmt som variant) Dim Resultat Hvis IsMissing (fmt) Deretter CurrDate = Format (Date, "dd-mm-yyyy") Else CurrDate = Format (Date, "dd mmmm, yyyy") End If End Function

Vær oppmerksom på at funksjonen ovenfor bruker ‘IsMissing’ for å kontrollere om argumentet mangler eller ikke. For å bruke IsMissing -funksjonen må det valgfrie argumentet være av datatypen variant.

Funksjonen ovenfor fungerer uansett hva du skriver inn som argument. I koden sjekker vi bare om det valgfrie argumentet er levert eller ikke.

Du kan gjøre dette mer robust ved å ta bare spesifikke verdier som argumenter og vise en feil i resten av tilfellene (som vist i koden nedenfor).

Funksjon CurrDate (Valgfri fmt som variant) Dim Resultat Hvis IsMissing (fmt) Deretter CurrDate = Format (Dato, "dd-mm-åååå") ElseIf fmt = 1 Deretter CurrDate = Format (Dato, "dd mmmm, åååå") Ellers CurrDate = CVErr (xlErrValue) Slutt hvis sluttfunksjon

Koden ovenfor oppretter en funksjon som viser datoen i "dd-mm-åååå" -formatet hvis det ikke kommer noe argument, og i "dd mmmm, åååå" -formatet når argumentet er 1. Det gir en feil i alle andre tilfeller.

Funksjon med nødvendige og valgfrie argumenter

Vi har allerede sett en kode som trekker ut den numeriske delen fra en streng.

La oss nå se på et lignende eksempel som tar både nødvendige og valgfrie argumenter.

Koden nedenfor oppretter en funksjon som trekker ut tekstdelen fra en streng. Hvis det valgfrie argumentet er SANT, gir det resultatet i store bokstaver, og hvis det valgfrie argumentet er FALSKT eller utelates, gir det resultatet som det er.

Funksjon 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) Neste i Hvis TextCase = True Da Resultat = UCase (Resultat) GetText = Resultat Sluttfunksjon

Vær oppmerksom på at i koden ovenfor har vi initialisert verdien av 'TextCase' som False (se innenfor parentesen på den første linjen).

Ved å gjøre dette har vi sørget for at det valgfrie argumentet starter med standardverdien, som er FALSK. Hvis brukeren angir verdien som SANN, returnerer funksjonen teksten med store bokstaver, og hvis brukeren angir det valgfrie argumentet som FALSKT eller utelater det, er teksten som returneres som den er.

Opprette en funksjon i VBA med en matrise som argument

Så langt har vi sett eksempler på å lage en funksjon med valgfrie/påkrevde argumenter - der disse argumentene var en enkelt verdi.

Du kan også opprette en funksjon som kan ta en matrise som argument. I Excel -regnearkfunksjoner er det mange funksjoner som tar matriseargumenter, for eksempel SUMME, VLOOKUP, SUMIF, COUNTIF, etc.

Nedenfor er koden som oppretter en funksjon som gir summen av alle partallene i det angitte området for cellene.

Funksjon AddEven (CellRef som Range) Dim Cell som Range for hver celle i CellRef If IsNumeric (Cell.Value) Then If Cell.Value Mod 2 = 0 Da Resultat = Resultat + Cell.Value Slutt Hvis slutt Hvis neste celle AddEven = Resultat slutt Funksjon

Du kan bruke denne funksjonen i regnearket og angi celleområdet som har tallene som argument. Funksjonen vil returnere en enkelt verdi - summen av alle partallene (som vist nedenfor).

I funksjonen ovenfor har vi i stedet for en enkelt verdi levert en matrise (A1: A10). For at dette skal fungere, må du sørge for at datatypen for argumentet kan godta en matrise.

I koden ovenfor spesifiserte jeg argumentet CellRef som Range (som kan ta en matrise som inngang). Du kan også bruke variantdatatypen her.

I koden er det en for hver sløyfe som går gjennom hver celle og sjekker om det er et antall ikke. Hvis det ikke er det, skjer ingenting, og det flytter til neste celle. Hvis det er et tall, sjekker det om det er jevnt eller ikke (ved å bruke MOD -funksjonen).

Til slutt blir alle partallene lagt til og summen tilordnet funksjonen.

Opprette en funksjon med ubestemt antall argumenter

Mens du oppretter noen funksjoner i VBA, vet du kanskje ikke det eksakte antallet argumenter som en bruker ønsker å levere. Så behovet er å lage en funksjon som kan godta så mange argumenter som er levert og bruke disse til å returnere resultatet.

Et eksempel på en slik regnearkfunksjon er SUM -funksjonen. Du kan levere flere argumenter til den (for eksempel denne):

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

Funksjonen ovenfor vil legge til verdiene i alle disse argumentene. Vær også oppmerksom på at disse kan være en enkeltcelle eller en rekke celler.

Du kan opprette en slik funksjon i VBA ved å ha det siste argumentet (eller det kan være det eneste argumentet) som valgfritt. Dette valgfrie argumentet bør også gå foran søkeordet 'ParamArray'.

‘ParamArray’ er en modifikator som lar deg godta så mange argumenter du vil. Vær oppmerksom på at bruk av ordet ParamArray før et argument gjør argumentet valgfritt. Du trenger imidlertid ikke å bruke ordet Valgfritt her.

La oss nå lage en funksjon som kan godta et vilkårlig antall argumenter og vil legge til alle tallene i de angitte argumentene:

Funksjon AddArguments (ParamArray arglist () som variant) For hver arg In arglist AddArguments = AddArguments + arg Next arg End Funksjon

Funksjonen ovenfor kan ta et hvilket som helst antall argumenter og legge til disse argumentene for å gi resultatet.

Vær oppmerksom på at du bare kan bruke en enkelt verdi, en cellereferanse, en boolsk eller et uttrykk som argumentet. Du kan ikke levere en matrise som argument. For eksempel, hvis et av argumentene dine er D8: D10, vil denne formelen gi deg en feil.

Hvis du vil kunne bruke begge flercellede argumenter, må du bruke koden nedenfor:

Funksjon AddArguments (ParamArray arglist () Som variant) For hver arg I arglist For hver celle I arg AddArguments = AddArguments + Cell Next Cell Next arg Neste arg End funksjon

Vær oppmerksom på at denne formelen fungerer med flere celler og matrisereferanser, men den kan ikke behandle hardkodede verdier eller uttrykk. Du kan lage en mer robust funksjon ved å kontrollere og behandle disse forholdene, men det er ikke meningen her.

Intensjonen her er å vise deg hvordan ParamArray fungerer, slik at du kan tillate et ubestemt antall argumenter i funksjonen. Hvis du vil ha en bedre funksjon enn den som er opprettet av koden ovenfor, bruker du SUM -funksjonen i regnearket.

Opprette en funksjon som returnerer en matrise

Så langt har vi sett funksjoner som returnerer en enkelt verdi.

Med VBA kan du opprette en funksjon som returnerer en variant som kan inneholde en hel rekke verdier.

Arrayformler er også tilgjengelige som innebygde funksjoner i Excel -regneark. Hvis du er kjent med matriseformler i Excel, vet du at disse skrives inn ved hjelp av Control + Shift + Enter (i stedet for bare Enter). Du kan lese mer om matriseformler her. Hvis du ikke vet om matriseformler, ikke bekymre deg, fortsett å lese.

La oss lage en formel som returnerer en rekke med tre tall (1,2,3).

Koden nedenfor vil gjøre dette.

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

I koden ovenfor har vi spesifisert funksjonen ‘ThreeNumbers’ som en variant. Dette gjør at den kan inneholde en rekke verdier.

Variabelen 'NumberValue' er deklarert som en matrise med 3 elementer. Den holder de tre verdiene og tilordner den til “TreNumre” -funksjonen.

Du kan bruke denne funksjonen i regnearket ved å gå inn i funksjonen og trykke Ctrl + Shift + Enter -tasten (hold Ctrl- og Shift -tastene inne og trykk deretter Enter).

Når du gjør dette, vil den returnere 1 i cellen, men i virkeligheten holder den alle de tre verdiene. For å kontrollere dette, bruk formelen nedenfor:

= MAX (ThreeNumbers ())

Bruk funksjonen ovenfor med Control + Shift + Enter. Du vil legge merke til at resultatet nå er 3, ettersom det er de største verdiene i matrisen som returneres av Max -funksjonen, som får de tre tallene som et resultat av vår brukerdefinerte funksjon - ThreeNumbers.

Du kan bruke den samme teknikken til å lage en funksjon som returnerer en rekke månedsnavn som vist med koden nedenfor:

Funksjon Måneder () Som variant Dim Månedsnavn (1 Til 12) Månedsnavn (1) = "Januar" Månedsnavn (2) = "Februar" Månedsnavn (3) = "Mars" Månedsnavn (4) = "April" Månedsnavn (5) = "May" MonthName (6) = "June" MonthName (7) = "July" MonthName (8) = "August" MonthName (9) = "September" MonthName (10) = "October" MonthName (11) = "November "MonthName (12) =" December "Months = MonthName Sluttfunksjon

Når du skriver inn funksjonen = Måneder () i Excel -regnearket og bruker Ctrl + Shift + Enter, returnerer det hele rekken med månedsnavn. Vær oppmerksom på at du bare ser januar i cellen, da det er den første verdien i matrisen. Dette betyr ikke at matrisen bare returnerer én verdi.

For å vise deg det faktum at den returnerer alle verdiene, gjør du dette - velg cellen med formelen, gå til formellinjen, velg hele formelen og trykk F9. Dette viser deg alle verdiene funksjonen returnerer.

Du kan bruke dette ved å bruke INDEX -formelen nedenfor for å få en liste over alle månedsnavnene på en gang.

= INDEKS (Måneder (), RAD ())

Hvis du har mange verdier, er det ikke en god praksis å tildele disse verdiene en etter en (som vi har gjort ovenfor). I stedet kan du bruke Array -funksjonen i VBA.

Så den samme koden der vi lager funksjonen ‘Måneder’ vil bli kortere som vist nedenfor:

Funksjonsmåneder () Som variantmåneder = Array ("januar", "februar", "mars", "april", "mai", "juni", _ "juli", "august", "september", "oktober" , "November", "Desember") Sluttfunksjon

Funksjonen ovenfor bruker Array -funksjonen til å tilordne verdiene direkte til funksjonen.

Vær oppmerksom på at alle funksjonene som er opprettet ovenfor returnerer en horisontal rekke verdier. Dette betyr at hvis du velger 12 horisontale celler (la oss si A1: L1), og skriver inn formelen = måneder () i celle A1, vil det gi deg alle månedsnavnene.

Men hva om du vil ha disse verdiene i et vertikalt celleområde.

Du kan gjøre dette ved å bruke TRANSPOSE -formelen i regnearket.

Bare velg 12 vertikale celler (sammenhengende), og skriv inn formelen nedenfor.

Forstå omfanget av en brukerdefinert funksjon i Excel

En funksjon kan ha to omfang - Offentlig eller Privat.

EN Offentlig omfang betyr at funksjonen er tilgjengelig for alle arkene i arbeidsboken, så vel som alle prosedyrene (del og funksjon) på tvers av alle modulene i arbeidsboken. Dette er nyttig når du vil kalle en funksjon fra en underprogram (vi får se hvordan dette gjøres i neste avsnitt).
EN Privat omfang betyr at funksjonen bare er tilgjengelig i modulen der den eksisterer. Du kan ikke bruke den i andre moduler. Du vil heller ikke se det i listen over funksjoner i regnearket. For eksempel, hvis funksjonsnavnet ditt er 'Måneder ()', og du skriver funksjon i Excel (etter = -tegnet), vil det ikke vise deg funksjonsnavnet. Du kan imidlertid fortsatt bruke det hvis du skriver inn formelnavnet.

Hvis du ikke angir noe, er funksjonen som standard en offentlig funksjon.

Nedenfor er en funksjon som er en privat funksjon:

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

Du kan bruke denne funksjonen i delrutinene og prosedyrene i de samme modulene, men kan ikke bruke den i andre moduler. Denne funksjonen vil heller ikke vises i regnearket.

Koden nedenfor vil gjøre denne funksjonen offentlig. Det vil også vises i regnearket.

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

Ulike måter å bruke en brukerdefinert funksjon i Excel

Når du har opprettet en brukerdefinert funksjon i VBA, kan du bruke den på mange forskjellige måter.

La oss først dekke hvordan du bruker funksjonene i regnearket.

Bruke UDF -er i regneark

Vi har allerede sett eksempler på bruk av en funksjon opprettet i VBA i regnearket.

Alt du trenger å gjøre er å skrive inn funksjonsnavnet, og det vises i intellisense.

Vær oppmerksom på at for at funksjonen skal vises i regnearket, må den være en offentlig funksjon (som forklart i delen ovenfor).

Du kan også bruke dialogboksen Sett inn funksjon for å sette inn den brukerdefinerte funksjonen (ved hjelp av trinnene nedenfor). Dette fungerer bare for funksjoner som er offentlige.

Gå til fanen Data.
Klikk på "Sett inn funksjon" -alternativet.
Velg Brukerdefinert som kategori i dialogboksen Sett inn funksjon. Dette alternativet vises bare når du har en funksjon i VB Editor (og funksjonen er Offentlig).
Velg funksjonen fra listen over alle de offentlige brukerdefinerte funksjonene.
Klikk på OK -knappen.

Trinnene ovenfor vil sette inn funksjonen i regnearket. Den viser også en dialogboks for funksjonsargumenter som gir deg detaljer om argumentene og resultatet.

Du kan bruke en brukerdefinert funksjon akkurat som alle andre funksjoner i Excel. Dette betyr også at du kan bruke den med andre innebygde Excel -funksjoner. For eksempel. formelen nedenfor vil gi navnet på arbeidsboken i store bokstaver:

= ØVERSTE (WorkbookName ())

Bruke brukerdefinerte funksjoner i VBA -prosedyrer og -funksjoner

Når du har opprettet en funksjon, kan du også bruke den i andre delprosedyrer.

Hvis funksjonen er Offentlig, kan den brukes i en hvilken som helst prosedyre i samme eller annen modul. Hvis det er privat, kan det bare brukes i samme modul.

Nedenfor er en funksjon som returnerer navnet på arbeidsboken.

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

Prosedyren nedenfor kaller funksjonen og viser deretter navnet i en meldingsboks.

Sub ShowWorkbookName () MsgBox WorkbookName End Sub

Du kan også ringe en funksjon fra en annen funksjon.

I kodene nedenfor returnerer den første koden navnet på arbeidsboken, og den andre returnerer navnet med store bokstaver ved å ringe den første funksjonen.

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

Funksjon WorkbookNameinUpper () WorkbookNameinUpper = UCase (WorkbookName) Sluttfunksjon

Kalle en brukerdefinert funksjon fra andre arbeidsbøker

Hvis du har en funksjon i en arbeidsbok, kan du også kalle denne funksjonen i andre arbeidsbøker.

Det er flere måter å gjøre dette på:

Opprette et tillegg
Lagringsfunksjon i Personal Macro Workbook
Refererer til funksjonen fra en annen arbeidsbok.

Opprette et tillegg

Ved å opprette og installere et tillegg, vil du ha den egendefinerte funksjonen i den tilgjengelig i alle arbeidsbøkene.

Anta at du har opprettet en egendefinert funksjon - 'GetNumeric', og du vil ha den i alle arbeidsbøkene. For å gjøre dette, opprett en ny arbeidsbok og ha funksjonskoden i en modul i denne nye arbeidsboken.

Følg nå trinnene nedenfor for å lagre det som et tillegg og deretter installere det i Excel.

Gå til kategorien Fil og klikk på Lagre som.
I dialogboksen Lagre som endrer du "Lagre som" -typen til .xlam. Navnet du tilordner filen vil være navnet på tillegget ditt. I dette eksemplet lagres filen med navnet GetNumeric.
- Du vil legge merke til at banen til filen der den blir lagret automatisk endres. Du kan bruke standard eller endre den hvis du vil.
Åpne en ny Excel -arbeidsbok og gå til kategorien Utvikler.
Klikk på alternativet Excel-tillegg.
I dialogboksen Tillegg blar du til og finner filen du lagret, og klikker OK.

Nå er tillegget aktivert.

Nå kan du bruke den egendefinerte funksjonen i alle arbeidsbøkene.

Lagre funksjonen i Personal Macro Workbook

En personlig makro arbeidsbok er en skjult arbeidsbok i systemet som åpnes når du åpner Excel -programmet.

Det er et sted hvor du kan lagre makrokoder og deretter få tilgang til disse makroene fra hvilken som helst arbeidsbok. Det er et flott sted å lagre de makroene du vil bruke ofte.

Som standard er det ingen personlig makro arbeidsbok i Excel. Du må lage den ved å registrere en makro og lagre den i den personlige makro -arbeidsboken.

Du finner de detaljerte trinnene for hvordan du oppretter og lagrer makroer i den personlige makro -arbeidsboken her.

Refererer til funksjonen fra en annen arbeidsbok

Selv om de to første metodene (å lage et tillegg og bruke personlig makro arbeidsbok) ville fungere i alle situasjoner, må arbeidsboken være åpen hvis du vil referere til funksjonen fra en annen arbeidsbok.

Anta at du har en arbeidsbok med navnet 'Arbeidsbok med formel, og den har funksjonen med navnet 'GetNumeric '.

For å bruke denne funksjonen i en annen arbeidsbok (mens Arbeidsbok med formel er åpen), kan du bruke formelen nedenfor:

= 'Arbeidsbok med formel'! GetNumeric (A1)

Formelen ovenfor bruker den brukerdefinerte funksjonen i Arbeidsbok med formel filen og gi deg resultatet.

Vær oppmerksom på at siden arbeidsboknavnet har mellomrom, må du legge det til i enkelt anførselstegn.

Bruke Exit Function Statement VBA

Hvis du vil avslutte en funksjon mens koden er i gang, kan du gjøre det ved å bruke "Avslutt funksjon" -setningen.

Koden nedenfor trekker ut de tre første numeriske tegnene fra en alfanumerisk tekststreng. Så snart den får de tre tegnene, avsluttes funksjonen og returnerer resultatet.

Funksjon GetNumericFirstThree (CellRef As Range) Så lenge Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Avslutt deretter funksjonen If IsNumeric (Mid (CellRef, i, 1)) Deretter J = J + 1 Resultat = Resultat og midt (CellRef, i, 1) GetNumericFirstThree = Resultat slutt Hvis neste i slutten funksjon

Funksjonen ovenfor kontrollerer antall tegn som er numeriske, og når den får 3 numeriske tegn, forlater den funksjonen i neste sløyfe.

Feilsøking av en brukerdefinert funksjon

Det er noen få teknikker du kan bruke når du feilsøker en brukerdefinert funksjon i VBA:

Feilsøke en egendefinert funksjon ved å bruke meldingsboksen

Bruk MsgBox -funksjonen til å vise en meldingsboks med en bestemt verdi.

Verdien du viser kan være basert på hva du vil teste. For eksempel, hvis du vil kontrollere om koden blir kjørt eller ikke, vil en melding fungere, og hvis du vil kontrollere om sløyfene fungerer eller ikke, kan du vise en bestemt verdi eller sløyfetelleren.

Feilsøke en egendefinert funksjon ved å angi brytepunktet

Sett et brytpunkt for å kunne gå gjennom hver linje en om gangen. For å angi et brytpunkt, velg linjen der du vil ha den og trykk på F9, eller klikk på det grå vertikale området som er igjen til kodelinjene. Enhver av disse metodene vil sette inn et brytpunkt (du vil se en rød prikk i det grå området).

Når du har angitt brytpunktet og du har utført funksjonen, går det til bruddpunktlinjen og stopper deretter. Nå kan du gå gjennom koden ved å bruke F8 -tasten. Trykk F8 en gang for å gå til neste linje i koden.

Feilsøke en egendefinert funksjon ved å bruke Debug.Print i koden

Du kan bruke Debug.Print -setning i koden for å få verdiene til de angitte variablene/argumentene i det umiddelbare vinduet.

For eksempel i koden nedenfor har jeg brukt Debug.Print for å få verdien av to variabler - 'j' og 'Resultat'

Funksjon GetNumericFirstThree (CellRef As Range) Så lenge Dim StringLength As Integer StringLength = Len (CellRef) For i = 1 To StringLength If J = 3 Avslutt deretter funksjonen If IsNumeric (Mid (CellRef, i, 1)) Deretter J = J + 1 Resultat = Resultat og midt (CellRef, i, 1) Debug.Print J, Resultat GetNumericFirstThree = Resultat slutt Hvis neste i sluttfunksjon

Når denne koden er utført, viser den følgende i det umiddelbare vinduet.

Excel innebygde funksjoner vs. VBA brukerdefinert funksjon

Det er få sterke fordeler med å bruke innebygde Excel-funksjoner fremfor tilpassede funksjoner som er opprettet i VBA.

Innebygde funksjoner er mye raskere enn VBA -funksjonene.
Når du oppretter en rapport/dashbord ved hjelp av VBA -funksjoner, og du sender den til en klient/kollega, trenger de ikke å bekymre deg for om makroene er aktivert eller ikke. I noen tilfeller blir klienter/kunder redd ved å se en advarsel i den gule linjen (som bare ber dem om å aktivere makroer).
Med innebygde Excel -funksjoner trenger du ikke bekymre deg for filutvidelser. Hvis du har makroer eller brukerdefinerte funksjoner i arbeidsboken, må du lagre den i .xlsm.

Selv om det er mange sterke grunner til å bruke innebygde Excel-funksjoner, er det i noen få tilfeller bedre at du bruker en brukerdefinert funksjon.

Det er bedre å bruke en brukerdefinert funksjon hvis den innebygde formelen er stor og komplisert. Dette blir enda mer relevant når du trenger noen andre til å oppdatere formlene. For eksempel, hvis du har en enorm formel som består av mange forskjellige funksjoner, kan endring av en referanse til en celle være kjedelig og utsatt for feil. I stedet kan du opprette en tilpasset funksjon som bare tar ett eller to argumenter og gjør alt det tunge løfter backend.
Når du må få gjort noe som ikke kan gjøres av Excel innebygde funksjoner. Et eksempel på dette kan være når du vil trekke ut alle de numeriske tegnene fra en streng. I slike tilfeller oppveier fordelen med å bruke en brukerdefinert funksjonsgaranti sine negative.

Hvor du skal sette VBA-koden for en brukerdefinert funksjon

Når du oppretter en egendefinert funksjon, må du sette koden i kodevinduet for arbeidsboken der du vil ha funksjonen.

Nedenfor er trinnene for å sette koden for "GetNumeric" -funksjonen i arbeidsboken.

Gå til kategorien Utvikler.
Klikk på Visual Basic -alternativet. Dette vil åpne VB -redaktøren i backend.
I Project Explorer-ruten i VB Editor, høyreklikker du på et hvilket som helst objekt for arbeidsboken der du vil sette inn koden. Hvis du ikke ser Project Explorer, går du til kategorien View og klikker på Project Explorer.
Gå til Sett inn og klikk på Modul. Dette vil sette inn et modulobjekt for arbeidsboken din.
Kopier og lim inn koden i modulvinduet.