Sådan oprettes brugerdefineret funktion i Microsoft Excel

Anonim

I denne artikel vil vi lære at oprette brugerdefinerede funktioner i Microsoft Excel ved hjælp af VBA.

Brugerdefineret funktion:- Microsoft Excel har allerede mange funktioner, men stadig har alle forskellige krav, situation, vi kan oprette vores egen funktion i henhold til kravet, der kaldes brugerdefineret funktion. Vi kan bruge brugerdefineret funktion som andre funktioner i Excel.

Nedenfor er de emner, som vi vil oprette den brugerdefinerede funktion til:

1). Hvordan tæller man antallet af ord i celle eller område?

2). Hvordan udtrækkes et ord fra en sætning eller celle i Excel?

3). Hvordan opretter man formlen til ISO?

4). Sådan kender du regnearket og projektmappens navn ved hjælp af VBA?

5). Hvordan udtrækkes det første og sidste ord fra en celle i Excel?

Hvordan opretter man den brugerdefinerede funktion til at tælle antallet af ord i celle eller område?

Vi har data i ark 1, hvor vi har nogle adresser, så vi vil tælle ordene i en celle eller et område ved at oprette den brugerdefinerede funktion via VBA i Excel.

Følg nedenstående givne trin for at gøre den brugerdefinerede funktion:-

  • Åbn VBA -side, og tryk på tasten Alt+F11.
  • Indsæt et modul.

Skriv nedenstående kode:

 Funktion WORDSCOUNT (rRange As Range) Så Long Dim rCell As Range Dim Count As Long For Every rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Næste rCell WORDSCOUNT = lCount -slutfunktion

Kode Forklaringer: - For at gøre den brugerdefinerede funktion starter vi koden til funktionsnavn og definerer variablerne. Vi har brugt "For hver sløjfer" i koden til at tælle ordene i området.
Hvordan bruges denne funktion i Excel?

Følg nedenstående trin for at bruge denne funktion:-

  • Gå til Excel -ark.
  • For at tælle ordene for en celle skal du indtaste formlen i celle D7.
  • = WORDSCOUNT (C7), celle C7 er cellen, hvor vi vil beregne ordene.
  • Funktionen returnerer 6, hvilket betyder, at celle C7 indeholder 6 ord.
  • Hvis du vil foretage den samme beregning for resten af ​​cellerne, skal du kopiere den samme formel og indsætte i området.

  • For at tælle ordene i intervallet skal du bruge formlen som = WORDSCOUNT (C7: C16), og trykke på Enter.
  • Funktionen returnerer antallet af ord.


Bemærk:- Denne UDF vil være nyttig til at tælle ordene i et område eller i en enkelt celle.

Nu skriver vi koden for at tælle ordet ved hjælp af den angivne afgrænser (,). Følg nedenstående trin:-

 Funktion SEPARATECOUNTWORDS (rRange As Range, valgfri separator som variant) Så Long Dim rCell As Range Dim Count As Long If IsMissing (separator) Then separator = "," End If For Every rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Erstat (Trim (rCell), separator, "")) Næste rCell SEPARATECOUNTWORDS = lCount End -funktion

Følg nedenstående trin for at bruge denne funktion:-

  • Gå til Excel -ark.
  • For at tælle de specifikke afgrænsere i ordet vil vi bruge denne definerede funktion.
  • = SEPARATECOUNTWORDS (C7), og tryk på Enter.
  • Funktionen returnerer antallet af de specifikke afgrænsere.


Hvordan udtrækkes et ord fra en sætning eller celle i Microsoft Excel ved hjælp af VBA?

Vi har data i ark1. I hvilke vi har nogle adresser, så vi ønsker at udtrække ordene fra en sætning eller celle eller et område ved at oprette den brugerdefinerede funktion gennem VBA i Excel.


Følg nedenstående givne trin for at gøre den brugerdefinerede funktion:-

  • Åbn VBA -side, og tryk på tasten Alt+F11.
  • Indsæt et modul.
  • Skriv nedenstående kode:-
 Funktion GETWORD (tekst som variant, N som heltal, valgfri afgrænser som variant) som streng Hvis IsMissing (afgrænser) Så afgrænser = "" Slut hvis GETWORD = opdelt (tekst, afgrænser) (N - 1) Afslut funktion

Kode Forklaring:- I ovennævnte kode har vi nævnt funktionsnavn med variablerne. Og så havde vi defineret kriterierne for at udtrække ordet fra sætning eller celle.

Nu vil vi lære at bruge denne formel. Følg nedenstående trin:-

  • Gå til Excel -ark.
  • Brug denne formel i celle D7.
  • = GETWORD (C7,2), og tryk på Enter.
  • Funktionen returnerer andet ord fra cellen, fordi vi i formel havde nævnt for 2nd nummerets ord. Hvis du vil hente ordet, der er placeret på 3rd position, skal du ændre tallet fra 2 til 3 i formlen.


Hvordan opretter man ISO Ugenummerformel i Microsoft Excel ved hjælp af VBA?

Vi lærer, hvordan vi kan oprette ISO -ugenummerformel i Excel med denne UDF. Denne funktion vil vi bruge til at identificere, at den nævnte dato hører til hvilket ugenummer i året.

Vi har en liste med dato i arket, og i den anden kolonne ønsker vi at hente ugenumrene.

Følg nedenstående givne trin for at oprette UDF for dette krav:-

  • Åbn VBA -side, og tryk på tasten Alt+F11.
  • Indsæt et modul.
  • Skriv nedenstående kode:-
 Funktion ISOWEEKNUMBER (Angiv som dato) Så længe Dim Dt As Date Dt = DateSerial (Year (Indate - Weekday (Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + Weekday (Dt) + 5 ) / 7) Afslut funktion

Kode Forklaring:-:- I ovenstående kode har vi nævnt funktionsnavn med variablerne. Og så havde vi indstillet datoværdien, og så havde vi defineret funktionen "ISOWEENUMBER" -funktionen.
Hvordan kan vi bruge denne funktion i vores Excel -fil?

  • Gå til Excel -ark.
  • Indtast formlen i celle D7.
  • = ISOWEEKNUMBER (C7), og tryk på Enter.
  • Funktionen returnerer ugen for den indtastede dato i cellen. For at hente ugenummeret for hver dato skal du kopiere den samme formel inden for rækkevidde.


Nu lærer vi, hvordan vi returnerer ISO-standarderne på året i Excel- Årets første mandag.

Denne funktion vil grundlæggende kontrollere, at 1st Mandag i året falder på hvilken dato, og derefter begynder det at beregne antallet af uger fra denne dato. Lad os se, hvordan vi kan oprette UDF til dette krav.


Følg de givne trin herunder:-

  • Åbn VBA -side, og tryk på tasten Alt+F11.
  • Indsæt et modul.
  • Skriv nedenstående kode:-
 Funktion ISOSTYR (år som heltal) Som dato Dim WD Som heltal Dim NY som dato NY = DateSerial (år, 1, 1) WD = (NY - 2) Mod 7 Hvis WD <4 Så ISOSTYR = NY - WD Else ISOSTYR = NY - WD + 7 End If End -funktion

Kode Forklaring: - I ovenstående kode har vi nævnt funktionsnavn med variablerne. Og så havde vi angivet kriterierne for variablerne, og derefter havde vi defineret formelindgangen.

Du skal bare angive år 2001 i dette format, og formlen giver dig 1st Mandag i året.

Nu lærer vi, hvordan du bruger UDF i Excel -fil. Følg nedenstående trin:-

  • Gå til Excel -ark.
  • Indtast formlen i celle D7.
  • = ISOSTYR (C7), og tryk på Enter.
  • Funktionen returnerer datoen den 1st Mandag i nytårs første uge.
  • For at returnere datoen den 1st Mandag i nytårs første uge, kopier den samme formel og indsæt i området.


Hvordan kender man regnearket og projektmappens navn ved hjælp af VBA i Microsoft Excel?

Følg nedenstående kode og trin:-

  • Åbn VBA -side, og tryk på tasten Alt+F11.
  • Indsæt et modul.
  • Skriv nedenstående kode:-
 Funktion Arbejdsarknavn () Regnearksnavn = Område ("A1"). Parent.Name Slutfunktion

Kode Forklaring:- I ovenstående kode har vi nævnt funktionsnavn, og så havde vi defineret, hvordan man kender bladnavnet.

For at bruge denne formel skal du bare indtaste formlen i en hvilken som helst celle på denne måde: -= Regnearksnavn (). Funktionen returnerer arknavnet.


Følg nedenstående trin og kode for at oprette funktionen til projektmappens navn:-

  • Åbn VBA -side tryk på tasten Alt+F11.
  • Indsæt et modul.
  • Skriv nedenstående kode:-
 Function Workbookname () Workbookname = ThisWorkbook.Name Slutfunktion

Kode Forklaring:-:- I ovenstående kode har vi nævnt funktionsnavn, og derefter havde vi defineret, hvordan vi skulle lære projektmappens navn at kende.

For at bruge denne formel skal du bare indtaste formlen i en hvilken som helst celle på denne måde: - = Workbookname (). Funktionen returnerer arknavnet.


Hvordan udtrækkes det første og sidste ord fra en celle ved hjælp af VBA i Microsoft Excel?

Vi har data i ark1, hvor vi har nogle adresser, så vi vil udtrække det sidste og første ord fra en sætning eller celle eller et område ved at oprette den brugerdefinerede funktion gennem VBA i Excel.

Først skriver vi funktionen til at udtrække det første ord. Følg nedenstående trin:-

  • Åbn VBA -side tryk på tasten Alt+F11.
  • Indsæt et modul

Skriv nedenstående kode:-

 Funktion GETFW (Tekst som streng, valgfri adskiller som variant) Dim FW som streng Hvis IsMissing (separator) Så er Separator = "" Slut hvis FW = Venstre (Tekst, InStr (1, Tekst, Separator, vbTextCompare)) GETFW = Erstat (FW , Separator, "") Afslutningsfunktion

Kode Forklaring: - I ovennævnte kode har vi nævnt funktionsnavn med variablerne. Og så har vi defineret kriterierne for at udtrække ordet fra sætning eller celle.

Nu vil vi lære at bruge denne formel. Følg nedenstående trin:-

  • Gå til Excel -ark.
  • Brug denne formel i celle D9.
  • = GETFW (C9), og tryk på Enter.
  • Funktionen returnerer det første ord fra dataene. For at hente det første ord for alle cellerne skal du kopiere den samme formel i området.

Nu skriver vi koden for at udtrække det sidste ord fra cellen. Følg nedenstående kode:-

  • Åbn VBA -side tryk på tasten Alt+F11.
  • Indsæt et modul.
  • Skriv nedenstående kode:-
 Funktion GETLW (tekst som streng, valgfri adskiller som variant) Dim LW som streng Hvis IsMissing (separator) Så er Separator = "" Slut hvis LW = StrReverse (tekst) LW = Venstre (sidste ord, InStr (1, LW, Separator, vbTextCompare) ) GETLW = StrReverse (Replace (LW, Separator, "")) Afslut funktion

Nu vil vi lære at bruge denne formel. Følg nedenstående trin:-

  • Gå til Excel -ark.
  • Brug denne formel i celle D9.
  • = GETLW (C9) Tryk på Enter.
  • Funktionen returnerer det sidste ord fra dataene. For at hente det sidste ord for alle cellerne skal du kopiere den samme formel i området.

Dette er de funktioner, vi kan definere gennem VBA og derefter kan bruge det som formel for Excel. Vi kan også oprette mange flere brugerdefinerede funktioner. Bliv ved med at lære med os, vi finder på mere vanskelige formler.