Suport Curs Excel Avansat

@2009 SC INTERCONSULT 2001 SRL – Suport curs EXCEL Avansat (nov.09) Tel/fax: 0248-262340

www.formare.ro 1/46

http://www.formare.ro/


www.formare.ro 2/46

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.

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

1 2 3 4 5 6 7 8 9 11 12 13 14 15 10



www.formare.ro 3/46

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.

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

Numele şi tipul obiectului pentru

care sunt afişate proprietăţile

Valoarea unui

parametru(proprietate)

Numele parametrului

( proprietăţii )



www.formare.ro 4/46

controale. 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 folosit 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 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 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ă

Lista derulantă



www.formare.ro 5/46

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 6/46

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

vizualizare conţine: o bară de titlu, o bară de meniuri, o bară standard şi două cadrane. În

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.

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ă Descriere Operaţii permise

Integer (nr.întreg) tip de dată număr întreg cuprins între

–32.768 şi +32.767

adunarea, scăderea, înmulţirea,

împărţirea, atribuirea

Cadranul foilor de

calcul

Cadran de editare

cod VBA

Lista cu obiecte

Lista

evenimentelor

obiectului



www.formare.ro 7/46

Tip dată Descriere Operaţii permise

Long tip de dată număr întreg cuprins între

– 2.147.483.648 şi +2.147.483.647

adunarea, scăderea, înmulţirea,

împărţirea, atribuirea

Single (simplă

precizie)

tip de dată număr real (maxim 8 zecimale) Idem

String (șir)

Tip de dată şir de caractere atribuire, concatenarea

(adunarea) şi trunchierea

Boolean (logic)

tip de dată logică şi este format din

mulţimea { TRUE (ADEVĂRAT) şi

FALSE (FALS)}

operaţii logice (AND, OR, etc)

şi atribuire

Object (obiect)

tip de dată complex

Variant

tip 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 limbajul 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 care 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” care 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 numesc 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.



www.formare.ro 8/46

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,

Observăm că proprietatea unui obiect este separată de numele obiectului printr-un punct.

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

proprietate/parametru

Descriere

BackColor, Color,

ColorIndex

Acesti parametri indică culoarea obiectului; de obicei culoarea

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



www.formare.ro 9/46

Nume

proprietate/parametru

Descriere

atunci obiectul este printabil; Dacă valoarea este False atunci

obiectul nu este printabil (nu apare la imprimantă).

Top Reprezintă coordonata (de pe axa Y) a colţului stânga sus a

obiectului; Acest parametru este de tipul Single.

Width Reprezintă lungimea obiectului şi este un parametru de tipul

Single.

Visible Stabileşte 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 )



www.formare.ro 10/46

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ă




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

Nume eveniment Descriere

Activate

Evenimentul este declanșat la activarea unui obiect (ex.

Deschiderea unei foi de calcul)

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

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, metode, 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 Descriere

Name Acest parametru indică numele foi respective;

Cells(i , j) 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.




Î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ă:

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 Descriere

Activate Realizează activarea unei foi de calcul;

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

Delete Realizează ştergerea unei foi de calcul;

Proprietate a

foii de calcul




Nume metoda Descriere

PrintOut From,

To, Copies

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;

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 Descriere

Activate Ruleaza codul la activarea unei foi de calcul;

SelectChange Ruleaza codul la selectarea unei celule sau zone

Metodă a foii

de calcul




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.




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 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 …




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

Bloc_instrucţiuni (a)

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




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 are următoarea sintaxă:

Select 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




Case 10 To 49: MsgBox "Valoarea este in intervalul 10-49"



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.




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()


i = 1

Do While valoare <= 5

valoare = valoare + i

i=i+1

Loop


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()





valoare = 1

Do Until valoare = 20

valoare = valoare + 1

Loop


End Sub

Raționamentul este similar cu cel al exemplului prcedent.

4. Proceduri și funcţ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 funcț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




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




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




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


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:




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




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

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 vom alege CommandButton1, iar din lista de

evenimente corespunzătoare lui vom alege evenimentul Click.

Tabela de proprietăţi

(parametri)

Butonul de comandă cu

parametrul Caption

schimbat

Parametrul Caption al

butonului de comandă a fost

schimbat din

CommandButton1 în Afisează




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

Controlul CommandButton1

Lista derulantă cu obiectele care

se află pe prima foaie de calcul

Declararea evenimentului Click

corespunzător controlului

CommandButton1




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 + "'"




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



With Selection.Interior

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With




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.Italic = False


With Selection.Interior

.Color = vbYellow

End With

With Selection.Font

.Color = vbRed




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 echipament

(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 butonul "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"




x = ActiveCell.Row ' x = rândul celulei active

y = ActiveCell.Column ' y = coloana celulei active

If y <> 1 Then

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

End If

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




'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 celule ce

vor fi totalizate

Range(sel).Select ' este selectatã zona de celule după care se va face totalul

Range(adresa).Activate 'Se activează celula unde va fi scris totalul

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




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

Utilizând cunoștințele acumulate până acum, vom 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.:


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

.HasTitle = True

.ChartTitle.Characters.Text = "Grafic productie" '

titlul graficului

.Axes(xlCategory, xlPrimary).HasTitle = True

.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =

"Zile" 'explicaţia de pe abcisă (categorie)




.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


ListBox1.ColumnCount = 3

Call numar_inr(nrinreg)

zona = ("a1:c" & CStr(nrinreg))

ListBox1.List() = Foaie2.Range(zona).Value

Foaie1.Select

End Sub





'citirea datelor se face din list box, coloana 1, 2 sau 3

ListBox1.BoundColumn = 1

LbNume.Caption = ListBox1.Value


Lbsectia.Caption = ListBox1.Value


LbSalariu.Caption = ListBox1.Value

End Sub


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:




Câmpuri care vor fi

completate(celule care vor fi

completate)

Butonul BtnTipar Butonul BtnFormular




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 numerice.

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


'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




If maxim <= ActiveSheet.Cells(1, 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




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




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)




DateTxt.Text = ""

DateBtn.Caption = "<-"

Else

DateTxt.Text = PivotLst.Text


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


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


RandBtn.Caption = "->"

End If

Call validare

End Sub

Function validare()




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)




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




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)




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.




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

3.1. Instrucţiunea de decizie If … End If ................................................................................... 16 3.2. Instrucţiunea de decizie 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


Date post:	29-Nov-2015
Category:	Documents
Upload:	ivan-cristina
View:	98 times
Download:	9 times

Suport Curs Excel Avansat

Documents