Uvod

Ovo je vodič o pisanju koda u proračunske tablice programa Excel pomoću Visual Basic-a za aplikacije (VBA).

Excel je jedan od najpopularnijih Microsoftovih proizvoda. 2016. izvršni direktor Microsofta rekao je "Razmislite o svijetu bez Excela. To mi je jednostavno nemoguće." Pa, možda svijet ne može razmišljati bez Excela.

Godine 1996. bilo je preko 30 milijuna korisnika programa Microsoft Excel (izvor).
Danas se procjenjuje da 750 milijuna korisnika programa Microsoft Excel ima. To je malo više od stanovništva Europe i 25 puta više korisnika nego što ih je bilo 1996.

Mi smo jedna velika sretna obitelj!

U ovom vodiču naučit ćete o VBA i kako napisati kod u Excel proračunsku tablicu pomoću Visual Basica.

Preduvjeti

Za razumijevanje ovog vodiča nije vam potrebno prethodno iskustvo u programiranju. Međutim, trebat će vam:

Osnovno do srednje poznavanje Microsoft Excela
Ako želite slijediti primjere VBA-a u ovom članku, trebat će vam pristup Microsoft Excelu, po mogućnosti najnovijoj verziji (2019), ali Excel 2016 i Excel 2013 sasvim će dobro funkcionirati.
Spremnost za isprobavanje novih stvari

ciljevi učenja

Tijekom ovog članka naučit ćete:

Što je VBA
Zašto biste koristili VBA
Kako se postaviti u Excelu za pisanje VBA
Kako riješiti neke stvarne probleme s VBA-om

Važni pojmovi

Evo nekoliko važnih koncepata koje biste trebali znati kako biste u potpunosti razumjeli ovaj vodič.

Objekti : Excel je objektno orijentiran, što znači da je sve objekt - Excelov prozor, radna knjiga, list, grafikon, ćelija. VBA omogućuje korisnicima manipulaciju i izvršavanje radnji s objektima u Excelu.

Ako nemate iskustva s objektno orijentiranim programiranjem, a ovo je potpuno novi koncept, odvojite sekundu i pustite da to potone!

Postupci : postupak je dio VBA koda, napisan u Visual Basic Editoru, koji izvršava zadatak. Ponekad se to naziva i makronaredbom (više o makronaredbama u nastavku). Postoje dvije vrste postupaka:

Podprogrami: skupina VBA izraza koja izvodi jednu ili više radnji
Funkcije: grupa VBA izraza koja izvodi jednu ili više radnji i vraća jednu ili više vrijednosti

Napomena: možete imati funkcije koje djeluju unutar potprograma. Vidjet ćete kasnije.

Makronaredbe : Ako ste neko vrijeme proveli učeći naprednije funkcije programa Excel, vjerojatno ste se susreli s konceptom "makronaredbe". Korisnici Excela mogu snimati makronaredbe koje se sastoje od korisničkih naredbi / pritiska tipki / klikova i reproducirati ih brzinom munje za postizanje ponavljajućih zadataka. Snimljeni makronaredbe generiraju VBA kôd koji zatim možete pregledati. Zapravo je prilično zabavno snimiti jednostavnu makronaredbu, a zatim pogledati VBA kod.

Imajte na umu da je ponekad možda lakše i brže snimiti makronaredbu, a ne ručno kodirati VBA postupak.

Na primjer, možda radite u upravljanju projektima. Jednom tjedno morate pretvoriti sirovo izvezeno izvješće iz vašeg sustava upravljanja projektima u lijepo oblikovano, čisto izvješće za vodstvo. Imena pretjeranih proračunskih projekata morate oblikovati podebljanim crvenim tekstom. Promjene oblikovanja možete zabilježiti kao makronaredbu i pokrenuti ih kad god trebate izvršiti promjenu.

Što je VBA?

Visual Basic za aplikacije programski je jezik koji je razvio Microsoft. Svaki softverski program u paketu Microsoft Office u paketu je s jezikom VBA bez dodatnih troškova. VBA omogućuje korisnicima Microsoft Officea da stvaraju male programe koji rade u okviru softverskih programa Microsoft Office.

Zamislite VBA kao pećnicu za pizzu u restoranu. Excel je restoran. Kuhinja dolazi sa standardnim komercijalnim aparatima, poput velikih hladnjaka, štednjaka i običnih pećnica - to su sve standardne značajke programa Excel.

Ali što ako želite napraviti pizzu na drva? To ne možete učiniti u standardnoj komercijalnoj pećnici za pečenje. VBA je peć za pizzu.

Njam.

Zašto koristiti VBA u Excelu?

Jer pizza na drva je najbolja!

Ali ozbiljno.

Mnogi ljudi provode puno vremena u Excelu kao dio svog posla. Vrijeme u Excelu također se kreće drugačije. Ovisno o okolnostima, 10 minuta u programu Excel može se osjećati kao vječnost ako niste u stanju učiniti ono što vam treba ili 10 sati može proći vrlo brzo ako sve ide izvrsno. Tada biste se trebali zapitati, zašto, pobogu, provodim 10 sati u Excelu?

Ponekad su ti dani neizbježni. Ali ako svakodnevno u programu Excel provodite 8-10 sati radeći ponavljajuće zadatke, ponavljajući puno istih procesa, pokušavajući očistiti druge korisnike datoteke ili čak ažurirajući druge datoteke nakon što su u datoteku Excel unesene promjene, VBA postupak bi mogao biti rješenje za vas.

Trebali biste razmisliti o korištenju VBA ako trebate:

Automatizirajte ponavljajuće zadatke
Stvorite jednostavne načine za interakciju korisnika s vašim proračunskim tablicama
Manipulirajte velikim količinama podataka

Postavljanje postavljanja za pisanje VBA u programu Excel

Kartica programera

Da biste napisali VBA, trebat ćete dodati karticu Developer na vrpcu, tako da ćete vidjeti vrpcu poput ove.

Da biste dodali karticu Developer na vrpcu:

Na kartici Datoteka idite na Opcije> Prilagodi vrpcu.
Pod Prilagodi vrpcu i pod Glavne kartice potvrdite okvir Razvojni programer.

Nakon što prikažete karticu, kartica Developer ostaje vidljiva, osim ako ne poništite potvrdni okvir ili ne morate ponovo instalirati Excel. Za više informacija pogledajte Microsoftovu dokumentaciju za pomoć.

VBA urednik

Idite na karticu Developer i kliknite gumb Visual Basic. Otvorit će se novi prozor - ovo je Visual Basic Editor. Za potrebe ovog vodiča samo trebate biti upoznati s oknom Project Explorer i oknom Svojstva svojstva.

Primjeri za Excel VBA

Prvo stvorimo datoteku u kojoj ćemo se poigrati.

Otvorite novu Excel datoteku
Spremite ga kao radnu knjigu s omogućenim makronaredbama (. Xlsm)
Odaberite karticu Developer
Otvorite VBA Editor

Krenimo s nekoliko jednostavnih primjera kako biste napisali kôd u proračunsku tablicu pomoću Visual Basica.

Primjer # 1: Prikažite poruku kada korisnici otvore Excel radnu knjigu

U VBA uređivaču odaberite Umetni -> Novi modul

Napišite ovaj kod u prozor Modula (ne lijepite!):

Sub Auto_Open ()

MsgBox ("Dobro došli u XYZ radnu knjigu.")

Kraj pod

Spremite, zatvorite radnu knjigu i ponovno otvorite radnu knjigu. Ovaj bi se dijaloški okvir trebao prikazati.

Ta da!

Kako to radi?

Ovisno o vašem poznavanju programiranja, možda imate neke pretpostavke. Nije osobito složeno, ali događa se poprilično toga:

Sub (kratica za „Subroutine): zapamtite od početka,„ grupa VBA izjava koja izvodi jednu ili više radnji. “
Auto_Open: ovo je specifična potprogram. Automatski pokreće vaš kôd kada se otvori datoteka Excel - ovo je događaj koji pokreće postupak. Auto_Open pokrenut će se samo kad se radna knjiga otvori ručno; neće se pokrenuti ako se radna knjiga otvori putem koda iz druge radne knjige (Workbook_Open će to učiniti, saznajte više o razlici između te dvije).
Prema zadanim postavkama pristup potprogramu je javan. To znači da bilo koji drugi modul može koristiti ovu potprogram. Svi primjeri u ovom vodiču bit će javne potprograme. Ako je potrebno, potprograme možete proglasiti privatnim. To će možda biti potrebno u nekim situacijama. Saznajte više o modifikatorima pristupa potprogramima.
msgBox: ovo je funkcija - skupina VBA izraza koja izvodi jednu ili više radnji i vraća vrijednost. Vraćena vrijednost je poruka "Dobro došli u XYZ radnu knjigu."

Ukratko, ovo je jednostavna potprogram koji sadrži funkciju.

Kada bih ovo mogao koristiti?

Možda imate vrlo važnu datoteku kojoj se pristupa rijetko (recimo, jednom u tromjesečju), ali se automatski ažurira svakodnevno drugim VBA postupkom. Kad mu pristupe, pristupaju mnogi ljudi iz više odjela, širom tvrtke.

Problem: Većinu vremena kada korisnici pristupe datoteci, zbunjeni su svrhom ove datoteke (zašto postoji), kako se često ažurira, tko je održava i kako trebaju s njom komunicirati. Novi zaposlenici uvijek imaju gomilu pitanja, a vi ih morate postavljati uvijek iznova i iznova.
Rješenje: izradite korisničku poruku koja sadrži jezgrovit odgovor na svako od ovih često odgovaranih pitanja.

Primjeri iz stvarnog svijeta

Upotrijebite funkciju MsgBox za prikaz poruke kada se dogodi bilo koji događaj: korisnik zatvara Excel radnu knjigu, korisnik ispisuje, novi list se dodaje u radnu knjigu itd.
Upotrijebite funkciju MsgBox za prikaz poruke kada korisnik treba ispuniti uvjet prije zatvaranja Excel radne knjige
Koristite funkciju InputBox za dobivanje informacija od korisnika

Primjer # 2: Dopustite korisniku da izvrši drugi postupak

U VBA uređivaču odaberite Umetni -> Novi modul

Napišite ovaj kod u prozor Modula (ne lijepite!):

Sub UserReportQuery ()

Prigušeni korisnički unos toliko dugo

Prigušeni odgovor kao cjelovit

UserInput = vbDaNe

Odgovor = MsgBox ("Obraditi XYZ izvješće?", Korisnički unos)

Ako je odgovor = vbDa, onda obradite izvještaj

Kraj pod

Izvještaj o potprocesu ()

MsgBox ("Hvala na obradi izvještaja XYZ.")

Kraj pod

Spremite i vratite se na karticu Developer u programu Excel i odaberite opciju "Button". Kliknite ćeliju i gumbu dodijelite makronaredbu UserReportQuery.

Sada kliknite gumb. Ova poruka treba prikazati:

Kliknite "da" ili pritisnite Enter.

Još jednom, tada!

Napominjemo da sekundarna potprogram, ProcessReport, može biti bilo što . Pokazat ću više mogućnosti u primjeru # 3. Ali prvo...

Kako to radi?

Ovaj se primjer nadovezuje na prethodni primjer i ima dosta novih elemenata. Krenimo s novim stvarima:

Dim UserInput As Long: Dim je skraćenica od “dimension” i omogućuje vam deklariranje imena varijabli. U ovom je slučaju UserInput naziv varijable, a Long vrsta podataka. Jednostavno na engleskom, ovaj redak znači "Evo varijable koja se naziva" UserInput ", a riječ je o dugoj varijabli tipa."
Priguši odgovor kao cjelovit: proglašava drugu varijablu koja se naziva "Odgovor" s tipom podataka Integer. Ovdje saznajte više o vrstama podataka.
UserInput = vbYesNo: dodjeljuje vrijednost varijabli. U ovom slučaju, vbYesNo, koji prikazuje gumbe Yes i No. Postoji mnogo vrsta gumba, saznajte više ovdje.
Odgovor = MsgBox ("Obraditi XYZ izvješće?", UserInput): dodjeljuje vrijednost varijable Answer funkciji MsgBox i varijabli UserInput. Da, varijabla unutar varijable.
Ako je odgovor = vbDa, onda ProcessReport: ovo je “If naredba”, uvjetna izjava, koja nam omogućuje da kažemo je li x istina, tada učinite y. U ovom slučaju, ako je korisnik odabrao "Da", izvršite potprogram ProcessReport.

Kada bih ovo mogao koristiti?

To bi se moglo koristiti na mnogo, mnogo načina. Vrijednost i svestranost ove funkcionalnosti više je definirana onim što radi sekundarna potprogram.

Na primjer, možda imate datoteku koja se koristi za generiranje 3 različita tjedna izvješća. Ova su izvješća oblikovana na dramatično različite načine.

Problem: Svaki put kada treba generirati jedno od ovih izvješća, korisnik otvori datoteku i promijeni oblikovanje i grafikone; i tako dalje. Ova se datoteka intenzivno uređuje najmanje 3 puta tjedno i potrebno je najmanje 30 minuta svaki put kad se uređuje.
Rješenje: stvorite 1 gumb po vrsti izvješća, koji automatski reformira potrebne komponente izvješća i generira potrebne grafikone.

Primjeri iz stvarnog svijeta

Stvorite dijaloški okvir za korisnika za automatsko popunjavanje određenih podataka na više listova
Upotrijebite funkciju InputBox da biste od korisnika dobili informacije koje se zatim popunjavaju na više listova

Primjer # 3: Dodajte brojeve u raspon pomoću petlje za sljedeći krug

Jer su petlje vrlo korisne ako trebate izvoditi ponavljajuće zadatke na određenom rasponu vrijednosti - nizovima ili rasponima ćelija. Na jednostavnom engleskom, petlja kaže "za svaki x, učini y".

U VBA uređivaču odaberite Umetni -> Novi modul

Napišite ovaj kod u prozor Modula (ne lijepite!):

Primjer petlje ()

Zatamni X kao cjelovito

Za X = 1 do 100

Raspon ("A" i X) .Vrijednost = X

Sljedeći X

Kraj pod

Spremite i vratite se na karticu Developer u programu Excel i odaberite gumb Macros. Pokrenite makronaredbu LoopExample.

To bi se trebalo dogoditi:

Itd., Do 100. reda.

Kako to radi?

Dim X As Integer: proglašava varijablu X tipom podataka Integer.
Za X = 1 do 100: ovo je početak petlje For. Jednostavno rečeno, govori petlji da se ponavlja sve dok X = 100. X je brojač . Petlja će se izvršavati sve do X = 100, izvršiti posljednji put, a zatim zaustaviti.
Raspon ("A" & X) .Vrijednost = X: ovo proglašava opseg petlje i što staviti u taj raspon. Budući da je X = 1 u početku, prva ćelija će biti A1, a tada će petlja staviti X u tu ćeliju.
Sljedeći X: ovo govori petlji da se ponovno pokrene

Kada bih ovo mogao koristiti?

Petlja For-Next jedna je od najsnažnijih funkcionalnosti VBA-a; postoje brojni potencijalni slučajevi uporabe. Ovo je složeniji primjer koji zahtijeva više slojeva logike, ali komunicira svijet mogućnosti u petljama For-Next.

Možda imate popis svih proizvoda koji se prodaju u vašoj pekari u stupcu A, vrstu proizvoda u stupcu B (kolači, krafne ili kiflice), troškove sastojaka u stupcu C i prosječni tržišni trošak svake vrste proizvoda u još jedan list.

Morate shvatiti koja bi trebala biti maloprodajna cijena svakog proizvoda. Mislite da bi to trebali biti troškovi sastojaka plus 20%, ali ako je moguće i 1,2% ispod tržišnog prosjeka. Petlja For-Next omogućila bi vam ovu vrstu izračuna.

Primjeri iz stvarnog svijeta

Upotrijebite petlju s ugniježđenim izrazom if da biste dodali određene vrijednosti u zasebni niz samo ako ispunjavaju određene uvjete
Izvršite matematičke izračune za svaku vrijednost u rasponu, npr. Izračunajte dodatne naboje i dodajte ih vrijednosti
Prelistajte svaki znak u nizu i izvucite sve brojeve
Nasumičnim odabirom broja vrijednosti iz niza

Zaključak

Sad kad smo razgovarali o pizzi i kiflicama i oh-da, kako napisati VBA kod u Excel proračunskim tablicama, napravimo provjeru učenja. Pogledajte možete li odgovoriti na ova pitanja.

Što je VBA?
Kako se mogu postaviti za početak korištenja VBA u programu Excel?
Zašto i kada biste koristili VBA?
Koje bih probleme mogao riješiti s VBA?

Ako imate dobru ideju kako biste mogli odgovoriti na ova pitanja, onda je ovo uspjelo.

Bez obzira jeste li povremeni ili napredni korisnik, nadam se da je ovaj vodič pružio korisne informacije o tome što se u vašim proračunskim tablicama programa Excel može postići samo s malo koda.

Sretno kodiranje!

Resursi za učenje

Excel VBA programiranje za lutke, John Walkenbach
Započnite s VBA, Microsoftova dokumentacija
Učenje VBA u Excelu, Lynda

Malo o meni

Ja sam Chloe Tucker, umjetnica i programerica u Portlandu u državi Oregon. Kao bivši odgojitelj, neprestano tražim sjecište učenja i poučavanja, odnosno tehnologije i umjetnosti. Obratite mi se na Twitteru @_chloetucker i pogledajte moju web stranicu na chloe.dev.