Suport Curs Excel Avansat

5/7/2018

Supor t Curs Exc e l Ava nsa t - slide pdf.c om

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

www.formare.ro http://slide pdf.c om/re a de r/full/supor t-c urs-exc e l-ava nsa t

Tel/fax: 0248-262340

1/46 1/46

5/7/2018



Tel/fax: 0248-262340

1. INTRODUCERE

Acest curs îşi propune să vă familiarizeze cu opţiunile 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 informaţii vor completa cunoştinţele deja acumulate privind lucrul cu foile de calcul

tabelar MS Excel, astfel încât să facă din acesta un instrument puternic care să preia mare parte din sarcinile dumneavoastră de serviciu.

2. Elemente de programare în VBA pentru EXCEL In spatele MS Excel firma Microsoft a inclus un editor de comenzi VBA (Visual Basic for Applications), ce permite modificarea

și scrierea de macrouri și aplicații care să automatizeze

lucrul cu foile de calcul Excel. Acest editor

este de fapt un limbaj de programare de nivel înalt,

utilizarea lui implicând cunoștințe corespunzătoare. 2.1. Bara de controale standard

Bara de controale standard se afişează pe ecran astfel: – din meniul Outils se alege opţiunea Personalizer; – va apărea o fereastră cu trei butoane din care se alege opţiunea Barres d’outils şi se bifează opţiunea 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 părţi distincte şi anume: prima parte este formată din butoane funcţionale (1, 2 şi 3) utile pentru conceperea programului în VBA; a doua parte este formată din butoanele 4 – 14, corespunzătoare tipurilor de controale uzuale (obiectelor fizice din


2/46 2/46

5/7/2018



Tel/fax: 0248-262340

program) şi a treia parte este formată din butonul 15 care este folosit pentru a adăuga noi controale. Butonul 1 este folosit pentru intrarea/ieşirea din modul de proiectare. Dacă acest buton

este activat, în foaia de calcul pot fi adăugate noi controale (obiecte fizice). În acest caz controalele de pe foaia

de calcul nu sunt active. În cazul în care butonul nu este apăsat

controalele sunt active, iar programul poate fi rulat. Butonul 2 este

folosit pentru afişarea unui tabel cu ajutorul căruia se pot vizualiza

principalele proprietăţi (parametrii) ale controlului activ din foaia de calcul sau chiar al foii de calcul.

Numele şi tipul obiectului pentru care sunt afişate proprietăţile

Valoarea unui parametru(proprietate)

Numele parametrului ( proprietăţii )

Butonul 3 este folosit pentru activarea editorului de cod VBA.

Observaţie:

Pentru inserarea unei control pe foaia de calcul se apasă pe butonul corespunzător

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 căsuţă de selectare (CheckBox) şi are două stări

selectat sau deselectat. Se utilizează atunci când este necesară selectarea uneia sau mai multor opţiuni dintr -o listă. Butonul 5 reprezintă

un control de tipul căsuţă 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 instrucţiuni). Este unul dintre cele mai folosite


3/46 3/46

5/7/2018



controale.

Tel/fax: 0248-262340

Orice aplicație pe care o creăm poate fi executată prin apăsarea unui buton de

comandă. Butonul 7

este un control de tipul buton radio (OptionButton ) şi are două stări

selectat/neselectat. Dacă pe o foaie de calcul se găsesc 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

afişarea unor valori selectate dintr-o listă. Butonul 9

reprezintă un control de tipul listă derulantă(ComboBox) şi este fol osit pentru

alegerea unei valori dintr-o listă derulantă.

Cea mai importantă metodă a unui control ComboBox este AddItem. Metoda AddItem permite asăugarea unei noi valori în lista derulantă. Butonul 10

reprezintă un control de tipul buton comutator (ToggleButton), care are două

stări: apăsat şi neapăsat. Butonul 11

reprezintă un control de tipul buton de incrementare/decrementare

(SpinButton) şi incrementează(creşte)/decrementează(descreşte) valoarea parametrului Value de câte ori este apăsat una din cele două săgeţi(î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 stabileşte 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 folo sit 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 fișier tip imagine va conţine (afișa) acest control. Butonul 15 (More controls)

este folosit pentru a adăuga noi controale care nu se găsesc

în bara standard. După ce se apasă click pe buton va apărea o listă derulantă din care se poate alege controlul dorit. Butonul More controls

Un control din listă


4/46

Lista derulantă 4/46

5/7/2018



Exemplu: In

Tel/fax: 0248-262340

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.


5/46 5/46

5/7/2018



Tel/fax: 0248-262340

2.2. Editorul VBA

După inserarea controalelor în foaia de calcul, următorul pas îl constituie scrierea codului VBA pentru fiecare control (obiect) în parte. Editorul VBA se accesează prin apăsarea combinaţiei de taste Alt + F11. Fereastra de conţine: o bară de titlu, o bară de meniuri, o bară standard şi două cadrane. În

vizualizare

cadranul din partea stângă sunt afişate foile de lucru. Dacă dorim să accesăm codul corespunzător unei anumite foi de lucru se execută dublu -click pe iconiţa foii respective. În cadranul din partea dreaptă se scrie codul corespunzător foii respective.

Lista evenimentelor obiectului

Lista cu obiecte Cadran de editare cod VBA

Cadranul foilor de calcul

2.3. Tipuri de date folosite în VBA

Ca orice limbaj de programare și VBA folosește date și instrucțiuni care, structurate într -un anumit mod, permit execuția comenzilor dorite. Principalele tipuri de date folosite în cadrul VBA sunt prezentate în tabelul următor:

Tip dată Integer (nr.întreg)

tip de dată numărDescriere întreg cuprins între –32.768 şi +32.767


Operaţii permise adunarea, scăderea, înmulţirea, împărţirea, atr ibuirea

6/46 6/46

5/7/2018



Descriere

Tip dată

Tel/fax: 0248-262340

Operaţii permise

tip de dată număr întreg cuprins între adunarea, scăderea, înmulţirea, – 2.147.483.648 şi +2.147.483.647 împărţirea, atribuirea (simplă tip de dată număr real (maxim 8 zecimale) Idem

Long

Single precizie)

String (șir) Boolean (logic)

Tip de dată şir de car actere

atribuire,

concatenarea

(adunarea) şi trunchierea tip de dată logică şi este format din operaţii logice (AND, OR, etc) mulţimea { TRUE (ADEVĂRAT) şi şi atribuire FALSE (FALS)}

Object (obiect)

tip de dată complex

Variant

ti p de dată care le include pe toate celelalte

2.4. Linii de cod, proceduri, funcții, parametri

Sarcina noastră este să creăm mici aplicații (programe) prin care să automatizăm o parte din lucrările zilnice. Pentru aceasta vom insera un buton de comandă (CommandButton) în foaia de lucru, cu numele Btn1. Executând dubluclick pe acesta vom ajunge în editorul Visual Basic.

Aici începem să creăm aplicația dorită, prin scrierea de linii de comandă. Liniile de comandă conțin cuvinte-cheie recunoscute de limba jul de programare, iar scrierea lor trebuie să respecte sintaxa specifică VBA. Dacă dorim să aflăm sintaxa anumitor comenzi, putem genera un mic macro care să execute aceste comenzi, după care să-l edităm și să copiem din el liniile de cod c are ne interesează.

Liniile de cod care se repetă le putem grupa în proceduri (subrutine) sau funcții. Acestea se pot apela după numele lor ori de câte ori este nevoie. Procedurile și funcțiile pot avea parametri. Spre exemplu putem crea o functie numită ”stergere” car e poate avea ca parametru numele foii de calcul pe care dorim să o ștergem. In principal, liniile de cod vor executa anumite operațiuni asupra unor obiecte VBA. 2.5. Descrierea unui obiect

Principalele elemente cu care vom lucra în cadrul VBA se nume sc obiecte. Exemple de obiecte sunt: butoanele de comandă, foaia de calcul, celula, rândul, coloana, etc. Obiectele VBA sunt identificate printr-un

nume și se pot caracteriza cu ajutorul a trei

elemente : proprietăţi, metode şi evenimente.


7/46 7/46

5/7/2018



Tel/fax: 0248-262340

a) Proprietăţile reprezintă caracteristicile obiectului respectiv; exemple de propietăți:

culoare, tip font, lățime, înălțime, etc. Aceste proprietăţi pot fi modificate conform sintaxei : numeObiect.numeProprietate = Valoare,

-un punct.

Observăm că proprietatea unui obiect este separată de numele obiectului printr Citirea unei linii de cod ce include o înșiruire de obiecte și parametri se face de la dreapta la stânga, conform modelului următor: WorkSheets(”Foaia1”).Cells(1, "a").Font.Color = vbGreen, se

citește de la dreapta la stânga, astfel: culoarea fontului celulei (1,”a”) din foaia de calcul cu numele ”Foaia1” va fi de culoare verde. Observăm că obiectele și proprietățile acestora sunt separate între ele printr -un punct. In tabelul de mai jos sunt prezentate sintetic principalele proprietăţi ale unui obiect VBA:

Nume

Descriere

proprietate/parametru

BackColor, Color,

Acesti parametri indică culoarea obiectului; de obicei culoarea

ColorIndex

este stabilită cu ajutorul tabelei de proprietăţi, care pune la dispoziţia utilizatorului o serie de culori standard.

Caption

Reprezintă textul care este afişat pe obiect şi este un parametru de tip String (Şir de caractere).

Enabled

Acest parametru indică dacă obiectul este activ sau nu şi este de tipul Boolean (logic). Dacă

parametrul are valoarea True

(Adevărat), atunci obiectul este activ. Dacă parametrul are valoarea False (Fals) atunci obiectul este inactiv. 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.

Height

Reprezintă înălţimea obiectului şi este un parametru de tipul Single.

Left

Reprezintă coordonata (de pe axa X) a colţului stânga sus a obiectului; Acest parametru este de tipul Single.

PrintObject

Stabileşte dacă obiectul este printabil sau nu şi este un parametru de tip Boolean (Logic); Dacă valoarea este True


8/46 8/46

5/7/2018



Nume

Tel/fax: 0248-262340

Descriere

proprietate/parametru

atunci obiectul este printabil; Dacă valoarea este False atunci obiectul nu este printabil (nu apare la imprimantă). Reprezintă coordonata (de pe axa Y) a colţului stânga sus a

Top

obiectului; Acest parametru este de tipul Single.

Reprezintă lungimea obiectului şi este un parametru de tipul

Width

Single. Stabileşte

Visible

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. b) Metodele

sunt proceduri sau funcţii care pot să aibă un număr diferit de parametri şi

reprezintă operaţiile care se pot efectua asupra unui obiect. Exemple de metode: ștergerea, copierea, activarea, etc.

Apelarea unei metode o vom ilustra prin câteva exemple : Exemplul 1 : Pentru acest prim exemplu vom crea un fisier excel numit ziua1.xls , iar in prima foaie de calcul inserăm 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 )


9/46 9/46

5/7/2018



Tel/fax: 0248-262340

Observaţie: Metodele obiectelor pot fi doar apelate nu şi modificate. c) Evenimentele

sunt proceduri care pot să aibă un număr diferit de parametri şi

reprezintă reacţia obiectului la diferite acţiuni ale utilizatorului; procedurile se apelează automat la o acţiune 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 următoarea: 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 afișat un mesaj. Operațiunea este ilustrată în figura următoare Selectăm obiectul

Selectăm evenimentul

Foaia de calcul curentă


10/46 10/46

5/7/2018



Tel/fax: 0248-262340

Principalele evenimente ale unui obiect sunt sintetizate în tabelul de mai jos: Nume eveniment

Activate

Descriere

Evenimentul este declanșat

la activarea unui obiect (ex.

Deschiderea unei foi de calcul)

Acest eveniment este declanşat în momentul care se efectuează

Click

un click al butonului din stânga al mouse -lui pe obiect. (Ex.apăsarea unui buton de comandă) DoubleClick

Evenimentul este

declanşat în momentul în care se efectuează

un dubluclick pe obiect. MouseDown

Acest eveniment este declanşat în momentul în care butonul stâng/drept al mous-ului este apăsat iar cursorul mous -ului se află pe obiect .

MouseMove

Acest eveniment este declanşat

în momentul în care mous-ul

este mişcat pe obiect. MouseUp

Acest eveniment este declanşat în momentul în care butonul stâng/drept al mous-ului nu mai este apăsat iar cursorul mous ului se află pe obiect .

2.6. Foaia de calcul, proprietăţi, met ode, evenimente

Foaia de calcul este principalul obiect de lucru în programarea scripturilor Excel. Principalele proprietăţi ale unei foi de calcul sunt prezentate în tabelul de mai jos: Nume proprietate

Name Cells(i , j)

Descriere

Acest parametru indică numele foi respective; Acest parametru indică valoarea celulei de pe rândul i coloana j; j poate fi atât de tip integer cât şi string (șir de caractere)

Range("celulă1:celulă2")

Este un parametru de tip obiect, prin care se face o referire la o

zonă de celule.


11/46 11/46

5/7/2018



Tel/fax: 0248-262340

În editorul VBA proprietăţile foii de calcul sunt afişate 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

' afişează numele primei foi de calcul Feuil1.Name = "Prima Foaie" ' numele primei foi de calcul este schimbat cu „Prima Foaie”

MsgBox

Feuil1.Cells(2, "A")

' afişează conţinutul celulei de pe rândul 2, coloana A Feuil1.Cells(4, 2)= "100"

' Valoarea celulei de pe rândul 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

Realizează activarea unei foi de calcul;

Descriere

Select

Realizează selectarea unei foi de calcul; (similar cu Activate)

Delete

Realizează ştergerea unei foi de calcul;


12/46 12/46

5/7/2018



Nume metoda PrintOut From, To, Copies

Tel/fax: 0248-262340

Descriere

Realizează printarea foii de calcul la care se face referire;

Această metodă are 3 parametri de tip număr întreg; From indică numărul pagini de la care se începe printarea; To indică numărul pagini până la care se printează; Copies reprezintă numărul de copii printat;

Metodă a foii de calcul

In

editorul VBA metodele foii de calcul sunt afişate 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

Descriere Ruleaza codul la activarea unei foi de calcul;

SelectChange

Ruleaza codul la selectarea unei celule sau zone


13/46 13/46

5/7/2018



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 funcţii); Pentru a fi recunoscută de calculator o variabilă trebuie mai întâi să fie declarată. Această declarare se face cu ajutorul cuvântului rezervat “Dim”. Sintaxa:

Dim numeVariabilă1

as tipVariabilă, numeVariabilă2

as

tipVariabilă... Exemplul 4:

Dim valoare as Integer Private Sub BtnFoaie_Click() valoare = Feuil1.Cells(43, "b") MsgBox valoare End Sub Observatie: Pentru a preîntâmpina 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.


14/46 14/46

5/7/2018



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 C Str(parametru).

Conversia este necesară deoarece nu putem aduna un șir cu un număr. 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 transformăm 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 …


15/46 15/46

5/7/2018



Tel/fax: 0248-262340

3. Instrucţiuni VBA

Într -un program datele sunt prelucrate cu ajutorul instrucţiunilor. Instrucţiunile se împart în două categorii: instrucţiuni de decizie şi instrucţiuni repetitive; 3.1. Instrucţiunea de decizie If … End If Instrucţiunea If….End If are următoarea sintaxă:

If

cond1 then

(a)

Bloc_instrucţiuni End If

sau

If

cond1 then

Bloc_instrucţiuni1

Else

(b) Bloc_instrucţiuni2

End If

În cazul (a) instrucţiunea evaluează condiţia cond1 şi dacă este adevărată execută blocul de instrucţiuni Bloc_instrucţiuni, iar dacă este falsă atunci instrucţiunea nu execută nimic. În cazul (b) instrucţiunea evaluează condiţia cond1 şi dacă este adevărată execută blocul de instrucţiuni Bloc_instrucţiuni1, iar dacă

este falsă execută blocul de instrucţiuni

Bloc_instrucţiuni2. 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 <= compar Then MsgBox "Numarul este mai mic sau egal decat " + CStr(compar) ’Mesajul

afisat

va

contine

o

concatenare

de

siruri.

Deoarece

compar este de tip Integer, este necesar sa-l convertim in sir cu instrucțiunea „CStr()” prezentată anterior www.formare.ro http://slide pdf.c om/re a de r/full/supor t-c urs-exc e l-ava nsa t

16/46 16/46

5/7/2018



Tel/fax: 0248-262340

Else MsgBox "Numarul este mai mare decat " + CStr(compar) End If End Sub

Tema: Sa se afiseze mesajul sub forma „ Numarul X este mai mare decat 30” 3.2. Instrucţiunea de decizie Select Case … End Select Instrucţiunea Select Case ….. End Select Select

are următoarea sintaxă:

Case val

Case val1 , val2 ,……

:

Bloc_instructiuni1

Case lis1 , lis2 ,…… : Bloc_instructiuni2 Case str1 , str2 ,…… : Bloc_instructiuni3 Case ELSE: Bloc_instructiuni4 End Select

unde val este o variabilă iar val1, val2, …………., lis1,lis2, ……….., str1, str2 reprezintă valori de acelaşi tip cu cel al variabilei val, sau expresii care returnează un rezultat de acelaşi tip cu cel al variabilei val.

Această instrucţiune se execută astfel: dacă val se găseşte în lista sau intervalul de valori /expresii val1, val2, … atunci se execută Bloc_instrucţiuni1; dacă val se găseşte în lista sau intervalul de valori/ expresii

lis1, lis2, … atunci se execută Bloc_instrucţiuni2 etc.; dacă val nu se găseşte în

nici una dintre aceste liste de valori instrucţiunea Select Case nu execută nimic sau, dacă există, execută Bloc_instructiuni4, aferent lui CASE ELSE . Exemplul 6 :

Private Sub BtnCase_Click() Dim valoare As Integer Dim mesaj As String

valoare = InputBox("Introduceti o valoare intre 10 si 200:", "Instructiunea Case", 10) ’Observati

modul

de

introducere

al

parametrilor

instructiunii

InputBox

Select Case valoare


17/46 17/46

5/7/2018



Tel/fax: 0248-262340

Case 10 To 49: MsgBox "Valoarea este in intervalul 10-49" Case 50 To 100: MsgBox "Valoarea este in intervalul 50-100" Case 101 To 200: MsgBox "Valoarea este in intervalul 101-200" End Select End Sub Tema: In locul citirii de la tastatură să se preia valoarea dintr-o celulă a foii de calcul curente. 3.3. Instrucţiunea repetitivă For … Next

Instrucţiunea For …. Next are următoarea sintaxă : For

contor = val1 To val2 [Step X]

Bloc_instrucţiuni

Next

unde contor, val1, val2 sunt toate de acelaşi

tip. Această instrucţiune realizează executarea

Bloc_instrucţiuni de val2 – val1 ori; instrucţiunea are astfel un număr cunoscut de repetări. Pasul incrementării poate fi, de asemenea indicat (vezi argumentul Step). De exemplu, variabila contor poate lua valori din 2 în 2.

Observaţie:

val2 trebuie să fie mai mare decât val1 pentru ca instrucţiunea să se execute; dacă val2 este mai mic decât val1 atunci Bloc_instrucţiuni nu se va executa. Exemplul 7 :

Private Sub BtnForNext_Click() Dim i As Integer, valoare As Integer valoare = 1 For i = 1 To 5 valoare = valoare + 1 MsgBox "Valoarea este: " + CStr(valoare) Next i End Sub Tema: Scrierea rezultatului valoare într-o celulă a foii de calcul curente.


18/46 18/46

5/7/2018



Tel/fax: 0248-262340

3.4. Instrucţiunea repetitivă Do While … Loop

Instrucţiunea Do While … Loop are următoarea sintaxă: Do While condiţie

Bloc_instrucţini Loop

Această instrucţiune execută Bloc_instrucţini atâta timp cât este îndeplinită condiţia. Exemplul 8 :

Private Sub BtnDoWhile_Click() Dim valoare As Integer i = 1 Do While valoare <= 5 valoare = valoare + i i=i+1 Loop MsgBox "Valoarea este: " + CStr(valoare)

End Sub

Secvenţa este executată atâta timp cât variabila i este mai mică sau egală decât trei şi anume: la prima execuţie, când i=1, se verifică dacă valoare <=5 şi cum condiţia este îndeplinită se face valoare=valoare+1 și i=2; la a doua execuţie i= 2 se verifică dacă valoare <=5 şi cum condiţia este îndeplinită se face valoare=valoare+1 și i=3; la fel până la a 6-a execuție, când valoare>5 și programul realizează ieșirea din bucla Do While...Loop .

3.5. Instrucţiunea repetitivă Do Until … Loop

Instrucţiunea Do Until … Loop are următoarea sintaxă: Do Until condiţie

Bloc_instrucţiune Loop

Această instrucţiune execută Bloc_instrucţini până când este îndeplinită condiţia. Exemplul 9 :

Private Sub BtnDoUntil_Click() www.formare.ro http://slide pdf.c om/re a de r/full/supor t-c urs-exc e l-ava nsa t

19/46 19/46

5/7/2018



Tel/fax: 0248-262340

Dim valoare As Integer valoare = 1 Do Until valoare = 20 valoare = valoare + 1 Loop MsgBox "Valoarea este: " + CStr(valoare) End Sub

Raționamentul este similar cu cel al exemplului prcedent.

4. Proceduri și f uncţii

În general un program este format din proceduri (evenimente) şi funcţii. De multe ori într -un program sunt secvenţe de cod care se repetă. Pentru a evita scrierea repetată a acestor secvenţe, se utilizează funcţiile şi procedurile, care se scriu o singură dată şi apoi se apelează după nume de câte ori este nevoie. Procedurile si f uncțiile sunt subprograme

care efectuează anumite operaţii solicitate de

programul apelant, iar in cazul functiilor returneaza anumite valori. a) Procedura are următoarea sintaxă:

Sub NumeProcedura([Lista_de_parametri as tipData]) Bloc_instrucţiuni

End Sub b) Funcția are următoarea sintaxă: Private Function NumeFunctie ( [Lista_de_parametri as tipData] )

Bloc_instrucţiuni numeFunctie=valoare_rezultata End Functie

Observatie: Lista de parametrii este optionala


20/46 20/46

5/7/2018



Tel/fax: 0248-262340

Exemplul 10 :

Private Sub BtnFunctie_Click() Dim numar As Integer, putere As Integer, i As Integer

For i = 1 To 5 numar = InputBox("Introduceti numarul:") putere

=

InputBox("Indicati

puterea

la

care

se

calculeaza

numarul") MsgBox Patrat(numar, putere) Next i End Sub -------------------------Function Patrat(nr, pt) Patrat = nr ^ pt ’semnul ^ se utilizeaza pentru ridicarea la putere

End Function

Exemplul 11 :

Private Sub BtnProcedura_Click() Dim numar As Integer, putere As Integer, i As Integer

For i = 1 To 5 numar = InputBox("Introduceti numarul:") Call Scrie(numar, i) Next i

End Sub Private Sub Scrie(nr, randul) ActiveSheet.Cells(randul, "D").Value = nr End Sub

Notă: Se observă corespondența între numar  nr si i  randul


21/46 21/46

5/7/2018



Tel/fax: 0248-262340

5. Lucrul cu liste si alte obiecte VBA

Uneori este util sa preluam date din liste de valori, in loc de a le introduce de la tastatura. O lista se poate utiliza prin urmatorii parametri:

.ColumnCount = numar coloane .BoundColumn = numarul coloanei de pe care se va face citirea . AddItem = Adauga articol pe rand nou . List(r,c) = adauga articol pe randul „r”, coloana „c”, cu conditia ca randul „r” sa fi fost creat in prealabil cu AddItem

.List()=adauga articole dintr-o zona (range) de celule .ListFillRange = numeZona ’adauga articole dintr-o zona (range)

de celule .Clear = sterge toate valorile din lista .RemoveItem

(index)

=

șterge

un

rând

din

listă

(index

are

valoare de la 0 la nr.articole din lista)

Exemplul 12

‘ Declaram variabilele

Dim nume As String, sectie As String, ore As Single Dim nrIndex As Integer

Private Sub ListBox1_Click() ’Ruleaza la executarea unui click pe ListBox1

nrIndex = ListBox1.ListIndex ’memoreaza pozitia articolului in lista ’Inseram in foaie 2 butoane de optiuni (unul pentru adaugare

articol selectat, celalalt pentru stergere articol selectat din lista ActiveSheet.Cells(1,1)=”nume” ActiveSheet.Cells(1,2)=”sectie” ActiveSheet.Cells(1,3)=”ore”

If OptionButton1.Value = True Then


22/46 22/46

5/7/2018



Tel/fax: 0248-262340

nume = ListBox1.List(nrIndex, 0) sectie = ListBox1.List(nrIndex, 1) ore = ListBox1.List(nrIndex, 2)

ActiveSheet.Cells(2, 1) = nume ActiveSheet.Cells(2, 2) = sectie ActiveSheet.Cells(2, 3) = ore

Else On Error Resume Next ListBox1.RemoveItem (nrIndex) End If

End Sub

Private Sub Worksheet_Activate() With ListBox1 .Clear ' initializam lista .ColumnHeads = False .ColumnCount = 3 ' dimensionam lista la 3 coloane .List() = Sheet3.Range("a1:c5").Value

End With End Sub Foaia de calcul va ar ăta așa:


23/46 23/46

5/7/2018



Tel/fax: 0248-262340

Datele sunt încărcate în listă din foaia de calcul ”date lista”


24/46 24/46

5/7/2018



Tel/fax: 0248-262340

6. Aplicaţii VBA

In acest capitol vom realiza o serie de aplicaţii ce se pot constitui în puncte de plecare pentru realizarea unor aplicaţii personalizate, în funcţie de necesităţile fiecărui utilizator în parte. 6.1. Aplicaţia 1 – Determinarea zonei de date dintr-o foaie de calcul

Vom realiza o primă aplicaţie care va determina numarul de randuri ce contin date intr-o foaie de calcul (consideram nr.de coloane cunoscut). Zona identificata va fi selectata si i se vor modifica anumite proprietati. Pentru acesta vom deschide unul din fisierele create,

iar în ultima foaie de calcul vom

insera o zona de date (4 linii si 3 coloane) si un buton de comandă, căruia îi vom schimba parametrul Caption (din cel implicit CommandButton1 în „Afiseaza”) cu ajutorul tabelei de

proprietăţi (parametri). Tabela de proprietăţi arametri Butonul de comandă cu parametrul Caption schimbat

Parametrul Caption al

butonului de comandă a fost schimbat din

CommandButton1 în Afisează

După aceasta vom activa editorul de cod VBA (apăsând pe al treilea buton) şi din lista derulantă cu obiecte de pe prima foaie de calcul vo m alege CommandButton1, iar din lista de evenimente corespunzătoare lui vom alege evenimentul Click.


25/46 25/46

5/7/2018



Tel/fax: 0248-262340

Controlul CommandButton1

Lista derulantă cu obiectele care se află e rima foaie de calcul Declararea evenimentului Click

corespunzător controlului CommandButton1

Codul corespunzător evenimentului Click este următorul: Private Sub CommandButton1_Click() Dim nr_linii As Integer Dim valoare As Variant, zona As String

'Calcul nr. linii

cu date

valoare = ActiveSheet.Cells(1, 1)

Do While valoare <> 0 nr_linii = nr_linii + 1 valoare = ActiveSheet.Cells(nr_linii, 1) Loop nr_linii = nr_linii - 1 MsgBox nr_linii

' Selectare zona date zona = "a1:" + "c" + CStr(nr_linii) MsgBox zona ActiveSheet.Range(zona).Select

’Inseram

in

foaia

de

calcul

2

OptionButton

(avand

Caption=”Modifica”, respectiv „Reseteaza”)

If OptionButton1.Value = True Then


26/46 26/46

5/7/2018



Tel/fax: 0248-262340

Selection.Interior.Color = vbRed Selection.Font.Bold = True Selection.Font.Italic = True

Else

Selection.Interior.Color = vbWhite Selection.Font.Bold = False Selection.Font.Italic = False

End If

End Sub 6.2. Aplicaţia 2 – Evidentierea unei celule selectate cu ajutorul mouse-lui

În a doua aplicaţie vom realiza un cod care va colora celula pe care se face click. In plus, putem afisa/prelua adresa si continutul celulei selectate. Pentru aceasta vom folosi tot foaia de lucru de la Aplicatia 1. Codul va fi de forma:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim nr_rand As Integer, nr_col As Integer Dim continut_cel As String, adresa_cel As String Dim mesaj As String nr_rand = ActiveCell.Row WITH ActiveCell `WITH…END WITH ne permite sa scriem ActiveCell

`doar o singura data, nu pe fiecare rand de cod .Interior.Color = vbBlue .Font.Color = vbYellow adresa_cel = .Address continut_cel = .Value `Scrie in variabila continut_cel valoarea `gasita in celula selectata END WITH mesaj = "Valoarea celulei " + adresa_cel + " , selectate este '" + continut_cel + "'" www.formare.ro http://slide pdf.c om/re a de r/full/supor t-c urs-exc e l-ava nsa t

27/46 27/46

5/7/2018



Tel/fax: 0248-262340

MsgBox mesaj End Sub Observatie: In loc de ActiveCell, putem folosi Target

Temă: Inserati 2 controale de tip OptionButton pentru a controla culorile celulelor selectate (ex.fond verde, font albastru, respectiv fond alb, font negru)

6.3. Aplicaţia 3 – Formatarea automată a unei zone de celule

Această aplicaţie își propune crearea unui buton de comanda care să genereze aplicarea unor formate definite de utilizator pe zona de celule selectate.

Deoarece este dificil să reținem sintaxele unor comenzi mai complicate în Excel vom genera macrouri, pe care apoi le vom studia și modifica după necesități. In următorul exemplu avem nevoie să aplicăm următoarea formatare unei zone de celule selectate: font bolduit și înclinat, colorat cu roșu, celula cu fond galben , aliniament sus și stânga, format numeric cu 2 zecimale, coloanele să se potrivească la conținut (autofit).

Pentru aceasta, vom parcurge urmatorii pași: 1.înregistrăm un macro unde vom avea toate aceste formate 2. creăm un buton de comandă 3.în interiorul codului butonului de comandă vom copia codul generat automat de macro 4.operăm modificările necesare la cod și rulăm aplicația Codul generat de macro va fi de forma:

Sub formatare() ' ' formatare Macro ' Range("A2:C3").Select ‘ Am selectat la intamplare o zona de celule Selection.Font.Bold = True Selection.Font.Italic = True With Selection.Interior .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With www.formare.ro http://slide pdf.c om/re a de r/full/supor t-c urs-exc e l-ava nsa t

28/46 28/46

5/7/2018



Tel/fax: 0248-262340

With Selection.Font .Color = -16776961 .TintAndShade = 0 End With Selection.Style = "Comma" With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("A:C").Select Columns("A:C").EntireColumn.AutoFit Range("C8").Select End Sub

Identificăm acțiunile realizate de macrou, ștergem ce este în plus și adăugăm ce este necesar. Codul final va fi de forma:

Private Sub BtnFormat_Click() 'Range("A2:C3").Select Selection.Font.Bold = False Selection.Font.Bold = True Selection.Font.Italic = False Selection.Font.Italic = True With Selection.Interior .Color = vbYellow End With With Selection.Font .Color = vbRed


29/46 29/46

5/7/2018



Tel/fax: 0248-262340

End With Selection.Style = "Comma" With Selection .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop End With

Selection.EntireColumn.AutoFit End Sub

Atenție: ca acest cod să funcționeze trebuie mai întâi să selectatăm o zonă de celule și numai după aceea să apăsăm butonul de comandă. Temă: încercați și alte tipuri de formatări 6.4. Aplicaţia 4 – Crearea unui configurator automat

Vom concepe o aplicaţie care realizează completarea unei liste de componente a căror valoare va fi însumată. Aplicaţia este utilă în realizarea de configuraţii specifice pentru un echi pament (automobil, calculator etc.) şi calcularea preţului acestuia. Aplicaţia cuprinde două foi de calcul. Prima foaie conţine lista componentelor şi preţurile acestora, iar în foaia a doua se realizează calculul listei particularizate. In prima foaie

de calcul sunt două butoane: unul pentru adăugare elemente, iar celălalt

pentru reiniţializare. Codul corespunzător aplicaţiei este: 1. Pentru prima foaie de calcul

Dim cnt As Integer

Private Sub Adauga_Click() 'procedura se activeazã prin click pe buton ul "Adauga" (evenimentul click)

Dim x As Integer, y As Integer Dim reper As String, valoare As Single 'Declarare variabile

cnt = cnt + 1 ' cnt= numãr înregistrări adăugate în foaia "Vizualizare"


30/46 30/46

5/7/2018



Tel/fax: 0248-262340

x = ActiveCell.Row ' x = rândul celulei active y = ActiveCell.Column ' y = coloana celulei active If y <> 1 Then

End If

y = 1 ’ aduce celula activă în coloana 1

reper = Feuil1.Cells(x, y) ' reper = numele reperului selectat valoare = Feuil1.Cells(x, y + 1) ' valoare = valoarea reperului selectat Feuil2.Cells(cnt, 1) = reper ' se adaugã numele reperului în foaia 2 "Vizualizare" pe poziţia cnt

Feuil2.Cells(cnt, 2) = valoare ' se adaugă valoarea reperului în foaia 2 "Vizualizare" pe poziţia cnt Feuil2.Cells(1, 3) = cnt ' se retine numărul de înregistrări din listã

End Sub Private Sub Terminare_Click() 'procedura se activează prin click pe butonul "Terminare" (evenimentul click)

cnt = 0 'se initializeazã contorul ce retine nr.de înregistrări Feuil2.Activate ' este activatã foaia 2 "Vizualizare" Feuil2.Cells.Select 'sunt selectate toate celulele din foaia 2 Selection.ClearContents ' şterge conţinutul zonei selectate Feuil2.Cells(1, 1).Select ' selectează prima celulã Feuil1.Activate ' revenire în foaia 1 "Introducere" End Sub 2. Pentru a doua foaie de calcul

Dim cnt As Integer Private Sub Worksheet_Activate() 'Procedura se executã prin activarea foii 2 "Vizualizare"

Dim adresa As String, sel As String 'Declararea variabilelor If Feuil2.Cells(1, 3) <> "" Then cnt = Feuil2.Cells(1, 3) + 1 Else Exit Sub End If


31/46 31/46

5/7/2018



Tel/fax: 0248-262340

'se verificã dacã existã înregistrări în foaia 2; dacã sunt se adaugă o linie pt.TOTAL; dacã nu sunt înregistrări se iese din procedurã

Feuil2.Cells(cnt, 1) = "TOTAL" 'In foaia 2 pe rândul cnt, coloana 1 se scrie "TOTAL"

adresa = "B" + CStr(cnt) ' se calculează poziţia celulei ce va conţine valoarea totalã

sel = "B1:B" + CStr(cnt - 1) ' variabila sel conţine adresa zonei de celul e ce vor fi totalizate

Range(sel).Select ' este selectatã zona de celule după care se va face totalul 'Se activează celula unde va fi scris totalul Range(adresa).Activate ActiveCell.Formula = "=SUM(" + sel + ")" ' în celula activã se inserează formula sum(sel)

Columns("A:A").EntireColumn.AutoFit ' se potriveşte întreaga coloanã la mărimea conţinutului

Columns("B:B").EntireColumn.AutoFit End Sub


32/46 32/46

5/7/2018



Tel/fax: 0248-262340

6.5. Aplicaţia 5 – Actualizarea dinamică a unui grafic

Utilizând cunoștințele acumulate până acum, v om concepe o aplicaţie care realizează actualizarea dinamică a unui grafic. Această aplicaţie este utilă în cazul graficelor care sunt des modificate (sunt inserate rânduri/înregistrări şi coloane/câmpuri noi). Aplicaţia cuprinde o singură foaie de calcul pe care se află 3 serii de date şi un buton Command1 numit „Actualizare”. Codul corespunzător aplicaţiei este următorul.: Private Sub CommandButton1_Click() Call cauta(nr_c, nr_r) ' determina câte rânduri şi coloane avem ocupate

zona = "A1:" + Chr(64 + nr_c) + CStr(nr_r) ' Stabileşte zona de date, iar chr(64) = "A", chr(65)="B" ... etc. Call grafic(zona) ' apeleaza funcţia de trasare a graficului End Sub

Function cauta(c, r) r = 1 Do Until Me.Cells(r, 1) = "" r = r + 1 Loop r = r - 1 c = 1 Do Until Me.Cells(1, c) = "" c = c + 1 Loop c = c - 1 End Function Function grafic(z) Range(z).Select ‘selectează zona de date On Error Resume Next 'in caz de eroare merge mai departe Me.ChartObjects.Delete ' sterge toate graficele din foaia activă

Charts.Add 'crează un grafic nou With ActiveChart

titlul

.HasTitle = True .ChartTitle.Characters.Text = "Grafic productie" ' graficului

.Axes(xlCategory, = True .Axes(xlCategory, xlPrimary).HasTitle xlPrimary).AxisTitle.Characters.Text = "Zile" 'explicaţia de pe abcisă (categorie) www.formare.ro http://slide pdf.c om/re a de r/full/supor t-c urs-exc e l-ava nsa t

33/46 33/46

5/7/2018



Tel/fax: 0248-262340

.Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Buc." ' explicaţie pentru ordonată (valoare) .ChartType = xlLineMarkers ' indicăm tipul graficului .SetSourceData Source:=Sheets("Foaie1").Range(z), PlotBy:=xlColumns 'indicăm sursa de date .SeriesCollection(1).Name = "=""Depart.1""" ' Legenda .SeriesCollection(2).Name = "=""Depart.2""" .SeriesCollection(3).Name = "=""Depart.3""" .Location Where:=xlLocationAsObject, Name:="Foaie1" ' indicăm locaţia graficului 'Observaţie: Codul de mai sus este preluat dintr-un macro. Trebuie păstrată această ordine în interiorul lui with ... end with

End With End Function 6.6. Aplicaţia 6 – Crearea unui formular şi completarea automată a acestuia

Vom crea o aplicaţie care realizează completarea automată a unei cereri în funcţie de un nume care este citit de la tastatură. Această aplicaţie va folosi două foi de calcul. În cea de -a doua foaie de calcul se introduc datele care sunt completate în cerere. În prima foaie de calcul este realizat şablonul care va fi completat. Acest şablon este format dintr -un antet şi două butoane de comandă. Primul buton va fi utilizat pentru completarea automată a datelor, iar cel de-al doilea pentru printarea acestei cereri. Pentru realizarea acestei aplicaţii vom utiliza funcţiile numar_inr şi găseşte din aplicaţiile anterioare. Codul corespunzător primei foi de calcul este următorul: ' Declar variabilele nrinreg - numar inregistrari si zona - zona cu date Dim nrinreg As Integer: Dim zona As String Private Sub Worksheet_Activate() ListBox1.ColumnCount = 3 Call numar_inr(nrinreg) zona = ("a1:c" & CStr(nrinreg)) ListBox1.List() = Foaie2.Range(zona).Value Foaie1.Select End Sub

Private Sub CommandButton1_Click()


34/46 34/46

5/7/2018



Tel/fax: 0248-262340

'citirea datelor se face din list box, coloana 1, 2 sau 3 ListBox1.BoundColumn = 1 LbNume.Caption = ListBox1.Value ListBox1.BoundColumn = 2 Lbsectia.Caption = ListBox1.Value ListBox1.BoundColumn = 3 LbSalariu.Caption = ListBox1.Value

End Sub

Private Sub CommandButton2_Click() Foaie1.PrintOut 1, 1, 1 End Sub Private Function numar_inr(n) As Integer Dim inc As Integer inc = 1 Do While Foaie2.Cells(inc, 1) <> "" inc = inc + 1 Loop n = inc - 1 End Function

În evenimentul Click al butonului CommandButton2 este apelată metoda PrintOut a primei foi de calcul. Această metodă are trei parametri: primul parametru (From) indică numărul pagini de la care începe printarea, al doilea parametru (To) indică numărul pagini până la se printează, iar cel de-al treilea reprezintă numărul de copii care este printat. În această aplicaţie este printată o singură pagină, într -un singur exemplar. Prima foaie de calcul este prezentată in imaginea de mai jos:


35/46 35/46

5/7/2018



Butonul BtnFormular

Câmpuri care vor fi

Tel/fax: 0248-262340

Butonul BtnTipar

completate(celule care vor fi completate)


36/46 36/46

5/7/2018



Tel/fax: 0248-262340

6.7. Aplicaţia 7 – Inserarea de elemente grafice si dimensionarea dinamică a acestora

In cele ce urmează vom crea o aplicaţie care inserează automat elemente grafice a căror mărime depinde de un set de date numer ice. Codul aferent aplicației este următorul: Dim x As Integer, y As Integer, l As Integer, h As Integer, scara As Double Dim valoare As Integer, i As Integer, maxim As Integer Dim nr_valori As Integer, distanta As Integer

Private Sub Worksheet_Activate() 'Stabilim parametri formei grafice maxim = 0

x = 30: y = 30: l = 30: h = 200 distanta = 40 ' distanta intre formele grafice 'Selectam si stergem toate formele geometrice existente ActiveSheet.Shapes.SelectAll Selection.Delete 'Aflam nr.de valori pe care le avem pe o linie a foii de calcul valoare = ActiveSheet.Cells(1, 1).Value ' initializam valoarea nr_valori = 1 Do Until valoare = 0 'repetam bucla pana ajungem la capatul sirului de valori nr_valori = nr_valori + 1 valoare = ActiveSheet.Cells(1, nr_valori).Value

Loop nr_valori = nr_valori - 1 'corectam valoarea finala

'Aflam valoarea maxima

For i = 1 To nr_valori


37/46 37/46

5/7/2018



If

maxim

<=

ActiveSheet.Cells(1,

Tel/fax: 0248-262340

i).Value

Then

maxim

=

ActiveSheet.Cells(1, i).Value Next i

'Trasam formele geometrice

For i = 1 To nr_valori

ActiveSheet.Shapes.AddShape(msoShapeRectangle, _ x, y, l, h).Select x = x + distanta scara = ActiveSheet.Cells(1, i).Value

With Selection.ShapeRange scara = scara / maxim ' scalam marimea formei grafice .ScaleHeight scara, msoFase, msoScaleFromtopRight 'Coloram

formele

functie

de

valoarea

lor

(mica

=

rosie,

medie=galbena si mare=verde If scara <= 0.3 Then .Fill.ForeColor.RGB = RGB(255, 0, 0) If scara > 0.3 And scara <= 0.7 Then .Fill.ForeColor.RGB = RGB(255, 255, 0) If scara > 0.7 Then .Fill.ForeColor.RGB = RGB(0, 255, 0) End With Next i End Sub


38/46 38/46

5/7/2018



Tel/fax: 0248-262340

Rezultatul rulării aplicației este ilustrat în figura următoare:


39/46 39/46

5/7/2018



Tel/fax: 0248-262340

7. Crearea de formulare pentru configurarea tabelelor pivot

Vom construi o aplicaţie care utilizeaza un formular prin intermediul căruia poate fi configurat ușor un tabel pivot. Formularul va fi de forma:

Codul aferent formularului este următorul: Private Sub ColBtn_Click() If ColTxt.Text <> "" Then PivotLst.AddItem (ColTxt.Text) ColTxt.Text = "" ColBtn.Caption = "<-" Else ColTxt.Text = PivotLst.Text PivotLst.RemoveItem (PivotLst.ListIndex) ColBtn.Caption = "->" End If Call validare End Sub

Private Sub DateBtn_Click() If DateTxt.Text <> "" Then PivotLst.AddItem (DateTxt.Text)


40/46 40/46

5/7/2018



Tel/fax: 0248-262340

DateTxt.Text = "" DateBtn.Caption = "<-" Else DateTxt.Text = PivotLst.Text PivotLst.RemoveItem (PivotLst.ListIndex) DateBtn.Caption = "->" End If Call validare End Sub

Private Sub PagBtn_Click() If PagTxt.Text <> "" Then PivotLst.AddItem (PagTxt.Text) PagTxt.Text = "" PagBtn.Caption = "<-" Else PagTxt.Text = PivotLst.Text PivotLst.RemoveItem (PivotLst.ListIndex) PagBtn.Caption = "->" End If Call validare End Sub Private Sub RandBtn_Click() If RandTxt.Text <> "" Then PivotLst.AddItem (RandTxt.Text) RandTxt.Text = "" RandBtn.Caption = "<-" Else RandTxt.Text = PivotLst.Text PivotLst.RemoveItem (PivotLst.ListIndex) RandBtn.Caption = "->" End If Call validare End Sub Function validare() www.formare.ro http://slide pdf.c om/re a de r/full/supor t-c urs-exc e l-ava nsa t

41/46 41/46

5/7/2018



Tel/fax: 0248-262340

If PivotFrm.PagTxt.Value = "" Or PivotFrm.RandTxt.Value = "" Or _ PivotFrm.ColTxt.Value = "" Or PivotFrm.DateTxt.Value = "" Then PivotFrm.pivotBtn.Enabled = False Else PivotFrm.pivotBtn.Enabled = True End If

End Function

Private Sub pivotBtn_Click() campPag = PagTxt.Value campRow1 = RandTxt.Value campCol1 = ColTxt.Value campDta = DateTxt.Value

Call zona 'MsgBox zona ' '

Range("A2:I661").Select Application.CutCopyMode = False '

"'Lucru'!R1C1:R661C9").CreatePivotTable

TableDestination:="", TableName:=

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "'Lucru'!R1C1:R661C9").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select Charts.Add ActiveChart.Location Where:=xlLocationAsNewSheet ActiveWorkbook.ShowPivotTableFieldList = True With ActiveChart.PivotLayout.PivotTable.PivotFields(campPag) www.formare.ro http://slide pdf.c om/re a de r/full/supor t-c urs-exc e l-ava nsa t

42/46 42/46

5/7/2018



Tel/fax: 0248-262340

.Orientation = xlPageField .Position = 1 End With With ActiveChart.PivotLayout.PivotTable.PivotFields(campRow1) .Orientation = xlColumnField .Position = 1 End With With ActiveChart.PivotLayout.PivotTable.PivotFields(campCol1) .Orientation = xlRowField .Position = 1 End With ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _ PivotTable.PivotFields(campDta), "Sum of BUDGET", xlSum '---------------'

ActiveChart.PlotArea.Select

' Sheets("Tabel pivot").Select

End Sub

Function zona() As String Dim rand As Integer, coloana As Integer rand = 2: coloana = 1 Do While Sheets("Lucru").Cells(rand, 1) <> "" rand = rand + 1 Loop rand = rand - 1 Do While Sheets("Lucru").Cells(2, coloana) <> "" coloana = coloana + 1 Loop coloana = coloana - 1 zona = "A2:" + Chr(64 + coloana) + CStr(rand) www.formare.ro http://slide pdf.c om/re a de r/full/supor t-c urs-exc e l-ava nsa t

43/46 43/46

5/7/2018



Tel/fax: 0248-262340

zona = "R2C1:R" + CStr(rand) + "C" + CStr(coloana) ' R1C1:R661C9 'zona = "'Lucru'!" + zona End Function

Codul pentru crearea tabelului pivot l-am luat dintr-un macro.

In foaia de lucru numita ”Lucru” vom avea un tabel de date și un buton de comandă, conform figurii:

Codul aferent butonului de comandă este următorul: Private Sub BtnAfisareFormular_Click() On Error Resume Next Sheets("Tabel pivot").Delete PivotFrm.pivotBtn.Enabled = False i = 1 'Stabilim nr.de coloane nevide din foaia de lucru camp = ActiveSheet.Cells(2, i) Do While camp <> "" camp = ActiveSheet.Cells(2, i)


44/46 44/46

5/7/2018



Tel/fax: 0248-262340

i = i + 1 Loop r = i - 2 'Stabilim nr.de coloane pentru listBox PivotFrm.PivotLst.ColumnCount = 1 'Incarcam listBoxul cu date (aceste date sunt cele din capul de tabel) For k = 0 To r PivotFrm.PivotLst.AddItem (ActiveSheet.Cells(1, k + 1)) Next k

'Initializam campurile formularului PivotFrm.PagTxt.Value = campPag PivotFrm.RandTxt.Value = campRow1 PivotFrm.ColTxt.Value = campCol1 PivotFrm.DateTxt.Value = campDta

'Selectam primul element din listBox PivotFrm.PivotLst.ListIndex = 0 'PivotFrm.PivotLst.List(k) = Myarray()

PivotFrm.Show 'PivotFrm.Visible = True End Sub

Temă: identificați operațiunile executate pe fiecare rând de cod și inserați comentarii explicative.


45/46 45/46

5/7/2018



Tel/fax: 0248-262340

C U P R I N S

1. INTRODUCERE ...................................................................................................................... 1 2. Elemente de programare în VBA pentru EXCEL ....................................................................... 2 2.1. Bara de controale standard .................................................................................................... 2 2.2. Editorul VBA ........................................................................................................................ 6 2.3. Tipuri de date folosite în VBA .............................................................................................. 6 2.4. Linii de cod, proceduri, funcții, parametri ............................................................................ 7 2.5. Descrierea unui obiect........................................................................................................... 7 2.6. Foaia de calcul, proprietăţi, metode, evenimente................................................................ 11 2.7. Declararea variabilelor VBA .............................................................................................. 14 Concatenarea mai multor șiruri de caractere se realizeaza cu ajutorul operatorului ’+’ ......... 15 Conversia unui tip de variabilă (numeric, dată, etc.) în șir de caractere se realizeaza cu ajutorul intructiunii CStr(parametru). Conversia este necesară deoarece nu putem aduna un șir cu un număr. ....................................................................................................................................... 15 3. Instrucţiuni VBA ....................................................................................................................... 16 16 3.1. … End If ................................................................................... 3.2. Instrucţiunea Instrucţiunea de de decizie decizie If Select Case … End Select ............................................................ 17 3.3. Instrucţiunea repetitivă For … Next ................................................................................ 18 Tema: Scrierea rezultatului valoare într -o celulă a foii de calcul curente. ................................ 18 3.4. Instrucţiunea repetitivă Do While … Loop......................................................................... 19 3.5. Instrucţiunea repetitivă Do Until … Loop .......................................................................... 19 4. Funcţii și proceduri ................................................................................................................... 20 5. Lucrul cu liste si alte obiecte VBA ........................................................................................... 22 6. Aplicaţii VBA ........................................................................................................................... 25 6.1. Aplicaţia 1 – Determinarea zonei de date dintr-o foaie de calcul ....................................... 25 6.2. Aplicaţia 3 – Formatarea automată a unei zone de celule ................................................... 28 6.3. Aplicaţia 2 – Evidentierea unei celule selectate cu ajutorul mouse-lui .............................. 27 6.4. Aplicaţia 4 – Actualizarea dinamică a unui grafic .............................................................. 32 6.5. Aplicaţia 5 – Crearea unui configurator automat ................................................................ 30 6.6. Aplicaţia 6 – Crearea unui formular şi completarea automată a acestuia ........................... 34 6.7. Aplicaţia 7 – Inserarea de elemente grafice si dimensionarea dinamică a acestora ........... 37 7. Crearea de formulare pentru configurarea tabelelor pivot ........................................................ 40


46/46 46/46

Suport Curs Excel Avansat

Recommend Documents