VBA Excel: primjeri programa. Makronaredbe u programu Excel

Malo ljudi zna da je prva verzija popularnog programa Microsoft Excel pojavila 1985. godine. Od tada je prošla nekoliko izmjena i potražnja među milijunima korisnika širom svijeta. Istodobno, mnogi rade sa samo malim dijelom mogućnosti ovog stolnog procesora i čak ne pogađaju kako bi mogli olakšati život u Excel programskim vještinama.

Što je VBA?

Programiranje u programu Excel izvodi se kroz programski jezik Visual Basic for Application, koji je izvorno ugrađen u najpoznatiji stolni procesor tvrtke Microsoft.

Svojim zaslugama, stručnjaci priznaju komparativnu jednostavnost razvoja. Kao što pokazuje praksa, korisnici VBA mogu svladati i one koji nemaju profesionalne vještine programiranja. Posebnost VBA je izvršavanje skripte u okruženju uredskih aplikacija.

Nedostatak programa su problemi povezani s kompatibilnošću različitih verzija. Oni su zbog činjenice da se kod programa VBA odnosi na funkcionalnost koja je prisutna u novoj verziji proizvoda, ali nije u starijoj. Također, nedostaci uključuju preveliku otvorenost koda za promjenu neovlaštene osobe. Ipak, Microsoft Office, kao i IBM Lotus Symphony, omogućuju korisniku šifriranje početnog koda i postavljanje lozinke za gledanje.

Objekti, zbirke, svojstva i metode

S tim je konceptima potrebno razumjeti tko će raditi u VBA okruženju. Prije svega, potrebno je razumjeti što je objekt. U programu Excel, to je list, knjiga, ćelija i raspon. Ovi objekti imaju posebnu hijerarhiju, tj. poslušajte jedni druge.

Glavna je aplikacija, koja odgovara programu Excel. Zatim slijedite radne knjige, radne listove i raspon. Na primjer, da biste pristupili ćeliji A1 na određenom listu, morate odrediti put koji uključuje hijerarhiju.

Što se tiče koncepta "zbirke", onda je to skupina objekata iste klase koja u unosu izgleda kao ChartObjects. Njegovi pojedinačni elementi su također objekti.

Sljedeći pojam je svojstva. Oni su neophodna osobina svakog objekta. Na primjer, za Range, ovo je vrijednost ili formula.

Metode su naredbe koje pokazuju što treba učiniti. Prilikom pisanja koda u VBA, morate ih odvojiti od objekta za razdoblje. Na primjer, kao što će biti prikazano kasnije, vrlo često prilikom programiranja u programu Excel, upotrijebite naredbu Cells (1,1). Odaberite. To znači da morate odabrati ćeliju s koordinatama (1.1) tj. A1.

Uz to, često se koristi Selection.ClearContents. Izvođenje znači brisanje sadržaja odabrane ćelije.

Kako započeti

Prije svega, morate stvoriti datoteku i spremiti ga dodjeljivanjem naziva i odabirom "Excel radna knjiga s makro podrškom" tipa.

Tada morate ići na VB aplikaciju, samo koristite tipke "Alt" i "F11". Sljedeća:

• na traci izbornika koja se nalazi na vrhu prozora, kliknite ikonu pored ikone programa Excel;
• odaberite naredbu Mudule;
• spremite klikom na ikonu sa slikom disketu;
• napisati, recimo, skicu koda.

Izgleda ovako:

Podprogram ()

"Naš kod

Završi Sub

Napominjemo da će linija "Naš kôd" biti istaknuta u drugoj boji (zelena). Razlog je u apostrofi na početku retka, što ukazuje na to da slijedi komentar.

Sada možete napisati bilo koji kôd i stvoriti novi alat za sebe u programu VBA Excel (vidi primjere programa u nastavku). Naravno, oni koji su upoznati s osnovama Visual Basic, bit će mnogo jednostavniji. Međutim, čak i oni koji ih nemaju, ako žele, mogu se brzo naviknuti na to.

Makronaredbe u programu Excel

Za ovo ime skriveni su programi napisani u jeziku Visual Basic for Application. Dakle, programiranje u Excelu je stvaranje makronaredbi sa željenim kodom. Zahvaljujući ovoj značajci Microsoftov se procesor proračunske tablice razvija i prilagođava zahtjevima određenog korisnika. Nakon razumijevanja kako stvoriti module za pisanje makronaredbi, možete početi gledati određene primjere programa VBA Excel. Najbolje je početi s najosnovnijim kodovima.

Primjer 1

Zadatak: napisati program koji će kopirati vrijednost sadržaja jedne ćelije, a zatim pisati na drugu.

Da biste to učinili:

• otvorite karticu "Prikaz";
• idite na ikonu "Makronaredbe";
• Pritisnite "Snimanje makronaredbe";
• ispunite otvoreni obrazac.

Radi jednostavnosti, u „Macro ime” napustiti „Makros1” iu „prečac tipku” umetnuta, na primjer, hh (to znači da možete pokrenuti primjer program će biti «Ctrl + H» Blitz tim). Pritisnite Enter.

Sada kada je makronaredba već započela, kopirajte sadržaj ćelije u drugu. Vratite se na izvornu ikonu. Kliknite "Snimanje makronaredbe". Ova akcija znači kraj programa.

Sljedeća:

• ponovno idite na retku "makronaredbe";
• na popisu odaberite "Makro 1";
• kliknite "Izvrši" (isti postupak započinje pokretanjem tipkovničkog prečaca "Ctrl + hh").

Kao rezultat toga, tu je akcija koja je izvršena tijekom snimanja makronaredbe.

Ima smisla vidjeti što izgleda kôd. Da biste to učinili, vratite se u retku "Makronaredbe" i kliknite "Uredi" ili "Prijava". Kao rezultat toga, oni su u VBA okruženju. Zapravo, makro kod sam po sebi je između linija Sub Macro1 () i End Sub.

Ako je kopiranje izvršeno, na primjer, od ćelije A1 do ćelije C1, onda će jedna od linija koda izgledati kao Range ("C1"). U prijevodu izgleda kao "Range (" C1 "). Odaberite", drugim riječima, prelazi u VBA Excel, u ćeliji C1.

Aktivni dio koda završava naredbom ActiveSheet.Paste. To znači snimanje sadržaja odabrane ćelije (u ovom slučaju A1) u odabranoj ćeliji C1.

Primjer 2

VBA ciklusi omogućuju vam stvaranje različitih makronaredbi u programu Excel.

VBA ciklusi omogućuju stvaranje različitih makronaredbi. Pretpostavimo da postoji funkcija y = x + x² + 3x³ cos (x). Morate stvoriti makronaredbu kako biste dobili njegov grafikon. To možete učiniti samo pomoću VBA petlje.

Za početnu i konačnu vrijednost argumenta funkcije uzmite x1 = 0 i x2 = 10. Osim toga, morate unijeti konstantnu vrijednost za korak promjene argumenta i početne vrijednosti brojača.

Svi primjeri makronaredbi VBA Excel kreiraju se prema istom postupku kao gore. U ovom konkretnom slučaju kôd izgleda kao:

Podprogram ()

x1 = 1

x2 = 10

shag = 0,1

i = 1

Učinite dok x1 < x2 (petlja će biti izvršena dok je izraz x1 istinit < x2)

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

Stanice (i, 1). Vrijednost = x1 (vrijednost x1 je zapisana u ćeliju s koordinatama (i, 1))

Stanice (i, 2) .Value = y (vrijednost y napisan je u ćeliju s koordinatama (i, 2))

i = i + 1 (brojač radi) -

x1 = x1 + shag (argument se mijenja veličinom koraka) -

petlja

Završi Sub.

Kao rezultat pokretanja ove makronaredbe u "Excel" dobivamo dva stupca, od kojih prva sadrži vrijednosti za x, a drugu - za y.

Zatim grafikon grafikona na način koji je standard za Excel.

Primjer 3

Za implementaciju ciklusa u programu VBA Excel 2010, kao u drugim verzijama, uz već navedeni Do Do konstrukt, koristi se za.

Razmislite o programu koji će stvoriti stupac. U svakoj od njegovih ćelija napisat će se trgovi odgovarajućeg broja crte. Korištenje za konstrukt će vam omogućiti da ga vrlo brzo napišete, bez korištenja brojača.

Prvo morate izraditi makronaredbu, kao što je gore opisano. Zatim napišemo kod sebe. Smatramo da smo zainteresirani za vrijednosti za 10 stanica. Kôd izgleda ovako.

Za i = 1 do 10 Dalje

Naredba je prevedena na "ljudski" jezik, poput "Ponovite od 1 do 10 u koracima jednog".

Ako je zadatak da dobijete stupac s kvadratima, na primjer, sve neparne brojeve u rasponu od 1 do 11, onda pišemo:

Za i = 1 do 10 korak 1 Dalje.

Ovdje korak je korak. U ovom slučaju, to je jednako dvama. Prema zadanim postavkama, odsutnost te riječi u petlji znači da je korak pojedinačan.

Dobiveni rezultati trebaju biti pohranjeni u stanicama s brojem (i, 1). Tada svaki put kada pokrenete ciklus povećanjem i veličinom koraka, broj reda će se automatski povećavati. Dakle, kod će biti optimiziran.

Općenito, kôd će izgledati ovako:

Podprogram ()

Za i = 1 do 10 korak 1 (možete pisati jednostavno za i = 1 do 10)

Stanice (i, 1) .Value = i ^ 2 (tj. Vrijednost kvadrata i) napisana je u ćeliji (i, 1)

Dalje (u određenom smislu igra ulogu brojača i znači još jedan početak ciklusa)

Završi Sub.

Ako se pravilno obavljeno, uključujući snimanje i trčanje makronaredbe (vidi. Upute gore), onda se to naziva svaki put dao veličina će se dobiti stupac (u ovom slučaju sastoji od 10 stanica).

Primjer 4

U svakodnevnom životu vrlo često postoji potreba za donošenjem odluke ovisno o nekoj vrsti stanja. Ne možete ih bez njih u programu VBA Excel. Primjeri programa u kojima je izabran daljnji tijek izvođenja algoritma, a ne početno unaprijed definiran, najčešće koriste If gradnju hellip-Tada (za složene slučajeve) Ako hellip-onda hellip-END Ako.

Razmotrimo konkretan slučaj. Pretpostavimo da morate stvoriti makronaredbu za Excel, tako da je napisana ćelija s koordinatama (1,1):

1, ako je argument pozitivan -

0, ako je argument nulte-

-1 ako je argument negativan.

Stvaranje takve makronaredbe za Excel započinje na standardni način, koristeći "hot" tipke Alt i F11. Tada je napisan sljedeći kod:

Podprogram ()

x = ćelije (1, 1). Vrijednost (ova naredba dodjeljuje vrijednost sadržaja ćelije s koordinatama (1, 1))

Ako x> 0, onda ćelije (1, 1). Vrijednost = 1

Ako x = 0, onda ćelije (1, 1). Vrijednost = 0

Ako x<0 Tada Stanice (1, 1). Vrijednost = -1

Završi Sub.

Ostaje za pokretanje makronaredbe i dobivanje ispravne vrijednosti za argument u programu Excel.

VBA funkcije

Kao što ste možda primijetili, programiranje u najslavnijem Microsoftovom stolnom procesoru nije tako teško. Pogotovo ako naučite kako koristiti VBA funkcije. Ukupno, ovaj programski jezik, stvoren posebno za pisanje aplikacija u programu Excel i Word, ima oko 160 funkcija. Mogu se podijeliti u nekoliko velikih skupina. To su:

• Matematičke funkcije. Primjenjujući ih na argument, dobivate vrijednost kosinus, prirodni logaritam, cijeli dio i tako dalje.
• Financijske funkcije. Zbog njihove dostupnosti i korištenja programa u Excelu, možete dobiti učinkovite alate za računovodstvo i financijsko računovodstvo.
• Funkcije obrade reda. To uključuje Array, IsArray-LBound-UBound.
• VBA Excel funkcionira za niz. Ovo je prilično velika grupa. To uključuje, na primjer, funkcije prostora za stvaranje niza s brojem razmaka jednakim argumentu cijelog broja, ili Asc za prevod znakova u ANSI kôd. Svi su naširoko upotrebljavani i omogućuju vam da radite sa žicama u programu Excel, stvarajući aplikacije koje uvelike olakšavaju rad s tim tablicama.
• Funkcije pretvorbe vrste podataka. Na primjer, CVar vraća vrijednost izraza Ekspresija, pretvarajući je u Variant vrstu podataka.
• Funkcije rada s datumima. Oni značajno proširuju standardne značajke programa Excel. Dakle, funkcija WeekdayName vraća ime (puna ili djelomična) dana u tjednu po broju. Još je korisnija Timer. To daje broj sekundi koji su prošli od ponoći do određenog trenutka.
• Funkcije za pretvaranje numeričkog argumenta u različite brojeve sustava. Na primjer, listopad izdvaja oktalni prikaz broja.
• Funkcije oblikovanja. Najvažniji od njih je Format. Vraća vrijednost Variant vrste s izrazom oblikovanim prema uputama koje su navedene u opisu formata.
• i drugima.

Proučavanje svojstava tih funkcija i njihova primjena značajno će proširiti opseg primjene Excela.

Primjer 5

Pokušajmo riješiti složenije probleme. Na primjer:

S obzirom na papirnati dokument izvješća o stvarnoj razini troškova poduzeća. zahtijeva:

• razviti svoj dio predloška pomoću stolnog procesora "Excel";
• stvoriti VBA program koji će upitati izvorne podatke da ih popuni, izraditi potrebne izračune i popuniti ih odgovarajućim stanicama predloška.

Razmislite o jednom od rješenja.

Izrada predloška

Sve se radnje izvode na standardnom listu u programu Excel. Rezervirane slobodne ćelije za unos podataka mjesec, godina, naziv tvrtke-potrošača, iznos troškova, njihovu razinu, promet. Budući da broj poduzeća (društava) za koje se sastavlja izvješće nije fiksiran, stanice za izradu vrijednosti na temelju rezultata i ime stručnjaka prethodno nisu rezervirane. Radni list dobiva novo ime. Na primjer, "Օ tchet".

varijable

Da biste napisali program za automatsko popunjavanje predloška, ​​morate odabrati simbole. Oni će se koristiti za varijable:

• NN- broj trenutnog retka tablice;
• TP i TF - planirani i stvarni promet;
• SF i SP - stvarni i planirani iznos troškova;
• IP i IF - planirana i stvarna razina troškova.

Označavaju ista slova, ali s "prefiksom" Toog je akumulacija ukupnog za ovaj stupac. Na primjer, ItogTP se odnosi na stupac tablice pod nazivom "planirani promet".

Rješavanje problema pomoću VBA programiranja

Upotrebom napisane notacije dobivamo formule za odstupanja. Ako želite izračunati u%, imate (F - P) / P * 100, au zbroju - (F - P).

Rezultati ovih izračuna mogu se odmah dodati odgovarajućim stanicama tablice programa Excel.

Za rezultate na činjenicu i prognozu dobiveni su formula ItogP = ItogP + P i ItogF = ItogF + F.

Za odstupanja koristiti = (ItogF - ItogP) / ItogP * 100 ako se obračun obavlja se kao postotak, a ako u ukupnoj vrijednosti - (ItogF - ItogP).

Rezultati se odmah zapisuju u odgovarajuće ćelije, tako da ih nije potrebno dodijeliti varijablama.

Prije pokretanja stvorenog programa, morate spremiti radnu knjigu, na primjer, pod nazivom "Report1.xls".

Gumb Stvori izvješće izvješća treba pritisnuti samo jednom nakon unosa podataka zaglavlja. Trebali biste znati druga pravila. Konkretno, gumb "Add line" treba pritisnuti svaki put kad unesete vrijednosti za svaku aktivnost u tablici. Nakon unosa svih podataka, trebate kliknuti gumb "Završi", a zatim se prebaciti u prozor programa Excel.

Sada znate kako riješiti zadatke za Excel pomoću makronaredbi. Sposobnost koristiti VBA excel (vidi primjere programa. Gore) i možda ćete morati raditi u okruženju od najpopularnijih u ovom trenutku, tekst editor „Word”. Konkretno, snimanjem, kao što je prikazano na početku ovog članka, ili pisanja koda za stvaranje gumbe izbornika kroz koje mnogi od operacije na tekst može obaviti pritiskom tipke na dužnosti ili putem ikone „View” i kartici „Makro”.