Funksjonene som VLOOKUP, COUNTIF, SUMIF kalles regnearkfunksjoner. Vanligvis er funksjonene som er forhåndsdefinerte i Excel og klare til bruk i et regneark, regnearkfunksjoner. Du kan ikke endre eller se koden bak disse funksjonene i VBA.
På den annen side er de brukerdefinerte funksjonene og funksjonene som er spesifikke for VBA som MsgBox eller InputBox VBA -funksjoner.
Vi vet alle hvordan vi bruker VBA -funksjoner i VBA. Men hva om vi vil bruke VLOOKUP i en VBA. Hvordan gjør vi det? I denne artikkelen vil vi utforske akkurat det.
Bruke regnearkfunksjoner i VBA
For å få tilgang til regnearkfunksjonen bruker vi en applikasjonsklasse. Nesten alle regnearkfunksjonene er oppført i Application.WorksheetFunction -klassen. Og ved å bruke punktoperator får du tilgang til dem alle.
I en hvilken som helst del, skriv Application.WorksheetFunction. Og begynn å skrive navnet på funksjonen. VBA's intellisense vil vise navnet på funksjonene som er tilgjengelige for bruk. Når du har valgt funksjonsnavnet, vil den be om variablene, som enhver funksjon på excel. Men du må passere variabler i VBA -forståelig format. For eksempel, hvis du vil passere et område A1: A10, må du passere det som et områdeobjekt som Range ("A1: A10").
Så la oss bruke noen regnearkfunksjoner for å forstå det bedre.
Slik bruker du VLOOKUP -funksjonen i VBA
For å demonstrere hvordan du kan bruke en VLOOKUP -funksjon i VBA, her har jeg eksempeldata. Jeg må vise navn og by for oppgitt påloggings -ID i en meldingsboks ved hjelp av VBA. Dataene er spredt i område A1: K26.
Trykk ALT+F11 for å åpne VBE og sett inn en modul.
Se koden nedenfor.
Sub WsFuncitons () Dim loginID As String Dim name, city As String loginID = "AHKJ_1-3357042451" 'Bruker VLOOKUP-funksjon for å få navnet på den oppgitte id i tabellnavn = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26" ), 2, 0) 'Bruke VLOOKUP -funksjon for å hente byen med gitt ID i tabellby = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & "City:" & city) End Sub
Når du kjører denne koden, får du dette resultatet.
Du kan se hvor raskt VBA skriver ut resultatet i en meldingsboks. La oss nå undersøke koden.
Hvordan virker det?
1.
Dim loginID As String
Dim navn, by As String
Først har vi deklarert to variabler av strengtype for å lagre resultatet returnert av VLOOKUP -funksjonen. Jeg har brukt strengtypevariabler fordi jeg er sikker på at resultatet som returneres av VLOOKUP vil være en strengverdi. Hvis regnearkfunksjonen forventes å returnere tall, dato, område, etc. type verdi, bruker du den typen variabel for å lagre resultatet. Hvis du ikke er sikker på hvilken type verdi som skal returneres av regnearkfunksjonen, bruker du variabeltypetypen.
2.
loginID = "AHKJ_1-3357042451"
Deretter har vi brukt loginID -variabelen til å lagre oppslagsverdi. Her har vi brukt en hardkodet verdi. Du kan også bruke referanser. For eksempel. Du kan bruke Range ("A2"). Verdi for å dynamisk oppdatere oppslagsverdi fra område A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 2, 0)
Her bruker vi VLOOKUP -funksjonen for å få. Når du nå retter funksjonen og åpner parentesen, viser den nødvendige argumenter, men ikke så beskrivende som den viser i Excel. Se for deg selv.
Du må huske hvordan og hvilken variabel du trenger å bruke. Du kan alltid gå tilbake til regnearket for å se de beskrivende variabledetaljene.
Her er oppslagsverdien Arg1. For Arg1 bruker vi loginID. Oppslagstabellen er Arg2. For Arg2 brukte vi Range ("A1: K26"). Vær oppmerksom på at vi ikke brukte A2: K26 direkte som på Excel. Kolonneindeksen er Arg3. For Arg3 brukte vi 2, siden navnet er i den andre kolonnen. Oppslagstypen er Arg4. Vi brukte 0 som Arg4.
city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0)
På samme måte får vi bynavnet.
4.
MsgBox ("Navn:" & navn & vbLf & "By:" og by)
Til slutt skriver vi ut navn og by ved hjelp av Messagebox.
Hvorfor bruke regnearkfunksjon i VBA?
Regnearkfunksjonene har enorme beregninger, og det vil ikke være smart å ignorere kraften til regnearkfunksjoner. For eksempel, hvis vi vil ha standardavviket til et datasett og du vil skrive hele koden for dette, kan det ta deg timer. Men hvis du vet hvordan du bruker regnearkfunksjonen STDEV.P i VBA for å få beregningen på en gang.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub
Bruke flere regnearkfunksjoner VBA
La oss si at vi må bruke en indeksmatch for å hente noen verdier. Nå, hvordan ville du slå opp formelen i VBA. Dette er det jeg antar 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 feil, men det er langt. Den riktige måten å bruke flere funksjoner er ved å bruke en With -blokk. Se eksemplet nedenfor:
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
Som du kan se, har jeg brukt With block til å fortelle VBA at jeg skal bruke egenskapene og funksjonene til Application.WorksheetFunction. Så jeg trenger ikke å definere det overalt. Jeg brukte nettopp punktoperatoren for å få tilgang til INDEX, MATCH, VLOOKUP og STDEV.P funksjoner. Når vi bruker End With -setning, har vi ikke tilgang til funksjoner uten å bruke fullt kvalifiserte funksjonsnavn.
Så hvis du må bruke flere regnearkfunksjoner i VBA, bruk med blokk.
Ikke alle regnearkfunksjonene er tilgjengelige gjennom Application.WorksheetFunction
Noen regnearkfunksjoner er direkte tilgjengelige for bruk i VBA. Du trenger ikke å bruke Application.WorksheetFunction -objektet.
For eksempel funksjoner som Len () som brukes til å få antall tegn i en streng, venstre, høyre, midten, trim, offset osv. Disse funksjonene kan brukes direkte i VBA. Her er et eksempel.
Sub GetLen () Strng = "Hei" Debug.Print (Len (strng)) Slutt Sub
Se, her brukte vi LEN -funksjonen uten å bruke Application.WorksheetFunction -objektet.
På samme måte kan du bruke andre funksjoner som venstre, høyre, midten, røye etc.
Sub GetLen () Strng = "Hei" Debug.Print (Len (strng)) Debug.Print (venstre (strng, 2)) Debug.Print (høyre (strng, 1)) Debug.Print (Midt (strng, 3, 2)) Slutt Sub
Når du kjører suben ovenfor, kommer den tilbake:
5 Han vil
Så ja gutta, dette er hvordan du kan bruke regnearksfunksjonen til Excel i VBA. Jeg håper jeg var forklarende nok, og denne artikkelen hjalp deg. Hvis du har spørsmål angående denne artikkelen eller noe annet relatert VBA, kan du spørre i kommentarfeltet nedenfor. Inntil da kan du lese om andre relaterte emner nedenfor.
Hva er CSng -funksjon i Excel VBA | SCng-funksjonen er en VBA-funksjon som konverterer hvilken som helst datatype til et flytende punkt med én presisjon ("gitt at det er et tall"). Jeg bruker stort sett CSng -funksjonen til å konvertere tekstformaterte tall til faktiske tall.
Hvordan få tekst og tall i revers gjennom VBA i Microsoft Excel | For å reversere tall og tekst bruker vi sløyfer og midtfunksjon i VBA. 1234 vil bli konvertert til 4321, "du" vil bli konvertert til "uoy". Her er utdraget.
Formater data med tilpassede tallformater ved hjelp av VBA i Microsoft Excel | Bruk denne VBA -kodebiten for å endre tallformatet for spesifikke kolonner i excel. Det dekker tallformatet for spesifisert til spesifisert format med ett klikk.
Bruke regnearksendringshendelse for å kjøre makro når noen endring gjøres | Så for å kjøre makroen din når arket oppdateres, bruker vi regnearkhendelser i VBA.
Kjør makro hvis det er gjort endringer på ark i spesifisert område | Bruk denne VBA -koden for å kjøre makrokoden når verdien i et spesifisert område endres. Den oppdager alle endringer som er gjort i det angitte området, og vil utløse hendelsen.
Enkleste VBA -kode for å markere gjeldende rad og kolonne ved hjelp | Bruk denne lille VBA -biten til å markere gjeldende rad og kolonne i arket.
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
VLOOKUP -funksjonen i Excel | Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
COUNTIF i Excel 2016 | Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.