VBA Excel: primeri programov. Makri v Excelu

Le malo ljudi ve, da je bila prva različica priljubljenega izdelka Microsoft Excel leta 1985. Od takrat je prešel več sprememb in je na voljo med milijoni uporabnikov po vsem svetu. Vendar, samo veliko dela z malo zmožnosti te preglednice in sploh ne vem, kako bi lahko olajšala življenje sposobnostjo Excel programiranja.

Kaj je VBA?

Programiranje v Excelu poteka preko programskega jezika Visual Basic for Application, ki je prvotno vgrajen v najbolj znani Microsoftov procesor.

Za svoje zasluge strokovnjaki pripisujejo primerjalno enostavnost razvoja. Kot kaže praksa, lahko uporabniki VBA obvladujejo tudi tiste, ki nimajo poklicnih programskih veščin. Posebnost VBA je izvajanje scenarija v okolju pisarniških aplikacij.

Pomanjkljivost programa so težave, povezane z združljivostjo različnih različic. So posledica dejstva, da se koda programa VBA nanaša na funkcionalnost, ki je prisotna v novi različici izdelka, vendar ni v stari. Tudi pomanjkljivosti vključujejo preveliko odprtost kod za spremembe s strani nepooblaščene osebe. Kljub temu Microsoft Office, kot tudi IBM Lotus Symphony, omogočajo uporabniku, da šifrira začetno kodo in nastavi geslo za ogled.

Objekti, zbirke, lastnosti in metode

S temi koncepti morate razumeti, kdo bo delal v okolju VBA. Najprej je treba razumeti, kaj je predmet. V Excelu je to list, knjiga, celica in obseg. Ti predmeti imajo posebno hierarhijo, npr. Poslušajte drug drugega.

Glavna je aplikacija, ki ustreza programu Excel. Nato sledite Delovni zvezki, Delovni listi in tudi Območje. Na primer, če želite dostopati do celice A1 na določenem listu, morate določiti pot, ki vključuje hierarhijo.

Kar se tiče pojma "zbiranje", je to skupina predmetov istega razreda, ki v vnosu izgleda kot ChartObjects. Njeni posamezni elementi so tudi predmeti.

Naslednji pojem je lastnosti. So nujna značilnost vsakega predmeta. Na primer, za obseg, to je vrednost ali formula.

Metode so ukazi, ki kažejo, kaj je treba storiti. Pri pisanju kode v VBA jih morate ločiti od predmeta za obdobje. Na primer, kot bo prikazano pozneje, zelo pogosto pri programiranju v Excelu uporabite ukaz Cells (1,1). Izberite. To pomeni, da morate izbrati celico s koordinatami (1.1) tj. A1.

S tem se pogosto uporablja Selection.ClearContents. Izvedba pomeni odstranitev vsebine izbrane celice.

Kako začeti

Najprej morate ustvariti datoteko in jo shraniti tako, da dodate ime in izberete "Delovni zvezek Excel z makro podporo".

Potem morate iti v aplikacijo VB, samo uporabite tipke "Alt" in "F11". Naprej:

• v menijski vrstici, ki se nahaja na vrhu okna, kliknite ikono poleg ikone Excel;
• izberite ukaz Mudule;
• shranite s klikom na ikono s sliko disketa;
• pišite, recimo, skico kode.

Izgleda takole:

Pod program ()

"Naša koda

End Sub

Upoštevajte, da bo vrstica »Naša koda« označena v drugačni barvi (zelena). Vzrok je v apostrofu na začetku vrstice, kar pomeni, da sledi komentar.

Zdaj lahko napišete katero koli kodo in ustvarite novo orodje zase v VBA Excel (glej primere spodaj navedenih programov). Seveda bodo tisti, ki poznajo osnove Visual Basic, veliko lažji. Toda tudi tisti, ki jih nimajo, če hočejo, se lahko hitro navadijo na to.

Makri v Excelu

Za to ime so skriti programi, napisani v jeziku Visual Basic for Application. Tako je programiranje v Excelu ustvarjanje makrov z želeno kodo. Zahvaljujoč tej funkciji se razvija procesor Microsoftovih preglednic, ki se prilagaja zahtevam določenega uporabnika. Po razumevanju ustvarjanja modulov za pisanje makrov lahko začnete pogledati specifične primere programov VBA Excel. Najbolje je, da začnete z najbolj osnovnimi kodami.

Primer 1

Naloga: napišite program, ki bo kopiral vrednost vsebine ene celice in nato pisal drugemu.

Za to:

• odprite kartico »Pogled«;
• pojdite na ikono "Makri";
• Pritisnite "Record makro";
• izpolnite odprti obrazec.

Zaradi enostavnosti, v "Makro Ime" pusti "Makros1" in v "ključ Bližnjica" je vstavljena, na primer, hh (to pomeni, da lahko vodijo primer Program bo "Ctrl + h» blitz ekipa). Pritisnite Enter.

Zdaj, ko se je makro snemanje že začelo, kopirajte vsebino celice v drugo. Vrnite se na prvotno ikono. Kliknite na "Record Macro". Ta ukrep pomeni konec programa.

Naprej:

• znova pojdite na vrstico "Makri";
• na seznamu izberite "Makro 1";
• kliknite "Izvedi" (isto dejanje se začne z zagonom bližnjične tipke "Ctrl + hh").

Zato je med snemanjem makra izvedeno dejanje.

Smiselno je videti, kako izgleda koda. Če želite to narediti, se vrnite v vrstico »Makri« in kliknite »Uredi« ali »Prijava«. Zato so v okolju VBA. Pravzaprav je makro koda samo med vrsticami Sub Macro1 () in End Sub.

Če je bilo kopiranje izvedeno, na primer iz celice A1 v celico C1, bo ena od vrstic kode izgledala kot Range ("C1"). Izberite. V prevodu je videti kot "Range (" C1 "). Izberite", z drugimi besedami, preklopi na VBA Excel, v celico C1.

Aktivni del kode preneha z ukazom ActiveSheet.Paste. To pomeni snemanje vsebine izbrane celice (v tem primeru A1) v izbrani celici C1.

Primer 2

Cikli VBA vam pomagajo ustvariti različne makre v Excelu.

Cikli VBA vam pomagajo ustvariti različne makre. Recimo, da obstaja funkcija y = x + x² + 3x³ - cos (x). Morate ustvariti makro, da dobite svoj graf. To lahko storite le z uporabo zank VBA.

Za začetno in končno vrednost argumenta funkcije x1 = 0 in x2 = 10. Poleg tega morate vnesti konstantno vrednost za korak spreminjanja argumenta in začetno vrednost števca.

Vsi primeri makrojev VBA Excel so ustvarjeni po enakem postopku kot zgoraj. V tem primeru koda izgleda:

Podprogramm ()

x1 = 1

x2 = 10

shag = 0,1

i = 1

Medtem ko x1 < x2 (zanka se izvede, medtem ko je izraz x1 resničen < x2)

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

Celice (i, 1) .Value = x1 (vrednost x1 je zapisana v celico z koordinatami (i, 1))

Celice (i, 2) .Value = y (vrednost y je zapisana v celico z koordinatami (i, 2))

i = i + 1 (števec deluje) -

x1 = x1 + shag (argument se spremeni za velikost koraka) -

Loop

End Sub.

Kot rezultat zagona tega makra v "Excelu" dobimo dva stolpca, od katerih prvi vsebuje vrednosti za x, v drugem pa za y.

Nato grafikon grafikona na način, ki je standarden za Excel.

Primer 3

Za izvajanje ciklov v VBA Excel 2010, kot v drugih različicah, skupaj z že navedenim Do Do construct, Za uporablja.

Razmislite o programu, ki bo ustvaril stolpec. V vsaki od njegovih celic bodo zapisani kvadrati ustrezne številke vrstice. Uporaba Za konstrukt vam bo omogočila pisanje zelo kratko, brez uporabe števca.

Najprej morate ustvariti makro, kot je opisano zgoraj. Nato napišemo kodo. Menimo, da nas zanimajo vrednosti za 10 celic. Koda izgleda takole.

Za i = 1 do 10 Naprej

Ukaz je preveden v "človeški" jezik, kot je "Ponovite od 1 do 10 v korakih enega".

Če je naloga, da dobimo stolpec s kvadrati, na primer vse neparne številke v razponu od 1 do 11, potem pišemo:

Za i = 1 do 10 korak 1 Naprej.

Tu korak je korak. V tem primeru je enako dvema. Privzeto je, da odsotnost te besede v zanki pomeni, da je korak enojna.

Dobljene rezultate je treba shraniti v celicah s številko (i, 1). Nato vsakič, ko zaženete cikel s povečanjem i za velikost koraka, se bo tudi število vrstic samodejno povečalo. Tako bo koda optimizirana.

Na splošno bo koda izgledala tako:

Pod program ()

Za i = 1 do 10 korak 1 (lahko enostavno napišete Za i = 1 do 10)

Celice (i, 1) .Value = i ^ 2 (to je vrednost kvadrata i) je zapisana v celici (i, 1)

Naprej (v določenem smislu igra vlogo števca in pomeni en cikel začetka)

End Sub.

Če je to narejeno pravilno, vključno s snemanja in voznimi makrov (gl. Navodila zgoraj), nato pa se imenuje vsakič, ko zaradi velikosti bo dosežena stolpec (v tem primeru, ki sestoji iz 10 celic).

Primer 4

V vsakdanjem življenju je zelo pogosto treba sprejeti odločitev, odvisno od neke vrste stanja. Brez njih v VBA Excelu ne morete storiti. Primeri programov, pri katerih je izbran nadaljnji potek izvajanja algoritma, namesto prvotno predhodno določenega, najpogosteje uporabljajo konstrukcijo Če hellip-Potem (za zapletene primere) Če hellip-Potem hellip-END Če.

Poglejmo konkreten primer. Recimo, da morate ustvariti makro za Excel, tako da je celica z koordinatami (1,1) napisana:

1, če je argument pozitiven,

0, če je argument ničelne točke,

-1, če je argument negativen.

Ustvarjanje takšnega makra za Excel se začne na standardni način, z uporabo "vročih" tipk Alt in F11. Nato je napisana naslednja koda:

Pod program ()

x = celice (1, 1) .Value (ta ukaz dodeljuje vrednost vsebine celice z koordinatami (1, 1))

Če x> 0 Potem celice (1, 1) .Value = 1

Če x = 0 Potem celice (1, 1). Vrednost = 0

Če x<0 Potem celice (1, 1) .Value = -1

End Sub.

Še vedno je treba zagnati makro in dobiti pravilno vrednost za argument v Excelu.

Funkcije VBA

Kot ste morda opazili, programiranje v najbolj znanih procesorjih namiznega računalnika Microsofta ni tako težavno. Še posebej, če se naučite uporabljati funkcije VBA. Skupaj je ta programski jezik, ustvarjen posebej za pisanje aplikacij v Excelu in Wordu, približno 160 funkcij. Lahko jih razdelimo na več velikih skupin. To so:

• Matematične funkcije. Če jih uporabite za argument, dobite vrednost kosinusa, naravnega logaritma, celotnega dela in tako naprej.
• Finančne funkcije. Zaradi svoje razpoložljivosti in uporabe programov v Excelu lahko dobite učinkovita orodja za računovodstvo in finančno računovodstvo.
• Funkcije obdelave polja. Ti vključujejo Array, IsArray-LBound-UBound.
• Funkcije VBA Excel za niz. To je precej velika skupina. Vključuje na primer funkcije Space za ustvarjanje niza s številnimi presledki, ki so enakovredni celoštevilnemu argumentu, ali Asc za prevajanje znakov v kodo ANSI. Vsi ti se pogosto uporabljajo in vam omogočajo delo z nizi v Excelu in ustvarjate aplikacije, ki znatno olajšajo delo s temi tabelami.
• Funkcije pretvorbe podatkov. Na primer, CVar vrne vrednost argumenta Expression in jo pretvori v vrsto podatkov Varianta.
• Funkcije dela z datumi. Znatno razširjajo standardne funkcije Excel. Torej, funkcija WeekdayName vrne ime (polno ali delno) dneva v tednu po njeni številki. Še bolj uporaben je Timer. Določa število sekund, ki so minile od polnoči do določenega trenutka dneva.
• Funkcije za pretvorbo numeričnega argumenta v različne sisteme številk. Okt, na primer, izda oktalsko predstavo števila.
• Funkcije oblikovanja. Najpomembnejši med njimi je Format. Vrne vrednost tipa Variant z izrazom, formatiranim v skladu z navodili, ki so podana v opisu oblike.
• in drugi.

Študija lastnosti teh funkcij in njihova uporaba bo znatno razširila obseg uporabe Excel-a.

Primer 5

Poskusimo rešiti bolj zapletene probleme. Na primer:

Glede na papirni dokument poročila dejanske ravni stroškov podjetja. Zahtevano:

• razviti svoj del predloge s pomočjo procesorja "Excel";
• ustvarite program VBA, ki bo poizvedoval izvorne podatke, da izpolnite, naredite potrebne izračune in jih izpolnite s primernimi celicami predlog.

Razmislite o eni od rešitev.

Ustvarjanje predloge

Vsi ukrepi se izvajajo na standardnem listu v Excelu. Rezervirane proste celice za vnos podatkov na mesec, leto, ime podjetja-potrošnika, višino stroškov, njihovo raven, promet. Ker je število podjetij (podjetja), v zvezi s katero se poročilo ni zabeležena, celice, da bi na podlagi vrednot in strokovno ime ni rezerviran vnaprej. Delovni list dobi novo ime. Na primer, "Zapri".

Spremenljivke

Za pisanje programa za samodejno izpolnjevanje predloge morate izbrati simbole. Uporabili se bodo za spremenljivke:

• NN- številka trenutne vrstice tabele;
• TP in TF - načrtovani in dejanski promet;
• SF in SP - dejanski in načrtovani znesek stroškov;
• IP in IF - načrtovana in dejanska raven stroškov.

Označite enake črke, vendar z "predpono" Itog kopičimo skupno število za ta stolpec. Na primer, ItogTP se nanaša na stolpec tabele z naslovom "načrtovani promet".

Reševanje težav z uporabo programa VBA

S predstavljenim zapisom dobimo formule za odstopanja. Če želite izračunati v%, imate (F - P) / P * 100 in v seštevku - (F - P).

Rezultate teh izračunov je mogoče najbolje dodati ustreznim celicam tabele Excel.

Rezultate o dejstvu in napovedi dobimo s formulami ItogP = ItogP + P in ItogF = ItogF + F.

Za odstopanja uporabljajo = (ItogF - ItogP) / ItogP * 100, če se izvede izračun v odstotkih, in če skupna vrednost - (ItogF - ItogP).

Rezultati so takoj zapisani v ustrezne celice, zato jih ni treba dodeliti spremenljivkam.

Pred zagonom ustvarjenega programa morate shraniti delovni zvezek, na primer imenovan "Report1.xls".

Gumb »Ustvari poročilo tabele« je treba pritisniti samo enkrat po vnosu podatkov o glavi. Moral bi vedeti druga pravila. Zlasti je treba pritisniti gumb "Dodaj vrstico" vsakič, ko vnesete vrednosti za vsako dejavnost v tabeli. Ko vnesete vse podatke, morate klikniti gumb »Dokončaj« in nato preklopiti v okno Excel.

Zdaj veste, kako rešiti naloge za Excel z uporabo makrov. Možnost uporabe VBA excel (glej vzorčne programe. Zgoraj) in boste morda morali delati v okolju, najbolj priljubljen v trenutku, urejevalnik besedil "Word." Še posebej, s snemanjem, kot je prikazano na začetku tega članka, ali s pisno kodo za ustvarjanje gumbov menija, preko katerih se veliko dejavnosti na besedilo mogoče storiti s pritiskom tipke na dolžnosti ali prek "View" ikono in zavihek "Makri".