Funktionerne som VLOOKUP, COUNTIF, SUMIF kaldes regnearksfunktioner. Generelt er de funktioner, der er foruddefineret i Excel og klar til brug på et regneark, regnearksfunktioner. Du kan ikke ændre eller se koden bag disse funktioner i VBA.
På den anden side er de brugerdefinerede funktioner og funktioner, der er specifikke for VBA som MsgBox eller InputBox, VBA -funktioner.
Vi ved alle, hvordan man bruger VBA -funktioner i VBA. Men hvad nu hvis vi vil bruge VLOOKUP i en VBA. Hvordan gør vi det? I denne artikel vil vi undersøge præcis det.
Brug af regnearksfunktioner i VBA
For at få adgang til regnearksfunktionen bruger vi en applikationsklasse. Næsten alle regnearksfunktionerne er opført i klassen Application.WorksheetFunction. Og ved hjælp af punktoperator kan du få adgang til dem alle.
I en hvilken som helst sub skal du skrive Application.WorksheetFunction. Og begynd at skrive navnet på funktionen. VBA's intellisense viser navnet på de tilgængelige funktioner. Når du har valgt funktionsnavnet, vil det bede om variablerne, ligesom enhver funktion på excel. Men du bliver nødt til at videregive variabler i VBA -forståeligt format. For eksempel, hvis du vil passere et område A1: A10, skal du sende det som et områdeobjekt som område ("A1: A10").
Så lad os bruge nogle regnearksfunktioner til at forstå det bedre.
Sådan bruges VLOOKUP -funktionen i VBA
For at demonstrere, hvordan du kan bruge en VLOOKUP -funktion i VBA, har jeg her eksempeldata. Jeg skal vise Navn og By for det givne Login -id i en meddelelsesboks ved hjælp af VBA. Dataene er spredt i område A1: K26.
Tryk på ALT+F11 for at åbne VBE og indsætte et modul.
Se nedenstående kode.
Sub WsFuncitons () Dim loginID As String Dim name, city As String loginID = "AHKJ_1-3357042451" 'Brug af VLOOKUP-funktion til at få navnet på det givne id i tabelnavn = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26" ), 2, 0) 'Brug af VLOOKUP -funktion til at hente byen med givet id i tabelby = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Navn:" & navn & vbLf & "By:" & by) Slut Sub
Når du kører denne kode, får du dette resultat.
Du kan se, hvor hurtigt VBA udskriver resultatet i en meddelelsesboks. Lad os nu undersøge koden.
Hvordan virker det?
1.
Dim loginID som streng
Dim navn, by As String
Først har vi erklæret to variabler af strengtype for at gemme resultatet returneret af VLOOKUP -funktionen. Jeg har brugt strengtypevariabler, fordi jeg er sikker på, at resultatet returneret af VLOOKUP vil være en strengværdi. Hvis din regnearksfunktion forventes at returnere tal, dato, område osv. Værditype, skal du bruge den slags variabel til at gemme resultatet. Hvis du ikke er sikker på, hvilken slags værdi der returneres af regnearksfunktionen, skal du bruge variabeltypevariabler.
2.
loginID = "AHKJ_1-3357042451"
Dernæst har vi brugt loginID -variablen til at gemme opslagsværdi. Her har vi brugt en hårdkodet værdi. Du kan også bruge referencer. For eksempel. Du kan bruge område ("A2"). Værdi til dynamisk opdatering af opslagsværdi fra område A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, område ("A1: K26"), 2, 0)
Her bruger vi VLOOKUP -funktionen til at få. Når du nu retter funktionen og åbner parentesen, viser den dig nødvendige argumenter, men ikke så beskrivende som den viser i Excel. Se selv.
Du skal huske, hvordan og hvilken variabel du skal bruge. Du kan altid gå tilbage til regnearket for at se de beskrivende variabeldetaljer.
Her er opslagsværdien Arg1. Til Arg1 bruger vi loginID. Opslagstabellen er Arg2. Til Arg2 brugte vi Range ("A1: K26"). Bemærk, at vi ikke brugte direkte A2: K26, som vi gør på Excel. Kolonneindekset er Arg3. Til Arg3 brugte vi 2, da navnet er i den anden kolonne. Opslagstypen er Arg4. Vi brugte 0 som Arg4.
city = Application.WorksheetFunction.VLookup (loginID, område ("A1: K26"), 4, 0)
På samme måde får vi bynavnet.
4.
MsgBox ("Navn:" & navn & vbLf & "By:" & by)
Endelig udskriver vi navn og by ved hjælp af Messagebox.
Hvorfor bruge regnearksfunktion i VBA?
Regnearkets funktioner besidder enorme beregninger, og det vil ikke være smart at ignorere kraften i regnearkfunktioner. For eksempel, hvis vi ønsker standardafvigelsen for et datasæt, og du vil skrive hele koden til dette, kan det tage dig timer. Men hvis du ved, hvordan du bruger regnearksfunktionen STDEV.P i VBA til at få beregningen på én gang.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub
Brug af flere regnearksfunktioner VBA
Lad os sige, at vi skal bruge et indeksmatch til at hente nogle værdier. Nu, hvordan ville du lave formlen i VBA. Det er hvad jeg tror du vil skrive:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
Dette er ikke forkert, men det er langt. Den rigtige måde at bruge flere funktioner på er ved at bruge en With -blok. Se eksemplet herunder:
Sub IndMtch () Med 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
Som du kan se, har jeg brugt With block til at fortælle VBA, at jeg vil bruge egenskaberne og funktionerne i Application.WorksheetFunction. Så jeg behøver ikke at definere det overalt. Jeg brugte lige punktoperatoren til at få adgang til INDEX, MATCH, VLOOKUP og STDEV.P funktioner. Når vi først bruger End With -sætning, har vi ikke adgang til funktioner uden at bruge fuldt kvalificerede funktionsnavne.
Så hvis du skal bruge flere regnearksfunktioner i VBA, skal du bruge med blok.
Ikke alle regnearksfunktionen er tilgængelige via Application.WorksheetFunction
Nogle regnearkfunktioner er direkte tilgængelige til brug i VBA. Du behøver ikke at bruge Application.WorksheetFunction -objektet.
For eksempel funktioner som Len (), der bruges til at få antallet af tegn i en streng, venstre, højre, midten, trim, offset osv. Disse funktioner kan bruges direkte i VBA. Her er et eksempel.
Sub GetLen () Strng = "Hej" Debug.Print (Len (strng)) Afslut Sub
Se, her brugte vi LEN -funktionen uden at bruge Application.WorksheetFunction -objektet.
På samme måde kan du bruge andre funktioner som venstre, højre, midten, kul osv.
Sub GetLen () Strng = "Hej" Debug.Print (Len (strng)) Debug.Print (venstre (strng, 2)) Debug.Print (højre (strng, 1)) Debug.Print (Mid (strng, 3, 2)) Afslut Sub
Når du kører ovenstående sub, returnerer den:
5 Han vil
Så ja fyre, sådan kan du bruge regnearksfunktionen i Excel i VBA. Jeg håber, jeg var forklarende nok, og denne artikel hjalp dig. Hvis du har spørgsmål vedrørende denne artikel eller andet relateret VBA, så spørg i kommentarfeltet herunder. Indtil da kan du læse om andre relaterede emner herunder.
Hvad er CSng -funktion i Excel VBA | SCng-funktionen er en VBA-funktion, der konverterer enhver datatype til et flydende punktnummer med en præcision ("da det er et tal"). Jeg bruger for det meste CSng -funktion til at konvertere tekstformaterede tal til faktiske tal.
Sådan får du tekst og tal i omvendt gennem VBA i Microsoft Excel | For at vende nummer og tekst bruger vi loops og mid -funktion i VBA. 1234 konverteres til 4321, "du" konverteres til "uoy". Her er uddraget.
Formater data med tilpassede talformater ved hjælp af VBA i Microsoft Excel | Brug dette VBA -kodestykke til at ændre talformatet for bestemte kolonner i excel. Det dækker nummerformatet for det angivne til det angivne format med et klik.
Brug af regnearksændringsbegivenhed til at køre makro, når der foretages ændringer | Så for at køre din makro, når arket opdateres, bruger vi regnearkhændelser i VBA.
Kør makro, hvis der foretages ændringer på ark i specificeret område | Hvis du vil køre din makrokode, når værdien i et bestemt område ændres, skal du bruge denne VBA -kode. Det registrerer enhver ændring, der er foretaget i det angivne område, og afbryder begivenheden.
Den enkleste VBA -kode til at fremhæve den aktuelle række og kolonne ved hjælp af | Brug dette lille VBA -udsnit til at fremhæve den aktuelle række og kolonne i arket.
Populære artikler:
50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
VLOOKUP -funktionen i Excel | Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
COUNTIF i Excel 2016 | Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.