mini-introducere in program area vba

MINI-INTRODUCERE IN PROGRAMAREA VBAde Lucian Constantin (moderator) pentru www.drexcel.ro

IntroducereToata lumea cred ca a auzit pana acum de macro-uri si functii definite de utilizator (User Defined Functions in engleza) mai ales cand este vorba de Excel. Pe parcursul articolului referirile vor fi in special la Microsoft Excel, dar majoritatea explicatiilor sunt valabile si pentru celelalte aplicatii din suita Office de la Microsoft. Poate multi s-au limitat (pentru ca le-a fost pur si simplu suficient) doar la inregistrarea unor macro-uri si eventual atasarea lor la butoane sau combinatii de taste, operatiuni prezentate chiar de DrExcel in 2 tutoriale pe aceasta tema: Cum pot sa ...Inregistez un MACRO si Cum pot sa ... Atribui un MACRO la un buton Pentru cei care vor sa descopere putin mai mult din acest instrument puternic numit si VBA (Visual Basic for Applications) pot citi acest articol adresat in primul rand incepatorilor pe taramul programarii, pentru ca cei care cunosc deja un limbaj de programare probabil vor recunoaste imediat elemente comune tuturor limbajelor de programare, iar pentru cei care vor sa beneficieze de un curs adevarat se pot inscrie la cursurile organizate de IT Learning - Curs Excel 2007 VBA

Macro (sau procedura) versus FunctiePe tema asta exista o gramada de controverse pe toate forumurile si nu as vrea sa starnesc si eu altele, dar la nivelul cel mai simplu: - un macro poate fi considerat un set de actiuni repetitive, fara a avea neaparata nevoie de transmiterea unor parametrii, acest tip de macro fiind numit si procedura rezultatul acelor actiuni fiind vizibil la nivelul modificarilor efectuate de acel macro (de ex. schimbarea culorii unor celule in Excel, . tiparirea unei foi , etc) - pe cand o functie intoarce intotdeauna un rezultat pe baza parametrilor trimisi catre acea functie (de ex. adunarea unor numere cum ar face functia SUM din Excel, sau insumarea unui produs de doua sau mai multe matrice cum este SUMPRODUCT ) Cu alte cuvinte procedura modifca in general atribute cum ar fi culoare, font sau executa actiuni cum ar fi tiparire, copiere etc., pe cand functia exeuta si ea ceva dar intoarce un rezultat. Si acum vine dilema... o procedura cu parametrii poate fi considerata functie?... conform regulilor de mai sus putem spune NU pentru ca procedura nu intoarce un rezultat cum intoarce functia, dar amblele folosesc codul VBA pe care multi il mai denumesc si macro si probabil de aici apare confuzia.MINI-INTRODUCERE IN PROGRAMAREA VBA, de Lucian Constantin, pentru http://www.drexcel.ro

Pagina 1/12

Cum arata o procedura/functieCele 2 notiuni sunt diferite nu numai din prisma functionalitatii lor dar si din modul de definire/declarare: O procedura este definita in felul urmator: Sub NumeProcedura() 'Aici vine codul vostru '. '... '. End Sub Pe cand o functie: Function NumeFunctie(Param1, Param2) 'Aici vine codul vostru '. '... '. NumeFunctie = ValoareDeReturnat End Function Deja, din acest moment diferentele par mult mai clare atat la nivelul cuvintelor cheie folosite la declararea lor: Sub in cazul procedurii (sau in engleza Subprocedure) si Function in cazul functiei cat si la modul in care se termina intotdeauna functiile se termina cu NumeFunctie = ValoareDeReturnat pentru a putea folosi valoarea respectiva in restul calculelor unde avem nevoie de acea functie. Si cum orice lucru care incepe pana la urma tot trebuie sa se termine, fie ca e procedura, fie ca e functie, se termina cu un cuvant cheie sugestiv End urmat de sub sau function in functie de cum sa facut deschiderea. Puteti avea oricate proceduri si functii doar aveti grija ca fiecare din ele sa inceapa cu Sub/Function si sa se si termine cu End Sub/Function Sub NumeProcedura1() 'Aici vine codul pentru Procedura1 End Sub Function NumeFunctie1(Param1, Param2) 'Aici vine codul pentru Functia1 End Function Sub NumeProcedura2() 'Aici vine codul pentru Procedura2 End Sub ... etc

MINI-INTRODUCERE IN PROGRAMAREA VBA, de Lucian Constantin, pentru http://www.drexcel.ro

Pagina 2/12

Cum ajung sa vad codul VBAFie ca ati inregistrat un macro cu optiunea record macro si vreti sa-l ajustati sau poate vreti sa scrieti unul de la zero sau sa creati o functie pe care Excel sau Word (sau altele care suporta acest mod de programare) nu o are definita in mod implicit, trebuie sa ajungeti in VBA Editor. Exista mai multe cai de a ajunge la el... dar cea mai simpla este prin combinatia de taste Alt+F11 valabila in toate aplicatiile suitei Office de la Microsoft. In Excel se mai poate ajunge si prin clic cu butonul din dreapta al moue-ului pe numele unei foi deja deschise si apoi alegand optiunea View code aceasta varianta deschizand pagina de cod special dedicata acelei foi.

Ce este VBA EditorVBA Editor nu este decat un alt instrument de editare a textului doar ca este specializat pe partea de editare a codului VBA, la fel cum Microsoft Word este specializat pe partea de editare texte iar Microsoft PowerPoint este specializat in editarea slide-urilor pentru prezentari.


Pagina 3/12

Pana aici nimic spectaculos, are ca toate aplicatiile de editare un meniu din care puteti alege diverse optiuni, o bara cu instrumente pentru acces rapid, pentru optiunile cel mai des folosite. Titlul ferestrei Microsoft Vissual Basic Book1 ne arata faptul ca suntem in editorul VBA al fisierului Excel intitulat Book1. Partea centrala se poate asemana cel mai bine cu ceea ce vedeti in Outlook... adica pe partea stanga sus avem exploratorul proiectului (Project Explorer) in care se vad obiectele din proiectul nostru, la fel cum in Outlook vedeti elementele din folderul personal. Mai jos, tot pe partea stanga este fereastra de proprietati a obiectului selectat in Project Explorer (pentru asta nu am echivalent Outlook), si unde puteti configura de mana anumite proprietati ale obiectelor respective. Si in final, partea centrala, acum doar o pata gri unde se vor deschide paginile in care veti scrie codul propriu-zis (echivalentul Outlook-ului pentru partea de vizualizare mesaje).

Unde scriu codul VBAPai cum spuneam si mai devreme partea centrala este si cea mai importanta pentru ca acolo vom scrie... iar ca sa deschidem o foaie de scris trebuie sa facem un dublu clic pe unul din obiectele din Project Explorer aflate sub nodul Microsoft Excel Objects. Pai o sa-mi spuneti bine, bine dar sunt 2 obiecte acolo, pe care il aleg? Raspunsul este relativ simplu daca raspundeti la urmatoarea intrebare: Unde trebuie sa fie disponibil codul meu? doar in Foaia1 (Sheet1) sau in toata cartea Excel (ThisWorkbook) Ca o paranteza, in poza se vede doar Sheet1 si ThisWorkbook pentru ca dupa instalare eu am configurat Excel sa genereze doar o singura foaie pentru fiecare workbook, cei care aveti setarile implicite veti avea 3 foi (deci inca doua in plus fata de mine) si automat vor fi si aceastea vizibile in Project Explorer ca Sheet2 si Sheet3. IMPORTANT: Cand este vorba de o functie aceasta nu va fi scrisa nici un Sheet1...N si nici in ThisWorkbook ci NUMAI intr-un modul separat si in care putei grupa mai multe functii si proceduri. Si din acest motiv mai putem avea o deosebire intre o functie si o procedura: procedura poate sta oriunde (intr-un Sheet, ThisWorkbook,sau un module) pe cand functiile nu pot sta decat in module.

Ce este un modulUn modul nu este altceva decat un alt obiect din proiectul nostru VBA si care ajuta la organizarea mai buna a functiilor si procedurilor pe care le vom scrie. Astfel putem avea un modul de lucru cu fisiere externe, pentru import date sau export date atunci e bine sa denumim acel modul in asa fel incat sa fie reprezentativ pentru ceea ce am adunat acolo de ex. ModulLucruFisiereExterne, alt modul pentru diverse functii matematice pe care l-am putea denumi ModulFunctiiMatematice, etc. Cum se adauga un modul la un proiect?... Exista 2 variante il cream noi acum sau importam unul gata facut. Daca il cream noi acum se face clic cu butonul din dreapta al mouseului pe oricare din obiectele actuale din proiect si se selecteaza optiunea Insert \ Module


Pagina 4/12

Va apare in cadrul proiectului inca un nod intitulat Modules la care va fi adaugat un modul intitulat Module1

Pentru a-i da acestui modul un nume relevant selectati in fereastra Project explorer obiectul Module1 si apoi modificati campul Name din fereastra Properties cu un nume sugestiv in acest exemplu l-am denumit modIntroducereVBA, iar dupa ce apasati Enter va apare numele dat si in Project Explorer Cand aveti scrise procedurile si functiile voastre intr-un astfel de modul, pentru a-l salva in ideea ca veti avea nevoie de el si in alta carte Excel, sau poate vreti sa i-l dati unui coleg, acesta se poate exporta facand click cu butonul din dreapta al mouse-ului pe modulul vostru si selectati optiunea Export file si astfel codul vostru va fi salvat intr-un fisier cu numele modulului si extensia BAS. Pentru importul in alt fisier excel doar faceti clic cu butonul din dreapta al mouse-ului pe oricare obiect din Project explorer si selectati optiunea Import file selectand fisierul BAS dorit.


Pagina 5/12

Inainte de a scrie primul exempluPentru ca acesta nu este un curs efectiv de programare, nu o sa povestesc prea mult despre ce inseamna obiecte, colectii, tipuri de date, structuri de control, etc. dar totusi macar o mica trecere in revista este necesara. Ce este un obiect... in ultima vreme nu se poate vorbi despre programare daca nu stim ce este un obiect... accesta, prin analogie cu obiectele reale are anumite: - proprietati sau atribute ca de ex. culoare, dimensiuni; - metode asociate de genul activeaza (activate) sau dezactiveaza (deactivate)sau mai exact anumite actiuni pe care obiectul stie sa le execute; - reactioneaza la anumite evenimente, cum ar fi click de mouse sau apasarea unei taste Mai multe obiecte la un loc formeaza colectii care pot fi controlate prin proprietati comune sau metode/evenimente stiute de intrgul grup. Pentru mai multe detalii vedeti articoul de pe site-ul Microsoft: Prezentarea macrocomenzilor particularizate in Excel, o introducere realizata de Curtis Frye. Ca tipuri de date cele mai folosite sunt cele de tip obiect (object), text (IT Learning) si numeric (integer, long) Ca structuri de control cele mai folosite sunt For... Next pentru bucle, If... Then... Else pentru testarea unor conditii simple sau Select... Case pentru testarea unor conditii complexe Pentru cei care vor mai multe informatii si vor sa invete singuri, cel mai bun punct de pornire este site-ul MSDN Microsoft (din pacate disponibil numai in limba engleza) - Visual Basic Reference iar pentru cei care prefera varianta cu indrumator inca o data va recomand cursul Excel 2007 VBA organizat de IT Learning.

Un prim exemplu (procedura sub)Si daca nu v-am plictisit pana acum, sa trecem si la fapte primul vostru cod VBA scris de mana obisnuitul Hello word piatra de temelie in orice limbaj de programare asa ca faceti click dublu pe ThisWorkbook si incepeti sa scrieti: Sub HelloWorld() MsgBox ("Hello World!") End Sub Pentru a executa codul de mai sus, va puteti pozitiona oriunde pe zona procedurii (de la primaul rand de unde incepe functia Sub... pana la ultimul unde se termina cu End Sub) si apasati F5 sau butonul play din bara de instrumente intitulat Run Sub/UserForm(F5)


Pagina 6/12

Ok... ce am facut aici?... pai in primul rand ati creat o procedura intitulata HelloWorld() care nu are nevoie de niciun parametru (asta inseamna parantezele () pentru ca daca ar fi avut parametrii se scriau intre paranteze). Aceasta procedura apeleaza functia MsgBox cu parametrul text Hello World! care va afiseaza o caseta de dialog in care va pune textul trimis ca parametru si asteapta sa confirmati/inchideti apasand butonul OK. In timp ce scriati exercitiul, ati observat puterea editorului VBA in momentul in care ati deschis paranteza dupa MsgBox:

Imediat v-a aparut denumirea functiei si parametrii pe care aceasta ii poate primi pentru a obtine efectul dorit aceasta facilitate poarta denumirea de IntelliSense si va ajuta exact ca si in Excel sa completati functia cu parametrii corecti fara sa fie nevoie sa memorati variantele posibile pentru fiecare functie. Acelasi mod inteligent va va ajuta si la completarea obiectelor cu atributele si metodele cunoscute de acel obiect de aceasta data inteligenta va apare dupa ce puneti un punct dupa numele obiectului puteti exersa cu obiectul Range pus in loc de MsgBox iar cand veti pune . (punct dupa cuvantul Range veti vedea ce stie acest obiect.


Pagina 7/12

Exemplul 2 (functie)Cum spuneam si mai devreme functiile nu pot sta decat intr-un modul asa ca daca nu ati creat un modul ca in capitolul Ce este un modul revedeti acel capitol si creati-l acum pentru ca altfel functia nu o sa fie utilizabila in foaia voastra Excel. Pentru ca functia prin definitie trebuie sa intoarca un rezultat, vom scrie o functie care aduna 2 numere a si b trimise ca parametrii, ca si cum nu am cunoaste functia SUM din Excel: Function AdunaNumere(ByVal a As Long, ByVal b As Long) Dim c As Long c = a + b AdunaNumere = c End Function Pentru a complica putin functia, am folosit o variabila intermediara c pentru a vedea si cum se declara variabilele cu Dim inainte de a fi folosite si ar fi bine daca v-ati face un obicei din declararea variabilelor pentru a ajuta VBA sa se miste ceva mai repede (chiar daca la o astfel de functie efectul nu este vizibil) Pentru a testa functia, lasati deschis editorul VBA si reveniti in foaia de calcul din Excel. Puneti in celula A1 valoarea 2 si in celula B1 valoarea 4 apoi in C1 scrieti =AdunaNumere(A1,B1). Daca ati facut totul corect pana aici ar trebui ca in celula C1 sa aveti valoarea 6.

In plus cred ca ati observat ca acum functia voastra apare printre functiile Excel pe masura ce tastati numele functiei. Asa cum apare in fereastra de mai sus, dupa ce ati tastat primele litere =Ad, pentru a selecta mai repede numele functiei voastre trebuie doar apasati sageata jos o singura data, apoi apasati TAB si veti avea completat automat =AdunaNumere( doar mai trebuie sa-i puneti parametrii si sa inchideti paranteza si gata. Pentru ca Excel nu va mai arata pentru functiile personale acel tool tips cu lista de parametrii si optiunile posibile, in situatia in care nu mai stiti parametrii functiei voastre, Alexx, un alt moderator al forumului DrExcel.ro, recomanda sa apasati Ctrl+Shift+A dupa ce ati ajuns sa scrieti =AdunaNumere( iar Excel va completa functia voastra automat cu parametrii corespunzatori: =AdunaNumere(a,b) astaptand sa-i spuneti voi cine este a si cine este b, adica A1 respectiv B1 cum am facut si in exemplul de mai sus.


Pagina 8/12

Inainte de incheiereAr mai fi multe de povestit, doar ca atunci nu ar mai fi o mini-introducere ci un curs complet, si nu acesta este obiectul acestei prezentari. Dar totusi inainte a a incheia va mai dau cateva mici sfaturi general valabile. 1. Divide et Impera sau cu alte cuvinte cu cat este mai putin cod cu atat se vede mai bine. Dupa cum ati vazut scrierea codului nu este o problema foarte mare atata timp cat stiti ce vreti sa faceti, totul este sa aveti suficienta rabdare si sa incercati dezvoltati codul pe bucatele cat mai mici cand aveti de scris o pagina de cod VBA la un moment dat veti fi intr-un impas nestiind cum va reactiona o anumita functie mergeti sub procedura/functia voastra si creati o procedura/functie unde sa testati doar acea bucatica de cod care va nedumireste pana obtineti rezultatul dorit apoi continuati in codul mare. Dusa la extrem aceasta sugestie ar inseamna si faptul ca ar fi bine sa folositi functii mai scurte si specializate pentru diverse lucruri apoi aceste bucati mai mici sa le apelati in corpul functiilor mai mari Cu alte cuvinte, daca aveti de calculat intro procedura, suma a 2 numere X si Y folosindu-va de functia AdunaNumere codul vostru ar putea fi ceva de genul: Sub ApelFunctieProprie() Dim X, Y, Z As Integer X = 10 Y = 15 Z = AdunaNumere(X, Y) MsgBox ("Valoarea lui Z = " & Z) End Sub Function AdunaNumere(ByVal a As Long, ByVal b As Long) Dim c As Long c = a + b AdunaNumere = c End Function 2. Fiti ordonati adica incercati pe cat posibil sa grupati procedurile si functiile pe module relevante pentru scopul pentru care au fost scrise asa va puteti crea propriile biblioteci de functii pe care sa le puteti importa/exporta cat mai usor pentru a le folosi si in alte proiecte ale voastre 3. Definiti variabilele inainte de a le utiliza - Incercati pe cat posibil sa declarati variabilele cu declaratia Dim inainte de a le folosi pentru a face coldul VBA ceva mai clar (stiti de la inceput ce tip va fi variabila declarata) si pentru a va forta sa faceti acest lucru folositi inainte de prima procedura sau functie din modulul vostru declaratia: Option Explicit Sub NumeProcedura1() 'Aici vine codul pentru Procedura1 End SubMINI-INTRODUCERE IN PROGRAMAREA VBA, de Lucian Constantin, pentru http://www.drexcel.ro

Pagina 9/12

Poate acest lucru o sa va fie mai greu la inceput pentru ca la orice variabila nedeclarata VBA va da o eroare: Compile error: Variable not defined Pentru testarea functionalitatii unor proceduri/functii scurte puteti folosi un modul separat de testare dar care sa nu aibe aceasta optiune pentru declararea explicita a variabilelor ca sa nu stati sa scrieti prea mult, dar pentru cele finale este bine sa o aveti. 4. Proceduri/Functii general valabile - Pentru codul VBA pe care doriti sa-l faceti disponibil in orice foaie Excel fara sa fie nevoie sa importati modulele voastre in fiecare carte Excel in parte aveti posibilitatea sa le puneti intr-o foaie dedicata acestui depozit de cod numit Personal.xls in Excel 2003 si Personal.XLSB in Excel 2007. Cum ajungeti sa scrieti cod in acea foaie speciala?... Exista 2 variante: - Varianta 1: Inregistrati un macro cat de mic iar cand va intreaba unde vreti sa-l puneti selectati Personal Macro Workbook

- Varianta 2: Creati voi fisierul Personal.XLS(B): Daca aveti Windows XP in folderul: C:\Documents and Settings\NumeUtilizator\Application Data\Microsoft\Excel\XLSTART Daca aveti Windows 7 in folderul: C:\Users\NumeUtilizator\AppData\Roaming\Microsoft\Excel\XLSTART Astfel, cand deschideti editorul VBA, pe langa proiectul cu numele cartii voastre, veti mai gasi un proiect VBAProject (PERSONAL.XLSB) si pe care ar fi bine sa-l redenumiti ca Personal (PERSONAL.XLSB) Redenumirea se face exact ca si in cazul modulelor se face clic pe numele obiectului VBAProject (PERSONAL.XLSB) iar in fereastra de proprietati tastati numele nou Personal asa cum se vede si in poza urmatoare:MINI-INTRODUCERE IN PROGRAMAREA VBA, de Lucian Constantin, pentru http://www.drexcel.ro

Pagina 10/12

In plus si modulele din proiectul personal ar trebui redenumite pentru a fi relevante pentru grupul de functii pe care le va adaposti. Dupa aceste operatiuni, functia de AdunaNumere o veti putea folosi in foaia Excel numai daca o prefixati si cu numele foii Personal.XLS(B): =PERSONAL.XLSB!adunanumere(A1,B1) Iar daca vrei sa apelati procedurile/functiile in VBA va trebu sa adaugati o referinta catre proiectul Personal din meniul Toos/References si bifati proiectul Personal


Pagina 11/12

Dupa ce ati facut referinta ca mai sus daca aveti de exemplu procedura HelloWorld din exemplul de la inceput pusa intr-un modul din Personal.xlsb puteti sa o apelati in modulul VBA din cartea voastra si astfel: Sub ApelInPersonal() HelloWorld End Sub Ia la apasarea tastei F5 pentru executarea acestei proceduri veti vedea aceeasi casuta de dialog cu Hello World! ca si in exemplul initial.

Acestea fiind spuse nu pot de cat sa va urez spor la treaba!

ResurseAici aveti cateva resurse generale direct pe de site-ul Microsoft 1. MSDN Excel Object Model Overview 2. MSDN Visual Basic Programming Guide 3. MSDN Visual Basic Language Features Dar sa nu uitam nici locul unde puteti gasi ajutorul de care aveti nevoie pentru lamurirea problemelor suplimentare 4. http://www.drexcel.ro/ 5. http://www.officespecialist.ro


Pagina 12/12