Funkcije poput VLOOKUP, COUNTIF, SUMIF nazivaju se funkcije radnog lista. Općenito, funkcije koje su unaprijed definirane u Excelu i spremne za upotrebu na radnom listu su funkcije radnog lista. Ne možete promijeniti ili vidjeti kôd iza ovih funkcija u VBA -i.
S druge strane, korisnički definirane funkcije i funkcije specifične za VBA poput MsgBox ili InputBox su VBA funkcije.
Svi znamo koristiti VBA funkcije u VBA. Ali što ako želimo koristiti VLOOKUP u VBA. Kako to učiniti? U ovom članku ćemo istražiti upravo to.
Korištenje funkcija radnog lista u VBA
Za pristup funkciji radnog lista koristimo klasu Application. Gotovo sve funkcije radnog lista navedene su u klasi Application.WorksheetFunction. Pomoću dot operatora možete im pristupiti svima.
U bilo kojoj podmeni upišite Application.WorksheetFunction. I počnite pisati naziv funkcije. VBA -in intellisense prikazat će naziv funkcija dostupnih za korištenje. Nakon što odaberete naziv funkcije, od nje će se tražiti varijable, kao i svaka funkcija u Excelu. No morat ćete proslijediti varijable u razumljivom formatu VBA. Na primjer, ako želite proslijediti raspon A1: A10, morat ćete ga proslijediti kao objekt raspona poput Range ("A1: A10").
Pa upotrijebimo neke funkcije radnog lista da bismo to bolje razumjeli.
Kako koristiti funkciju VLOOKUP u VBA
Da bih pokazao kako možete koristiti funkciju VLOOKUP u VBA, ovdje imam uzorke podataka. Moram prikazati ime i grad danog ID -a za prijavu u okvir za poruku koristeći VBA. Podaci su raspoređeni u rasponu A1: K26.
Pritisnite ALT+F11 da otvorite VBE i umetnete modul.
Pogledajte donji kod.
Pod WsFuncitons () Dim loginID kao niz Znak Dim naziv, grad kao String loginID = "AHKJ_1-3357042451" 'Korištenje funkcije VLOOKUP za dobivanje imena danog ID-a u tablici naziv = Application.WorksheetFunction.VLookup (loginID, Raspon ("A1: K26" ), 2, 0) 'Korištenje funkcije VLOOKUP za dobivanje grada danog ID -a u tablici grad = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & "Grad:" & grad) End Sub
Kada pokrenete ovaj kôd, dobit ćete ovaj rezultat.
Možete vidjeti koliko brzo VBA ispisuje rezultat u okvir s porukom. Sada ispitajmo kod.
Kako radi?
1.
Zatamni ID prijave kao niz
Dim ime, grad As String
Prvo smo deklarirali dvije varijable vrste niza za spremanje rezultata koje je vratila funkcija VLOOKUP. Koristio sam varijable tipa niza jer sam siguran da će rezultat koji vraća VLOOKUP biti vrijednost niza. Ako se od vaše funkcije radnog lista očekuje da vrati vrijednost, datum, raspon itd. Vrijednost, upotrijebite tu vrstu varijable za spremanje rezultata. Ako niste sigurni koju vrstu vrijednosti će funkcija funkcije radnog lista vratiti, upotrijebite varijable tipa varijante.
2.
loginID = "AHKJ_1-3357042451"
Zatim smo koristili varijablu loginID za spremanje vrijednosti pretraživanja. Ovdje smo koristili tvrdo kodiranu vrijednost. Možete koristiti i reference. Na primjer. Možete koristiti raspon ("A2"). Vrijednost za dinamičko ažuriranje vrijednosti traženja iz raspona A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, raspon ("A1: K26"), 2, 0)
Ovdje koristimo funkciju VLOOKUP za dobivanje. Sada kada ispravite funkciju i otvorite zagrade, ona će vam pokazati potrebne argumente, ali ne tako opisne kao što je prikazano u Excelu. Uvjerite se sami.
Morate se sjetiti kako i koju varijablu trebate koristiti. Uvijek se možete vratiti na radni list kako biste vidjeli pojedinosti opisne varijable.
Ovdje je vrijednost pretraživanja Arg1. Za Arg1 koristimo loginID. Tablica za pretraživanje je Arg2. Za Arg2 koristili smo Range ("A1: K26"). Imajte na umu da nismo izravno koristili A2: K26 kao što radimo u Excelu. Indeks stupaca je Arg3. Za Arg3 koristili smo 2, budući da se naziv nalazi u drugom stupcu. Vrsta pretraživanja je Arg4. Koristili smo 0 kao Arg4.
city = Application.WorksheetFunction.VLookup (loginID, raspon ("A1: K26"), 4, 0)
Slično dobivamo i naziv grada.
4.
MsgBox ("Naziv:" & naziv & vbLf & "Grad:" & grad)
Na kraju ispisujemo ime i grad koristeći Messagebox.
Zašto koristiti funkciju radnog lista u VBA?
Funkcije radnog lista posjeduju moć ogromnih izračuna i neće biti pametno zanemariti moć funkcija radnog lista. Na primjer, ako želimo standardnu devijaciju skupa podataka, a vi za to želite napisati cijeli kôd, to može potrajati satima. Ali ako znate koristiti funkciju radnog lista STDEV.P u VBA -i za izračun u jednom potezu.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Raspon ("A1: K26")) Kraj Sub
Korištenje više funkcija radnog lista VBA
Recimo da moramo koristiti indeksno podudaranje za dohvaćanje nekih vrijednosti. Kako biste sada ispisali formulu u VBA -i. Ovo ćete valjda napisati:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
Ovo nije pogrešno, ali je dugotrajno. Pravi način korištenja više funkcija je pomoću bloka With. Pogledajte donji primjer:
Sub IndMtch () S Application.WorksheetFunction Val = .Index (result_range, .Match (lookup_value, lookup_range, match_type)) val2 = .VLookup (arg1, arg2, arg3) val4 = .StDev_P (brojevi) Završi s završetkom Sub
Kao što vidite, upotrijebio sam With block da kažem VBA -i da ću koristiti svojstva i funkcije Application.WorksheetFunction. Dakle, ne moram to svugdje definirati. Upravo sam koristio dot operator za pristup funkcijama INDEX, MATCH, VLOOKUP i STDEV.P. Nakon što koristimo izraz End With, nećemo moći pristupiti funkcijama bez korištenja potpuno kvalificiranih naziva funkcija.
Dakle, ako morate koristiti više funkcija radnog lista u VBA -i, koristite s blokom.
Nisu sve funkcije radnog lista dostupne putem Application.WorksheetFunction
Neke su funkcije radnog lista izravno dostupne za upotrebu u VBA -i. Ne morate koristiti objekt Application.WorksheetFunction.
Na primjer, funkcije poput Len () koja se koristi za dobivanje broja znakova u nizu, lijevo, desno, srednji, trim, pomak itd. Ove se funkcije mogu izravno koristiti u VBA. Evo primjera.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) End Sub
Vidite, ovdje smo koristili funkciju LEN bez korištenja objekta Application.WorksheetFunction.
Slično možete koristiti i druge funkcije poput lijeve, desne, sredine, znaka itd.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) Debug.Print (lijevo (strng, 2)) Debug.Print (desno (strng, 1)) Debug.Print (sredina (strng, 3, 2)) Kraj Sub
Kada pokrenete gornju podmeni, vratit će se:
5 On će ll
Pa da, dečki, ovako možete koristiti funkciju radnog lista programa Excel u VBA. Nadam se da sam vam dovoljno objasnio i da vam je ovaj članak pomogao. Ako imate pitanja u vezi ovog članka ili bilo čega drugog vezanog uz VBA, pitajte u odjeljku komentara ispod. Do tada možete pročitati o drugim srodnim temama u nastavku.
Što je CSng funkcija u Excelu VBA | Funkcija SCng je VBA funkcija koja pretvara bilo koji tip podataka u broj s plutajućom zarezom jedne preciznosti ("s obzirom da je to broj"). Uglavnom koristim funkciju CSng za pretvaranje tekstualno oblikovanih brojeva u stvarne.
Kako dobiti obrnuti tekst i broj putem VBA u Microsoft Excelu | Za obrnuti broj i tekst koristimo petlje i srednju funkciju u VBA. 1234 će se pretvoriti u 4321, "ti" će se pretvoriti u "uoy". Evo isječka.
Oblikujte podatke prilagođenim formatima brojeva pomoću VBA u programu Microsoft Excel | Za promjenu formata broja određenih stupaca u Excelu upotrijebite ovaj isječak VBA. Pokriva format broja navedenog u određenom formatu jednim klikom.
Korištenje događaja promjene radnog lista za pokretanje makronaredbi kada se izvrši bilo kakva promjena | Dakle, za pokretanje vašeg makronaredbe kad god se list ažurira, koristimo VBA događaje na radnom listu.
Pokrenite makronaredbu ako dođe do promjene na listu u navedenom rasponu | Za pokretanje vašeg makro koda kad se promijeni vrijednost u navedenom rasponu, upotrijebite ovaj VBA kôd. Otkriva svaku promjenu napravljenu u navedenom rasponu i aktivirat će događaj.
Najjednostavniji VBA kôd za označavanje trenutnog retka i stupca pomoću | Pomoću ovog malog isječka VBA označite trenutačni redak i stupac lista.
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Funkcija VLOOKUP u Excelu | Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
COUNTIF u Excelu 2016 | Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu | Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.