Macrodefinitii si elemente de programare VBA in Excel

Macrodefinitii si elemente de programare VBA in Excel

Macrodefintiile reprezinta functii definite de utilizator pentru a automatiza o serie de operatii cu grad ridicat de utilizare.

Utilizarea macrodefiniitiilor trebuie precedata de stabilirea nivelului de securitate in cadrul aplicatiei Excel. Astfel aplicatia permite rularea de macrodefinitii care nu sunt semnate si autentificate de catre producator. Pentru a modifica nivelul de securitate se deschide meniulTools > Macro > Securitysi se alege nivelulLowsauMedium.

Cel mai simplu mod de a crea o macrodefinitie este de a inregistra operatia efectuata de catre utilizator. Aplicatia Excel permite acest lucru prin optiuneaTools > Macro > Record New Macro. Figura1descrie parametrii ce trebuie definiti la inregistrarea unei macrodefinitii noi.

Figura 1. Definirea parametrilor unei macrodefinitii inregistrate.

Odata intrat in modul de inregistrare, lucru indicat de aparitia barei deStop,, utilizatorul executa operatiile de inclus in macrodefinitie.

In aceasta etapa trebuie acordata atentie scopului macrodefinitiei. Daca se doreste inregistrarea unei operatii cu caracter general care sa fie aplicata oricarei alte selectii de celule, atunci utilizatorul trebuie sa evite selectia de celule sau foi de lucru in momentul inregistrarii macrodefinitiei, deoarece aceasta va fi aplicata la executie tot pe acelasi set de celule. Din acest motiv, anterior realizarii macrodefinitiei este indicat ca utilizatorul sa selecteze celula asupra careia va aplica operatiile cu caracter general.

Pentru exemplul luat in considerare, macrodefinitaFormatare, se selecteaza anterior momentului de start al inregistrarii oricare celula din foaia de lucru. Inregistrarea va contine doar operatiile de centrare a valorii celulei, precum si cele de aplicare a efectelorboldsiitalic.

Se observa din figura2ca s-a asociat macrodefinitiei si un short-cutCtrl+Shift+Fcare sa permita executia rapida a acesteia prin intermediul tastaturii.

Pentru a vizualiza sau modifica codul din spatele macrodefinitiei se deschide biblioteca de macrodefinitii asociata registrului de lucru curent prin optiuneaTools > Macro > Macros.

Figura 2. Colectia de macrodefinitii din registrul de lucru.

Pentru macrodefinitaFormatarecodul inregistrat este:

Sub Formatare()

'

' Formatare Macro

' Formatare celule prin centrare text si aplicare efecte de tip bold, italic.

'

' Keyboard Shortcut: Ctrl+Shift+F

'

With Selection

.HorizontalAlignment = xlCenter 'Efect centrare

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Font.Bold = True 'Efect bold

Selection.Font.Italic = True 'Efect italic

End Sub

Din codul descris anterior se observa ca macrodefinitia este o functie scrisa in Visual Basic.

Se defineste functia utilizator numitaPrimaCraciunce ofera angajatilor unei firme, un bonus a carui valoarea este determinata in functie de nivelul salariului, vechime si numarul de copii.

Editarea functiei se realizeaza prin intermediul editorului Visual Basic atasat aplicatiei Excel. Deschiderea acestuia se realizeaza prinTools > Macro > Visual Basic Editor, figura3.

Figura 3. Mediul de lucru din Visual Basic Editor.

Inainte de a declara functiile utilizatorului, trebuie definit un modul care sa contina aceste functii. Pentru aceasta se insereaza in proiect un modul de functii prinInsert > Moduledin meniul Editorului Visual Basic.

In zona de cod a acestuia se defineste functiaPrimaCraciun()al carui cod este:

Function PrimaCraciun(salariu, vechime, numarCopii, primaCopil)

If vechime < 10 Then

PrimaCraciun = 0.1 * salariu

Else

If vechime >= 10 And vechime < 15 Then

PrimaCraciun = 0.15 * salariu

Else

spor = 0.25 * salariu

End If

End If

PrimaCraciun = PrimaCraciun + numarCopii * primaCopil

End Function

Dupa cum se observa salariatul primeste o prima de craciun egala cu sporul de vechime plus o prima acordata pentru fiecare copil al acestuia.

Sporul de vechime este egal cu 10% din salariu de baza daca salariatul are o vechime mai mica de 10 ani, 15% pentru o vechime mai mica de 15 ani si 25% pentru o vechime mai mare de 15 ani.

Macrodefinitiile sunt accesibile in interiorul registrului de lucru prin intermediul optiuniiInsert > Functionsi apoi selectand categoria de functiiUser Defined.

Figura 4. Inserarea unei functii utilizator.

De asemenea, utilizatorul are posibilitatea de a crea si utiliza formulare de controale prin intermediul carora sa insereze aplicatii VBA in Excel. Pentru a exemplifica se considera formularul ce permite:

inserarea a doua valori;

adunarea, respectiv, scaderea valorilor cu afisarea rezultatului;

inserarea rezultatului operatiei in celula activa din foaia de lucru;

inchiderea formularului.

Pentru a realiza acest lucru, in Editorul Visual Basic:

1. se insereaza un formular care sa contina controalele prin intermediul carora sa fie citite valorile de la tastatura, sa fie aleasa operatia si sa fie afisate rezultatele; formularul este descris in figura5; formularul este creat prin optiuneaInsert > UserForma meniului editorului VisualBasic;

Figura 5. Generarea unui formular de catre utilizator.

1. din caseta de controale predefinite,ToolBox,se pun pe formular componentele necesare realizarii operatiilor, figura6.

Figura 6. Formular utilizator.

1. se adauga cod Visual Basic pentru cele patru butoane existente in formular; definirea codului pentru diferitele componente din formular se face realizand cu mouse-ul dublu clic pe elementul respectiv; in fereastra de cod devenita vizibila, figura7, se scriu secventele de cod asociate operatiilor dorite; se observa ca editorul Visual Basic asociaza implicit o functie fiecarei componente, pentru butoane selectand functia aferenta evenimentului de selectie a butonului;

Figura 7. Editorul de cod din Visual Basic.

1. pentru butonul de adunare, numitCommandButton1, se adauga functia:

Private Sub CommandButton1_Click()

suma = Int(TextBox1) + Int(TextBox2)

TextBox3 = suma

End Sub

se observa ca numele TexBox-ului aferent primei valori se numesteTextBox1, pentru ce de-a doua valoarea se numesteTextBox2si rezultatul este trecut inTextBox3;

pentru butonul de scadere, numitCommandButton3, se adauga functia:

Private Sub CommandButton3_Click()

diferenta = Int(TextBox1) - Int(TextBox2)

TextBox3 = diferenta

End Sub

pentru butonul de inchidere a formularului, numitCommandButton2, se adauga functia:

Private Sub CommandButton2_Click()

UserForm1.Hide

End Sub

pentru butonul de salvare a rezultatului in celula activa a foii de lucru curente, butonulCommandButton4, codul functiei este:

Private Sub CommandButton4_Click()

Application.ActiveCell.Value = Int(TextBox3)

With ActiveCell.Font

.Bold = True

.Italic = False

.Color = RGB(255, 0, 0)

End With

End Sub

ultima functie pune valoarea rezultatului in celula activa si formateaza aceasta celula.

Odata creat formularul si definite rutinele din spatele componentelor sale, acesta trebuie sa fie activat din foaia de lucru pentru a permite utilizarea sa; pentru acest lucru se insereaza in foaia de lucru curenta un buton care sa deschida formularul construit anterior:

1. in foaia de lucru curenta se activeaza bara de controale,View >Toolbars > Control ToolBox,, sau din bara de editareVisual Basicse selecteaza butonul;

2. se insereaza in foaia de lucru un control de tipCommandButton;

3. prin butonuldin baraControl ToolBoxse deschide pagina de proprietati a butonului creat si se modifica valoarea proprietatiiCaptioninFORMULAR;

4. se defineste codul asociat butonului; pentru a deschide editorul de cod fie se executa dublu clic pe buton fie se selecteaza butonulView Code,, din baraControl ToolBox;

5. codul butonului este

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

1. se iese din modul editare, deselectand butonul, din baraControl ToolBoxsauVisual Basic;

2. se selecteaza celula in care sa fie inserata noua valoare;

3. se activeaza formularul;

4. se salveaza rezultatul operatiei in celula activa.

Figura8descrie seria de sapte etape ce trebuie realizate pentru a implementa in foia de lucru Excel formularul utilizatorului.

Figura 8. Implementarea formular utilizator in foaia de lucru.