Vadnica za Excel VBA - Kako napisati kodo v preglednico z uporabo Visual Basic

Uvod

To je vadnica o pisanju kode v Excelove preglednice z uporabo Visual Basic for Applications (VBA).

Excel je eden najbolj priljubljenih Microsoftovih izdelkov. Leta 2016 je izvršni direktor Microsofta dejal: "Razmislite o svetu brez Excela. To je zame nemogoče." No, morda svet ne more razmišljati brez Excela.

  • Leta 1996 je bilo več kot 30 milijonov uporabnikov Microsoft Excela (vir).
  • Danes obstaja približno 750 milijonov uporabnikov Microsoft Excela. To je malo več kot prebivalstvo Evrope in 25-krat več uporabnikov kot leta 1996.

Smo ena velika srečna družina!

V tej vadnici boste izvedeli več o VBA in kako napisati kodo v Excelovi preglednici z uporabo Visual Basic.

Pogoji

Za razumevanje te vadnice ne potrebujete predhodnih izkušenj s programiranjem. Vendar boste potrebovali:

  • Osnovno do vmesno poznavanje Microsoft Excela
  • Če želite slediti primerom VBA v tem članku, boste potrebovali dostop do Microsoft Excela, po možnosti najnovejše različice (2019), vendar bosta Excel 2016 in Excel 2013 delovala povsem v redu.
  • Pripravljenost poskusiti nove stvari

Učni cilji

V tem članku boste izvedeli:

  1. Kaj je VBA
  2. Zakaj bi uporabljali VBA
  3. Kako se v Excelu nastaviti za pisanje VBA
  4. Kako rešiti nekaj resničnih težav z VBA

Pomembni koncepti

Tu je nekaj pomembnih konceptov, ki jih morate poznati, da boste v celoti razumeli to vadnico.

Predmeti : Excel je objektno usmerjen, kar pomeni, da je vse predmet - Excelovo okno, delovni zvezek, list, grafikon, celica. VBA uporabnikom omogoča manipulacijo in izvajanje dejanj s predmeti v Excelu.

Če nimate izkušenj z objektno usmerjenim programiranjem in je to povsem nov koncept, si vzemite trenutek, da se to pogrezne!

Postopki : postopek je del kode VBA, napisan v urejevalniku Visual Basic, ki opravi nalogo. Včasih se temu reče tudi makro (več o makrih spodaj). Obstajata dve vrsti postopkov:

  • Podprogrami: skupina stavkov VBA, ki izvaja eno ali več dejanj
  • Funkcije: skupina stavkov VBA, ki izvede eno ali več dejanj in vrne eno ali več vrednosti

Opomba: funkcije lahko delujejo znotraj podprogramov. Boste videli kasneje.

Makroni : če ste se za učenje naprednejših Excelovih funkcij porabili za čas, ste verjetno naleteli na koncept "makra". Uporabniki Excela lahko posnamejo makre, sestavljene iz uporabniških ukazov / pritiskov na tipke / klike, in jih predvajajo z bliskovito hitrostjo za izvajanje ponavljajočih se nalog. Posneti makri ustvarijo kodo VBA, ki jo lahko nato preučite. Pravzaprav je zelo zabavno posneti preprost makro in nato pogledati kodo VBA.

Upoštevajte, da je včasih morda lažje in hitreje posneti makro in ne ročno kodirati postopka VBA.

Na primer, morda delate v projektnem vodenju. Enkrat na teden morate surovo izvoženo poročilo iz sistema vodenja projektov spremeniti v lepo oblikovano, čisto poročilo za vodstvo. Imena preveč proračunskih projektov morate oblikovati v krepkem rdečem besedilu. Spremembe oblikovanja lahko zapišete kot makro in jih zaženete, kadar koli želite spremeniti.

Kaj je VBA?

Visual Basic for Applications je programski jezik, ki ga je razvil Microsoft. Vsak program v zbirki Microsoft Office je v kompletu z jezikom VBA brez dodatnih stroškov. VBA uporabnikom Microsoft Office omogoča, da ustvarijo majhne programe, ki delujejo znotraj programov Microsoft Office.

VBA si predstavljajte kot peč za pico v restavraciji. Excel je restavracija. Kuhinja je opremljena s standardnimi komercialnimi napravami, kot so veliki hladilniki, štedilniki in običajne ole pečice - to so vse standardne funkcije programa Excel.

Kaj pa, če želite pripraviti pico na drva? Tega ni mogoče storiti v običajni komercialni pečici. VBA je peč za pico.

Njam.

Zakaj uporabljati VBA v Excelu?

Ker je pica na drva najboljša!

Ampak resno.

Veliko ljudi preživi veliko časa v Excelu kot del svojih služb. Tudi čas v Excelu se premika drugače. Odvisno od okoliščin se lahko 10 minut v Excelu počuti kot večnost, če ne zmorete storiti, kar potrebujete, ali pa lahko 10 ur mine zelo hitro, če gre vse odlično. Takrat bi se morali vprašati, zakaj zaboga porabim 10 ur v Excelu?

Včasih so ti dnevi neizogibni. Če pa v Excelu 8-10 ur vsak dan opravljate ponavljajoča se opravila, ponavljate veliko istih postopkov, poskušate očistiti druge uporabnike datoteke ali celo posodobite druge datoteke po spremembi datoteke Excel, postopek VBA je lahko samo rešitev za vas.

Razmislite o uporabi VBA, če:

  • Avtomatizirajte ponavljajoče se naloge
  • Ustvarite preproste načine, kako lahko uporabniki komunicirajo z vašimi preglednicami
  • Manipulirajte z velikimi količinami podatkov

Priprava za pisanje VBA v Excelu

Zavihek za razvijalce

Če želite napisati VBA, boste morali na trak dodati zavihek Razvijalec, da boste videli tak trak.

Če želite na trak dodati zavihek Developer:

  1. Na zavihku Datoteka pojdite na Možnosti> Prilagodi trak.
  2. V razdelku Prilagodi trak in pod Glavnimi zavihki potrdite polje Razvijalec.

Ko prikažete zavihek, ostane zavihek razvijalec viden, razen če počistite potrditveno polje ali morate Excel znova namestiti. Za več informacij glejte Microsoftovo dokumentacijo za pomoč.

Urejevalnik VBA

Pomaknite se do zavihka za razvijalce in kliknite gumb Visual Basic. Pojavi se novo okno - to je urejevalnik Visual Basic. Za namene te vadnice morate biti seznanjeni s podoknom Project Explorer in podoknom Properties Properties.

Primeri Excel VBA

Najprej ustvarimo datoteko, v kateri se bomo igrali.

  1. Odprite novo datoteko Excel
  2. Shranite ga kot delovni zvezek z omogočeno makro (. Xlsm)
  3. Izberite jeziček Razvijalec
  4. Odprite urejevalnik VBA

Poglejmo si nekaj preprostih primerov, s katerimi boste v Visual Basic napisali kodo v preglednico.

Primer # 1: Prikažite sporočilo, ko uporabniki odprejo Excelov delovni zvezek

V urejevalniku VBA izberite Vstavi -> Nov modul

V okno modula napišite to kodo (ne prilepite!):

Sub Auto_Open ()

MsgBox ("Dobrodošli v delovnem zvezku XYZ.")

Končaj pod

Shranite, zaprite delovni zvezek in znova odprite delovni zvezek. To pogovorno okno bi se moralo prikazati.

Ta da!

Kako to počne?

Glede na vaše poznavanje programiranja boste morda ugibali. Ni posebej zapleteno, se pa dogaja kar nekaj:

  • Sub (okrajšava za “Subroutine): od začetka si zapomnite“ skupina izjav VBA, ki izvaja eno ali več dejanj. ”
  • Auto_Open: to je posebna podprogram. Ko se odpre datoteka Excel, samodejno zažene kodo - to je dogodek, ki sproži postopek. Auto_Open se zažene šele, ko se delovni zvezek odpre ročno; ne bo deloval, če se delovni zvezek odpre s kodo iz drugega delovnega zvezka (to bo storil Workbook_Open, preberite več o razliki med njima).
  • Dostop do podprograma je privzeto javen. To pomeni, da lahko kateri koli drug modul uporablja to podprogram. Vsi primeri v tej vadnici bodo javne podprograme. Po potrebi lahko podprograme razglasite za zasebne. To bo v nekaterih primerih potrebno. Preberite več o modifikatorjih dostopa do podprograma.
  • msgBox: to je funkcija - skupina stavkov VBA, ki izvede eno ali več dejanj in vrne vrednost. Vrnjena vrednost je sporočilo "Dobrodošli v delovnem zvezku XYZ."

Skratka, to je preprosta podprogram, ki vsebuje funkcijo.

Kdaj lahko to uporabim?

Morda imate zelo pomembno datoteko, do katere dostopate redko (recimo enkrat na četrtletje), vendar jo samodejno dnevno posodabljate po drugem postopku VBA. Ko do njega dostopa veliko ljudi v več oddelkih po vsem podjetju.

  • Težava: Večino časa, ko uporabniki dostopajo do datoteke, so zmedeni glede namena te datoteke (zakaj obstaja), kako pogosto se posodablja, kdo jo vzdržuje in kako naj z njo sodelujejo. Pri novih najemnikih je vedno veliko vprašanj, ta vprašanja pa morate vedno znova postavljati.
  • Rešitev: ustvarite uporabniško sporočilo, ki vsebuje jedrnat odgovor na vsako od teh pogostih vprašanj.

Primeri iz resničnega sveta

  • S funkcijo MsgBox prikažite sporočilo, kadar pride do kakršnega koli dogodka: uporabnik zapre Excel-ov delovni zvezek, uporabnik natisne, nov list se doda v delovni zvezek itd.
  • S funkcijo MsgBox prikažite sporočilo, ko mora uporabnik izpolniti pogoj, preden zapre Excel-ov delovni zvezek
  • Za pridobitev informacij od uporabnika uporabite funkcijo InputBox

2. primer: Dovolite uporabniku, da izvede drug postopek

V urejevalniku VBA izberite Vstavi -> Nov modul

V okno modula napišite to kodo (ne prilepite!):

Sub UserReportQuery ()

Zatemni uporabniški vnos tako dolgo

Zatemni odgovor kot celo število

UserInput = vbDaNe

Odgovor = MsgBox ("Obdelati poročilo XYZ?", Uporabniški vnos)

Če je odgovor = vbDa, potem obdelajte poročilo

Končaj pod

Poročilo o podprocesu ()

MsgBox ("Hvala za obdelavo poročila XYZ.")

Končaj pod

Shranite in se vrnite na jeziček Razvijalec v Excelu in izberite možnost »Gumb«. Kliknite celico in gumbu dodelite makro UserReportQuery.

Zdaj kliknite gumb. To sporočilo mora prikazati:

Kliknite "da" ali pritisnite Enter.

Še enkrat, tada!

Upoštevajte, da je lahko sekundarna podprogram, ProcessReport, karkoli . V primeru št. 3 bom prikazal več možnosti. Najprej pa ...

Kako to počne?

Ta primer temelji na prejšnjem primeru in vsebuje kar nekaj novih elementov. Pojdimo na novo:

  • Dim UserInput As Long: Dim je kratica za »dimension« in omogoča prijavo imen spremenljivk. V tem primeru je UserInput ime spremenljivke, Long pa podatkovni tip. V preprosti angleščini ta vrstica pomeni "Tu je spremenljivka, imenovana" UserInput ", in to je spremenljivka Long."
  • Zatemni odgovor kot celo število: razglasi drugo spremenljivko, imenovano »Odgovor«, s podatkovnim tipom Celo število. Več o vrstah podatkov preberite tukaj.
  • UserInput = vbYesNo: spremenljivki dodeli vrednost. V tem primeru vbYesNo, ki prikaže gumba Da in Ne. Obstaja veliko vrst gumbov, več o tem preberite tukaj.
  • Answer = MsgBox (“Obdelati poročilo XYZ?”, UserInput): dodeli vrednost spremenljivke Answer funkciji MsgBox in spremenljivki UserInput. Da, spremenljivka znotraj spremenljivke.
  • Če je odgovor = vbDa, potem ProcessReport: to je "If statement", pogojni stavek, ki nam omogoča, da rečemo, če je x res, potem naredite y. V tem primeru, če je uporabnik izbral »Da«, izvedite podprogram ProcessReport.

Kdaj lahko to uporabim?

To bi lahko uporabili na veliko, veliko načinov. Vrednost in vsestranskost te funkcionalnosti je bolj opredeljena s tem, kaj počne sekundarna podprogram.

Na primer, morda imate datoteko, ki se uporablja za ustvarjanje 3 različnih tedenskih poročil. Ta poročila so oblikovana na dramatično različne načine.

  • Težava: vsakič, ko je treba ustvariti eno od teh poročil, uporabnik odpre datoteko in spremeni oblikovanje in grafikone; tako naprej in tako naprej. Datoteka se obsežno ureja vsaj trikrat na teden in vsakič, ko jo uredite, traja vsaj 30 minut.
  • Rešitev: ustvarite 1 gumb na vrsto poročila, ki samodejno preoblikuje potrebne komponente poročil in ustvari potrebne diagrame.

Primeri iz resničnega sveta

  • Ustvarite pogovorno okno za uporabnika, ki samodejno vnese določene podatke na več listov
  • S funkcijo InputBox pridobite informacije od uporabnika, ki se nato naselijo na več listih

3. primer: dodajte številke v obseg z zanko naprej

Zanke so zelo koristne, če morate izvajati ponavljajoča se opravila za določen obseg vrednosti - polja ali obsege celic. V preprosti angleščini zanka pravi "za vsak x, naredi y."

V urejevalniku VBA izberite Vstavi -> Nov modul

V okno modula napišite to kodo (ne prilepite!):

Sub LoopExample ()

Zatemni X kot celo število

Za X = 1 do 100

Obseg ("A" in X) .Vrednost = X

Naslednji X

Končaj pod

Shranite in se vrnite na jeziček Razvijalec v Excelu in izberite gumb Makri. Zaženite makro LoopExample.

To bi se moralo zgoditi:

Itd, do 100. vrstice.

Kako to počne?

  • Dim X As Integer: razglasi spremenljivko X kot podatkovni tip Integer.
  • Za X = 1 do 100: to je začetek zanke For. Preprosto povedano, zanki pove, naj se ponavlja, dokler X = 100. X je števec . Zanka se bo izvajala do X = 100, izvedla se je še zadnjič in nato ustavila.
  • Obseg ("A" in X). Vrednost = X: to določa obseg zanke in kaj naj bo v tem obsegu. Ker je X = 1 na začetku, bo prva celica A1, takrat bo zanka postavila X v to celico.
  • Next X: to pove, da se zanka znova zažene

Kdaj lahko to uporabim?

Zanka For-Next je ena najmočnejših funkcionalnosti VBA; obstaja veliko možnih primerov uporabe. To je bolj zapleten primer, ki bi zahteval več plasti logike, vendar v zankah For-Next sporoča svet možnosti.

Morda imate seznam vseh izdelkov, prodanih v vaši pekarni v stolpcu A, vrsto izdelka v stolpcu B (pecivo, krofi ali kolački), stroške sestavin v stolpcu C in tržne povprečne stroške posamezne vrste izdelka v še en list.

Ugotoviti morate, kakšna naj bo maloprodajna cena posameznega izdelka. Mislite, da bi to morali biti stroški sestavin plus 20%, po možnosti pa tudi 1,2% pod tržnim povprečjem. Zanka For-Next vam omogoča, da izvedete to vrsto izračuna.

Primeri iz resničnega sveta

  • Uporabite zanko z ugnezdenim stavkom if, da dodate posebne vrednosti v ločeno matriko le, če izpolnjujejo določene pogoje
  • Opravite matematične izračune za vsako vrednost v območju, npr. Izračunajte dodatne stroške in jih dodajte vrednosti
  • Prelistajte vsak znak v nizu in izvlecite vse številke
  • Naključno izberite število vrednosti iz polja

Zaključek

Zdaj, ko smo govorili o pici in kolačkih in oh-ja, kako napisati kodo VBA v Excelove preglednice, opravimo preverjanje učenja. Preverite, ali lahko odgovorite na ta vprašanja.

  • Kaj je VBA?
  • Kako se lahko nastavim za začetek uporabe VBA v Excelu?
  • Zakaj in kdaj bi uporabljali VBA?
  • Katere težave bi lahko rešil z VBA?

Če imate dobro idejo, kako bi lahko odgovorili na ta vprašanja, je bilo to uspešno.

Ne glede na to, ali ste občasni uporabnik ali izkušen uporabnik, upam, da je ta vadnica zagotovila koristne informacije o tem, kaj je mogoče doseči z le malo kode v preglednicah Excel.

Veselo kodiranje!

Učni viri

  • Excel VBA programiranje za telebane, John Walkenbach
  • Začnite z VBA, Microsoftova dokumentacija
  • Učenje VBA v Excelu, Lynda

Nekaj ​​o meni

Sem Chloe Tucker, umetnica in razvijalec v Portlandu v Oregonu. Kot nekdanji vzgojitelj nenehno iščem presečišče učenja in poučevanja ali tehnologije in umetnosti. Obrnite se na mene na Twitterju @_chloetucker in si oglejte mojo spletno stran na chloe.dev.