In dit artikel gaan we leren hoe u door de gebruiker gedefinieerde functies in Microsoft Excel kunt maken met behulp van de VBA.
Door de gebruiker gedefinieerde functie: - Microsoft Excel heeft al veel functies, maar toch heeft iedereen verschillende vereisten, situatie, we kunnen onze eigen functie maken volgens de vereiste die door de gebruiker gedefinieerde functie wordt genoemd. We kunnen de door de gebruiker gedefinieerde functie gebruiken zoals andere functies in Excel.
Hieronder staan de onderwerpen waarvoor we de door de gebruiker gedefinieerde functie zullen maken:
1). Hoe het aantal woorden in cel of bereik te tellen?
2). Hoe een woord uit een zin of cel in Excel te extraheren?
3). Hoe maak je de formule voor ISO?
4). Hoe de naam van het werkblad en de werkmap kennen met behulp van de VBA?
5). Hoe het eerste en laatste woord uit een cel in Excel te extraheren?
Hoe maak je de door de gebruiker gedefinieerde functie om het aantal woorden in cel of bereik te tellen?
We hebben gegevens in blad 1 waarin we enkele adressen hebben, dus we willen de woorden in een cel of een bereik tellen door de door de gebruiker gedefinieerde functie te maken via VBA in Excel.
Om de door de gebruiker gedefinieerde functie te maken, volgt u de onderstaande stappen: -
- Open VBA-pagina en druk op de toets Alt+F11.
- Plaats een module.
Schrijf de onderstaande code::
Functie WORDSCOUNT(rBereik als bereik) As Long Dim rCell As Range Dim Count zo lang voor elke rCellInrRange lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1 Volgende rCell WORDSCOUNT = lCount End-functie
Code Verklaringen: - Om de door de gebruiker gedefinieerde functie te maken, starten we de code naar de functienaam en definiëren we de variabelen. We hebben "For Each-lussen" in de code gebruikt om de woorden in het bereik te tellen.
Hoe gebruik je deze functie in Excel?
Volg onderstaande stappen om deze functie te gebruiken: -
- Ga naar Excel-blad.
- Om de woorden voor een cel te tellen, voert u de formule in cel D7 in.
- =WOORDENAANTAL(C7), cel C7 is de cel waarin we de woorden willen berekenen.
- De functie retourneert 6, wat betekent dat cel C7 6 woorden bevat.
- Om dezelfde berekening voor de rest van de cellen uit te voeren, kopieert u dezelfde formule en plakt u deze in het bereik.
- Om de woorden in het bereik te tellen, gebruikt u de formule als =WORDSCOUNT(C7:C16) en drukt u op Enter.
- De functie retourneert het aantal woorden.
Opmerking: - Deze UDF is handig om de woorden in een bereik of in een enkele cel te tellen.
Nu zullen we de code schrijven om het woord te tellen met behulp van het opgegeven scheidingsteken (,). Volg de onderstaande stappen: -
Functie SEPARATECOUNTWORDS(rBereik als bereik, optioneel scheidingsteken als variant) As Long Dim rCell As Range Dim Count As Long If IsMissing(separator) Then separator = "," End If For Each rCellInrRange lCount = lCount + Len(Trim(rCell)) - Len(Replace(Trim(rCell), separator, "")) Next rCell SEPARATECOUNTWORDS = lCount End Function
Volg onderstaande stappen om deze functie te gebruiken: -
- Ga naar Excel-blad.
- Om de specifieke scheidingstekens in het woord te tellen, gebruiken we deze gedefinieerde functie.
- =SEPARATECOUNTWORDS(C7) en druk op Enter.
- De functie retourneert het aantal van de specifieke scheidingstekens.
Hoe een woord uit een zin of cel in Microsoft Excel te extraheren met behulp van de VBA?
We hebben gegevens in blad 1. Waarin we enkele adressen hebben, dus we willen de woorden uit een zin of cel of een bereik halen door de door de gebruiker gedefinieerde functie te creëren via VBA in Excel.
Om de door de gebruiker gedefinieerde functie te maken, volgt u de onderstaande stappen: -
- Open VBA-pagina en druk op de toets Alt+F11.
- Plaats een module.
- Schrijf de onderstaande code::-
Functie GETWORD(Tekst als variant, N als geheel getal, optioneel scheidingsteken als variant) As String If IsMissing(scheidingsteken) Then scheidingsteken = " " End If GETWORD = Split(Tekst, scheidingsteken)(N - 1) Eindfunctie
Code Uitleg:- In de bovengenoemde code hebben we de functienaam bij de variabelen genoemd. En toen hadden we de criteria gedefinieerd om het woord uit een zin of cel te halen.
Nu zullen we leren hoe we deze formule kunnen gebruiken. Volg de onderstaande stappen: -
- Ga naar Excel-blad.
- Gebruik deze formule in cel D7.
- =GETWORD(C7,2) en druk op Enter.
- De functie retourneert het tweede woord uit de cel omdat in de formule die we hadden genoemd voor 2nd het woord van het nummer. Als u het woord wilt ophalen dat op 3 . staatrd positie, moet u het getal van 2 naar 3 in de formule wijzigen.
Hoe maak je een ISO-weeknummerformule in Microsoft Excel met behulp van de VBA?
We zullen leren hoe we met deze UDF ISO-weeknummerformule in Excel kunnen maken. Deze functie zullen we gebruiken om te identificeren dat de genoemde datum bij welk weeknummer van het jaar hoort.
We hebben een lijst met datums in het blad en in de tweede kolom willen we de weeknummers ophalen.
Volg onderstaande stappen om de UDF voor deze vereiste te maken::-
- Open VBA-pagina en druk op de toets Alt+F11.
- Plaats een module.
- Schrijf de onderstaande code: -
Functie ISOWEEKNUMMER(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) Functie beëindigen
Code Uitleg:- :- In de bovenstaande code hebben we de functienaam bij de variabelen genoemd. En toen hadden we de datumwaarde ingesteld en vervolgens hadden we de criteria van de functie "ISOWEENUMBER" gedefinieerd.
Hoe kunnen we deze functie gebruiken in ons Excel-bestand?
- Ga naar Excel-blad.
- Voer de formule in cel D7 in.
- =ISOWEEKNUMMER(C7), en druk op Enter.
- De functie retourneert de week voor de ingevoerde datum in de cel. Om nu het weeknummer voor elke datum op te halen, kopieert u dezelfde formule in het bereik.
Nu zullen we leren hoe we de ISO-normen begin van het jaar kunnen retourneren in Excel - Eerste maandag van het jaar.
Deze functie controleert in principe dat 1NS De maandag van het jaar valt op welke datum en dan begint het het aantal weken vanaf die datum te berekenen. Laten we eens kijken hoe we de UDF voor deze vereiste kunnen maken.
Volg onderstaande stappen::-
- Open VBA-pagina en druk op de toets Alt+F11.
- Plaats een module.
- Schrijf de onderstaande code::-
Functie 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 Anders ISOSTYR = NY - WD + 7 End If End-functie
Code Uitleg: - In de bovenstaande code hebben we de functienaam bij de variabelen genoemd. En dan hadden we de criteria voor de variabelen ingesteld en vervolgens de formule-invoer gedefinieerd.
U hoeft alleen het jaar 2001 in dit formaat op te geven en de formule geeft u 1NS maandag van het jaar.
Nu zullen we leren hoe we de UDF in Excel-bestand kunnen gebruiken. Volg de onderstaande stappen: -
- Ga naar Excel-blad.
- Voer de formule in cel D7 in.
- =ISOSTYR(C7) en druk op Enter.
- De functie retourneert de datum van de 1NS Maandag van de eerste week van het nieuwe jaar.
- Om de datum van de 1 . terug te gevenNS Maandag van de eerste week van het nieuwe jaar, kopieer dezelfde formule en plak in het bereik.
Hoe de naam van het werkblad en de werkmap te kennen met behulp van de VBA in Microsoft Excel?
Volg onderstaande gegeven code en stappen::-
- Open VBA-pagina en druk op de toets Alt+F11.
- Plaats een module.
- Schrijf de onderstaande code::-
Functie Werkbladnaam() Werkbladnaam = Bereik("A1").Parent.Name End Function
Code Uitleg: - In de bovenstaande code hebben we de functienaam genoemd en vervolgens hadden we gedefinieerd hoe de bladnaam te weten.
Om deze formule te gebruiken, hoeft u de formule op deze manier in een willekeurige cel in te voeren: -=Werkbladnaam(). De functie retourneert de bladnaam.
Om de functie voor de naam van de werkmap te maken, volgt u de onderstaande stappen en code: -
- Open VBA-pagina druk op de toets Alt+F11.
- Plaats een module.
- Schrijf de onderstaande code: -
Functie Workbookname() Workbookname = ThisWorkbook.Name End Function
Code Uitleg:-:- In de bovenstaande code hebben we de functienaam genoemd en vervolgens hadden we gedefinieerd hoe we de naam van de werkmap konden leren kennen.
Om deze formule te gebruiken, hoeft u de formule op deze manier in een willekeurige cel in te voeren: - =Werkmapnaam(). De functie retourneert de bladnaam.
Hoe het eerste en laatste woord uit een cel te extraheren met behulp van VBA in Microsoft Excel?
We hebben gegevens in blad1 waarin we enkele adressen hebben, dus we willen het laatste en eerste woord uit een zin of cel of een bereik halen door de door de gebruiker gedefinieerde functie te creëren via VBA in Excel.
Eerst zullen we de functie schrijven om het eerste woord te extraheren. Volg de onderstaande stappen: -
- Open VBA-pagina druk op de toets Alt+F11.
- Een module invoegen
Schrijf de onderstaande code::-
Functie GETFW(Text As String, Optionele Separator As Variant) Dim FW As String If IsMissing(Separator) Then Separator = " " End If FW = Left(Text, InStr(1, Text, Separator, vbTextCompare)) GETFW = Replace(FW) , Scheidingsteken, "") Eindfunctie
Code Uitleg: - In de bovengenoemde code hebben we de functienaam bij de variabelen genoemd. En dan hebben we de criteria gedefinieerd om het woord uit een zin of cel te extraheren.
Nu zullen we leren hoe we deze formule kunnen gebruiken. Volg de onderstaande stappen: -
- Ga naar Excel-blad.
- Gebruik deze formule in cel D9.
- =GETFW(C9) en druk op Enter.
- De functie retourneert het eerste woord uit de gegevens. Kopieer nu dezelfde formule in het bereik om het eerste woord voor alle cellen op te halen.
Nu gaan we de code schrijven om het laatste woord uit de cel te extraheren. Volg onderstaande code: -
- Open VBA-pagina druk op de toets Alt+F11.
- Plaats een module.
- Schrijf de onderstaande code::-
Functie GETLW(Text As String, Optionele Separator As 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(Replace(LW, Separator, "")) End Function
Nu zullen we leren hoe we deze formule kunnen gebruiken. Volg de onderstaande stappen: -
- Ga naar Excel-blad.
- Gebruik deze formule in cel D9.
- =GETLW(C9) Druk op Enter.
- De functie retourneert het laatste woord uit de gegevens. Kopieer nu dezelfde formule in het bereik om het laatste woord voor alle cellen op te halen.
Dit zijn de functies die we via VBA kunnen definiëren en vervolgens kunnen gebruiken als formule van Excel. We kunnen ook veel meer door de gebruiker gedefinieerde functies creëren. Blijf met ons leren, we komen met meer lastige formules.
