I den här artikeln kommer vi att lära oss hur man skapar användardefinierade funktioner i Microsoft Excel med hjälp av VBA.
Användardefinierad funktion:- Microsoft Excel har redan många funktioner, men fortfarande har alla olika krav, situation, vi kan skapa vår egen funktion enligt kravet som kallas användardefinierad funktion. Vi kan använda användardefinierad funktion som andra funktioner i Excel.
Nedan följer de ämnen för vilka vi kommer att skapa den användardefinierade funktionen:
1). Hur räknar man antalet ord i cell eller intervall?
2). Hur extraherar jag ett ord från en mening eller cell i Excel?
3). Hur skapar jag formeln för ISO?
4). Hur känner jag till kalkylbladet och arbetsbokens namn med hjälp av VBA?
5). Hur extraherar jag det första och sista ordet från en cell i Excel?
Hur skapar jag en användardefinierad funktion för att räkna antalet ord i cell eller intervall?
Vi har data i blad 1 där vi har några adresser så vi vill räkna orden i en cell eller ett intervall genom att skapa den användardefinierade funktionen via VBA i Excel.
För att göra den användardefinierade funktionen, följ nedanstående steg:-
- Öppna VBA -sidan och tryck på Alt+F11.
- Sätt i en modul.
Skriv koden nedan:
Funktion WORDSCOUNT (rRange As Range) As Long Dim rCell As Range Dim Count Så länge för varje rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Replace (Trim (rCell), "", "")) + 1 Nästa rCell WORDSCOUNT = lCount End -funktion
Kodförklaringar: - För att göra den användardefinierade funktionen startar vi koden till funktionsnamn och definierar variablerna. Vi har använt "För varje loop" i koden för att räkna orden i intervallet.
Hur använder jag den här funktionen i Excel?
För att använda denna funktion, följ nedanstående steg:-
- Gå till Excel -blad.
- För att räkna orden för en cell anger du formeln i cell D7.
- = WORDSCOUNT (C7), cell C7 är cellen där vi vill beräkna orden.
- Funktionen returnerar 6, vilket betyder att cell C7 innehåller 6 ord.
- För att göra samma beräkning för resten av cellerna, kopiera samma formel och klistra in i intervallet.
- För att räkna orden i intervallet, använd formeln som = WORDSCOUNT (C7: C16) och tryck på Enter.
- Funktionen returnerar antalet ord.
Obs:- Denna UDF kommer att vara till hjälp för att räkna orden i ett område eller i en enda cell.
Nu skriver vi koden för att räkna ordet med den angivna avgränsaren (,). Följ nedanstående steg:-
Funktion SEPARATECOUNTWORDS (rRange As Range, Valfri separator som variant) Så lång Dim rCell Som Range Dim Count As Long If IsMissing (separator) Sedan separator = "," End If för varje rCellInrRange lCount = lCount + Len (Trim (rCell)) - Len (Ersätt (Trim (rCell), separator, "")) Nästa rCell SEPARATECOUNTWORDS = lCount End -funktion
För att använda denna funktion, följ nedanstående steg:-
- Gå till Excel -blad.
- För att räkna de specifika avgränsarna i ordet kommer vi att använda den här definierade funktionen.
- = SEPARATECOUNTWORDS (C7) och tryck på Enter.
- Funktionen returnerar antalet specifika avgränsare.
Hur extraherar jag ett ord från en mening eller cell i Microsoft Excel med VBA?
Vi har data i blad 1. I vilka vi har några adresser så vill vi extrahera orden från en mening eller cell eller ett intervall genom att skapa den användardefinierade funktionen via VBA i Excel.
För att göra den användardefinierade funktionen, följ nedanstående steg:-
- Öppna VBA -sidan och tryck på Alt+F11.
- Sätt i en modul.
- Skriv koden nedan:-
Funktion GETWORD (text som variant, N som heltal, valfri avgränsare som variant) som sträng om IsMissing (avgränsare) Sedan avgränsare = "" slut om GETWORD = delad (text, avgränsare) (N - 1) slutfunktion
Kod Förklaring:- I ovannämnda kod har vi nämnt funktionsnamn med variablerna. Och då hade vi definierat kriterierna för att extrahera ordet från mening eller cell.
Nu kommer vi att lära oss hur man använder denna formel. Följ nedanstående steg:-
- Gå till Excel -blad.
- Använd denna formel i cell D7.
- = GETWORD (C7,2) och tryck på Enter.
- Funktionen kommer att returnera andra ordet från cellen eftersom vi i formel nämnde för 2nd nummer ord. Om du vill hämta ordet som är placerat på 3rd position måste du ändra antalet från 2 till 3 i formeln.
Hur skapar jag ISO -veckonummerformel i Microsoft Excel med VBA?
Vi kommer att lära oss hur vi kan skapa ISO -veckonummerformel i Excel med denna UDF. Denna funktion kommer vi att använda för att identifiera att det nämnda datumet hör till vilket veckonummer på året.
Vi har en lista med datum i arket, och i den andra kolumnen vill vi hämta veckonumren.
För att skapa UDF för detta krav, följ nedanstående steg:-
- Öppna VBA -sidan och tryck på Alt+F11.
- Sätt i en modul.
- Skriv koden nedan:-
Funktion ISOWEEKNUMBER (Indate As Date) As Long Dim Dt As Date Dt = DateSerial (Year (Indate - Weekday (Indate - 1) + 4), 1, 3) ISOWEEKNUMBER = Int ((Indate - Dt + Weekday (Dt) + 5 ) / 7) Avsluta funktion
Kod Förklaring:-:- I koden ovan har vi nämnt funktionsnamn med variablerna. Och sedan hade vi ställt in datumvärdet och sedan hade vi definierat kriterierna för “ISOWEENUMBER”.
Hur kan vi använda den här funktionen i vår Excel -fil?
- Gå till Excel -blad.
- Ange formeln i cell D7.
- = ISOWEEKNUMBER (C7) och tryck på Enter.
- Funktionen returnerar veckan för det angivna datumet i cellen. För att hämta veckonumret för varje datum, kopiera samma formel inom intervallet.
Nu kommer vi att lära oss hur man återställer ISO-standardernas början av året i Excel- Årets första måndag.
Denna funktion kontrollerar i princip att 1st Måndagen i året kommer att falla på vilket datum och sedan kommer det att börja beräkna antalet veckor från det datumet. Låt oss se hur vi kan skapa UDF för detta krav.
Följ nedan angivna steg:-
- Öppna VBA -sidan och tryck på Alt+F11.
- Sätt i en modul.
- Skriv koden nedan:-
Funktion ISOSTYR (År som heltal) Som datum Dim WD Som heltal Dim NY Som datum NY = DateSerial (år, 1, 1) WD = (NY - 2) Mod 7 Om WD <4 Då ISOSTYR = NY - WD Else ISOSTYR = NY - WD + 7 End If End -funktion
Kod Förklaring: - I koden ovan har vi nämnt funktionsnamn med variablerna. Och då hade vi satt kriterierna för variablerna och sedan hade vi definierat formelinmatningen.
Du behöver bara ange år 2001 i detta format och formeln ger dig 1st Årets måndag.
Nu lär vi oss hur du använder UDF i Excel -fil. Följ nedanstående steg:-
- Gå till Excel -blad.
- Ange formeln i cell D7.
- = ISOSTYR (C7) och tryck på Enter.
- Funktionen returnerar datumet för 1st Måndag i nyårs första vecka.
- För att returnera datumet för 1st Måndag i nyårs första veckan, kopiera samma formel och klistra in i intervallet.
Hur vet jag kalkylbladet och arbetsbokens namn med hjälp av VBA i Microsoft Excel?
Följ nedanstående kod och steg:-
- Öppna VBA -sidan och tryck på Alt+F11.
- Sätt i en modul.
- Skriv koden nedan:-
Function Worksheetname () Worksheetname = Range ("A1"). Parent.Name End Function
Kod Förklaring:- I koden ovan har vi nämnt funktionsnamn och sedan hade vi definierat hur man känner till bladets namn.
För att använda denna formel behöver du bara ange formeln i valfri cell på detta sätt: -= Arbetsbladnamn (). Funktionen returnerar bladets namn.
För att skapa funktionen för arbetsbokens namn, följ nedanstående steg och kod:-
- Öppna VBA -sidan tryck på Alt+F11.
- Sätt i en modul.
- Skriv koden nedan:-
Function Workbookname () Workbookname = ThisWorkbook.Name Slutfunktion
Kodförklaring:-:- I koden ovan har vi nämnt funktionsnamn och sedan hade vi definierat hur vi skulle lära känna arbetsbokens namn.
För att använda denna formel behöver du bara ange formeln i valfri cell på detta sätt: - = Workbookname (). Funktionen returnerar bladets namn.
Hur extraherar jag det första och sista ordet från en cell med VBA i Microsoft Excel?
Vi har data i blad1 där vi har några adresser så vi vill extrahera det sista och första ordet från en mening eller cell eller ett intervall genom att skapa den användardefinierade funktionen via VBA i Excel.
Först skriver vi funktionen för att extrahera det första ordet. Följ nedanstående steg:-
- Öppna VBA -sidan tryck på tangenten Alt+F11.
- Sätt i en modul
Skriv koden nedan:-
Funktion GETFW (Text As String, Valfri Separator Som Variant) Dim FW As String If IsMissing (Separator) Then Separator = "" End If FW = Left (Text, InStr (1, Text, Separator, vbTextCompare)) GETFW = Ersätt (FW , Separator, "") Slutfunktion
Kod Förklaring: - I ovan nämnda kod har vi nämnt funktionsnamn med variablerna. Och sedan har vi definierat kriterierna för att extrahera ordet från mening eller cell.
Nu kommer vi att lära oss hur man använder denna formel. Följ nedanstående steg:-
- Gå till Excel -blad.
- Använd denna formel i cell D9.
- = GETFW (C9) och tryck på Enter.
- Funktionen returnerar det första ordet från data. Nu, för att hämta det första ordet för alla celler, kopiera samma formel i intervallet.
Nu skriver vi koden för att extrahera det sista ordet från cellen. Följ koden nedan:-
- Öppna VBA -sidan tryck på Alt+F11.
- Sätt i en modul.
- Skriv koden nedan:-
Funktion GETLW (Text As String, Valfri 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 (Ersätt (LW, Separator, "")) Avsluta funktion
Nu kommer vi att lära oss hur man använder denna formel. Följ nedanstående steg:-
- Gå till Excel -blad.
- Använd denna formel i cell D9.
- = GETLW (C9) Tryck på Enter.
- Funktionen returnerar det sista ordet från data. Nu, för att hämta det sista ordet för alla celler, kopiera samma formel i intervallet.
Det här är de funktioner vi kan definiera genom VBA och sedan kan använda den som Excel -formel. Vi kan också skapa många fler användardefinierade funktioner. Fortsätt lära dig med oss, vi kommer med mer knepiga formler.
