Crearea funcțiilor particularizate în Excel

Funcțiile particularizate, cum ar fi macrocomenzile, utilizează limbajul de programare Visual Basic for Applications (VBA ). Acestea diferă de macrocomenzi în două moduri semnificative. Mai întâi, utilizează proceduri Function în locul procedurilor Sub . Aceasta înseamnă că încep cu o instrucțiune Function în loc de o instrucțiune Sub și se termină cu Funcția End în loc de End Sub. În al doilea rând, efectuează calcule în loc să efectueze acțiuni. Anumite tipuri de instrucțiuni, cum ar fi instrucțiunile care selectează și formatează zone, sunt excluse din funcțiile particularizate. În acest articol, veți afla cum să creați și să utilizați funcții particularizate. Pentru a crea funcții și macrocomenzi, lucrați cu Visual Basic Editor (VBE), care se deschide într-o fereastră nouă, separată de Excel.

Să presupunem că firma dvs. oferă o reducere de cantitate de 10 procente la vânzarea unui produs, cu condiția ca comanda să fie pentru mai mult de 100 de unități. În paragrafele următoare, vom demonstra o funcție pentru a calcula această reducere.

Exemplul de mai jos afișează un formular de comandă care listează fiecare articol, cantitate, preț, reducere (dacă există) și prețul extins rezultat.

Pentru a crea o funcție DISCOUNT particularizată în acest registru de lucru, urmați acești pași:

1. Apăsați Alt+F11 pentru a deschide Editor Visual Basic (pe Mac, apăsați FN+ALT+F11), apoi faceți clic pe Inserare > modul. O fereastră nouă de modul apare în partea dreaptă a Editor Visual Basic.

2. Copiați și lipiți următorul cod în noul modul.

   Function DISCOUNT(quantity, price)
      If quantity >=100 Then
        DISCOUNT = quantity * price * 0.1
      Else
        DISCOUNT = 0
      End If
    
    DISCOUNT = Application.Round(Discount, 2)
   End Function
   

Acum sunteți gata să utilizați noua funcție DISCOUNT. Închideți Editor Visual Basic, selectați celula G7 și tastați următoarele:

=DISCOUNT(D7,E7)

Excel calculează reducerea de 10 procente pentru 200 de unități la 47,50 lei pe unitate și returnează 950,00 lei.

În prima linie a codului VBA, Function DISCOUNT(cantitate, preț), ați indicat că funcția DISCOUNT necesită două argumente, cantitate șipreț. Atunci când apelați funcția într-o celulă din foaia de lucru, trebuie să includeți aceste două argumente. În formula =DISCOUNT(D7,E7), D7 este argumentul cantitate și E7 este argumentul preț . Acum puteți copia formula DISCOUNT la G8:G13 pentru a obține rezultatele afișate mai jos.

Să luăm în considerare modul în care Excel interpretează această procedură funcțională. Când apăsați pe Enter, Excel caută numele DISCOUNT în registrul de lucru curent și constată că este o funcție particularizată într-un modul VBA. Numele argumentelor încadrate între paranteze, cantitate și preț sunt substituenți pentru valorile pe care se bazează calculul reducerii.

Instrucțiunea If din următorul bloc de cod examinează argumentul cantitate și determină dacă numărul de articole vândute este mai mare sau egal cu 100:

If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Dacă numărul de articole vândute este mai mare sau egal cu 100, VBA execută următoarea instrucțiune, care înmulțește valoarea cantitate cu valoarea prețului , apoi înmulțește rezultatul cu 0,1:

Discount = quantity * price * 0.1

Rezultatul este stocat ca reducere variabilă. O instrucțiune VBA care stochează o valoare într-o variabilă se numește instrucțiune de atribuire , deoarece evaluează expresia din partea dreaptă a semnului egal și atribuie rezultatul numelui variabilei din stânga. Deoarece variabila Reducere are același nume ca procedura funcției, valoarea stocată în variabilă este returnată formulei foii de lucru care se numește funcția DISCOUNT.

Dacă cantitatea este mai mică decât 100, VBA execută următoarea instrucțiune:

Discount = 0

În sfârșit, următoarea instrucțiune rotunjește valoarea atribuită variabilei Discount la două zecimale:

Discount = Application.Round(Discount, 2)

VBA nu are funcția ROUND, dar Excel o are. Prin urmare, pentru a utiliza ROUND în această instrucțiune, spuneți VBA să caute metoda Round (funcția) în obiectul Application (Excel). Puteți face acest lucru adăugând cuvântul Aplicație înainte de cuvântul Round. Utilizați această sintaxă oricând trebuie să accesați o funcție Excel dintr-un modul VBA.

O funcție particularizată trebuie să înceapă cu o instrucțiune Function și să se termine cu o instrucțiune End Function. În plus față de numele funcției, instrucțiunea Function specifică de obicei unul sau mai multe argumente. Totuși, puteți crea o funcție fără argumente. Excel include mai multe funcții predefinite, de exemplu RAND și NOW, care nu utilizează argumente.

După instrucțiunea Function, o procedură de funcție include una sau mai multe instrucțiuni VBA care iau decizii și efectuează calcule utilizând argumentele transmise funcției. În sfârșit, undeva în procedura de funcție, trebuie să includeți o instrucțiune care atribuie o valoare unei variabile cu același nume ca funcția. Această valoare este returnată formulei care apelează funcția.

Numărul de cuvinte cheie VBA pe care le puteți utiliza în funcții particularizate este mai mic decât numărul pe care îl puteți utiliza în macrocomenzi. Funcțiilor particularizate nu li se permite să facă altceva decât să returneze o valoare unei formule dintr-o foaie de lucru sau unei expresii utilizate în altă macrocomandă sau funcție VBA. De exemplu, funcțiile particularizate nu pot redimensiona ferestrele, nu pot edita o formulă dintr-o celulă sau nu pot modifica opțiunile de font, culoare sau model pentru textul dintr-o celulă. Dacă includeți cod de "acțiune" de acest tip într-o procedură de funcție, funcția returnează #VALUE! eroare.

Singura acțiune pe care o poate efectua o procedură de funcție (în afară de efectuarea calculelor) este afișarea unei casete de dialog. Puteți utiliza o instrucțiune InputBox într-o funcție particularizată ca mijloc de intrare de la utilizatorul care execută funcția. Puteți utiliza o instrucțiune MsgBox ca mijloc de a transmite informații utilizatorului. De asemenea, puteți utiliza casete de dialog particularizate sau Formulare utilizator, dar acesta este un subiect dincolo de domeniul acestei introduceri.

Chiar și macrocomenzile simple și funcțiile particularizate pot fi dificil de citit. Le puteți face mai ușor de înțeles tastând text explicativ sub formă de comentarii. Adăugați comentarii precedând textul explicativ cu un apostrof. De exemplu, următorul exemplu arată funcția DISCOUNT cu comentarii. Adăugarea de comentarii ca acestea vă ajută pe dvs. sau pe alții să vă păstrați codul VBA pe măsură ce trece timpul. Dacă trebuie să efectuați o modificare la cod în viitor, veți înțelege mai ușor ce ați făcut inițial.

Un apostrof spune programului Excel să ignore totul din partea dreaptă, pe aceeași linie, astfel încât să puteți crea comentarii pe linii fie pe rând, fie în partea dreaptă a liniilor care conțin cod VBA. Puteți începe un bloc relativ lung de cod cu un comentariu care explică scopul său general, apoi să utilizați comentarii în linie pentru a documenta instrucțiuni individuale.

O altă modalitate de a vă documenta macrocomenzile și funcțiile particularizate este să le dați nume descriptive. De exemplu, în loc să denumiți o etichetă de macrocomandă, o puteți denumi MonthLabels pentru a descrie mai precis scopul pe care îl servește macrocomanda. Utilizarea numelor descriptive pentru macrocomenzi și funcții particularizate este utilă mai ales atunci când ați creat mai multe proceduri, în special atunci când creați proceduri care au scopuri similare, dar nu identice.

Modul în care vă documentați macrocomenzile și funcțiile particularizate este o chestiune de preferință personală. Important este să adoptați o anumită metodă de documentație și să o utilizați în mod unitar.

Pentru a utiliza o funcție particularizată, registrul de lucru care conține modulul în care ați creat funcția trebuie să fie deschis. Dacă registrul de lucru nu este deschis, primiți un #NAME? atunci când încercați să utilizați funcția. Dacă faceți referire la funcție într-un alt registru de lucru, trebuie să precedați numele funcției cu numele registrului de lucru în care se află funcția. De exemplu, dacă creați o funcție numită DISCOUNT într-un registru de lucru numit Personal.xlsb și o apelați din alt registru de lucru, trebuie să tastați =personal.xlsb!discount(), nu pur și simplu =discount().

Puteți salva unele secvențe de taste (și posibile erori de tastare) selectând funcțiile particularizate din caseta de dialog Inserare funcție. Funcțiile dvs. particularizate apar în categoria Definit de utilizator:

O modalitate mai simplă de a face funcțiile particularizate disponibile în permanență este să le stocați într-un registru de lucru separat, apoi să salvați registrul de lucru ca program de completare. Apoi puteți face programul de completare disponibil oricând rulați Excel. Iată cum să procedați:

După ce urmați acești pași, funcțiile particularizate vor fi disponibile de fiecare dată când rulați Excel. Dacă doriți să adăugați la biblioteca de funcții, reveniți la Editor Visual Basic. Dacă vă uitați în Visual Basic Editor Project Explorer sub un titlu VBAProject, veți vedea un modul denumit după fișierul programului de completare. Programul de completare va avea extensia .xlam.

Dacă faceți dublu clic pe modulul respectiv în Project Explorer, Visual Basic Editor va afișa codul de funcție. Pentru a adăuga o funcție nouă, poziționați punctul de inserare după instrucțiunea End Function care termină ultima funcție din fereastra Cod și începeți să tastați. Puteți crea oricâte funcții aveți nevoie în acest mod și acestea vor fi întotdeauna disponibile în categoria Definit de utilizator din caseta de dialog Inserare funcție .

Acest conținut a fost inițial creat de Mark Dodge și Craig Stinson, ca parte a cărții lor Microsoft Office Excel 2007 Inside Out. De atunci, a fost actualizată pentru a se aplica și la versiunile mai noi de Excel.