Funcțiile precum VLOOKUP, COUNTIF, SUMIF sunt numite funcții ale foii de lucru. În general, funcțiile care sunt predefinite în Excel și gata de utilizare pe o foaie de lucru sunt funcții de foaie de lucru. Nu puteți modifica sau vedea codul din spatele acestor funcții în VBA.
Pe de altă parte, funcțiile definite de utilizator și funcțiile specifice VBA, cum ar fi MsgBox sau InputBox, sunt funcții VBA.
Știm cu toții cum să folosim funcțiile VBA în VBA. Dar dacă vrem să folosim VLOOKUP într-un VBA. Cum facem asta? În acest articol, vom explora exact asta.
Utilizarea funcțiilor Foaie de lucru în VBA
Pentru a accesa funcția de foaie de lucru folosim o clasă de aplicații. Aproape toate funcțiile foii de lucru sunt listate în clasa Application.WorksheetFunction. Și folosind operatorul punct, le puteți accesa pe toate.
În orice sub, scrieți Application.WorksheetFunction. Și începeți să scrieți numele funcției. Sensul inteligen al VBA va arăta numele funcțiilor disponibile pentru a fi utilizate. După ce ați ales numele funcției, acesta va cere variabilele, ca orice funcție pe Excel. Dar va trebui să treceți variabile în format inteligibil VBA. De exemplu, dacă doriți să treceți un interval A1: A10, va trebui să-l treceți ca obiect de interval, cum ar fi Range („A1: A10”).
Deci, să folosim câteva funcții ale foii de lucru pentru a o înțelege mai bine.
Cum se folosește funcția VLOOKUP în VBA
Pentru a demonstra cum puteți utiliza o funcție VLOOKUP în VBA, aici am exemple de date. Trebuie să arăt numele și orașul codului de autentificare dat într-o casetă de mesaje folosind VBA. Datele sunt răspândite în intervalul A1: K26.
Apăsați ALT + F11 pentru a deschide VBE și a introduce un modul.
Vezi codul de mai jos.
Sub WsFuncitons () Dim login ID As String Dim name, city As String loginID = "AHKJ_1-3357042451" 'Utilizarea funcției VLOOKUP pentru a obține numele id-ului dat în numele tabelului = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26" ), 2, 0) 'Folosind funcția VLOOKUP pentru a obține orașul idului dat în tabelul oraș = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & "City:" & city) End Sub
Când rulați acest cod, veți obține acest rezultat.
Puteți vedea cât de repede VBA imprimă rezultatul într-o casetă de mesaj. Acum să examinăm codul.
Cum functioneazã?
1.
Dim login ID ca șir
Nume slab, oraș As String
Mai întâi am declarat două variabile de tip șir pentru a stoca rezultatul returnat de funcția VLOOKUP. Am folosit variabile de tip șir, deoarece sunt sigur că rezultatul returnat de VLOOKUP va fi o valoare șir. Dacă funcția de foaie de lucru este de așteptat să returneze numărul, data, intervalul, etc. tipul valorii, utilizați acel tip de variabilă pentru a stoca rezultatul. Dacă nu sunteți sigur, ce fel de valoare va fi returnată de funcția de foaie de lucru, utilizați variabile de tip variantă.
2.
loginID = "AHKJ_1-3357042451"
Apoi am folosit variabila loginID pentru a stoca valoarea de căutare. Aici am folosit o valoare codificată. Puteți utiliza și referințe. De exemplu. Puteți utiliza Range („A2”). Valoare pentru a actualiza dinamic valoarea de căutare din intervalul A2.
3.
name = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 2, 0)
Aici folosim funcția VLOOKUP pentru a obține. Acum, când corectați funcția și deschideți parantezele, aceasta vă va arăta argumentele necesare, dar nu la fel de descriptiv pe cât arată pe Excel. Convinge-te singur.
Trebuie să vă amintiți cum și ce variabilă trebuie să utilizați. Puteți reveni oricând la foaia de lucru pentru a vedea detaliile variabilei descriptive.
Aici, valoarea de căutare este Arg1. Pentru Arg1 folosim loginID. Tabelul de căutare este Arg2. Pentru Arg2 am folosit Range („A1: K26”). Rețineți că nu am folosit direct A2: K26 așa cum facem pe Excel. Indexul coloanelor este Arg3. Pentru Arg3 am folosit 2, deoarece numele se află în a doua coloană. Tipul de căutare este Arg4. Am folosit 0 ca Arg4.
city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0)
În mod similar, obținem numele orașului.
4.
MsgBox ("Nume:" & nume & vbLf & "Oraș:" & oraș)
În cele din urmă, imprimăm numele și orașul folosind Messagebox.
De ce să folosiți funcția Foaie de lucru în VBA?
Funcțiile foii de lucru posedă puterea unor calcule imense și nu va fi inteligent să ignori puterea funcțiilor foii de lucru. De exemplu, dacă dorim abaterea standard a unui set de date și doriți să scrieți cod întreg pentru aceasta, vă poate dura câteva ore. Dar dacă știi cum să folosești funcția de foaie de lucru STDEV.P în VBA pentru a obține calculul dintr-o singură dată.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Range ("A1: K26")) End Sub
Folosirea mai multor funcții Foaie de lucru VBA
Să presupunem că trebuie să folosim o potrivire a indexului pentru a prelua unele valori. Acum, cum ați scrie formula în VBA. Iată ce cred că veți scrie:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
Acest lucru nu este greșit, dar este lung. Modul corect de a utiliza mai multe funcții este utilizarea unui bloc With. Vedeți exemplul de mai jos:
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
După cum puteți vedea, am folosit blocul With pentru a spune VBA că voi folosi proprietățile și funcțiile Application.WorksheetFunction. Deci nu trebuie să-l definesc peste tot. Tocmai am folosit operatorul punct pentru a accesa funcțiile INDEX, MATCH, VLOOKUP și STDEV.P. După ce folosim instrucțiunea End With, nu putem accesa funcții fără a folosi nume de funcții complet calificate.
Deci, dacă trebuie să utilizați mai multe funcții de foaie de lucru în VBA, utilizați cu bloc.
Nu toate funcțiile Foaie de lucru sunt disponibile prin Application.WorksheetFunction
Unele funcții ale foii de lucru sunt disponibile direct pentru a fi utilizate în VBA. Nu este nevoie să utilizați obiectul Application.WorksheetFunction.
De exemplu, funcții precum Len () care este utilizată pentru a obține numărul de caractere dintr-un șir, stânga, dreapta, mijloc, decupare, decalaj etc. Aceste funcții pot fi utilizate direct în VBA. Iată un exemplu.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) End Sub
Vedeți, aici am folosit funcția LEN fără a utiliza obiectul Application.WorksheetFunction.
În mod similar, puteți utiliza alte funcții precum stânga, dreapta, mijloc, caracter etc.
Sub GetLen () Strng = "Hello" Debug.Print (Len (strng)) Debug.Print (left (strng, 2)) Debug.Print (right (strng, 1)) Debug.Print (Mid (strng, 3, 2)) Sfârșitul Sub
Când rulați sub-ul de mai sus, acesta va reveni:
5 El o ll
Deci, băieți, așa puteți folosi funcția de foaie de lucru Excel în VBA. Sper că am fost suficient de explicativ și acest articol te-a ajutat. Dacă aveți întrebări cu privire la acest articol sau orice altceva legat de VBA, întrebați în secțiunea de comentarii de mai jos. Până atunci puteți citi mai jos despre alte subiecte conexe.
Ce este funcția CSng în Excel VBA | Funcția SCng este o funcție VBA care convertește orice tip de date într-un număr cu virgulă mobilă de o singură precizie („având în vedere că este un număr”). Folosesc mai ales funcția CSng pentru a converti numerele formatate de text în numere reale.
Cum să obțineți Text & Number în Reverse prin VBA în Microsoft Excel | Pentru a inversa numărul și textul, folosim bucle și funcția mid în VBA. 1234 va fi convertit la 4321, „tu” va fi convertit la „uoy”. Iată fragmentul.
Formatați date cu formate de număr personalizate utilizând VBA în Microsoft Excel | Pentru a modifica formatul numeric al coloanelor specifice din Excel, utilizați acest fragment VBA. Acoperă formatul numărului formatului specificat la formatul specificat într-un singur clic.
Utilizarea evenimentului de modificare a foii de lucru pentru a rula macro când se efectuează orice modificare | Deci, pentru a rula macro-ul dvs. ori de câte ori se actualizează foaia, folosim Evenimentele Foaiei de lucru ale VBA.
Executați macro dacă există modificări efectuate pe foaie în intervalul specificat | Pentru a rula codul macro atunci când se modifică valoarea dintr-un interval specificat, utilizați acest cod VBA. Acesta detectează orice modificare făcută în intervalul specificat și va declanșa evenimentul.
Cel mai simplu cod VBA pentru a evidenția rândul curent și coloana folosind | Utilizați acest mic fragment VBA pentru a evidenția rândul și coloana curentă a foii.
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Funcția VLOOKUP în Excel | Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
COUNTIF în Excel 2016 | Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.