I denne artikkelen skal vi lære hvordan du oppretter brukerdefinerte funksjoner i Microsoft Excel ved å bruke VBA.
Brukerdefinert funksjon:- Microsoft Excel har allerede mange funksjoner, men fortsatt har alle forskjellige krav, situasjon, vi kan lage vår egen funksjon i henhold til kravet som kalles brukerdefinert funksjon. Vi kan bruke brukerdefinert funksjon som andre funksjoner i Excel.
Nedenfor er temaene vi skal lage den brukerdefinerte funksjonen for:
1). Hvordan telle antall ord i celle eller område?
2). Hvordan trekker jeg ut et ord fra en setning eller celle i Excel?
3). Hvordan lage formelen for ISO?
4). Hvordan kjenne regnearket og arbeidsboknavnet ved hjelp av VBA?
5). Hvordan trekker jeg ut det første og siste ordet fra en celle i Excel?
Hvordan lage den brukerdefinerte funksjonen for å telle antall ord i celle eller område?
Vi har data i ark 1 der vi har noen adresser, så vi vil telle ordene i en celle eller et område ved å opprette den brukerdefinerte funksjonen gjennom VBA i Excel.
For å gjøre den brukerdefinerte funksjonen, følg trinnene nedenfor:-
- Åpne VBA -siden og trykk på tasten Alt+F11.
- Sett inn en modul.
Skriv koden nedenfor:
Funksjon WORDSCOUNT (rRange As Range) Så lenge Dim rCell Som Range Dim Count Så lenge for hver rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Neste rCell WORDSCOUNT = lCount Sluttfunksjon
Kode Forklaringer: - For å gjøre den brukerdefinerte funksjonen starter vi koden til funksjonsnavn og definerer variablene. Vi har brukt “For each loops” i koden for å telle ordene i området.
Hvordan bruke denne funksjonen i Excel?
Følg trinnene nedenfor for å bruke denne funksjonen:-
- Gå til Excel -ark.
- For å telle ordene for en celle, skriv inn formelen i celle D7.
- = WORDSCOUNT (C7), celle C7 er cellen der vi vil beregne ordene.
- Funksjonen returnerer 6, noe som betyr at celle C7 inneholder 6 ord.
- For å gjøre den samme beregningen for resten av cellene, kopierer du den samme formelen og limer inn i området.
- For å telle ordene i området, bruk formelen som = ORDTAL (C7: C16), og trykk Enter.
- Funksjonen vil returnere antall ord.
Merk:- Denne UDF vil være nyttig for å telle ordene i et område eller i en enkelt celle.
Nå skal vi skrive koden for å telle ordet ved å bruke den angitte skilletegnet (,). Følg trinnene nedenfor:-
Funksjon SEPARATECOUNTWORDS (rRange As Range, valgfri separator som variant) Så lenge Dim rCell Som Range Dim Count As Long If IsMissing (separator) Then separator = "," End If For Every rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Erstatt (Trim (rCell), separator, "")) Neste rCell SEPARATECOUNTWORDS = lCount End -funksjon
Følg trinnene nedenfor for å bruke denne funksjonen:-
- Gå til Excel -ark.
- For å telle de spesifikke skilletegnene i ordet, bruker vi denne definerte funksjonen.
- = SEPARATECOUNTWORDS (C7) og trykk Enter.
- Funksjonen vil returnere tellingen av de spesifikke skilletegnene.
Hvordan trekke ut et ord fra en setning eller celle i Microsoft Excel ved hjelp av VBA?
Vi har data i ark1. Der vi har noen adresser, så vi ønsker å trekke ut ordene fra en setning eller celle eller et område ved å opprette den brukerdefinerte funksjonen gjennom VBA i Excel.
For å gjøre den brukerdefinerte funksjonen, følg trinnene nedenfor:-
- Åpne VBA -siden og trykk på tasten Alt+F11.
- Sett inn en modul.
- Skriv koden nedenfor:-
Funksjon GETWORD (tekst som variant, N som heltall, valgfri skilletegn som variant) som streng hvis IsMissing (skilletegn) deretter delimiter = "" Slutt hvis GETWORD = delt (tekst, skilletegn) (N - 1) Sluttfunksjon
Kode Forklaring:- I koden ovenfor har vi nevnt funksjonsnavn med variablene. Og så hadde vi definert kriteriene for å trekke ut ordet fra setning eller celle.
Nå skal vi lære hvordan du bruker denne formelen. Følg trinnene nedenfor:-
- Gå til Excel -ark.
- Bruk denne formelen i celle D7.
- = GETWORD (C7,2) og trykk Enter.
- Funksjonen vil returnere andre ord fra cellen fordi vi i formel hadde nevnt for 2nd tallets ord. Hvis du vil hente ordet som er plassert på 3rd posisjon, må du endre tallet fra 2 til 3 i formelen.
Hvordan lage ISO Ukenummerformel i Microsoft Excel ved hjelp av VBA?
Vi vil lære hvordan vi kan lage ISO -ukenummerformel i Excel med denne UDF. Denne funksjonen vil vi bruke til å identifisere at den nevnte datoen tilhører hvilket ukenummer i året.
Vi har en liste over dato i arket, og i den andre kolonnen ønsker vi å hente uketallene.
Følg trinnene nedenfor for å opprette UDF for dette kravet:-
- Åpne VBA -siden og trykk på tasten Alt+F11.
- Sett inn en modul.
- Skriv koden nedenfor:-
Funksjon ISOWEEKNUMBER (Angi som dato) Så lenge Dim Dt As Date Dt = DateSerial (Year (Indate - Weekday (Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + Weekday (Dt) + 5 ) / 7) Sluttfunksjon
Kode Forklaring:-:- I koden ovenfor har vi nevnt funksjonsnavn med variablene. Og så hadde vi angitt datoverdien, og så hadde vi definert kriteriene for “ISOWEENUMBER” -funksjonen.
Hvordan kan vi bruke denne funksjonen i Excel -filen?
- Gå til Excel -ark.
- Skriv inn formelen i celle D7.
- = ISOWEEKNUMBER (C7), og trykk Enter.
- Funksjonen vil returnere uken for den angitte datoen i cellen. Nå for å hente ukenummeret for hver dato, kopierer du den samme formelen i området.
Nå skal vi lære å returnere ISO-standardene på året i Excel- første mandag i året.
Denne funksjonen vil i utgangspunktet kontrollere at 1st Mandag i året kommer til å falle på hvilken dato, og deretter vil det begynne å beregne antall uker fra den datoen. La oss se hvordan vi kan lage UDF for dette kravet.
Følg trinnene nedenfor:-
- Åpne VBA -siden og trykk på tasten Alt+F11.
- Sett inn en modul.
- Skriv koden nedenfor:-
Funksjon ISOSTYR (Year As Integer) As Date Dim WD As Integer Dim NY As Date NY = DateSerial (Year, 1, 1) WD = (NY - 2) Mod 7 If WD <4 Then ISOSTYR = NY - WD Else ISOSTYR = NY - WD + 7 End If End -funksjon
Kode Forklaring: - I koden ovenfor har vi nevnt funksjonsnavn med variablene. Og da hadde vi satt kriteriene for variablene, og så hadde vi definert formelinngangen.
Du trenger bare å gi året 2001 i dette formatet, og formelen gir deg 1st Mandag i året.
Nå skal vi lære hvordan du bruker UDF i Excel -fil. Følg trinnene nedenfor:-
- Gå til Excel -ark.
- Skriv inn formelen i celle D7.
- = ISOSTYR (C7) og trykk Enter.
- Funksjonen vil returnere datoen for 1st Mandag i nyttårs første uke.
- For å returnere datoen for 1st Mandag i nyttårs første uke, kopier den samme formelen og lim inn i området.
Hvordan vite navnet på regnearket og arbeidsboken ved å bruke VBA i Microsoft Excel?
Følg den gitte koden og trinnene nedenfor:-
- Åpne VBA -siden og trykk på tasten Alt+F11.
- Sett inn en modul.
- Skriv koden nedenfor:-
Funksjon Arbeidsarknavn () Arbeidsarknavn = Område ("A1"). Parent.Name Sluttfunksjon
Kode Forklaring:- I koden ovenfor har vi nevnt funksjonsnavn, og da hadde vi definert hvordan vi kjenner bladnavnet.
For å bruke denne formelen trenger du bare å skrive inn formelen i en hvilken som helst celle på denne måten: -= Arbeidsarknavn (). Funksjonen vil returnere arkenavnet.
For å opprette funksjonen for arbeidsboknavn, følg trinnene og koden nedenfor:-
- Åpne VBA -siden, trykk på tasten Alt+F11.
- Sett inn en modul.
- Skriv koden nedenfor:-
Function Workbookname () Workbookname = ThisWorkbook.Name Sluttfunksjon
Kode Forklaring:-:- I koden ovenfor har vi nevnt funksjonsnavn og da hadde vi definert hvordan vi skulle bli kjent med arbeidsboknavnet.
For å bruke denne formelen trenger du bare å skrive inn formelen i en hvilken som helst celle på denne måten: - = Workbookname (). Funksjonen vil returnere arkenavnet.
Hvordan trekker jeg ut det første og siste ordet fra en celle ved å bruke VBA i Microsoft Excel?
Vi har data i ark1 der vi har noen adresser, så vi vil trekke ut det siste og første ordet fra en setning eller celle eller et område ved å opprette den brukerdefinerte funksjonen gjennom VBA i Excel.
Først skal vi skrive funksjonen for å trekke ut det første ordet. Følg trinnene nedenfor:-
- Åpne VBA -siden, trykk på tasten Alt+F11.
- Sett inn en modul
Skriv koden nedenfor:-
Funksjon GETFW (Text As String, Valgfri separator som variant) Dim FW As String If IsMissing (Separator) Then Separator = "" End If FW = Left (Text, InStr (1, Text, Separator, vbTextCompare)) GETFW = Replace (FW , Separator, "") Sluttfunksjon
Kode Forklaring: - I koden ovenfor har vi nevnt funksjonsnavn med variablene. Og så har vi definert kriteriene for å trekke ut ordet fra setning eller celle.
Nå skal vi lære hvordan du bruker denne formelen. Følg trinnene nedenfor:-
- Gå til Excel -ark.
- Bruk denne formelen i celle D9.
- = GETFW (C9) og trykk Enter.
- Funksjonen vil returnere det første ordet fra dataene. For å hente det første ordet for alle cellene, kopierer du den samme formelen i området.
Nå skal vi skrive koden for å trekke ut det siste ordet fra cellen. Følg koden nedenfor:-
- Åpne VBA -siden, trykk på tasten Alt+F11.
- Sett inn en modul.
- Skriv koden nedenfor:-
Funksjon GETLW (Text As String, Valgfri separator som variant) Dim LW As String If IsMissing (Separator) Then Separator = "" End If LW = StrReverse (Text) LW = Left (lastword, InStr (1, LW, Separator, vbTextCompare) ) GETLW = StrReverse (Erstatt (LW, separator, "")) Sluttfunksjon
Nå skal vi lære hvordan du bruker denne formelen. Følg trinnene nedenfor:-
- Gå til Excel -ark.
- Bruk denne formelen i celle D9.
- = GETLW (C9) Trykk Enter.
- Funksjonen vil returnere det siste ordet fra dataene. Nå, for å hente det siste ordet for alle cellene, kopierer du den samme formelen i området.
Dette er funksjonene vi kan definere gjennom VBA og deretter bruke det som formel for Excel. Vi kan også lage mange flere brukerdefinerte funksjoner. Fortsett å lære med oss, vi kommer med mer vanskelige formler.
