De functies zoals VERT.ZOEKEN, AANTAL.ALS, SOM.ALS worden werkbladfuncties genoemd. Over het algemeen zijn de functies die vooraf zijn gedefinieerd in Excel en klaar voor gebruik op een werkblad, werkbladfuncties. U kunt de code achter deze functies in VBA niet wijzigen of zien.
Aan de andere kant zijn de door de gebruiker gedefinieerde functies en functies die specifiek zijn voor VBA, zoals MsgBox of InputBox, VBA-functies.
We weten allemaal hoe we VBA-functies in VBA moeten gebruiken. Maar wat als we VERT.ZOEKEN in een VBA willen gebruiken. Hoe doen we dat? In dit artikel zullen we precies dat onderzoeken.
Werkbladfuncties gebruiken in VBA
Om toegang te krijgen tot de werkbladfunctie gebruiken we een Application class. Bijna alle werkbladfuncties worden vermeld in de klasse Application.WorksheetFunction. En met de dot-operator hebt u toegang tot ze allemaal.
Schrijf in een willekeurige sub Application.WorksheetFunction. En begin met het schrijven van de naam van de functie. VBA's intellisense toont de naam van de functies die beschikbaar zijn voor gebruik. Nadat u de functienaam hebt gekozen, zal deze om de variabelen vragen, zoals elke functie in Excel. Maar u zult variabelen moeten doorgeven in een begrijpelijk VBA-formaat. Als u bijvoorbeeld een bereik A1:A10 wilt doorgeven, moet u het doorgeven als een bereikobject zoals Range("A1:A10").
Laten we dus enkele werkbladfuncties gebruiken om het beter te begrijpen.
De functie VERT.ZOEKEN gebruiken in VBA
Om te demonstreren hoe u een VERT.ZOEKEN-functie in VBA kunt gebruiken, heb ik hier voorbeeldgegevens. Ik moet de naam en de stad van de opgegeven aanmeldings-ID in een berichtvenster weergeven met behulp van VBA. De gegevens zijn verspreid in bereik A1:K26.
Druk op ALT+F11 om VBE te openen en een module in te voegen.
Zie onderstaande code.
Sub WsFuncitons() Dim loginID As String Dim naam, stad As String loginID = "AHKJ_1-3357042451" 'De functie VERT.ZOEKEN gebruiken om de naam van de gegeven id in de tabelnaam te krijgen = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26") ), 2, 0) 'De functie VERT.ZOEKEN gebruiken om de stad van de gegeven id in de tabel op te halen city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0) MsgBox ("Name: " & name & vbLf & "Stad: " & stad) Einde Sub
Wanneer u deze code uitvoert, krijgt u dit resultaat.
U kunt zien hoe snel de VBA het resultaat in een berichtvenster afdrukt. Laten we nu de code eens bekijken.
Hoe werkt het?
1.
Dim login-ID als string
Dim naam, stad As String
Eerst hebben we twee variabelen van het tekenreekstype gedeclareerd om het resultaat op te slaan dat wordt geretourneerd door de functie VERT.ZOEKEN. Ik heb variabelen van het tekenreekstype gebruikt omdat ik zeker weet dat het resultaat dat wordt geretourneerd door VERT.ZOEKEN een tekenreekswaarde zal zijn. Als wordt verwacht dat uw werkbladfunctie een getal, datum, bereik, enz. Type waarde retourneert, gebruikt u dat soort variabele om het resultaat op te slaan. Als u niet zeker weet welk soort waarde wordt geretourneerd door de werkbladfunctie, gebruikt u varianttypevariabelen.
2.
loginID = "AHKJ_1-3357042451"
Vervolgens hebben we de loginID-variabele gebruikt om de opzoekwaarde op te slaan. Hier hebben we een hardcoded waarde gebruikt. Je kunt ook referenties gebruiken. Bijvoorbeeld. U kunt Range("A2").Value gebruiken om de opzoekwaarde van bereik A2 dynamisch bij te werken.
3.
naam = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 2, 0)
Hier gebruiken we de VERT.ZOEKEN-functie om te krijgen. Als u nu de functie naar rechts opent en de haakjes opent, worden de vereiste argumenten weergegeven, maar niet zo beschrijvend als in Excel. Kijk zelf maar.
U moet onthouden hoe en welke variabele u moet gebruiken. U kunt altijd teruggaan naar het werkblad om de details van de beschrijvende variabele te bekijken.
Hier is de opzoekwaarde Arg1. Voor Arg1 gebruiken we loginID. De opzoektabel is Arg2. Voor Arg2 hebben we Range("A1:K26") gebruikt. Merk op dat we niet rechtstreeks A2:K26 hebben gebruikt zoals we dat doen in Excel. De kolommenindex is Arg3. Voor Arg3 hebben we 2 gebruikt, aangezien de naam in de tweede kolom staat. Het opzoektype is Arg4. We gebruikten 0 als Arg4.
city = Application.WorksheetFunction.VLookup(loginID, Range("A1:K26"), 4, 0)
Op dezelfde manier krijgen we de naam van de stad.
4.
MsgBox ("Naam: " & naam & vbLf & "Plaats: " & stad)
Ten slotte drukken we de naam en de stad af met behulp van Messagebox.
Waarom de werkbladfunctie in VBA gebruiken?
De werkbladfuncties beschikken over de kracht van immense berekeningen en het is niet slim om de kracht van werkbladfuncties te negeren. Als we bijvoorbeeld de standaarddeviatie van een dataset willen en je wilt hier hele code voor schrijven, dan kan dat uren duren. Maar als je de werkbladfunctie STDEV.P in VBA weet te gebruiken om de berekening in één keer te krijgen.
Sub GetStdDev() std = Application.WorksheetFunction.StDev_P(Range("A1:K26")) End Sub
Meerdere werkbladfuncties gebruiken VBA
Laten we zeggen dat we een indexovereenkomst moeten gebruiken om enkele waarden op te halen. Hoe zou je de formule in VBA maken. Dit is wat ik denk dat je gaat schrijven:
Sub IndMtch() Val = Application.WorksheetFunction.Index(result_range, _ Application.WorksheetFunction.Match(lookup_value, _ lookup_range, match_type)) End Sub
Dit is niet verkeerd, maar het is wel lang. De juiste manier om meerdere functies te gebruiken is door een With-blok te gebruiken. Zie het onderstaande voorbeeld:
Sub IndMtch() With Application.WorksheetFunction Val = .Index(result_range, .Match(lookup_value, lookup_range, match_type)) val2 = .VLookup(arg1, arg2, arg3) val4 = .StDev_P(numbers) End With End Sub
Zoals je kunt zien, heb ik With block gebruikt om VBA te vertellen dat ik de eigenschappen en functies van Application.WorksheetFunction zal gebruiken. Ik hoef het dus niet overal te definiëren. Ik heb zojuist de puntoperator gebruikt om toegang te krijgen tot de functies INDEX, MATCH, VLOOKUP en STDEV.P. Zodra we de End With-instructie gebruiken, hebben we geen toegang tot functies zonder volledig gekwalificeerde functienamen te gebruiken.
Dus als u meerdere werkbladfuncties in VBA moet gebruiken, gebruik dan met blok.
Niet alle werkbladfuncties zijn beschikbaar via de toepassing.Werkbladfunctie
Sommige werkbladfuncties zijn direct beschikbaar voor gebruik in VBA. U hoeft geen Application.WorksheetFunction-object te gebruiken.
Bijvoorbeeld functies zoals Len() die wordt gebruikt om het aantal tekens in een string te krijgen, left, right, mid, trim, offset etc. Deze functies kunnen direct in VBA worden gebruikt. Hier is een voorbeeld.
Sub GetLen() Strng = "Hallo" Debug.Print (Len(strng)) End Sub
Kijk, hier hebben we de LEN-functie gebruikt zonder het Application.WorksheetFunction-object te gebruiken.
Op dezelfde manier kunt u andere functies gebruiken, zoals links, rechts, midden, char enz.
Sub GetLen() Strng = "Hallo" Debug.Print (Len(strng)) Debug.Print (left(strng,2)) Debug.Print (right(strng,1)) Debug.Print (Mid(strng, 3, 2)) Einde sub
Wanneer u de bovenstaande sub uitvoert, keert deze terug:
5 Hij zal
Dus ja jongens, dit is hoe je de werkbladfunctie van Excel in VBA kunt gebruiken. Ik hoop dat ik voldoende uitleg heb gegeven en dat dit artikel je heeft geholpen. Als je vragen hebt over dit artikel of iets anders met betrekking tot VBA, stel ze dan in de commentaarsectie hieronder. Tot die tijd kunt u hieronder lezen over andere gerelateerde onderwerpen.
Wat is de CSng-functie in Excel VBA | De SCng-functie is een VBA-functie die elk gegevenstype converteert naar een getal met enkele precisie met drijvende komma ("aangezien het een getal is"). Ik gebruik meestal de CSng-functie om tekstgeformatteerde getallen om te zetten in werkelijke getallen.
Hoe tekst en nummer omgekeerd te krijgen via VBA in Microsoft Excel | Om nummer en tekst om te keren gebruiken we loops en mid-functie in VBA. 1234 wordt omgezet in 4321, "jij" wordt omgezet in "uoy". Hier is het fragment.
Gegevens opmaken met aangepaste getalnotaties met VBA in Microsoft Excel | Gebruik dit VBA-fragment om de getalnotatie van specifieke kolommen in Excel te wijzigen. Het verbergt het getalformaat van gespecificeerd naar gespecificeerd formaat in één klik.
Werkbladwijzigingsgebeurtenis gebruiken om macro uit te voeren wanneer een wijziging is aangebracht | Dus om uw macro uit te voeren wanneer het blad wordt bijgewerkt, gebruiken we de werkbladgebeurtenissen van VBA.
Voer macro uit als er een wijziging is aangebracht op het blad in het opgegeven bereik | Gebruik deze VBA-code om uw macrocode uit te voeren wanneer de waarde in een opgegeven bereik verandert. Het detecteert elke wijziging die in het opgegeven bereik is aangebracht en activeert de gebeurtenis.
Eenvoudigste VBA-code om huidige rij en kolom te markeren met behulp van | Gebruik dit kleine VBA-fragment om de huidige rij en kolom van het blad te markeren.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
De VERT.ZOEKEN-functie in Excel | Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
AANTAL.ALS in Excel 2016 | Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.