Suport Curs Excel Avansat

@2009 SC INTERCONSULT 2001 SRL Suport curs EXCEL Avansat (nov.09)

Tel/fax: 0248-262340

www.formare.ro

1/46


Tel/fax: 0248-262340

1. INTRODUCEREAcest curs i propune s v familiarizeze cu opiunile avansate ale MS EXCEL, ce includ:

definirea i editarea de macrocomenzi elemente de programare n Visual Basic for Application (VBA);

crearea de formulare;

crearea de aplicatii. Toate aceste informaii vor completa cunotinele deja acumulate privind lucrul cu foile de calcul tabelar MS Excel, astfel nct s fac din acesta un instrument puternic care s preia mare parte din sarcinile dumneavoastr de serviciu.

2. Elemente de programare n VBA pentru EXCELIn spatele MS Excel firma Microsoft a inclus un editor de comenzi VBA (Visual Basic for Applications), ce permite modificarea i scrierea de macrouri i aplicaii care s automatizeze lucrul cu foile de calcul Excel. Acest editor este de fapt un limbaj de programare de nivel nalt, utilizarea lui implicnd cunotine corespunztoare. 2.1. Bara de controale standard Bara de controale standard se afieaz pe ecran astfel: din meniul Outils se alege opiunea Personalizer; va aprea o fereastr cu trei butoane din care se alege opiunea Barres doutils i se bifeaz opiunea Commandes; Caseta de controale este ilustrat n figura de mai jos.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

Aceast bar este format din trei pri distincte i anume: prima parte este format din butoane funcionale (1, 2 i 3) utile pentru conceperea programului n VBA; a doua parte este format din butoanele 4 14, corespunztoare tipurilor de controale uzuale (obiectelor fizice din

www.formare.ro

2/46


Tel/fax: 0248-262340

program) i a treia parte este format din butonul 15 care este folosit pentru a aduga noi controale. Butonul 1 este folosit pentru intrarea/ieirea din modul de proiectare. Dac acest buton este activat, n foaia de calcul pot fi adugate noi controale (obiecte fizice). n acest caz controalele de pe foaia de calcul nu sunt active. n cazul n care butonul nu este apsat controalele sunt active, iar programul poate fi rulat. Butonul 2 este folosit pentru afiarea unui tabel cu ajutorul cruia se pot vizualiza principalele proprieti (parametrii) ale controlului activ din foaia de calcul sau chiar al foii de calcul.

Numele i tipul obiectului pentru care sunt afiate proprietile

Valoarea unui parametru(proprietate) Numele parametrului ( proprietii )

Butonul 3 este folosit pentru activarea editorului de cod VBA. Observaie: Pentru inserarea unei control pe foaia de calcul se apas pe butonul corespunztor controlului (unul dintre butoanele 4 14), dup care, cu ajutorul mous-ului, se insereaz controlul pe foaia de calcul (procedeu identic cu inserarea unei imagini). Descrierea controalelor Butonul 4 reprezint un control de tipul csu de selectare (CheckBox) i are dou stri selectat sau deselectat. Se utilizeaz atunci cnd este necesar selectarea uneia sau mai multor opiuni dintr-o list. Butonul 5 reprezint un control de tipul csu de text (TextBox) i este folosit pentru introducerea de la tastatur a unor date. Butonul 6 reprezint un control de tipul buton de comand (CommandButton) i este folosit pentru executarea unor comenzi (blocuri de instruciuni). Este unul dintre cele mai folosite

www.formare.ro

3/46


Tel/fax: 0248-262340

controale. Orice aplicaie pe care o crem poate fi executat prin apsarea unui buton de comand. Butonul 7 este un control de tipul buton radio (OptionButton ) i are dou stri selectat/neselectat. Dac pe o foaie de calcul se gsesc mai multe astfel de controale numai unul dintre ele poate s fie n starea selectat. Butonul 8 reprezint un control de tipul caset list(ListBox) i este folosit pentru afiarea unor valori selectate dintr-o list. Butonul 9 reprezint un control de tipul list derulant(ComboBox) i este folosit pentru alegerea unei valori dintr-o list derulant. Cea mai important metod a unui control ComboBox este AddItem. Metoda AddItem permite asugarea unei noi valori n lista derulant. Butonul 10 reprezint un control de tipul buton comutator (ToggleButton), care are dou stri: apsat i neapsat. Butonul 11 reprezint un control de tipul buton de incrementare/decrementare (SpinButton) i incrementeaz(crete)/decrementeaz(descrete) valoarea parametrului Value de cte ori este apsat una din cele dou sgei(n sus sau n jos). Butonul 12 reprezint un control de tipul bar derulant (ScrollBar) i poate fi folosit pentru defilarea unor controale sau foi de calcul. Aceasta bar de defilare poate s fie vertical sau orizontal. Orientarea barei de defilare se stabilete cu ajutorul parametrului Orientation; dac acest parametru are valoarea 0 atunci bara este orizontal iar dac este 1 atunci bara este vertical. Butonul 13 reprezint un control de tip etichet (Label) i de obicei este folosit pentru etichetarea altor controale. Butonul 14 este folosit pentru a insera n foaia de calcul un control de imagine (Image). Cel mai important parametru al acestui control este Picture. Acest parametru este folosit pentru a stabili ce fiier tip imagine va conine (afia) acest control. Butonul 15 (More controls) este folosit pentru a aduga noi controale care nu se gsesc n bara standard. Dup ce se apas click pe buton va aprea o list derulant din care se poate alege controlul dorit.Butonul More controls

Un control din list

www.formare.ro

4/46

Lista derulant


Tel/fax: 0248-262340

Exemplu: In continuare este ilustrat folosirea controalelor ntr-o foaie de calcul Excel. Se observ obiecte de tip: buton de comand, etichete (label), butoane de validare (check box), butoane de incrementare, list derulant etc.

www.formare.ro

5/46


Tel/fax: 0248-262340

2.2. Editorul VBA Dup inserarea controalelor n foaia de calcul, urmtorul pas l constituie scrierea codului VBA pentru fiecare control (obiect) n parte. Editorul VBA se acceseaz prin apsarea combinaiei de taste Alt + F11. Fereastra de vizualizare conine: o bar de titlu, o bar de meniuri, o bar standard i dou cadrane. n cadranul din partea stng sunt afiate foile de lucru. Dac dorim s accesm codul corespunztor unei anumite foi de lucru se execut dublu-click pe iconia foii respective. n cadranul din partea dreapt se scrie codul corespunztor foii respective.

Lista evenimentelor obiectului

Lista cu obiecte Cadranul foilor de calcul Cadran de editare cod VBA

2.3. Tipuri de date folosite n VBA Ca orice limbaj de programare i VBA folosete date i instruciuni care, structurate ntr-un anumit mod, permit execuia comenzilor dorite. Principalele tipuri de date folosite n cadrul VBA sunt prezentate n tabelul urmtor: Tip dat Integer (nr.ntreg) Operaii permise adunarea, scderea, nmulirea, mprirea, atribuirea

Descriere tip de dat numr ntreg cuprins ntre 32.768 i +32.7676/46

www.formare.ro


Tel/fax: 0248-262340

Tip dat Long Single precizie) String (ir)

Descriere Operaii permise tip de dat numr ntreg cuprins ntre adunarea, scderea, nmulirea, 2.147.483.648 i +2.147.483.647 mprirea, atribuirea (simpl tip de dat numr real (maxim 8 zecimale) Idem Tip de dat ir de caractere atribuire, concatenarea (adunarea) i trunchierea tip de dat logic i este format din operaii logice (AND, OR, etc) mulimea { TRUE (ADEVRAT) i i atribuire FALSE (FALS)} tip de dat complex tip de dat care le include pe toate celelalte

Boolean (logic)

Object (obiect)

Variant

2.4. Linii de cod, proceduri, funcii, parametri Sarcina noastr este s crem mici aplicaii (programe) prin care s automatizm o parte din lucrrile zilnice. Pentru aceasta vom insera un buton de comand (CommandButton) n foaia de lucru, cu numele Btn1. Executnd dubluclick pe acesta vom ajunge n editorul Visual Basic. Aici ncepem s crem aplicaia dorit, prin scrierea de linii de comand. Liniile de comand conin cuvinte-cheie recunoscute de limbajul de programare, iar scrierea lor trebuie s respecte sintaxa specific VBA. Dac dorim s aflm sintaxa anumitor comenzi, putem genera un mic macro care s execute aceste comenzi, dup care s-l editm i s copiem din el liniile de cod care ne intereseaz. Liniile de cod care se repet le putem grupa n proceduri (subrutine) sau funcii. Acestea se pot apela dup numele lor ori de cte ori este nevoie. Procedurile i funciile pot avea parametri. Spre exemplu putem crea o functie numit stergere care poate avea ca parametru numele foii de calcul pe care dorim s o tergem. In principal, liniile de cod vor executa anumite operaiuni asupra unor obiecte VBA.

2.5. Descrierea unui obiect Principalele elemente cu care vom lucra n cadrul VBA se numesc obiecte. Exemple de obiecte sunt: butoanele de comand, foaia de calcul, celula, rndul, coloana, etc. Obiectele VBA sunt identificate printr-un nume i se pot caracteriza cu ajutorul a trei elemente : proprieti, metode i evenimente.

www.formare.ro

7/46


Tel/fax: 0248-262340

a) Proprietile reprezint caracteristicile obiectului respectiv; exemple de propieti: culoare, tip font, lime, nlime, etc. Aceste proprieti pot fi modificate conform sintaxei : numeObiect.numeProprietate = Valoare, Observm c proprietatea unui obiect este separat de numele obiectului printr-un punct. Citirea unei linii de cod ce include o niruire de obiecte i parametri se face de la dreapta la stnga, conform modelului urmtor: WorkSheets(Foaia1).Cells(1, "a").Font.Color = vbGreen, se citete de la dreapta la stnga, astfel: culoarea fontului celulei (1,a) din foaia de calcul cu numele Foaia1 va fi de culoare verde. Observm c obiectele i proprietile acestora sunt separate ntre ele printr-un punct. In tabelul de mai jos sunt prezentate sintetic principalele proprieti ale unui obiect VBA:

Nume proprietate/parametru BackColor, Color, ColorIndex

Descriere Acesti parametri indic culoarea obiectului; de obicei culoarea este stabilit cu ajutorul tabelei de proprieti, care pune la dispoziia utilizatorului o serie de culori standard. Reprezint textul care este afiat pe obiect i este un parametru de tip String (ir de caractere). Acest parametru indic dac obiectul este activ sau nu i este de tipul Boolean (logic). Dac parametrul are valoarea True (Adevrat), atunci obiectul este activ. Dac parametrul are valoarea False (Fals) atunci obiectul este inactiv.

Caption

Enabled

Font

Acest parametru este folosit pentru stabilirea caracteristicilor fontului folosit pentru scrierea textului de pe obiect.

ForeColor

Prin acest parametru se poate stabili culoarea fontului pentru scrierea textului de pe obiect. Reprezint nlimea obiectului i este un parametru de tipul Single. Reprezint coordonata (de pe axa X) a colului stnga sus a obiectului; Acest parametru este de tipul Single. Stabilete dac obiectul este printabil sau nu i este un parametru de tip Boolean (Logic); Dac valoarea este True

Height

Left

PrintObject

www.formare.ro

8/46


Tel/fax: 0248-262340

Nume proprietate/parametru

Descriere atunci obiectul este printabil; Dac valoarea este False atunci obiectul nu este printabil (nu apare la imprimant). Reprezint coordonata (de pe axa Y) a colului stnga sus a obiectului; Acest parametru este de tipul Single. Reprezint lungimea obiectului i este un parametru de tipul Single. Stabilete dac obiectul este vizibil sau nu; Acest parametru este de tipul Boolean; Dac valoarea parametrului este True, atunci obiectul este vizibil; Dac valoarea parametrului este False, atunci obiectul nu este vizibil n foaia de calcul.

Top

Width

Visible

b) Metodele sunt proceduri sau funcii care pot s aib un numr diferit de parametri i reprezint operaiile care se pot efectua asupra unui obiect. Exemple de metode: tergerea, copierea, activarea, etc. Apelarea unei metode o vom ilustra prin cteva exemple : Exemplul 1 : Pentru acest prim exemplu vom crea un fisier excel numit ziua1.xls, iar in prima foaie de calcul inserm 2 butoane de comand. La aceste butoane vom modifica proprietatea nume in Btn1, respectiv Btn2, proprietatea Caption in Buton 1, respectiv Buton 2. Executand dubluclick pe fiecare buton in parte vom intra in partea de cod a fiecaruia si scriem urmatorul cod: Private Sub Btn1_Click() MsgBox "Ati apasat butonul 1" Btn1.Enabled = False Btn2.Enabled = True End Sub Tema: 1. Scrieti codul aferent celuilalt buton de comanda (Btn2), astfel incat la apasarea fiecaruia dintre butoane, sa se aplice metoda Enable=False pentru butonul apasat si Enable=True pentru celalalt buton de comanda. 2. Modificati din liniile de cod, cateva proprietati ale butoanelor de comanda (ex. Forecolor )www.formare.ro 9/46


Tel/fax: 0248-262340

Observaie: Metodele obiectelor pot fi doar apelate nu i modificate. c) Evenimentele sunt proceduri care pot s aib un numr diferit de parametri i

reprezint reacia obiectului la diferite aciuni ale utilizatorului; procedurile se apeleaz automat la o aciune a utilizatorului asupra unui obiect. Un eveniment trebuie declarat n prealabil, iar declararea unui eveniment se face ntr-o procedur special generat automat. Sintaxa unui eveniment este urmtoarea: Private Sub numeObiect_numeEveniment() Bloc_instructiuni End Sub Din exemplul precedent observam ca butoanelor de comanda Btn1 i Btn2 li s-a aplicat evenimentul Click(). Exemplul 2: Private Sub Worksheet_Activate() MsgBox "Ati activat Foaia3" End Sub La activarea Foii3 de calcul va fi afiat un mesaj. Operaiunea este ilustrat n figura urmtoareSelectm obiectul Selectm evenimentul

Foaia de calcul curent

www.formare.ro

10/46


Tel/fax: 0248-262340

Principalele evenimente ale unui obiect sunt sintetizate n tabelul de mai jos: Nume eveniment Activate Descriere Evenimentul este declanat la activarea unui obiect (ex. Deschiderea unei foi de calcul) Click Acest eveniment este declanat n momentul care se efectueaz un click al butonului din stnga al mouse-lui pe obiect. (Ex.apsarea unui buton de comand) DoubleClick Evenimentul este declanat n momentul n care se efectueaz un dubluclick pe obiect. MouseDown Acest eveniment este declanat n momentul n care butonul stng/drept al mous-ului este apsat iar cursorul mous-ului se afl pe obiect . MouseMove Acest eveniment este declanat n momentul n care mous-ul este micat pe obiect. MouseUp Acest eveniment este declanat n momentul n care butonul stng/drept al mous-ului nu mai este apsat iar cursorul mousului se afl pe obiect .

2.6. Foaia de calcul, proprieti, metode, evenimente Foaia de calcul este principalul obiect de lucru n programarea scripturilor Excel. Principalele proprieti ale unei foi de calcul sunt prezentate n tabelul de mai jos: Nume proprietate Name Cells(i , j) Range("celul1:celul2") Descriere Acest parametru indic numele foi respective; Acest parametru indic valoarea celulei de pe rndul i coloana j; j poate fi att de tip integer ct i string (ir de caractere) Este un parametru de tip obiect, prin care se face o referire la o zon de celule.

www.formare.ro

11/46


Tel/fax: 0248-262340

n editorul VBA proprietile foii de calcul sunt afiate automat ntr-o list derulant dup ce este scris numele foii de calcul urmat de delimitatorul punct, ca n exemplul ce urmeaz:

Proprietate a foii de calcul

Dup ce apare aceast list derulant se efectueaz dublu-click pe parametrul dorit i acesta este inserat automat. Exemplul 3: In prima foaie de calcul din fisierul creat anterior sa se insereze un buton de comanda numit BtnFoaie, avand proprietatea Caption=Nume foaie Introducem urmatoarea linie de cod MsgBox Feuil1.Name ' afieaz numele primei foi de calcul Feuil1.Name = "Prima Foaie" ' numele primei foi de calcul este schimbat cu Prima Foaie MsgBox Feuil1.Cells(2, "A")

' afieaz coninutul celulei de pe rndul 2, coloana A Feuil1.Cells(4, 2)= "100" ' Valoarea celulei de pe rndul 4, coloana 2 (adic B) devine egal cu 100 Feuil1.Range("A1:D10").Font.Italic = True ' n domeniul celulelor A1:D10, fontul devine de tip nclinat (italic) Principalele metode ale unei foi de calcul sunt prezentate n tabelul ce urmeaz: Nume metoda Activate Select Delete Descriere Realizeaz activarea unei foi de calcul; Realizeaz selectarea unei foi de calcul; (similar cu Activate) Realizeaz tergerea unei foi de calcul;

www.formare.ro

12/46


Tel/fax: 0248-262340

Nume metoda PrintOut From, To, Copies

Descriere Realizeaz printarea foii de calcul la care se face referire; Aceast metod are 3 parametri de tip numr ntreg; From indic numrul pagini de la care se ncepe printarea; To indic numrul pagini pn la care se printeaz; Copies reprezint numrul de copii printat;

Metod a foii de calcul

In editorul VBA metodele foii de calcul sunt afiate automat ntr-o list derulant, dup ce este scris numele foii de calcul urmat de delimitatorul punct. Exemple: Feuil1.Activate ' realizeaz activarea foii de calcul Feuil1 Feuil2.Delete ' realizeaz tergerea foii de calcul Feuil2 Feuil3.Move Sheets(2) ' realizeaz mutarea foii de calcul Feuil3 naintea foii de calcul Feuil2 Feuil1.PrintOut 1,2,1 ' realizeaz printarea primelor dou pagini ale foii de calcul ntr-un singur exemplar ActiveSheet `Prin intermediul aceste comenzi se lucreaza cu foaia activa Principalele evenimente ce pot fi atribuite unei foi de calcul sunt prezentate n tabelul de mai jos: Nume eveniment Activate SelectChange Descriere Ruleaza codul la activarea unei foi de calcul; Ruleaza codul la selectarea unei celule sau zone

www.formare.ro

13/46


Tel/fax: 0248-262340

2.7. Declararea variabilelor VBA Variabilele sunt date cu care opereaz calculatorul i sunt de dou tipuri: globale (sunt recunoscute n tot programul); locale (sunt recunoscute doar n interiorul unei proceduri sau funcii);

Pentru a fi recunoscut de calculator o variabil trebuie mai nti s fie declarat. Aceast declarare se face cu ajutorul cuvntului rezervat Dim. Sintaxa: Dim numeVariabil1 tipVariabil... Exemplul 4: Dim valoare as Integer Private Sub BtnFoaie_Click() valoare = Feuil1.Cells(43, "b") MsgBox valoare End Sub Observatie: Pentru a prentmpina eventualele erori de scriere a variabilelor, pe prima linie de cod se va scrie instructiunea OPTION EXPLICIT. In caz contrar, dac o variabil declarat cu numele rind, spre exemplu, este apelata in program cu numele rand, programul nu va semnala greseala, in schimb rezultatul obtinut va fi eronat. Prin utilizarea instructiunii OPTION EXPLICIT programul va solicita declararea variabilei rand si qstfel vom observa greseala de scriere. as tipVariabil, numeVariabil2 as

www.formare.ro

14/46


Tel/fax: 0248-262340

2.8. Principalele operatii cu variabile a) Concatenarea mai multor iruri de caractere se realizeaza cu ajutorul operatorului + Ex.: sir1=Dumitru, sir2=Ilie sir3=sir1+ +sir2 => sir3=Dumitru Ilie b) Conversia unui tip de variabil (numeric, dat, etc.) n: 1. ir de caractere - se realizeaza cu ajutorul intructiunii CStr(parametru). Conversia este necesar deoarece nu putem aduna un ir cu un numr. Ex.: sir1=Varsta este:, var1=45 sir2= sir1+cstr(var1) => sir2=Varsta este:45 2. numar intreg - se realizeaza cu ajutorul intructiunii CInt(parametru). Conversia se aplic numerelor zecimale (tip Single sau Double) pe care dorim s le transformm in numere intregi. Ex.: num1=154.5458452, CInt(num1)=154 c) Rotunjirea unui tip de variabil numeric se realizeaz cu intructiunea: Round(variabila,nrZecimale) Ex.: num1=154.5458452, Round(num1,0)=154

c) Generarea unui numar aleator se face cu functia Rnd(). Functia Rnd() genereaza aleatoriu numere zecimale intre 0 si 1. Pentru a genera numere cuprinse intr-un anumit interval (A si B), sintaxa este urmatoarea: A+B*Rnd()Ex.: numAleator=10+100*Rnd() => 43, 78, 51

www.formare.ro

15/46


Tel/fax: 0248-262340

3. Instruciuni VBAntr-un program datele sunt prelucrate cu ajutorul instruciunilor. Instruciunile se mpart n dou categorii: instruciuni de decizie i instruciuni repetitive; 3.1. Instruciunea de decizie If End If Instruciunea If.End If are urmtoarea sintax: If cond1 then Bloc_instruciuni End If sau If cond1 then Bloc_instruciuni1 Else Bloc_instruciuni2 End If n cazul (a) instruciunea evalueaz condiia cond1 i dac este adevrat execut blocul de instruciuni Bloc_instruciuni, iar dac este fals atunci instruciunea nu execut nimic. n cazul (b) instruciunea evalueaz condiia cond1 i dac este adevrat execut blocul de instruciuni Bloc_instruciuni1, iar dac Bloc_instruciuni2. Exemplu1 5 : Private Sub BtnInstr_Click() Dim compar As Integer Dim numar As Integer numar = InputBox("Introduceti numarul") se introduce de la tastatura valoarea pentru numar compar = 30 If numar

Suport Curs Excel Avansat

Documents

Transcript of Suport Curs Excel Avansat