VBA Excel: exemple de programe. Macrografice în Excel

Puțini oameni știu că prima versiune a produsului popular Microsoft Excel a apărut în 1985. De atunci, a fost supus unor modificări și este în căutare în rândul a milioane de utilizatori din întreaga lume. În același timp, mulți lucrează cu doar o mică parte din capabilitățile acestui procesor de masă și nici nu ghicesc cum ar putea face viața mai ușoară în abilitățile de programare Excel.

Ce este VBA?

Programarea în Excel se realizează prin limbajul de programare Visual Basic for Application, care este inițial încorporat în cel mai faimos procesor de masă de la Microsoft.

Pentru meritele sale, experții atribuie ușurința comparativă de dezvoltare. După cum arată practica, utilizatorii VBA pot master chiar și cei care nu au abilități de programare profesionale. Particularitatea VBA este executarea scenariului în mediul de aplicații de birou.

Dezavantajul programului sunt problemele asociate compatibilității diferitelor versiuni. Ele se datorează faptului că codul programului VBA se referă la funcționalitatea care este prezentă în noua versiune a produsului, dar nu este cea veche. De asemenea, dezavantajele includ deschiderea prea mare a codului pentru schimbare de către o persoană neautorizată. Cu toate acestea, Microsoft Office, precum și IBM Lotus Symphony, permit utilizatorului să utilizeze criptarea codului inițial și să stabilească o parolă pentru vizualizarea acestuia.

Obiecte, colecții, proprietăți și metode

Cu aceste concepte trebuie să înțelegeți cine va lucra în mediul VBA. În primul rând, este necesar să înțelegem ce este un obiect. În Excel, aceasta este o foaie, o carte, o celulă și o gamă. Aceste obiecte au o ierarhie specială, adică se supun reciproc.

Principala este aplicația, care corespunde programului Excel propriu-zis. Apoi urmați cărțile de lucru, foile de lucru și, de asemenea, gama. De exemplu, pentru a accesa celula A1 pe o anumită foaie, trebuie să specificați o cale care include ierarhia.

În ceea ce privește conceptul de "colectare", atunci acesta este un grup de obiecte din aceeași clasă care, în intrare, arată ca ChartObjects. Elementele sale individuale sunt, de asemenea, obiecte.

Următoarea noțiune este proprietățile. Ele reprezintă o caracteristică necesară a oricărui obiect. De exemplu, pentru interval, aceasta este valoarea sau formula.

Metodele sunt comenzi care arată ce trebuie făcut. Când scrieți cod în VBA, trebuie să le separați de obiect cu o perioadă. De exemplu, după cum se va arăta mai târziu, foarte des atunci când se programează în Excel, utilizați comanda Cells (1,1) .Selectați. Aceasta înseamnă că trebuie să selectați o celulă cu coordonate (1.1), adică A1.

Cu aceasta, Selection.ClearContents este adesea folosit. Efectuarea acesteia înseamnă ștergerea conținutului celulei selectate.

Cum să începem

Mai întâi, trebuie să creați un fișier și să îl salvați atribuindu-i un nume și selectând tipul de suport "Excel book with macro support".

Apoi trebuie să mergeți la aplicația VB, folosiți doar tastele "Alt" și "F11". următor:

• în bara de meniu din partea de sus a ferestrei, faceți clic pe pictograma de lângă pictograma Excel;
• selectați comanda Mudule;
• salvați făcând clic pe pictograma cu imaginea floppy disk;
• scrie, să zicem, o schiță a codului.

Se pare ca aceasta:

Subprogram ()

Codul nostru

End Sub

Rețineți că linia "Codul nostru" va fi evidențiată într-o altă culoare (verde). Motivul este în apostrof la începutul liniei, ceea ce indică faptul că urmează un comentariu.

Acum puteți scrie orice cod și crea un nou instrument pentru tine în VBA Excel (vezi exemplele de programe de mai jos). Desigur, cei care sunt familiarizați cu bazele Visual Basic vor fi mult mai ușor. Cu toate acestea, chiar și cei care nu le au, dacă doresc, se pot obișnui repede.

Macrografice în Excel

Pentru acest nume, programele scrise în limba Visual Basic for Application sunt ascunse. Astfel, programarea în Excel este crearea de macrocomenzi cu codul dorit. Datorită acestei caracteristici, procesorul de calcul tabelar Microsoft se dezvoltă singur, adaptându-se la cerințele unui anumit utilizator. După ce ați înțeles cum să creați module pentru scrierea de macrocomenzi, puteți începe să căutați exemple specifice de programe VBA Excel. Cel mai bine este să începeți cu cele mai de bază coduri.

Exemplul 1

Sarcina: scrieți un program care va copia valoarea conținutului unei celule și apoi scrie la altul.

Pentru a face acest lucru:

• deschideți fila "Vizualizare";
• accesați pictograma "Macrocomenzi";
• Apăsați pe "Înregistrare macro";
• completați formularul deschis.

Pentru simplificare, în „Macro Name“ lăsați „Makros1“ și în „tasta de apelare rapidă“, se introduce, de exemplu, HH (acest lucru înseamnă că puteți rula programul de exemplu va fi «Ctrl + h» echipa Blitz). Apăsați Enter.

Acum, când înregistrarea macro a început deja, copiați conținutul unei celule în alta. Reveniți la pictograma originală. Faceți clic pe "Înregistrare macro". Această acțiune înseamnă sfârșitul programului.

următor:

• mergeți din nou la linia "Macrocomenzi";
• în listă alegeți "Macro 1";
• faceți clic pe "Executați" (aceeași acțiune este pornită pornind comanda rapidă de la tastatură "Ctrl + hh").

Ca urmare, există o acțiune care a fost efectuată în timpul înregistrării macrocomenzii.

Este logic să vedem cum arată codul. Pentru a face acest lucru, reveniți la linia "Macrocomenzi" și faceți clic pe "Editare" sau pe "Conectare". Ca urmare, acestea se află în mediul VBA. De fapt, codul macro în sine se află între liniile Sub Macro1 () și End Sub.

Dacă copierea a fost efectuată, de exemplu, de la celula A1 la celula C1, atunci una din liniile de cod va arăta ca Range ("C1"). În traducere, seamănă cu "Range (" C1 "). Selectați", cu alte cuvinte, trece la VBA Excel, în celula C1.

Partea activă a codului este terminată de comanda ActiveSheet.Paste. Înseamnă înregistrarea conținutului celulei selectate (în acest caz A1) în celula selectată C1.

Exemplul 2

Ciclurile VBA vă ajută să creați diverse macro-uri în Excel.

Ciclurile VBA vă ajută să creați macrocomenzi diferite. Să presupunem că există o funcție y = x + x² + 3x³ - cos (x). Trebuie să creați o macrocomandă pentru a obține graficul. Puteți face acest lucru numai folosind buclele VBA.

Pentru valoarea inițială și finală a argumentului funcției ia x1 = 0 și x2 = 10. În plus, trebuie să introduceți o valoare constantă pentru pasul de modificare a argumentului și a valorii inițiale a contorului.

Toate exemplele de macrocomenzi VBA Excel sunt create folosind aceeași procedură ca mai sus. În acest caz particular, codul arată astfel:

Sub programm ()

x1 = 1

x2 = 10

shag = 0,1

i = 1

În timp ce x1 < x2 (bucla va fi executată în timp ce expresia x1 este adevărată < x2)

y = x1 + x1 ^ 2 + 3 * x1 ^ 3 - Cos (x1)

Celulele (i, 1) .Value = x1 (valoarea x1 este înscrisă în celula cu coordonatele (i, 1))

Celulele (i, 2) .Value = y (valoarea y este scrisă în celula cu coordonate (i, 2))

i = i + 1 (funcționează contorul) -

x1 = x1 + shag (argumentul este modificat de dimensiunea pasului) -

buclă

End Sub.

Ca rezultat al rularea acestei macrocomenzi în "Excel" primim două coloane, primul din care conține valori pentru x, iar în al doilea - pentru y.

Apoi ei complot grafic într-un mod care este standard pentru Excel.

Exemplul 3

Pentru implementarea ciclurilor în VBA Excel 2010, ca și în alte versiuni, împreună cu construcția Do Do, deja utilizată, este folosită.

Luați în considerare programul care va crea coloana. În fiecare dintre celulele sale se vor scrie pătratele numărului de linie corespunzător. Folosind constructul Pentru vă va permite să îl scrieți foarte curând, fără a utiliza un contor.

Mai întâi trebuie să creați o macrocomandă, așa cum este descris mai sus. Apoi, scriem codul în sine. Credem că suntem interesați de valorile pentru 10 celule. Codul arată așa.

Pentru i = 1 până la 10 Înainte

Comanda este tradusă în limba "umană", cum ar fi "Repetați de la 1 la 10 în pași de una".

Dacă sarcina este de a obține o coloană cu pătrate, de exemplu, toate numerele impare din intervalul de la 1 la 11, atunci vom scrie:

Pentru i = 1 la 10 pasul 1 Următorul.

Pasul de aici este un pas. În acest caz, este egal cu două. În mod implicit, absența acestui cuvânt în buclă înseamnă că pasul este unic.

Rezultatele obținute trebuie păstrate în celulele cu numărul (i, 1). Apoi, de fiecare dată când porniți ciclul cu mărirea lui i cu mărimea pasului, numărul rândului va crește automat. Astfel, codul va fi optimizat.

În general, codul va arata astfel:

Subprogram ()

Pentru i = 1 până la 10 Pasul 1 (puteți scrie pur și simplu pentru i = 1 până la 10)

Celulele (i, 1) .Value = i ^ 2 (adică valoarea pătratului i) este scrisă în celula (i, 1)

În continuare (într-un anumit sens joacă rolul unui contor și înseamnă un început de ciclu)

End Sub.

Dacă este făcută corect, inclusiv înregistrarea și funcționare macro (vezi. Instrucțiunile de mai sus), atunci aceasta se numește de fiecare dată când o anumită mărime se va obține coloana (în acest caz, care constă din 10 celule).

Exemplul 4

În viața de zi cu zi, de cele mai multe ori este nevoie să luați o decizie în funcție de un anumit tip de condiție. Nu puteți face fără ele în VBA Excel. Exemple de programe în care este aleasă calea de execuție a algoritmului, mai degrabă decât predefinite inițial, utilizează cel mai adesea structura If hellip-Then (pentru cazuri complexe) Dacă hellip-Atunci hellip-END Dacă.

Să luăm în considerare un caz concret. Să presupunem că trebuie să creați o macro pentru Excel, astfel încât celula cu coordonatele (1,1) să fie scrisă:

1, dacă argumentul este pozitiv -

0, în cazul în care argumentul de zero-

-1 dacă argumentul este negativ.

Crearea unei astfel de macro-uri pentru Excel este pornită în mod standard, prin utilizarea tastelor "fierbinți" Alt și F11. Apoi se scrie următorul cod:

Subprogram ()

x = Celule (1, 1) .Value (această comandă atribuie valoarea conținutului celulei cu coordonatele (1, 1))

Dacă x> 0 Atunci celulele (1, 1) .Value = 1

Dacă x = 0 atunci celulele (1, 1) Valoare = 0

Dacă x<0 Atunci celulele (1, 1) .Value = -1

End Sub.

Rămâne să rulați macroul și să obțineți valoarea corectă pentru argumentul din Excel.

Funcții VBA

După cum probabil ați observat, programarea în cel mai faimos procesor de masă al Microsoft nu este atât de dificilă. Mai ales dacă învățați cum să utilizați funcțiile VBA. În total, acest limbaj de programare, creat special pentru scrierea de aplicații în Excel și Word, are aproximativ 160 de funcții. Ele pot fi împărțite în mai multe grupuri mari. Acestea sunt:

• Funcțiile matematice. Aplicându-le la argument, obțineți valoarea cosinusului, logaritmul natural, întreaga parte și așa mai departe.
• Funcții financiare. Datorită disponibilității și utilizării programării în Excel, puteți obține instrumente eficiente pentru contabilitate și contabilitate financiară.
• Funcțiile de procesare a arrayului. Acestea includ Array, IsArray-LBound-UBound.
• Funcțiile VBA Excel pentru un șir. Acesta este un grup destul de mare. Acesta include, de exemplu, funcțiile Spațiu pentru a crea un șir cu un număr de spații egal cu un argument întreg, sau Asc pentru a traduce caracterele în codul ANSI. Toate acestea sunt utilizate pe scară largă și vă permit să lucrați cu șiruri de caractere în Excel, creând aplicații care facilitează foarte mult lucrul cu aceste mese.
• Funcții de conversie de tip de date. De exemplu, CVar returnează valoarea argumentului Expression, transformându-l într-un tip de date Variant.
• Funcțiile de lucru cu datele. Acestea extind semnificativ caracteristicile standard ale programului Excel. Deci, funcția WeekdayName returnează numele (totală sau parțială) a zilei săptămânii prin numărul său. Chiar mai util este Timer. Acesta dă numărul de secunde care au trecut de la miezul nopții până la momentul specific al zilei.
• Funcții pentru conversia unui argument numeric la sisteme de numere diferite. De exemplu, Oct emite o reprezentare octală a unui număr.
• Funcțiile de formatare. Cel mai important dintre acestea este Format. Returnează o valoare de tip Variant cu o expresie formatată conform instrucțiunilor specificate în descrierea formatului.
• și altele.

Studiul proprietăților acestor funcții și aplicarea lor va extinde în mod semnificativ domeniul de aplicare al programului Excel.

Exemplul 5

Să încercăm să rezolvăm probleme mai complicate. De exemplu:

Având în vedere un document pe hârtie al raportului privind nivelul real al costurilor întreprinderii. necesită:

• să dezvolte o parte a șablonului cu ajutorul unui procesor de masă "Excel";
• creați un program VBA care va interoga datele sursă pentru al umple, va efectua calculele necesare și le va umple cu celulele șablon corespunzătoare.

Luați în considerare una dintre soluții.

Crearea unui șablon

Toate acțiunile sunt efectuate pe o foaie standard în Excel. Rezervate celule libere pentru introducerea datelor pe luna, an, numele companiei-consumator, valoarea costurilor, nivelul acestora, cifra de afaceri. Întrucât numărul companiilor (societăților) pentru care se întocmește un raport nu este fix, celulele pentru realizarea valorilor bazate pe rezultate și numele specialistului nu sunt rezervate anterior. Foaia de lucru are un nou nume. De exemplu, "Օ tchet".

variabile

Pentru a scrie un program pentru a umple automat șablonul, trebuie să selectați simbolurile. Acestea vor fi folosite pentru variabile:

• Numărul NN al rândului curent al mesei;
• TP și TF - cifra de afaceri planificată și reală;
• SF și SP - suma reală și planificată a costurilor;
• IP și IF - nivelul planificat și real al costurilor.

Indicați aceleași litere, dar cu "prefix" Itog acumularea totalului pentru această coloană. De exemplu, ItogTP se referă la coloana unui tabel intitulat "cifra de afaceri planificată".

Rezolvarea problemelor folosind programarea VBA

Folosind notația introdusă, obținem formule pentru abateri. Dacă doriți să calculați în%, aveți (F - P) / P * 100, iar în suma - (F - P).

Rezultatele acestor calcule pot fi cel mai bine adăugate imediat celulelor corespunzătoare din tabelul Excel.

Rezultatele de fapt și prognoza se obțin prin formulele ItogP = ItogP + P și ItogF = ItogF + F.

Pentru abateri utilizează = (ItogF - ItogP) / ItogP * 100, dacă calculul este în procente, iar în cazul valorii totale - (ItogF - ItogP).

Rezultatele sunt imediat scrise la celulele corespunzătoare, deci nu este nevoie să le atribuiți variabilelor.

Înainte de a rula programul creat, trebuie să salvați registrul de lucru, de exemplu, numit "Report1.xls".

Butonul Creare tabel de raport trebuie să fie apăsat doar o dată după introducerea informațiilor antetului. Ar trebui să știi alte reguli. În mod special, butonul "Adăugați linia" trebuie să fie apăsat de fiecare dată după ce introduceți valori pentru fiecare activitate din tabel. După introducerea tuturor datelor, trebuie să faceți clic pe butonul "Terminare" și apoi să treceți la fereastra Excel.

Acum știi cum să rezolvi sarcinile pentru Excel folosind macrocomenzi. Capacitatea de a utiliza VBA Excel (vezi exemple de programe. Mai sus) și va trebui să lucreze în mediul cel mai popular în acest moment, editorul de text „Word“. În special, prin înregistrarea, așa cum se arată la începutul acestui articol, sau prin scrierea de cod pentru a crea butoanele de meniu prin care multe dintre operațiile asupra textului se poate face prin apăsarea tastelor de serviciu sau prin „View“ pictogramă și fila „Macrocomenzi“.