În acest tutorial vom afla despre funcția Excel VBA
1) Ce este Visual Basic în Excel?
2) Cum se utilizează VBA în Excel?
3) Cum se creează funcția definită de utilizator?
4) Cum se scrie Macro?
Cum se scrie codul VBA
Excel oferă utilizatorului o colecție mare de funcții gata făcute, mai mult decât suficient pentru a satisface utilizatorul mediu. Se pot adăuga multe altele prin instalarea diferitelor programe de completare disponibile. Cele mai multe calcule pot fi realizate cu ceea ce este furnizat, dar nu mai trece mult timp până când vă doriți să existe o funcție care să îndeplinească o anumită treabă și să nu găsiți nimic potrivit în listă. Ai nevoie de un UDF. Un UDF (User Defined Function) este pur și simplu o funcție pe care o creați cu VBA. UDF-urile sunt deseori numite „Funcții personalizate”. Un UDF poate rămâne într-un modul de cod atașat unui registru de lucru, caz în care acesta va fi întotdeauna disponibil atunci când acel registru de lucru este deschis. Alternativ, vă puteți crea propriul program de completare care conține una sau mai multe funcții pe care le puteți instala în Excel la fel ca un program de completare comercial. UDF-urile pot fi accesate și prin module de cod. Adesea UDF-urile sunt create de dezvoltatori pentru a funcționa exclusiv în codul unei proceduri VBA și utilizatorul nu este niciodată conștient de existența lor. La fel ca orice funcție, UDF poate fi la fel de simplu sau complex pe cât doriți. Să începem cu unul ușor …
O funcție pentru a calcula aria unui dreptunghi
Da, știu că ai putea face asta în capul tău! Conceptul este foarte simplu, astfel încât să vă puteți concentra asupra tehnicii. Să presupunem că aveți nevoie de o funcție pentru a calcula aria unui dreptunghi. Căutați în colecția de funcții Excel, dar nu există una potrivită. Acesta este calculul care trebuie făcut:
ZONA = LUNGIME x LĂȚIME
Deschideți un nou registru de lucru și apoi deschideți Editorul Visual Basic (Instrumente> Macro> Editor Visual Basic sau ALT + F11).
Veți avea nevoie de un modul în care să vă scrieți funcția, așa că alegeți Insert> Module. În tipul de modul gol: Zona funcțională și apăsați INTRODUCE.Editorul Visual Basic completează linia pentru dvs. și adaugă o linie de funcție finală ca și cum ați fi creat un subrutină. Până acum arată așa …
Zona de funcții () Funcția de sfârșit
Plasați cursorul între paranteze după „Zona”. Dacă te-ai întrebat vreodată la ce servesc parantezele, ești pe cale să afli! Vom specifica „argumentele” pe care le va lua funcția noastră (an argument este o informație necesară pentru a face calculul). Tip Lungime ca dublă, Lățime ca dublă și faceți clic pe linia goală de dedesubt. Rețineți că, pe măsură ce tastați, apare o casetă de derulare care afișează toate lucrurile adecvate pentru ceea ce tastați.
Această caracteristică se numește Listează automat membrii. Dacă nu apare, este oprit (porniți-l la Instrumente> Opțiuni> Editor) sau s-ar putea să fi făcut o eroare de tastare mai devreme. Este o verificare foarte utilă a sintaxei. Găsiți elementul de care aveți nevoie și faceți dublu clic pe el pentru a-l insera în codul dvs. Puteți să o ignorați și să tastați doar dacă doriți. Codul dvs. arată acum așa …
Zona de funcții (lungime ca dublă, lățime ca dublă) Funcție de sfârșit
Declararea tipului de date al argumentelor nu este obligatorie, dar are sens. Ai fi putut tasta Lungime, lățime și l-am lăsat așa, dar avertizarea Excel asupra tipului de date de așteptat ajută codul să ruleze mai repede și să preia erorile de intrare. The dubla tipul de date se referă la număr (care poate fi foarte mare) și permite fracții. Acum, pentru calculul în sine. În linia goală apăsați mai întâi TAB tasta pentru a vă indenta codul (ușurând citirea) și tastați Suprafață = lungime * lățime. Iată codul completat …
Zona de funcții (Lungime ca dublă, Lățime ca dublă) Zona = Lungime * Lățime Funcție de sfârșit
Veți observa că o altă funcție de ajutor a Editorului Visual Basic apare în timp ce tastați, Informații rapide automate…
Nu este relevant aici. Scopul său este de a vă ajuta să scrieți funcții în VBA, spunându-vă ce argumente sunt necesare. Vă puteți testa funcția imediat. Treceți la fereastra Excel și introduceți cifrele pentru Lungime și Lățime în celule separate. Într-o a treia celulă introduceți funcția dvs. ca și cum ar fi una dintre cele încorporate. În acest exemplu, celula A1 conține lungimea (17) și celula B1 lățimea (6.5). În C1 am tastat = zona (A1, B1) iar noua funcție a calculat aria (110.5) …
Uneori, argumentele unei funcții pot fi opționale. În acest exemplu am putea face Lăţime argument opțional. Să presupunem că dreptunghiul se întâmplă să fie un pătrat cu lungimea și lățimea egale. Pentru a salva utilizatorul care trebuie să introducă două argumente, i-am putea lăsa să introducă doar lungimea și să aibă funcția să folosească acea valoare de două ori (adică să se înmulțească lungimea x lungimea). Deci, funcția știe când poate face acest lucru, trebuie să includem un Declarație IF pentru a-l ajuta să decidă. Schimbați codul astfel încât să arate astfel …
Zonă funcțională (lungime dublă, lățime opțională ca variantă) Dacă lipsește (lățime), atunci zonă = lungime * lungime altă zonă = lungime * lățime sfârșit dacă funcție finală
Rețineți că tipul de date pentru Lățime a fost schimbat în Variantă pentru a permite valori nule. Funcția permite acum utilizatorului să introducă doar un argument de ex. = zona (A1)Declarația IF din funcție verifică dacă argumentul Lățime a fost furnizat și calculează în consecință …
O funcție pentru calcularea consumului de combustibil
Îmi place să verific verificarea consumului de combustibil al mașinii mele, așa că atunci când cumpăr combustibil, notez kilometrajul și cantitatea de combustibil necesară pentru a umple rezervorul. Aici în Marea Britanie combustibilul se vinde în litri. Milometrul mașinii (OK, deci este un kilometru) înregistrează distanța în mile. Și pentru că sunt prea bătrân și prost pentru a mă schimba, înțeleg doar MPG (mile pe galon). Acum, dacă crezi că totul este cam trist, ce zici de asta. Când ajung acasă deschid Excel și introduc datele într-o foaie de lucru care calculează MPG pentru mine și prezintă performanțele mașinii. Calculul este numărul de mile parcurse de mașină de la ultima umplere împărțit la numărul de galoane de combustibil utilizat …
MPG = (MILES ACEST UMPLERE - MILES ULTIMA UMPLERE) / GALONI DE COMBUSTIBIL
dar pentru că combustibilul vine în litri și există 4.546 litri într-un galon …
MPG = (MILES ACEST UMPLERE - MILES ULTIMA UMPLERE) / LITRI DE COMBUSTIBIL x 4.546
Iată cum am scris funcția …
Funcție MPG (StartMiles ca întreg, FinishMiles ca întreg, litri ca singuri) MPG = (FinishMiles - StartMiles) / Litri * 4.546 Funcție finală
și iată cum arată în foaia de lucru …
Nu toate funcțiile efectuează calcule matematice. Iată una care oferă informații …
O funcție care dă numele zilei
Sunt adesea întrebat dacă există o funcție de dată care oferă ziua săptămânii ca text (de exemplu, luni). Răspunsul este nu *, dar este destul de ușor să creați unul. (* Addendum: Am spus nu? Verificați nota de mai jos pentru a vedea funcția pe care am uitat-o!). Excel are funcția WEEKDAY, care returnează ziua săptămânii ca număr de la 1 la 7. Trebuie să alegeți care este ziua 1 dacă nu vă place valoarea implicită (duminică). În exemplul de mai jos funcția returnează „5” despre care se întâmplă să știu că înseamnă „joi”.
Dar nu vreau să văd un număr, vreau să văd „joi”. Aș putea modifica calculul adăugând o funcție VLOOKUP care se referea la un tabel undeva care conține o listă de numere și o listă corespunzătoare de nume de zile. Sau aș putea avea întregul lucru autonom cu mai multe instrucțiuni IF imbricate. Prea complicat! Răspunsul este o funcție personalizată …
Funcție DayName (InputDate as Date) Dim DayNumber As Integer DayNumber = Weekday (InputDate, vbSunday) Selectați cazul DayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" Case 3 DayName = "Tuesday" Case 4 DayName = "Wednesday" Cazul 5 DayName = "Joi" Cazul 6 DayName = "Vineri" Cazul 7 DayName = "Sâmbătă" Sfârșit Selectați funcția Sfârșit
Mi-am numit funcția „DayName” și este nevoie de un singur argument, pe care îl numesc „InputDate”, care (desigur) trebuie să fie o dată. Iată cum funcționează…
- Prima linie a funcției declară o variabilă pe care am numit-o "DayNumber", care va fi un număr întreg (adică un număr întreg).
- Următoarea linie a funcției atribuie o valoare acelei variabile utilizând funcția WEEKDAY din Excel. Valoarea va fi un număr între 1 și 7. Deși valoarea implicită este 1 = duminică, am inclus-o oricum pentru claritate.
- În cele din urmă a Declarație de caz examinează valoarea variabilei și returnează fragmentul de text corespunzător.
Iată cum arată în foaia de lucru …
Accesarea funcțiilor dvs. personalizate
Dacă un registru de lucru are atașat un modul de cod VBA care conține funcții personalizate, acele funcții pot fi ușor abordate în același registru de lucru așa cum este demonstrat în exemplele de mai sus. Utilizați numele funcției ca și cum ar fi una dintre funcțiile încorporate Excel.
Puteți găsi, de asemenea, funcțiile listate în Expertul cu funcții (numit uneori instrumentul Funcție lipire). Utilizați expertul pentru a insera o funcție în mod normal (Inserare> Funcție).
Derulați în jos lista categoriilor de funcții pentru a găsi Definit de utilizator și selectați-l pentru a vedea o listă a UDF-urilor disponibile …
Puteți vedea că funcțiilor definite de utilizator le lipsește orice altă descriere în afară de mesajul inutil „Nu există ajutor disponibil”, dar puteți adăuga o scurtă descriere …
Asigurați-vă că vă aflați în registrul de lucru care conține funcțiile. Mergi la Instrumente> Macro> Macro. Nu veți vedea funcțiile dvs. listate aici, dar Excel le știe! În Nume macro din partea de sus a casetei de dialog, tastați numele funcției, apoi faceți clic pe caseta de dialog Opțiuni buton. Dacă butonul este gri, fie ați scris greșit numele funcției, fie vă aflați într-un registru greșit, fie nu există! Aceasta deschide un alt dialog în care puteți introduce o scurtă descriere a funcției. Clic Bine pentru a salva descrierea și (aici este bitul confuz) faceți clic Anulare pentru a închide caseta de dialog Macro. Nu uitați să salvați registrul de lucru care conține funcția. Data viitoare când mergeți la Expertul cu funcții, UDF-ul dvs. va avea o descriere …
La fel ca macro-urile, funcțiile definite de utilizator pot fi utilizate în orice alt registru de lucru, atâta timp cât registrul de lucru care le conține este deschis. Cu toate acestea, nu este o bună practică să faceți acest lucru. Introducerea funcției într-un registru de lucru diferit nu este simplă. Trebuie să adăugați numele registrului de lucru al gazdei la numele funcției. Acest lucru nu este dificil dacă vă bazați pe Expertul cu funcții, ci neîndemânatic să scrieți manual. Expertul cu funcții afișează numele complete ale oricăror UDF din alte registre de lucru …
Dacă deschideți registrul de lucru în care ați utilizat funcția într-un moment în care registrul de lucru care conține funcția este închis, veți vedea un mesaj de eroare în celula în care ați utilizat funcția. Excel a uitat de asta! Deschideți registrul de lucru al funcției, recalculați și totul este din nou în regulă. Din fericire există o modalitate mai bună.
Dacă doriți să scrieți funcții definite de utilizator pentru utilizare în mai multe cărți de lucru, cea mai bună metodă este să creați un Excel Adauga la. Aflați cum puteți face acest lucru în tutorialul Construiți un program de completare Excel.
Addendum
Chiar ar trebui să știu mai bine! Niciodată, niciodată, nu spune niciodată! După ce ți-am spus că nu există o funcție care să ofere numele zilei, acum mi-am amintit de cea care poate. Uită-te la acest exemplu …
Funcția TEXT returnează valoarea unei celule ca text într-un format de număr specific. Deci, în exemplu aș fi putut alege = TEXT (A1, „ddd”) pentru a returna "Joi", = TEXT (A1, „mmmm”) pentru a returna „septembrie” etc. Ajutorul Excel conține câteva exemple de modalități de utilizare a acestei funcții.
Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook.
Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrie-ne la site de e-mail
